PostgreSQL Replication and Promotion
"Connected" Is Not the Same as "Keeping Up"
In one line
Replication lag often happens between receiving and applying, and that gap is shown more honestly by the byte difference of LSNs than by the time numbers in pg_stat_replication. And the mechanisms that hold on to WAL (slots, feedback) all, without exception, push a cost onto the primary.
Why this was needed
When a standby shows "streaming", everyone says replication is fine. Then one day the team that pulls reports from the standby says "there is no data after dawn". The connection is fine, and the WAL the primary sent has all landed on the standby's disk — it is just not being applied. There are accidents in the other direction too. If you delete a standby but leave its slot behind, the primary believes that standby will come back someday and does not delete WAL. Weeks later the primary's disk fills up and writes stop.
How it works
Inside a standby there are two workers. The WAL receiver (walreceiver) receives from the primary and writes to disk (pg_last_wal_receive_lsn()), and the startup process applies it in sequence (pg_last_wal_replay_lsn()). The two can stop independently.
| Why apply stops | What it looks like |
|---|---|
Someone called pg_wal_replay_pause() |
pg_get_wal_replay_pause_state() is paused, and the log says recovery has paused |
It reached a recovery target (recovery_target_*) |
Also paused. The log says recovery stopping at restore point. If you resume here, recovery ends and it promotes |
recovery_min_apply_delay |
Not a stop. It holds commit records for that long before applying them (a "delayed standby" setting for disaster recovery) |
| Conflicts with standby queries | Not a stop. If a standby query is still looking at rows the primary's VACUUM removed, apply waits for max_standby_streaming_delay and then cancels the query. With -1 it waits forever |
Measure in bytes. pg_wal_lsn_diff(flush_lsn, replay_lsn) is the amount received but not yet applied. Time numbers such as replay_lag are computed when apply passes a certain point, so if apply has stopped entirely, the last small value computed may simply remain.
Each of the two ways to avoid conflicts has its own price. If you increase the wait time, the standby falls that much further behind; if you tell the primary "my queries are still looking at these rows" with hot_standby_feedback = on, the conflicts disappear but the primary's VACUUM cannot remove those rows (they remain as the slot's xmin). No setting is free.
A slot is a promise. WAL after the slot's restart_lsn is not deleted. Even if the standby using it disappears, the promise remains. If you set max_slot_wal_keep_size, a slot holding more than that is cut off at a checkpoint (wal_status = lost) and the primary survives — the cut-off standby has to be rebuilt from a base backup.
Fixing the order in which you measure means less floundering. First, on the primary, put sent_lsn, flush_lsn, and replay_lsn side by side for each standby and see where the gap opens — between sending and receiving is the network, between receiving and flushing is the standby's disk, and between flushing and applying is apply. Second, if it is on the apply side, ask the standby for its pause state, and if it is not paused, look at what the startup process is waiting for (wait_event in pg_stat_activity) and at settings that delay apply. Third, read the standby log — pauses, recovery targets, and conflict waits each leave a one-line trace in the log, and that line even says what to do (or not to do).
What it looks like in the field
It is common for someone to set max_standby_streaming_delay = -1 on a reporting standby to "never cancel queries", and then have the standby fall six hours behind because of one analytical query running overnight. Monitoring was looking only at the connection state, so it was green. There was also a case where a recovery_target_name used in a PITR drill spread to the standbys through a configuration management tool, and one day, on reaching a restore point with that name, the standbys all stopped at once. Someone pressed "it stopped, so resume", and that standby quietly promoted and started accepting writes. Slot accidents are even quieter. While cutting back read-load standbys, someone deleted only the servers and left the slots, and over several weeks the primary's pg_wal swelled to hundreds of GB until one dawn the disk filled up. No alert fired in the meantime — because there was no alert measuring slots whose active is f in pg_replication_slots and how far behind their restart_lsn is.
What you do in the next lab
You set up one standby, stop only apply, and measure the difference between receiving and applying in bytes. You trigger a standby query being canceled because it conflicts with apply, and the cost moving to the primary's cleanup when you turn on feedback. You make a slot nobody uses hold more than 100MB of WAL and then cut it off with a limit. The last part is an incident — three standbys whose connections are fine but whose data is stale; you determine the causes and fix them without a restart (except where one is truly needed).