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 14 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​

# Add timescaledb to shared_preload_libraries
echo "shared_preload_libraries = 'timescaledb'" >> /etc/postgresql/16/main/postgresql.conf

# Restart PostgreSQL
systemctl restart postgresql

Step 3 — Create the PMP Database and User​

-- Connect as postgres superuser
sudo -u postgres psql

-- Create the PMP repository database
CREATE DATABASE pmp4pg_repo
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

-- Create the PMP application user
CREATE USER pmp4pg WITH PASSWORD 'your_secure_password_here';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE pmp4pg_repo TO pmp4pg;

-- Connect to the new database
\c pmp4pg_repo

-- Grant schema privileges
GRANT ALL ON SCHEMA public TO pmp4pg;

\q

Step 4 — Initialize the Schema​

Run the provided SQL initialization script to create all tables, indexes, hypertables and initial reference data.

# Run the schema initialization script
psql -U pmp4pg -h localhost -d pmp4pg_repo -f pmp4pg-schema-v1.0.0.sql

The script creates the following table categories:

CategoryTablesPurpose
Referencesite, host, server, agent, pmp_userPlatform entities
Liveash_samples, pg_stat_statements_delta, pg_stat_database_delta, os_metrics, pg_locksReal-time metrics
Historyash_history, pg_stat_statements_history, pg_stat_database_history, pg_stat_wal_history, pg_database_size_history, ...AWR snapshots
AWRawr_snapshotSnapshot registry
AlertsalertPlatform alerts

{screenshot: repository-schema-tables}

Step 5 — Verify the Installation​

# Connect and list tables
psql -U pmp4pg -h localhost -d pmp4pg_repo -c "\dt"

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

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​