Monitor WAL receive and replay lag on the standby (pg_stat_wal_receiver)

Command

-- Run on the standby:
SELECT pg_is_in_recovery() AS in_recovery,
       pg_last_wal_receive_lsn() AS receive_lsn,
       pg_last_wal_replay_lsn() AS replay_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) AS replay_backlog,
       now() - pg_last_xact_replay_timestamp() AS last_replay_age,
       pg_get_wal_replay_pause_state() AS pause_state;
SELECT pid, status, sender_host, sender_port, slot_name, received_tli, flushed_lsn, latest_end_lsn, now() - last_msg_receipt_time AS since_last_msg FROM pg_stat_wal_receiver;

What it does

Shows how much WAL the standby has received and how much it has applied (replay backlog), the age of the last replayed transaction, whether replay is paused, and the walreceiver connection state (which primary, slot, timeline, time of last message).

When to use it

To diagnose lag when the primary is not reachable or on cascading standbys; on a 'standby behind' alert, to separate a network (receive) problem from a replay (I/O, conflict) problem.

Caution

If pg_stat_wal_receiver returns no rows, the standby is not streaming (fed from the archive or disconnected). last_replay_age grows even when the primary has no writes; interpret it together with the receive_lsn/replay_lsn difference. If replay backlog grows while receive advances, the standby has long queries (max_standby_streaming_delay) or an I/O bottleneck.