Runbook: Investigate Replication Lag

1 · Prerequisites

Confirm every item is in place before any state change.

• The alert payload: which standby, which metric, what threshold, and when it first fired
• A connection to the primary and a connection to the standby, because the two views answer different questions
• Knowledge of whether this standby is deliberately delayed, and whether it is synchronous
• The estate inventory of standbys, so an absent one can be distinguished from a slow one
• Knowledge of what changed recently: a bulk load, a network change, a standby reboot, or a new workload on the standby

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · Confirm the standby is present at all. SELECT application_name, state FROM pg_stat_replication; on the primary. A standby that is not connected has no row, and every lag metric derived from this view returns NULL rather than a large number.
• · Check what the monitoring did with that NULL. A system that renders NULL as zero draws a perfect lag graph for a standby that is not replicating at all. If the alert's metric could return NULL, establish what it displays before trusting the graph.
• · Read byte lag, not time lag. SELECT application_name, state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind, write_lag, flush_lag, replay_lag FROM pg_stat_replication; Byte lag is meaningful whether or not the primary is committing.
• · Check whether the primary is idle. SELECT pg_current_wal_lsn(); twice, thirty seconds apart. If it has not moved, a metric based on time since the last replayed commit will climb with the wall clock while the standby is perfectly current.
• · Read the standby side. SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS unreplayed_bytes, (SELECT count(*) FROM pg_stat_wal_receiver) AS receiver;
• · Check whether the standby is deliberately delayed. SHOW recovery_min_apply_delay; A non-zero value means it is behind on purpose, and the correct alert threshold is that delay plus a margin.
• · Check for recovery conflicts. SELECT * FROM pg_stat_database_conflicts WHERE datname = current_database(); on the standby. Conflicts mean queries on the standby are being cancelled, which is a different symptom from lag.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1Classify the situation before diagnosing. There are four: the standby is absent; WAL is not arriving; WAL is arriving and not being applied; nothing is wrong and the metric is misleading. Each has a different fix and the classification takes two queries.
2. 2If the standby is absent, look at the standby, not the primary. SELECT * FROM pg_stat_wal_receiver; An empty result means the receiver process is not running — it cannot connect — as distinct from connected and stalled. The standby log will say why, once every five seconds.
3. 3**For a connection failure, check primary_conninfo first.** SHOW primary_conninfo; A literal IP address that no longer exists produces a standby that retries silently forever, logging a FATAL nobody reads.
4. 4**If WAL is arriving but not being applied, unreplayed_bytes is large.** Replay is single-threaded and can fall behind a heavy write workload, a large index build, or a conflicting query on the standby that keeps being retried.
5. 5Check what replay is waiting on. SELECT pid, wait_event_type, wait_event, state FROM pg_stat_activity WHERE backend_type = 'startup'; on the standby. A Lock wait means a query on the standby is blocking recovery.
6. 6**Check hot_standby_feedback and its consequences.** With it off, recovery cancels conflicting queries — visible in pg_stat_database_conflicts. With it on, recovery waits instead, and the standby holds the primary's vacuum horizon. Neither is wrong; the symptom differs.
7. 7If WAL is not arriving, check the network and the primary. SELECT count(*) FROM pg_stat_replication; on the primary against the inventory, and the standby log for could not receive data from WAL stream.
8. 8Check whether the slot is still holding. SELECT slot_name, active, wal_status, safe_wal_size FROM pg_replication_slots; on the primary. A wal_status other than reserved means the primary can no longer guarantee the WAL this standby needs, and the standby may not be able to catch up at all.
9. 9**Check the primary's pg_wal size.** A standby that has been absent for a long time with a slot in place will have caused WAL to accumulate, and that is a second incident hiding behind the first.
10. 10If the metric was misleading, fix the metric. Byte lag from the primary, unreplayed_bytes from the standby, and an absence check against an inventory. Time since the last replayed commit is not usable on an idle primary.
11. 11Test the corrected alert by causing the condition. SELECT pg_wal_replay_pause(); on the standby makes it genuinely lag, harmlessly and on demand; pg_wal_replay_resume() ends it. An alert that has never fired has never been shown to work.
12. 12Record the classification, the cause, and what was changed.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓pg_stat_replication on the primary shows the standby with state = 'streaming' and a small, stable byte lag.
• ✓pg_stat_wal_receiver on the standby shows status = 'streaming' and names the expected sender.
• ✓unreplayed_bytes on the standby is near zero, or is explained by a deliberate recovery_min_apply_delay.
• ✓A row written on the primary appears on the standby within seconds. This is the end-to-end check and it exercises the whole path rather than one view.
• ✓The corrected alert fires when replay is paused deliberately, and clears when it is resumed.
• ✓The absence alert fires when the standby is stopped deliberately, and clears when it is started.
• ✓The replication slot shows wal_status = 'reserved' and pg_wal on the primary has returned to its normal size.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶This procedure reads. Rollback covers only the actions a responder takes by reflex.
• ↶If replay was paused for a test, resume it: SELECT pg_wal_replay_resume();. A standby left paused falls further behind indefinitely and stops being a usable failover target.
• ↶If primary_conninfo was changed, it is reloadable — SELECT pg_reload_conf(); — and reverting is the same operation. Confirm the receiver reconnected afterwards rather than assuming.
• ↶If hot_standby_feedback was changed, understand what the change moved. Turning it on stops query cancellations on the standby and starts holding the primary's vacuum horizon; turning it off does the reverse. Both are real trades.
• ↶If a replication slot was dropped to relieve WAL pressure, the standby may no longer be able to catch up. Confirm it reconnected; if the WAL it needed has gone, rebuild it from a base backup.
• ↶If an alert was silenced during the investigation, restore it or shorten the silence. A muted replication alert is how a four-hour lag goes unnoticed.

6 · Escalation

When the runbook isn't enough, contact:

• · The standby has been disconnected for a long period and the WAL it needs no longer exists: escalate. It must be rebuilt from a base backup, and until then the estate has no failover target.
• · Replay cannot keep up with the primary's write rate: escalate to the platform owner. A standby on slower storage or with fewer resources than the primary will fall permanently behind, and that is a provisioning question.
• · The standby is synchronous and is holding commits: escalate to the incident owner. Every write on the primary is waiting, and the choice between waiting and clearing synchronous_standby_names is a durability decision.
• · A slot has wal_status of lost: escalate. The primary has discarded WAL this slot needed and the standby cannot resume; a rebuild is the only path.
• · The primary's pg_wal is growing because of this standby's slot: escalate before dropping the slot. Dropping it relieves the disk and commits you to rebuilding the standby.
• · The alert has been firing regularly for months and has been muted: escalate as a monitoring defect rather than treating this occurrence. A metric that is loudest when the cluster is healthiest trains people to ignore it.

References

1. PostgreSQL 18 documentation, pg_stat_replication
2. PostgreSQL 18 documentation, pg_stat_wal_receiver
3. PostgreSQL 18 documentation, Hot Standby
4. PostgreSQL 18 documentation, Recovery Control Functions