Skip to main content

Repository Setup

The PMP repository is a PostgreSQL database that stores all metrics collected by the agents — live ASH samples, AWR snapshots, trends history and platform reference data. This must be set up before deploying the backend.

warning

The PMP repository must run on a dedicated PostgreSQL instance, separate from any PostgreSQL servers you intend to monitor with PMP4PG.

Step 1 — Install PostgreSQL​

Install PostgreSQL 16 or later on your repository host.

Rocky Linux / RHEL / AlmaLinux​

# Install the PostgreSQL 16 repo
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module
dnf -qy module disable postgresql

# Install PostgreSQL 16
dnf install -y postgresql16-server postgresql16

# Initialize the database cluster
/usr/pgsql-16/bin/postgresql-16-setup initdb

# Enable and start PostgreSQL
systemctl enable --now postgresql-16

Ubuntu / Debian​

# Install prerequisites
apt-get install -y curl ca-certificates

# Add PostgreSQL APT repository
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
  | gpg --dearmor -o /usr/share/keyrings/postgresql.gpg
echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list

apt-get update
apt-get install -y postgresql-16

systemctl enable --now postgresql

Step 2 — Install TimescaleDB​

TimescaleDB is required for efficient time-series storage of ASH samples and history tables.

Rocky Linux / RHEL​

# Add TimescaleDB repository
dnf install -y https://packagecloud.io/timescale/timescaledb/packages/el/8/timescaledb-2-loader-postgresql-16-2.14.2-0.el8.x86_64.rpm

# Install TimescaleDB for PostgreSQL 16
dnf install -y timescaledb-2-postgresql-16

# Run the TimescaleDB tuning tool (recommended)
timescaledb-tune --quiet --yes

Ubuntu / Debian​

# Add TimescaleDB repository
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ \
  $(lsb_release -cs) main" > /etc/apt/sources.list.d/timescaledb.list
curl -fsSL https://packagecloud.io/timescale/timescaledb/gpgkey \
  | gpg --dearmor -o /usr/share/keyrings/timescaledb.gpg

apt-get update
apt-get install -y timescaledb-2-postgresql-16

# Tune PostgreSQL for TimescaleDB
timescaledb-tune --quiet --yes

Enable TimescaleDB in postgresql.conf​

# Rocky Linux / RHEL
echo "shared_preload_libraries = 'timescaledb'" \
  >> /var/lib/pgsql/16/data/postgresql.conf
systemctl restart postgresql-16

# Ubuntu / Debian
echo "shared_preload_libraries = 'timescaledb'" \
  >> /etc/postgresql/16/main/postgresql.conf
systemctl restart postgresql

Step 3 — Create the PMP Database and User​

Run the provided 01-create-user-and-database.sql script as the postgres superuser. This script creates the pmp4pg application user, the metadata database and the pmp schema.

psql -U postgres -h localhost -f 01-create-user-and-database.sql
info

This script is provided in the sql/ folder of the distribution archive (pmp4pg-community-v1.0.0.tar.gz). It includes CREATE SCHEMA pmp; — no manual schema creation is needed.

Step 4 — Initialize the Schema​

Run the provided 02-create-tables.sql script, connected as the pmp4pg user on the metadata database, to create all tables, indexes and initial reference data in the pmp schema.

psql -U pmp4pg -h localhost -d metadata -f 02-create-tables.sql

The script creates the following table categories:

CategoryPurpose
ReferencePlatform entities — organization, site, host, server, agent, user, project
LiveReal-time metrics — ASH samples, OS metrics, locks, stat deltas
HistoryLong-term aggregates — AWR snapshots, trends, history tables
AWRSnapshot registry and job execution tracking
ConfigurationSystem config, monitoring templates, scheduled jobs

Step 5 — Verify the Installation​

# Connect and list tables in the pmp schema
psql -U pmp4pg -h localhost -d metadata -c "\dt pmp.*"

# Check TimescaleDB hypertables
psql -U pmp4pg -h localhost -d metadata \
  -c "SELECT hypertable_name FROM timescaledb_information.hypertables;"

Expected output for \dt pmp.* should list all tables in the pmp schema.

{screenshot: repository-schema-tables}

postgresql.conf Recommendations​

For optimal performance of the PMP repository under monitoring load:

# Memory
shared_buffers = 512MB          # ~25% of RAM
work_mem = 16MB
maintenance_work_mem = 128MB

# TimescaleDB
shared_preload_libraries = 'timescaledb'

# WAL
wal_level = replica             # or minimal if no replication needed
max_wal_size = 1GB

# Autovacuum — important for live tables with high insert rate
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.005

Next Steps​