Monitor PostgreSQL with postgres_exporter

Run postgres_exporter with the right collectors and alert on PostgreSQL replication lag, connections and stuck transactions in Prometheus before users notice.

Published

Applies to postgres_exporter 0.21 and later; PostgreSQL 13 and later

postgres_exporter turns PostgreSQL’s statistics views into Prometheus metrics. Its defaults cover connections, replication and table stats. The collectors that catch the worst incidents, long transactions, slow queries and wraparound, ship turned off. You turn them on below, write one rules file and then break staging on purpose to prove each alert fires.

What you miss today

A host dashboard shows CPU, memory and disk for the database server. It shows nothing about the five failures that take a Postgres-backed product down at peak:

  • Connection saturation. Logins fail with FATAL: sorry, too many clients already once max_connections fills. CPU can look idle while it happens. The fix lives in the PgBouncer recipe, and the alerts here warn you before the error.
  • Replication lag. Reads from a replica return data from seconds or minutes ago. Users report “my change disappeared” long before anyone looks at a replica.
  • Long and idle-in-transaction sessions. A transaction left open holds locks and stops vacuum from removing dead rows. Queries behind the lock queue up, and tables bloat for as long as the session lives.
  • Slow queries. One query plan goes bad after a data change, and p99 climbs on every endpoint that touches the table.
  • Transaction ID wraparound. When freezing falls behind for long enough, Postgres stops assigning transaction IDs. PostgreSQL 17 and later log the error below. Versions up to 16 drop the words “that assign new transaction IDs”.
ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "app"

Each of these builds up for minutes or days before users feel it. The alerts below catch the build-up.

The exporter and metrics

Create a monitoring role first. pg_monitor gives read access to every statistics view the exporter needs, on PostgreSQL 10 and later:

CREATE ROLE postgres_exporter LOGIN PASSWORD 'change-me';
GRANT pg_monitor TO postgres_exporter;

For slow-query metrics, load pg_stat_statements. Add it to shared_preload_libraries in postgresql.conf, restart Postgres, then create the extension in the database the exporter connects to:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Run the exporter next to the database, one exporter per Postgres instance, primary and replicas alike. The container image is quay.io/prometheuscommunity/postgres-exporter, and it serves metrics on port 9187:

docker run -d --name postgres-exporter -p 9187:9187 \
  -e DATA_SOURCE_USER=postgres_exporter \
  -e DATA_SOURCE_PASS=change-me \
  quay.io/prometheuscommunity/postgres-exporter:v0.21.1 \
  --datasource.uri="postgres.internal:5432/app?sslmode=require" \
  --collector.long_running_transactions \
  --collector.long_running_transactions.threshold=5m \
  --collector.database_wraparound \
  --collector.stat_statements

Keep the password in an environment variable or a mounted file. Version 0.21 deprecates the DATA_SOURCE_URI variable in favor of the --datasource.uri flag, while DATA_SOURCE_USER and DATA_SOURCE_PASS stay. The three extra collectors are off by default. long_running_transactions counts transactions older than its threshold, one minute unless you set it.

Then point Prometheus at each exporter:

scrape_configs:
  - job_name: postgres
    static_configs:
      - targets: ["pg-primary.internal:9187", "pg-replica-1.internal:9187"]

The metrics that matter, and what each one tells you:

MetricWhat it tells you
pg_up1 when the exporter could connect. 0 means Postgres is down or refusing the exporter.
pg_stat_activity_countSessions by state, datname, usename, application_name and backend_type. Sum it, and compare it with pg_settings_max_connections.
pg_stat_activity_max_tx_durationAge in seconds of the oldest open transaction, per state. The idle in transaction series finds forgotten sessions.
pg_long_running_transactions_oldest_timestamp_secondsAge of the oldest transaction past the collector’s threshold, autovacuum excluded.
pg_replication_lag_secondsOn a replica, seconds since the last replayed transaction. It reads 0 once the replica has replayed everything it received, so a quiet primary doesn’t fake lag.
pg_stat_replication_pg_wal_lsn_diffOn the primary, bytes each standby still has to replay, labeled by application_name. It catches network lag that the replica’s own clock misses.
pg_replication_slots_active, pg_replication_slots_pg_wal_lsn_diffWhether each slot has a consumer, and how much WAL it holds back on disk.
pg_database_wraparound_age_datfrozenxidTransaction ID age of each database’s oldest unfrozen row.
pg_stat_database_deadlocksCounter of deadlocks per database.
pg_stat_statements_seconds_total, pg_stat_statements_calls_totalTime and calls per queryid. Their ratio gives mean latency per query.
pg_stat_user_tables_n_dead_tupDead rows per table, the input for the autovacuum recipe.

Two traps sit in this table. pg_stat_activity_count now carries user, application and wait-event labels, so it can produce many series per database. I wrap it in sum by with only the labels I need. And postgres_exporter 0.21 renamed the wraparound gauge. Older versions only expose pg_database_wraparound_age_datfrozenxid_seconds, a count of transaction IDs despite the suffix, which 0.21 keeps as a deprecated alias.

Managed Postgres. On Amazon RDS, Aurora, Azure Database for PostgreSQL, Cloud SQL and OCI Database with PostgreSQL, you can’t install anything on the database host. Run the exporter as a container or small VM in the same network. The admin role each provider gives you can grant pg_monitor. Turn on pg_stat_statements through the provider’s parameter group, server parameters or database flags, since you can’t edit postgresql.conf. On Aurora, pg_replication_lag_seconds doesn’t appear, because Aurora rejects the functions behind it. Turn on --collector.aurora_replica_status and alert on pg_aurora_replica_status_lag_seconds instead. Disk, CPU and memory come from CloudWatch, Azure Monitor, Cloud Monitoring or OCI Monitoring, so add their free-storage metric to the same alert routing.

The alert rules

The full rules file. Each description says what to check first.

groups:
  - name: postgres
    rules:
      - alert: PostgresDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "postgres_exporter can't connect to {{ $labels.instance }}"
          description: "Run pg_isready against the host. If Postgres is up, check the exporter's role, password and pg_hba.conf entry."
      - alert: PostgresConnectionsSaturated
        expr: |
          sum by (instance) (pg_stat_activity_count{backend_type="client backend"})
            / on (instance) max by (instance) (pg_settings_max_connections) > 0.9
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "{{ $labels.instance }} is above 90% of max_connections"
          description: "New logins fail soon with 'sorry, too many clients already'. Group pg_stat_activity by application_name to find the client holding slots, then pool it with PgBouncer."
      - alert: PostgresIdleInTransaction
        expr: max by (instance, datname) (pg_stat_activity_max_tx_duration{state="idle in transaction"}) > 300
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "A session on {{ $labels.datname }} has been idle in a transaction for over 5 minutes"
          description: "It holds locks and blocks vacuum. Find it with: SELECT pid, usename, application_name, xact_start FROM pg_stat_activity WHERE state = 'idle in transaction'. Fix the app, then set idle_in_transaction_session_timeout."
      - alert: PostgresLongTransaction
        expr: pg_long_running_transactions_oldest_timestamp_seconds > 1800
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "A transaction on {{ $labels.instance }} has run for over 30 minutes"
          description: "Check pg_stat_activity ordered by xact_start. A long report or migration holds back vacuum on every table; cancel it with pg_cancel_backend(pid) if it isn't expected."
      - alert: PostgresReplicaLagging
        expr: pg_replication_lag_seconds > 30 and on (instance) pg_replication_is_replica == 1
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Replica {{ $labels.instance }} is {{ $value | humanizeDuration }} behind"
          description: "Reads from this replica return stale data. Check replay conflicts and recovery_min_apply_delay on the replica, and disk and CPU on both hosts."
      - alert: PostgresStandbyBytesBehind
        expr: max by (instance, application_name) (pg_stat_replication_pg_wal_lsn_diff) > 1073741824
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "Standby {{ $labels.application_name }} is over 1 GiB of WAL behind the primary"
          description: "Check network throughput between primary and standby, and the standby's disk. A failover now would lose or wait for this WAL."
      - alert: PostgresReplicationSlotInactive
        expr: pg_replication_slots_active == 0
        for: 15m
        labels:
          severity: warning
        annotations:
          summary: "Replication slot {{ $labels.slot_name }} on {{ $labels.instance }} has no consumer"
          description: "An inactive slot keeps WAL on disk until the volume fills. Reconnect the consumer, or drop the slot with pg_drop_replication_slot if it's gone for good."
      - alert: PostgresSlotRetainingWAL
        expr: pg_replication_slots_pg_wal_lsn_diff > 21474836480
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "Slot {{ $labels.slot_name }} holds over 20 GiB of WAL on {{ $labels.instance }}"
          description: "Disk fills next and Postgres stops. Check free space now, then fix or drop the slot. Set max_slot_wal_keep_size to cap it."
      - alert: PostgresWraparoundRisk
        expr: max by (instance, datname) (pg_database_wraparound_age_datfrozenxid) > 1000000000
        for: 30m
        labels:
          severity: warning
        annotations:
          summary: "Database {{ $labels.datname }} has a transaction ID age above 1 billion"
          description: "Autovacuum can't freeze rows. Look for long transactions, old prepared transactions and stale replication slots, then read the autovacuum recipe."
      - alert: PostgresDeadlocks
        expr: increase(pg_stat_database_deadlocks[10m]) > 0
        for: 0m
        labels:
          severity: warning
        annotations:
          summary: "Deadlocks on {{ $labels.datname }} in the last 10 minutes"
          description: "Search the Postgres log for 'deadlock detected' to see both queries. Make the code paths lock rows in the same order."
      - alert: PostgresSlowQuery
        expr: |
          sum by (instance, datname, queryid) (rate(pg_stat_statements_seconds_total[10m]))
            / sum by (instance, datname, queryid) (rate(pg_stat_statements_calls_total[10m])) > 0.5
            and sum by (instance, datname, queryid) (rate(pg_stat_statements_calls_total[10m])) > 1
        for: 15m
        labels:
          severity: warning
        annotations:
          summary: "Query {{ $labels.queryid }} on {{ $labels.datname }} averages over 500 ms"
          description: "Look up the query text in pg_stat_statements by queryid, then run EXPLAIN (ANALYZE, BUFFERS) on staging data. A new index or a fresh ANALYZE fixes most plan changes."

Notes on the choices above:

  • PostgresDeadlocks uses for: 0m because increase() over 10 minutes already smooths a single scrape. The deadlock happened, and Postgres resolved it by killing one side.
  • PostgresSlowQuery skips queries that run less than once a second, so a nightly report doesn’t page anyone.
  • The wraparound threshold sits well above autovacuum_freeze_max_age, 200 million by default. Ages between the two are normal while autovacuum freezes. Ages past a billion mean something blocks it.

Test the alerts

Use a staging primary with a replica, and the same exporter version and rules as production. Load the rules into the staging Prometheus. For each alert, cause the failure, watch it go from pending to firing in the Prometheus UI, then undo it and watch it clear.

Down. Stop Postgres on the replica with pg_ctl stop -m fast. PostgresDown fires after one minute. Start it again.

Idle in transaction. Open a session and leave it:

BEGIN;
SELECT 1;
-- leave this psql window open for 8 minutes, then run ROLLBACK;

PostgresIdleInTransaction fires about seven minutes in, five for the threshold and two for for.

Connections. Run pgbench with more clients than 90% of max_connections. The pgbench recipe explains the flags.

pgbench -h pg-staging.internal -U app -c 95 -j 4 -T 600 -S app

On a server with max_connections = 100, that holds 95 client backends for ten minutes.

Replica lag. Pause replay on the replica, then write on the primary:

-- on the replica
SELECT pg_wal_replay_pause();
-- on the primary, run a write load such as: pgbench -c 4 -T 600 app
-- on the replica, after the alerts fire
SELECT pg_wal_replay_resume();

Both PostgresReplicaLagging and PostgresStandbyBytesBehind should fire, the second once enough WAL piles up.

Inactive slot. Create a slot that reserves WAL straight away, write for a while, then drop it:

SELECT pg_create_physical_replication_slot('alert_test', true);
-- write on the primary for 20 minutes
SELECT pg_drop_replication_slot('alert_test');

Drop it as soon as you’ve seen the alert. A forgotten test slot is the exact failure this alert exists for.

Deadlock. In two sessions, update two rows in opposite order inside transactions. Postgres logs ERROR: deadlock detected and cancels one.

Slow query and wraparound. Save SELECT pg_sleep(1); as sleep.sql and run it from four clients for 20 minutes, which gives about four calls a second:

pgbench -h pg-staging.internal -U app -n -f sleep.sql -c 4 -T 1200 app

You can’t age transaction IDs on staging in reasonable time, so check that rule another way. Lower its threshold to the current age from SELECT datname, age(datfrozenxid) FROM pg_database;, confirm it fires, then restore it.

It passes when every alert fires, reaches the channel your on-call reads, and clears once you undo the failure.

Tune the noise

Set thresholds from your own baseline. I pull a week of history before I trust any number in this file:

quantile_over_time(0.99, (sum by (instance) (pg_stat_activity_count{backend_type="client backend"}))[7d:5m])
max_over_time(pg_replication_lag_seconds[7d])
max_over_time(pg_stat_activity_max_tx_duration{state="active"}[7d])
  • Connections. If the weekly p99 already sits near 90%, the alert will fire every peak. Fix the pooling first, then keep the threshold.
  • Replica lag. Your app decides this one. If it reads from replicas right after a write, 30 seconds is far too loose. Set it from the staleness your product can accept, and see the Patroni recipe for lag that blocks failover.
  • Long transactions. Nightly ETL and pg_dump hold transactions for a long time by design. Run them as a separate role, and exclude it with usename!="etl" on a rule built from pg_stat_activity_max_tx_duration.
  • for durations. Keep for long enough to ride out one slow scrape, and shorter than the time users take to notice. Two to five minutes covers most rules here.
  • Severity. Page on critical only. Send warning to a channel someone reads during working hours.

If an alert fires and nobody needs to act, raise its threshold or delete it. An alert people learn to ignore costs you the one that matters.

More recipes

Further reading