Monitor PostgreSQL performance with Prometheus and Grafana dashboards

Update system packages

Start by updating your package manager to ensure you get the latest versions of monitoring tools.

sudo apt update && sudo apt upgrade -y

sudo dnf update -y

Install PostgreSQL exporter

Download and install the PostgreSQL exporter that will collect database metrics for Prometheus. This exporter connects to PostgreSQL and exposes metrics on port 9187.

cd /tmp
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
tar -xzf postgres_exporter-0.15.0.linux-amd64.tar.gz
sudo cp postgres_exporter-0.15.0.linux-amd64/postgres_exporter /usr/local/bin/
sudo chmod +x /usr/local/bin/postgres_exporter

Create PostgreSQL monitoring user

Create a dedicated PostgreSQL user for the exporter with minimal required permissions. This follows the principle of least privilege for security.

sudo -u postgres psql -c "CREATE USER postgres_exporter WITH PASSWORD 'secure_monitoring_password';"
sudo -u postgres psql -c "GRANT CONNECT ON DATABASE postgres TO postgres_exporter;"
sudo -u postgres psql -c "GRANT pg_monitor TO postgres_exporter;"

Configure PostgreSQL exporter environment

Create the environment file that contains the database connection string. The exporter uses this to connect to PostgreSQL and collect metrics.

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

Create systemd service for PostgreSQL exporter

Set up the exporter as a systemd service to ensure it starts automatically and can be managed like other system services.

[Unit]
Description=PostgreSQL Exporter
Wants=network-online.target
After=network-online.target
Requires=postgresql.service
After=postgresql.service

[Service]
Type=simple
User=postgres
Group=postgres
EnvironmentFile=/etc/default/postgres_exporter
ExecStart=/usr/local/bin/postgres_exporter
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

Create extended queries configuration

Configure custom queries for detailed PostgreSQL monitoring. This file defines additional metrics beyond the default ones.

sudo mkdir -p /etc/postgres_exporter
sudo chown postgres:postgres /etc/postgres_exporter

pg_database_size:
  query: "SELECT pg_database.datname, pg_database_size(pg_database.datname) as size FROM pg_database"
  master: true
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of the database"
    - size:
        usage: "GAUGE"
        description: "Disk space used by the database"

pg_slow_queries:
  query: "SELECT schemaname, tablename, attname, n_distinct, correlation FROM pg_stats"
  master: true
  metrics:
    - schemaname:
        usage: "LABEL"
        description: "Schema name"
    - tablename:
        usage: "LABEL"
        description: "Table name"
    - attname:
        usage: "LABEL"
        description: "Attribute name"
    - n_distinct:
        usage: "GAUGE"
        description: "Number of distinct values"
    - correlation:
        usage: "GAUGE"
        description: "Statistical correlation"

pg_table_stats:
  query: "SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables"
  master: true
  metrics:
    - schemaname:
        usage: "LABEL"
        description: "Schema name"
    - tablename:
        usage: "LABEL"
        description: "Table name"
    - n_tup_ins:
        usage: "COUNTER"
        description: "Number of tuples inserted"
    - n_tup_upd:
        usage: "COUNTER"
        description: "Number of tuples updated"
    - n_tup_del:
        usage: "COUNTER"
        description: "Number of tuples deleted"

Set correct file permissions

Ensure the PostgreSQL user can read the configuration files while maintaining security. The postgres user needs read access to the environment file and queries configuration.

sudo chown postgres:postgres /etc/default/postgres_exporter
sudo chmod 640 /etc/default/postgres_exporter
sudo chown postgres:postgres /etc/postgres_exporter/queries.yaml
sudo chmod 644 /etc/postgres_exporter/queries.yaml

Start and enable PostgreSQL exporter

Enable the service to start automatically on boot and start it immediately. This ensures continuous monitoring of your PostgreSQL instance.

sudo systemctl daemon-reload
sudo systemctl enable --now postgres_exporter
sudo systemctl status postgres_exporter

Configure Prometheus to scrape PostgreSQL metrics

Add the PostgreSQL exporter as a target in your Prometheus configuration. This tells Prometheus where to collect PostgreSQL metrics from.

global:
  scrape_interval: 15s

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

  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']
    scrape_interval: 30s
    scrape_timeout: 10s
    metrics_path: /metrics

Restart Prometheus to apply configuration

Reload Prometheus configuration to start collecting PostgreSQL metrics. The service will now scrape metrics every 30 seconds.

sudo systemctl restart prometheus
sudo systemctl status prometheus

Install Grafana dashboard

Import a pre-built PostgreSQL dashboard into Grafana for immediate visualization. This dashboard provides comprehensive PostgreSQL monitoring out of the box.

curl -X POST \
  http://admin:admin@localhost:3000/api/dashboards/import \
  -H 'Content-Type: application/json' \
  -d '{
    "dashboard": {
      "id": null,
      "title": "PostgreSQL Database Monitoring",
      "tags": ["postgresql", "database"],
      "timezone": "browser",
      "panels": [
        {
          "id": 1,
          "title": "Database Connections",
          "type": "stat",
          "targets": [
            {
              "expr": "pg_stat_database_numbackends",
              "legendFormat": "{{datname}}"
            }
          ],
          "gridPos": {"h": 8, "w": 12, "x": 0, "y": 0}
        },
        {
          "id": 2,
          "title": "Database Size (bytes)",
          "type": "graph",
          "targets": [
            {
              "expr": "pg_database_size_bytes",
              "legendFormat": "{{datname}}"
            }
          ],
          "gridPos": {"h": 8, "w": 12, "x": 12, "y": 0}
        },
        {
          "id": 3,
          "title": "Query Performance",
          "type": "graph",
          "targets": [
            {
              "expr": "rate(pg_stat_database_xact_commit[5m])",
              "legendFormat": "Commits/sec - {{datname}}"
            },
            {
              "expr": "rate(pg_stat_database_xact_rollback[5m])",
              "legendFormat": "Rollbacks/sec - {{datname}}"
            }
          ],
          "gridPos": {"h": 8, "w": 24, "x": 0, "y": 8}
        }
      ]
    }
  }'

Configure Grafana alerting rules

Set up alerting for critical PostgreSQL performance thresholds. These alerts will notify you when database performance degrades or issues occur.

groups:
  - name: postgresql_alerts
    interval: 30s
    rules:
      - alert: PostgreSQLDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL instance is down"
          description: "PostgreSQL instance {{ $labels.instance }} has been down for more than 1 minute."
      
      - alert: PostgreSQLTooManyConnections
        expr: (pg_stat_database_numbackends / pg_settings_max_connections) * 100 > 80
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL has too many connections"
          description: "PostgreSQL instance {{ $labels.instance }} has {{ $value }}% connections used."
      
      - alert: PostgreSQLSlowQueries
        expr: rate(pg_stat_database_xact_rollback[5m]) > 0.1
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL has slow queries"
          description: "PostgreSQL instance {{ $labels.instance }} has high rollback rate: {{ $value }} per second."
      
      - alert: PostgreSQLDatabaseSizeGrowth
        expr: increase(pg_database_size_bytes[1h]) > 1073741824
        for: 0m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL database growing rapidly"
          description: "Database {{ $labels.datname }} grew by {{ $value | humanize1024 }}B in the last hour."

Configure notification channels

Set up email notifications for PostgreSQL alerts. This ensures you receive immediate notification when database issues occur.

notifiers:
  - name: email-notifications
    type: email
    uid: email-notifier
    settings:
      addresses: admin@example.com
      subject: "[Grafana] PostgreSQL Alert - {{ .CommonLabels.alertname }}"
      body: |
        {{ range .Alerts }}
        Alert: {{ .Annotations.summary }}
        Description: {{ .Annotations.description }}
        Severity: {{ .Labels.severity }}
        Instance: {{ .Labels.instance }}
        {{ end }}

Restart Grafana to apply configurations

Reload Grafana to enable the new alerting rules and notification channels. The monitoring setup is now complete.

sudo systemctl restart grafana-server
sudo systemctl status grafana-server