PostgreSQL Performance Monitoring Tutorial: Complete Setup with pg_stat_monitor and Grafana on Linux VPS in 2026

Understanding PostgreSQL Performance Monitoring Requirements

PostgreSQL performance monitoring becomes essential when your database handles production workloads. Unlike basic logging, comprehensive monitoring tracks real-time metrics, query patterns, and resource usage across your entire database cluster.

This tutorial walks through setting up pg_stat_monitor with Grafana dashboards on Ubuntu 24.04 LTS. You'll implement automated alerting and historical trend analysis for your VPS database environment.

The pg_stat_monitor extension provides enhanced query tracking beyond PostgreSQL's built-in pg_stat_statements. It captures execution plans, wait events, and histogram data that standard monitoring tools miss.

Installing pg_stat_monitor Extension on Ubuntu VPS

Start by installing the required packages and dependencies:

sudo apt update
sudo apt install postgresql-14-pg-stat-monitor postgresql-contrib

Add the extension to your PostgreSQL configuration. Edit /etc/postgresql/14/main/postgresql.conf:

shared_preload_libraries = 'pg_stat_monitor'
pg_stat_monitor.pgsm_max = 1000
pg_stat_monitor.pgsm_query_max_len = 8192
pg_stat_monitor.pgsm_enable_query_plan = on
pg_stat_monitor.pgsm_track = 'all'

Restart PostgreSQL to load the extension:

sudo systemctl restart postgresql
sudo systemctl status postgresql

Connect to your database and create the extension:

sudo -u postgres psql
CREATE EXTENSION pg_stat_monitor;
SELECT pg_stat_monitor_reset();

Verify the installation by checking available monitoring views:

\d pg_stat_monitor
SELECT count(*) FROM pg_stat_monitor;

Setting Up Prometheus for Metrics Collection

Install Prometheus to collect and store PostgreSQL metrics. Download the latest version:

wget https://github.com/prometheus/prometheus/releases/download/v2.45.0/prometheus-2.45.0.linux-amd64.tar.gz
tar xzf prometheus-2.45.0.linux-amd64.tar.gz
sudo mv prometheus-2.45.0.linux-amd64 /opt/prometheus
sudo useradd --no-create-home --shell /bin/false prometheus
sudo chown -R prometheus:prometheus /opt/prometheus

Create the configuration directory and main config file:

sudo mkdir -p /etc/prometheus /var/lib/prometheus
sudo chown prometheus:prometheus /var/lib/prometheus

Configure Prometheus in /etc/prometheus/prometheus.yml:

global:
  scrape_interval: 15s
  evaluation_interval: 15s

scrape_configs:
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']

  - job_name: 'postgres'
    static_configs:
      - targets: ['localhost:9187']
    scrape_interval: 10s

Create a systemd service for Prometheus at /etc/systemd/system/prometheus.service:

[Unit]
Description=Prometheus Server
Wants=network-online.target
After=network-online.target

[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/opt/prometheus/prometheus \
  --config.file=/etc/prometheus/prometheus.yml \
  --storage.tsdb.path=/var/lib/prometheus/ \
  --web.console.templates=/opt/prometheus/consoles \
  --web.console.libraries=/opt/prometheus/console_libraries \
  --web.listen-address=0.0.0.0:9090

[Install]
WantedBy=multi-user.target

Start and enable the Prometheus service:

sudo systemctl daemon-reload
sudo systemctl start prometheus
sudo systemctl enable prometheus

Configuring postgres_exporter for Data Export

Install the PostgreSQL exporter to bridge your database metrics with Prometheus:

wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.12.1/postgres_exporter-0.12.1.linux-amd64.tar.gz
tar xzf postgres_exporter-0.12.1.linux-amd64.tar.gz
sudo mv postgres_exporter-0.12.1.linux-amd64/postgres_exporter /usr/local/bin/
sudo useradd --no-create-home --shell /bin/false postgres_exporter

Create a monitoring user in PostgreSQL with appropriate permissions:

sudo -u postgres psql
CREATE USER postgres_exporter WITH PASSWORD 'secure_monitoring_password';
GRANT pg_monitor TO postgres_exporter;
GRANT SELECT ON pg_stat_database TO postgres_exporter;

Configure the exporter environment variables in /etc/default/postgres_exporter:

DATA_SOURCE_NAME="postgresql://postgres_exporter:secure_monitoring_password@localhost:5432/postgres?sslmode=disable"
PG_EXPORTER_EXTEND_QUERY_PATH="/etc/postgres_exporter/queries.yml"

Create custom queries configuration at /etc/postgres_exporter/queries.yml:

pg_stat_monitor:
  query: "SELECT datname, calls, mean_time, max_time, rows, shared_blks_hit, shared_blks_read, query FROM pg_stat_monitor WHERE calls > 10"
  master: true
  metrics:
    - datname:
        usage: "LABEL"
        description: "Database name"
    - calls:
        usage: "COUNTER"
        description: "Number of query executions"
    - mean_time:
        usage: "GAUGE"
        description: "Mean execution time in milliseconds"
    - max_time:
        usage: "GAUGE"
        description: "Maximum execution time in milliseconds"

pg_database_size:
  query: "SELECT datname, pg_database_size(datname) as size_bytes FROM pg_database"
  master: true
  metrics:
    - datname:
        usage: "LABEL"
        description: "Database name"
    - size_bytes:
        usage: "GAUGE"
        description: "Database size in bytes"

Create the systemd service for postgres_exporter:

sudo tee /etc/systemd/system/postgres_exporter.service > /dev/null <

Start the exporter and verify it's collecting metrics:

sudo systemctl daemon-reload
sudo systemctl start postgres_exporter
sudo systemctl enable postgres_exporter
curl http://localhost:9187/metrics | grep pg_stat_monitor

Installing and Configuring Grafana Dashboards

Install Grafana for visualization. Add the repository and install:

wget -q -O - https://packages.grafana.com/gpg.key | sudo apt-key add -
echo "deb https://packages.grafana.com/oss/deb stable main" | sudo tee /etc/apt/sources.list.d/grafana.list
sudo apt update
sudo apt install grafana

Start Grafana and access the web interface:

sudo systemctl start grafana-server
sudo systemctl enable grafana-server

Access Grafana at http://your-vps-ip:3000 with default credentials (admin/admin). Change the password immediately.

Add Prometheus as a data source in Grafana:

1. Navigate to Configuration → Data Sources
2. Click "Add data source" and select Prometheus
3. Set URL to http://localhost:9090
4. Click "Save & Test" to verify the connection

Import the PostgreSQL monitoring dashboard. Create a new dashboard or import ID 9628 for comprehensive PostgreSQL metrics.

For production environments, consider HostMyCode Database Hosting with pre-configured monitoring and automatic failover capabilities.

Creating Custom Performance Monitoring Views

Set up custom monitoring views for specific performance patterns. Create a monitoring schema:

CREATE SCHEMA monitoring;

CREATE VIEW monitoring.slow_queries AS
SELECT 
  query,
  calls,
  mean_time,
  total_time,
  rows,
  100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_monitor 
WHERE mean_time > 1000
ORDER BY mean_time DESC;

CREATE VIEW monitoring.connection_stats AS
SELECT 
  datname,
  numbackends,
  xact_commit,
  xact_rollback,
  blks_read,
  blks_hit,
  100.0 * blks_hit / nullif(blks_hit + blks_read, 0) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

Create a stored procedure for automated monitoring reports:

CREATE OR REPLACE FUNCTION monitoring.generate_performance_report()
RETURNS TABLE(
  metric_name TEXT,
  metric_value NUMERIC,
  status TEXT
) AS $$
BEGIN
  RETURN QUERY
  WITH metrics AS (
    SELECT 'active_connections' as name, count(*)::numeric as value
    FROM pg_stat_activity WHERE state = 'active'
    UNION ALL
    SELECT 'cache_hit_ratio', avg(100.0 * blks_hit / nullif(blks_hit + blks_read, 0))
    FROM pg_stat_database
    UNION ALL
    SELECT 'slow_query_count', count(*)::numeric
    FROM pg_stat_monitor WHERE mean_time > 1000
  )
  SELECT 
    m.name,
    m.value,
    CASE 
      WHEN m.name = 'cache_hit_ratio' AND m.value < 95 THEN 'WARNING'
      WHEN m.name = 'slow_query_count' AND m.value > 50 THEN 'CRITICAL'
      ELSE 'OK'
    END
  FROM metrics m;
END;
$$ LANGUAGE plpgsql;

Implementing Automated Alerting Rules

Configure Alertmanager for automated notifications. Install and configure:

wget https://github.com/prometheus/alertmanager/releases/download/v0.25.0/alertmanager-0.25.0.linux-amd64.tar.gz
tar xzf alertmanager-0.25.0.linux-amd64.tar.gz
sudo mv alertmanager-0.25.0.linux-amd64 /opt/alertmanager
sudo useradd --no-create-home --shell /bin/false alertmanager
sudo chown -R alertmanager:alertmanager /opt/alertmanager

Create alerting rules in /etc/prometheus/postgresql_alerts.yml:

groups:
- name: postgresql
  rules:
  - alert: PostgreSQLDown
    expr: pg_up == 0
    for: 5m
    labels:
      severity: critical
    annotations:
      summary: "PostgreSQL is down"
      description: "PostgreSQL instance {{ $labels.instance }} is down"

  - alert: PostgreSQLSlowQueries
    expr: rate(pg_stat_monitor_mean_time[5m]) > 5000
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "Slow queries detected"
      description: "Average query time is {{ $value }}ms on {{ $labels.instance }}"

  - alert: PostgreSQLConnectionsHigh
    expr: pg_stat_database_numbackends > 80
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "High connection count"
      description: "{{ $labels.datname }} has {{ $value }} connections"

  - alert: PostgreSQLCacheHitRatioLow
    expr: pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read) < 0.95
    for: 10m
    labels:
      severity: warning
    annotations:
      summary: "Low cache hit ratio"
      description: "Cache hit ratio is {{ $value | humanizePercentage }} for {{ $labels.datname }}"

Update the Prometheus configuration to include the rules file:

rule_files:
  - "/etc/prometheus/postgresql_alerts.yml"

alerting:
  alertmanagers:
    - static_configs:
        - targets:
          - localhost:9093

Database Performance Analysis and Optimization

Use monitoring data to identify performance bottlenecks. Create analysis queries:

-- Top queries by total time
SELECT 
  substr(query, 1, 80) as query_snippet,
  calls,
  total_time,
  mean_time,
  stddev_time
FROM pg_stat_monitor 
ORDER BY total_time DESC 
LIMIT 10;

-- Lock analysis
SELECT 
  query,
  shared_blks_hit + shared_blks_read as total_blocks,
  shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) * 100 as hit_ratio,
  local_blks_hit + local_blks_read as local_blocks
FROM pg_stat_monitor
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC;

Monitor table and index usage patterns:

-- Unused indexes
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan < 10
ORDER BY pg_relation_size(indexrelid) DESC;

-- Table access patterns
SELECT 
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_tup_ins + n_tup_upd + n_tup_del as total_writes
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

For detailed PostgreSQL performance analysis techniques, refer to our PostgreSQL Log Analysis Tutorial that covers advanced troubleshooting methods.

Setting Up Automated Performance Reports

Create automated reporting scripts for daily performance summaries. Set up a monitoring script:

#!/bin/bash
# /opt/scripts/postgres_daily_report.sh

REPORT_DATE=$(date +%Y-%m-%d)
REPORT_FILE="/var/log/postgres_reports/daily_report_${REPORT_DATE}.txt"

mkdir -p /var/log/postgres_reports

echo "PostgreSQL Daily Performance Report - $REPORT_DATE" > $REPORT_FILE
echo "================================================" >> $REPORT_FILE
echo "" >> $REPORT_FILE

# Database sizes
echo "Database Sizes:" >> $REPORT_FILE
sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) as size FROM pg_database ORDER BY pg_database_size(datname) DESC;" >> $REPORT_FILE
echo "" >> $REPORT_FILE

# Connection stats
echo "Connection Statistics:" >> $REPORT_FILE
sudo -u postgres psql -c "SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database WHERE datname NOT IN ('template0', 'template1');" >> $REPORT_FILE
echo "" >> $REPORT_FILE

# Top slow queries
echo "Top 5 Slowest Queries (by mean time):" >> $REPORT_FILE
sudo -u postgres psql -c "SELECT substr(query, 1, 100) as query, calls, round(mean_time::numeric, 2) as avg_ms FROM pg_stat_monitor ORDER BY mean_time DESC LIMIT 5;" >> $REPORT_FILE
echo "" >> $REPORT_FILE

# Cache hit ratios
echo "Cache Hit Ratios:" >> $REPORT_FILE
sudo -u postgres psql -c "SELECT datname, round((100.0 * blks_hit / nullif(blks_hit + blks_read, 0))::numeric, 2) as hit_ratio FROM pg_stat_database WHERE datname NOT IN ('template0', 'template1');" >> $REPORT_FILE

echo "Report saved to: $REPORT_FILE"

Make the script executable and schedule it:

sudo chmod +x /opt/scripts/postgres_daily_report.sh
sudo crontab -e
# Add this line:
0 6 * * * /opt/scripts/postgres_daily_report.sh

Troubleshooting Common Monitoring Issues

Address frequent monitoring setup problems:

pg_stat_monitor not collecting data:

# Check if extension is loaded
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_monitor';

# Verify configuration
SHOW shared_preload_libraries;

# Reset statistics if needed
SELECT pg_stat_monitor_reset();

Prometheus connection failures:

# Test exporter connectivity
curl http://localhost:9187/metrics

# Check PostgreSQL user permissions
sudo -u postgres psql -c "\du postgres_exporter"

# Verify firewall settings
sudo ufw status
sudo ufw allow 9090/tcp
sudo ufw allow 9187/tcp

Grafana dashboard issues:

# Check data source connectivity
curl -H "Authorization: Bearer YOUR_API_KEY" http://localhost:3000/api/datasources

# Verify query syntax in Grafana query inspector
# Use Prometheus query browser at http://localhost:9090

Monitor disk space usage for metrics retention:

# Check Prometheus storage usage
du -sh /var/lib/prometheus

# Configure retention policies in prometheus.yml
--storage.tsdb.retention.time=30d
--storage.tsdb.retention.size=50GB

If you need guidance on connection troubleshooting, check our Database Connection Troubleshooting Guide for comprehensive error resolution strategies.

Frequently Asked Questions

How often should PostgreSQL performance metrics be collected?

Collect metrics every 10-15 seconds for real-time monitoring. For historical analysis, 5-minute intervals provide sufficient granularity without overwhelming storage. Critical production systems may need 5-second intervals during peak hours.

What are the essential PostgreSQL metrics to monitor?

Monitor connection count, cache hit ratio, query execution times, lock waits, database size growth, and replication lag. Track pg_stat_monitor for query patterns and pg_stat_database for overall database health. Set alerts for cache hit ratios below 95% and connection counts above 80% of max_connections.

How much storage does PostgreSQL monitoring data require?

Expect 50-100MB per day for basic metrics from a moderately active database. High-traffic systems with detailed query tracking can generate 500MB-1GB daily. Configure Prometheus retention between 30-90 days based on your analysis needs and available storage.

Can pg_stat_monitor impact database performance?

pg_stat_monitor adds minimal overhead, typically 1-3% CPU usage. The impact increases with query volume and enabled features like execution plan tracking. Monitor the pg_stat_monitor view size and reset statistics weekly for busy systems to maintain optimal performance.

How do I monitor PostgreSQL replication lag effectively?

Use pg_stat_replication on the primary server and pg_last_wal_receive_lsn() on replicas. Set up alerts for lag exceeding 10MB or 30 seconds. Configure monitoring queries to track both byte and time-based lag measurements for comprehensive replication health visibility.