How to monitor PostgreSQL

PostgreSQL replication lag: measure it, alert on it, reduce it

Lag has two units and you need both. Bytes of WAL say how much work the replica still has to do; time says how stale its reads are and how much a failover would lose.

On the primary: pg_stat_replication

Each connected standby has one row. Four WAL positions show how far a change has travelled: sent_lsn (sent), write_lsn (written by the standby), flush_lsn (durable on its disk) and replay_lsn (applied and visible to queries). write_lag, flush_lag and replay_lag are the times recent WAL took to reach each stage. pg_wal_lsn_diff() turns two positions into bytes. A non-superuser needs the pg_monitor role to see these columns.

SELECT application_name, client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn))   AS not_yet_sent,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn))              AS sent_not_flushed,
       pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn))            AS flushed_not_replayed,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)                 AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

A large not yet sent points at the primary or the network, sent but not flushed at the network or the standby's disk, flushed but not replayed at replay itself. When a standby has caught up and no WAL is being written, the lag columns show NULL after a short time: treat that as "no recent WAL", not as an error.

On the replica

SELECT pg_is_in_recovery()                                AS is_replica,
       pg_last_wal_receive_lsn()                           AS received,
       pg_last_wal_replay_lsn()                            AS replayed,
       pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replay_backlog_bytes,
       now() - pg_last_xact_replay_timestamp()             AS since_last_replayed_commit;

-- lag in seconds that reads 0 instead of growing when the primary is idle
SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0
            ELSE extract(epoch FROM now() - pg_last_xact_replay_timestamp()) END AS lag_seconds;

-- is the WAL receiver connected at all?
SELECT status, sender_host, last_msg_receipt_time FROM pg_stat_wal_receiver;

now() - pg_last_xact_replay_timestamp() is the age of the last replayed commit. On a busy system that is the lag; on an idle primary it keeps growing although nothing is behind, which is why the second query returns 0 when the received and replayed positions match. A replica only knows about WAL it has received, so also check that pg_stat_wal_receiver.status is streaming, and keep both clocks on NTP.

Replication slots and retained WAL

A slot makes the primary keep every WAL segment its consumer has not confirmed. If the consumer goes away, active turns false and pg_wal grows until the disk is full.

SELECT slot_name, slot_type, active, wal_status,
       pg_size_pretty(safe_wal_size)                                      AS safe_wal_size,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

wal_status reads reserved or extended when healthy, unreserved when the slot is past max_slot_wal_keep_size and about to lose WAL, and lost when the replica must be rebuilt. safe_wal_size is NULL when max_slot_wal_keep_size is -1 (the default: no limit). Set a limit that fits your disk and drop unused slots with pg_drop_replication_slot().

Logical replication lag

The publisher still has a walsender row per subscription in pg_stat_replication, and the slot's confirmed_flush_lsn shows what the subscriber has confirmed. On the subscriber, pg_stat_subscription lists the workers.

-- on the publisher: how far each logical slot's consumer has confirmed
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS behind
FROM pg_replication_slots WHERE slot_type = 'logical';

-- on the subscriber: apply workers have relid NULL (table-sync workers have a relid); pid is NULL when none is running
SELECT subname, pid, received_lsn, latest_end_lsn, last_msg_receipt_time, latest_end_time
FROM pg_stat_subscription WHERE relid IS NULL;

A NULL pid means the apply worker stopped, usually after a conflict the subscriber's log names, and the publisher's slot keeps WAL until it is fixed.

Thresholds that work

• Lag in seconds: warn at 30 s, page at 5 minutes for read replicas; for a failover standby, page at the data loss you can accept.
• Lag in bytes: alert when it keeps growing for 10 minutes, not at a fixed size; a burst during a nightly load is normal.
• Replicas streaming: page when one is missing, since lag rules go quiet when the row disappears.
• Slots: page on wal_status = 'unreserved' or an inactive slot holding a quarter of the free WAL disk.
• Subscriptions: page when an apply worker has had no pid for 5 minutes.

Worked example

Sampling pg_current_wal_lsn() 60 seconds apart shows 720 MB of WAL, so the primary writes 12 MB/s. A replica's replay_lag_bytes reads 2,400 MB: about 2,400 ÷ 12 = 200 seconds of writes. It replays at 20 MB/s, gaining 8 MB/s, so it catches up in 2,400 ÷ (20 − 12) = 300 seconds, about 5 minutes: a warning, not a page. At 11 MB/s it would never catch up; the backlog would grow by 3,600 MB an hour, and the right alert is the trend.

Why replicas fall behind, and how to reduce lag

• Network. Shows as sent-but-not-flushed. Enable wal_compression (lz4 or zstd from 15) and keep the standby near the primary.
• Slower standby disk. Shows as flush lag. Give replicas the primary's storage class.
• Serial replay. One process applies WAL. Batch huge UPDATE and DELETE jobs and time bulk loads and index builds.
• Query conflicts. Standby queries can hold replay for up to max_standby_streaming_delay (30 s by default). Send reports to a separate replica; hot_standby_feedback helps but bloats the primary.
• Lag by design. Exclude standbys with recovery_min_apply_delay from alerts.
• Logical apply. Large transactions apply at commit unless the subscription uses streaming = on (14+) or parallel (16+). Give every replicated table a primary key.

Alerting on replication lag with Vigil

With a databases entry the server agent runs one lag query per report: on a standby extract(epoch FROM now() - pg_last_xact_replay_timestamp()), on a primary the largest replay_lag in pg_stat_replication, in seconds. It is charted as db:<name>:replication_lag_s and the default rule opens an incident at 30 s (change it under Alert rules). As a result the standby figure grows while the primary is idle, and the primary figure is empty when no standby is connected. The agent does not read bytes, slots or subscriptions; add them as custom commands and give each a limit, for example pg_replicas_missing = 1 or pg_slot_retained_mb = 20000.

"commands": {
  "pg_replicas_missing": "sudo -u postgres psql -Atc \"select 1 - count(*) from pg_stat_replication where state = 'streaming'\"",
  "pg_replay_lag_mb": "sudo -u postgres psql -Atc \"select coalesce(max(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)), 0) / 1048576 from pg_stat_replication\"",
  "pg_slot_retained_mb": "sudo -u postgres psql -Atc \"select coalesce(max(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)), 0) / 1048576 from pg_replication_slots\"",
  "pg_subs_stopped": "sudo -u postgres psql -d app -Atc \"select count(*) from pg_stat_subscription where relid is null and pid is null\""
}

For a managed replica without the agent, use a heartbeat: a cron job pings Vigil only while lag is under 5 minutes, so missing pings alert on lag and on an unreachable replica alike.

*/5 * * * * [ "$(psql -h replica.internal -U vigil_monitor -d app -Atc "select case when pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() then 0 else extract(epoch from now() - pg_last_xact_replay_timestamp()) end < 300")" = t ] && curl -fsS -m 10 https://puzaricloud.in/ping/<token>