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 alreadyoncemax_connectionsfills. 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:
| Metric | What it tells you |
|---|---|
pg_up | 1 when the exporter could connect. 0 means Postgres is down or refusing the exporter. |
pg_stat_activity_count | Sessions by state, datname, usename, application_name and backend_type. Sum it, and compare it with pg_settings_max_connections. |
pg_stat_activity_max_tx_duration | Age in seconds of the oldest open transaction, per state. The idle in transaction series finds forgotten sessions. |
pg_long_running_transactions_oldest_timestamp_seconds | Age of the oldest transaction past the collector’s threshold, autovacuum excluded. |
pg_replication_lag_seconds | On 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_diff | On 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_diff | Whether each slot has a consumer, and how much WAL it holds back on disk. |
pg_database_wraparound_age_datfrozenxid | Transaction ID age of each database’s oldest unfrozen row. |
pg_stat_database_deadlocks | Counter of deadlocks per database. |
pg_stat_statements_seconds_total, pg_stat_statements_calls_total | Time and calls per queryid. Their ratio gives mean latency per query. |
pg_stat_user_tables_n_dead_tup | Dead 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:
PostgresDeadlocksusesfor: 0mbecauseincrease()over 10 minutes already smooths a single scrape. The deadlock happened, and Postgres resolved it by killing one side.PostgresSlowQueryskips 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_dumphold transactions for a long time by design. Run them as a separate role, and exclude it withusename!="etl"on a rule built frompg_stat_activity_max_tx_duration. fordurations. Keepforlong 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
criticalonly. Sendwarningto 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
- PostgreSQL Autovacuum Tuning for Bloat and Wraparound
Give autovacuum the budget and per-table thresholds your write rate needs, and clear what blocks it, so tables stop bloating and wraparound never stops writes.
- Patroni PostgreSQL High Availability and Failover
Set up Patroni automatic failover with health-checked routing and test it under load, so a dead primary costs seconds of errors and no lost writes.
- PostgreSQL Load Testing with pgbench
Use pgbench for PostgreSQL load testing at fixed, rising rates with your own queries, so you find the database's breaking point before your users do.
- PostgreSQL Connection Pooling with PgBouncer
PgBouncer in transaction mode lets hundreds of app connections share a small pool of Postgres connections, so scaling out pods stops hitting max_connections.