Lesson 25.58 · PostgreSQL Operations · 17 min read

Monitoring PostgreSQL

Q: What is the first alert to write?

PostgresDown on pgup == 0 for a minute, plus an alert on the exporter target itself being down (up == 0 for the job). Without the second, a crashed exporter makes every PostgreSQL alert go silent, which looks exactly like a healthy database. Then connection saturation, long transactions, slots, archiving and lag.

Q: Which metric shows the vacuum horizon problem?

The age of the oldest open transaction - pgstatactivitymaxtxduration in postgresexporter - and the number of sessions idle in transaction. A long-running or forgotten transaction holds back VACUUM on every table, so an alert at about ten minutes catches it long before the bloat shows up in query times.

Q: Does this work with managed PostgreSQL?

Yes. postgresexporter connects to Azure Flexible Server or RDS like any client, with a role that has pgmonitor. The provider also has its own metrics (CPU, storage, IOPS, connections, replication lag). Combine both: provider metrics for the machine and storage, exporter metrics for what happens inside the database.

Q: What would you monitor and alert on for a PostgreSQL server?

With postgresexporter (a pgmonitor role, not superuser) scraped by a metrics server: availability - pgup and the exporter target's own up; saturation - client connections as a share of maxconnections (alert around 80%), disk on the data and WAL volumes with predictlinear, XID age; the silent killers - the oldest transaction and idle-in-transaction sessions, inactive replication slots and their retained WAL, a failing WAL archive; and replication lag in seconds and bytes. Use for: durations, page only on user impact, ticket the rest, and keep a dashboard next to the alerts.

In plain words

A car dashboard does not wait for the engine to explode before telling you something is wrong: it shows the fuel level, the temperature, a warning light when the oil is low. Monitoring PostgreSQL is building that dashboard for the database: how full the seats are, how old the oldest open transaction is, whether the backup machine is still working, how far behind the copy is.

Alerts are the warning lights, and the good ones come on while there is still time to pull over.

Why this lesson exists

Every incident in this chapter had a number that would have warned you hours or days before the page: connections creeping toward max_connections, the age of the oldest transaction, dead tuples climbing, an archiver failing, a slot retaining WAL, replication lag. Monitoring PostgreSQL means turning those numbers into time series and alerts before the outage. This lesson wires postgres_exporter into the Prometheus from the observability chapters and writes the alert rules every PostgreSQL server should have.

What you need to know already: Prometheus, scrape configs, PromQL, alert rules, for: and promtool (the observability chapters); every view this chapter used (pg_stat_activity, pg_stat_replication, pg_replication_slots, pg_stat_archiver, pg_stat_user_tables).

The words you need first

postgres_exporter - the Prometheus exporter for PostgreSQL (prometheus-community): it connects to the server, runs catalog queries on every scrape and exposes the results on :9187/metrics.
pg_monitor - a built-in role that may read every statistics view and setting a monitoring tool needs, without being a superuser.
pg_up - 1 if the exporter could connect on its last scrape. The first alert.
Saturation - how full a fixed resource is: connections / max_connections, disk, XID age / 2^31. Alert on saturation trends, not only on "down".

The exporter

$ sudo apt install -y prometheus-postgres-exporter > /dev/null 2>&1
$ systemctl is-active prometheus-postgres-exporter
active
$ grep -v '^#' /etc/default/prometheus-postgres-exporter | grep -v '^$'
ARGS=""
DATA_SOURCE_NAME="user=postgres host=/run/postgresql dbname=postgres"
$ curl -s localhost:9187/metrics | grep -E '^pg_up|^pg_settings_max_connections'
pg_up 1
pg_settings_max_connections{server="localhost:5432"} 100

Out of the box it connects as postgres over the socket. Give it its own least-privilege role instead - a monitoring tool with superuser rights is a liability:

$ sudo -u postgres psql -c "create role postgres_exporter login password 'exp-lab-pw' in role pg_monitor"
CREATE ROLE
$ echo 'host    all             postgres_exporter 127.0.0.1/32            scram-sha-256' | sudo tee -a /etc/postgresql/18/main/pg_hba.conf > /dev/null
$ sudo systemctl reload postgresql@18-main
$ sudo sed -i 's#^DATA_SOURCE_NAME=.*#DATA_SOURCE_NAME="postgresql://postgres_exporter:[email protected]:5432/postgres?sslmode=disable"#' /etc/default/prometheus-postgres-exporter
$ sudo systemctl restart prometheus-postgres-exporter
$ curl -s localhost:9187/metrics | grep -E '^pg_up|^pg_stat_activity_count' | head -n 5
pg_up 1
pg_stat_activity_count{application_name="",backend_type="",datname="postgres",state="disabled",usename="",wait_event="",wait_event_type=""} 0
pg_stat_activity_count{application_name="",backend_type="",datname="postgres",state="fastpath function call",usename="",wait_event="",wait_event_type=""} 0
pg_stat_activity_count{application_name="",backend_type="",datname="postgres",state="idle",usename="",wait_event="",wait_event_type=""} 0
pg_stat_activity_count{application_name="",backend_type="",datname="postgres",state="idle in transaction",usename="",wait_event="",wait_event_type=""} 0

(The password sits in /etc/default/... - keep that file 0640 root:prometheus or use the exporter's --config.file with credentials from a secret store.)

Into Prometheus

$ printf "\n  - job_name: 'postgres'\n    static_configs:\n      - targets: ['localhost:9187']\n        labels:\n          cluster: 'orders-db'\n" | sudo tee -a /etc/prometheus/prometheus.yml > /dev/null
$ promtool check config /etc/prometheus/prometheus.yml
Checking /etc/prometheus/prometheus.yml
  SUCCESS: 2 rule files found
 SUCCESS: /etc/prometheus/prometheus.yml is valid prometheus config file syntax

Checking /etc/prometheus/rules/node.yml
  SUCCESS: 1 rules found

Checking /etc/prometheus/rules/orders.yml
  SUCCESS: 1 rules found
$ sudo systemctl reload prometheus
$ curl -s 'localhost:9090/api/v1/query' --data-urlencode 'query=up{job="postgres"}' | jq -r '.data.result[] | "\(.metric.instance) up=\(.value[1])"'
localhost:9187 up=1

(--data-urlencode because a PromQL selector's {...} in a curl URL is curl's own globbing syntax: curl 'host/api/v1/query?query=up{job="x"}' sends a different query than you typed.)

The series that matter, and what each is for:

metric	question it answers
`pg_up`	can we connect at all?
`sum(pg_stat_activity_count) / pg_settings_max_connections`	how close are we to `too many clients`?
`pg_stat_activity_count{state="idle in transaction"}`	the lock-and-bloat danger (lessons 6, 7)
`pg_stat_activity_max_tx_duration`	the oldest open transaction, in seconds - the horizon holder
`pg_locks_count{mode="accessexclusivelock"}`	a migration or `VACUUM FULL` holding everything
`pg_replication_lag_seconds` (on a replica)	how stale reads are, how much a failover loses
`pg_replication_slot_slot_is_active`, `pg_replication_slots_pg_wal_lsn_diff`	a slot holding WAL for a gone replica (disk-full risk)
`rate(pg_stat_archiver_failed_count[5m])`	the archive is broken (PITR is silently gone)
`pg_stat_user_tables_n_dead_tup`	vacuum not keeping up
`pg_database_size_bytes`	growth, for capacity planning
`rate(pg_stat_database_xact_commit[5m])`, `rate(pg_stat_database_deadlocks[5m])`	throughput, application bugs

Plus, from node_exporter: free space on the data and WAL filesystems (node_filesystem_avail_bytes with predict_linear - the observability chapter), and the I/O of the data disk.

$ curl -s 'localhost:9090/api/v1/query' --data-urlencode 'query=sum(pg_stat_activity_count) / max(pg_settings_max_connections)' | jq -r '.data.result[0].value[1]'
0.01

Alert rules

A rule file that covers this chapter's incidents (thresholds are starting points - tune them to your traffic):

$ sudo tee /etc/prometheus/rules/postgres.yml > /dev/null <<'EOF'
groups:
  - name: postgres
    rules:
      - alert: PostgresDown
        expr: pg_up == 0
        for: 1m
        labels: {severity: page}
        annotations:
          summary: "PostgreSQL {{ $labels.instance }} is not accepting connections"
      - alert: PostgresConnectionsNearLimit
        expr: sum by (instance) (pg_stat_activity_count) / max by (instance) (pg_settings_max_connections) > 0.8
        for: 5m
        labels: {severity: page}
        annotations:
          summary: "{{ $labels.instance }}: {{ $value | humanizePercentage }} of max_connections in use"
      - alert: PostgresLongTransaction
        expr: max by (instance) (pg_stat_activity_max_tx_duration) > 600
        for: 2m
        labels: {severity: ticket}
        annotations:
          summary: "a transaction on {{ $labels.instance }} has been open for {{ $value | humanizeDuration }}"
      - alert: PostgresReplicationSlotInactive
        expr: pg_replication_slot_slot_is_active == 0
        for: 30m
        labels: {severity: ticket}
        annotations:
          summary: "slot {{ $labels.slot_name }} is inactive and retains WAL"
      - alert: PostgresWalArchiveFailing
        expr: increase(pg_stat_archiver_failed_count[15m]) > 0
        labels: {severity: page}
        annotations:
          summary: "WAL archiving on {{ $labels.instance }} is failing - PITR is broken and pg_wal grows"
      - alert: PostgresReplicationLag
        expr: pg_replication_lag_seconds > 300
        for: 5m
        labels: {severity: ticket}
EOF
$ promtool check rules /etc/prometheus/rules/postgres.yml
Checking /etc/prometheus/rules/postgres.yml
  SUCCESS: 6 rules found
$ sudo systemctl reload prometheus

Now break something on purpose and watch the alert go pending -> firing (for: 1m):

$ sudo systemctl stop postgresql@18-main
$ curl -s localhost:9090/api/v1/alerts | jq -r '.data.alerts[] | select(.labels.alertname | startswith("Postgres")) | "\(.labels.alertname) \(.state)"'
PostgresDown firing
$ sudo systemctl start postgresql@18-main

Things that make these alerts good rather than noisy:

for: on everything that can blip; none on things that are always bad (a failing archive).
Severity by impact: down and connection exhaustion page someone; a long transaction or an inactive slot is a ticket for working hours - unless the trend says the disk fills tonight.
Alert on the exporter itself too (up{job="postgres"} == 0): when the exporter dies, every other rule goes quiet - which looks exactly like "all fine".
A dashboard next to the alerts (connections by state and application, TPS, lag, top tables by dead tuples) - the community Grafana dashboards for postgres_exporter are a good start.

Quick check

$ sudo -u postgres psql -Atc "select pg_has_role('pg_monitor', 'pg_read_all_stats', 'member')"

The exporter's role got pg_monitor, nothing else. Does it see other sessions' query text in pg_stat_activity?

yes: pg_monitor includes pg_read_all_stats (and pg_read_all_settings, pg_stat_scan_tables)
no: only a superuser sees other roles' queries
only for sessions of its own database

Answer

yes: pg_monitor includes pg_read_all_stats (and pg_read_all_settings, pg_stat_scan_tables) - pg_monitor bundles pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables: enough for every exporter query without superuser. (A role without it sees "<insufficient privilege>" in other roles' query column.)

In an interview: "What would you alert on for a PostgreSQL server?" - availability (pg_up, and the exporter's own up), saturation (connections vs max_connections, disk with predict_linear, XID age), the silent killers (oldest transaction / idle in transaction, inactive replication slots retaining WAL, failing WAL archiving), replication lag, and errors/deadlocks - with for: durations and paging only on user impact.

Quick check

$ sudo -u postgres psql -Atc "show track_io_timing"

Do pg_stat_statements and EXPLAIN (BUFFERS) show time spent on I/O out of the box?

yes, always
no: track_io_timing is off by default
only on replicas

Answer

no: track_io_timing is off by default - track_io_timing is off (it calls the clock on every read); turn it on (pg_test_timing says if the clock is cheap) to see I/O time in EXPLAIN and pg_stat_statements.

What you can do now

Install postgres_exporter with its own pg_monitor role and scrape it from Prometheus.
Name the PostgreSQL metrics behind each incident in this chapter.
Write and test alert rules for down, connections, long transactions, slots, archiving and lag.
Make alerts quiet when they should be, and loud when the monitoring itself breaks.

Why it helps

Every incident in this chapter had a number that rose for hours or days first: connections toward max_connections, the oldest transaction's age, dead tuples, archiver failures, WAL held by a slot, replication lag. postgres_exporter turns them into Prometheus series and alert rules turn them into pages before the outage.

The lesson also covers what makes alerts trustworthy: for: durations, severities by impact, and alerting on the exporter itself so a dead exporter does not look like "all fine".

Commands in this lesson

apt systemctl grep curl psql echo sed printf promtool tee

FAQ

Why give the exporter its own role?

Connecting as postgres means a superuser password sits in a file read by a network-facing service. The built-in pg_monitor role allows reading every statistics view and setting the exporter needs and nothing more. Combine it with a narrow pg_hba.conf line from the exporter's address and keep the credentials file readable only by the exporter.

What is the first alert to write?

PostgresDown on pg_up == 0 for a minute, plus an alert on the exporter target itself being down (up == 0 for the job). Without the second, a crashed exporter makes every PostgreSQL alert go silent, which looks exactly like a healthy database. Then connection saturation, long transactions, slots, archiving and lag.

Which metric shows the vacuum horizon problem?

The age of the oldest open transaction - pg_stat_activity_max_tx_duration in postgres_exporter - and the number of sessions idle in transaction. A long-running or forgotten transaction holds back VACUUM on every table, so an alert at about ten minutes catches it long before the bloat shows up in query times.

Why use for: in alert rules?

To avoid paging on blips. Connections can spike for a few seconds during a deploy, and lag jumps during a large write. A for: of a few minutes makes the condition persist before the alert fires. Do not use it for conditions that are always bad, such as failed WAL archiving, where every minute counts.

Does this work with managed PostgreSQL?

Yes. postgres_exporter connects to Azure Flexible Server or RDS like any client, with a role that has pg_monitor. The provider also has its own metrics (CPU, storage, IOPS, connections, replication lag). Combine both: provider metrics for the machine and storage, exporter metrics for what happens inside the database.

In an interview Mid

What would you monitor and alert on for a PostgreSQL server?

With postgres_exporter (a pg_monitor role, not superuser) scraped by a metrics server: availability - pg_up and the exporter target's own up; saturation - client connections as a share of max_connections (alert around 80%), disk on the data and WAL volumes with predict_linear, XID age; the silent killers - the oldest transaction and idle-in-transaction sessions, inactive replication slots and their retained WAL, a failing WAL archive; and replication lag in seconds and bytes. Use for: durations, page only on user impact, ticket the rest, and keep a dashboard next to the alerts.

Also asked: Why should the monitoring role not be a superuser? · How do you avoid alerts going silent when the exporter dies? · How would you alert before the database runs out of connections?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.