Postgres failover postmortem: standby received five hours of WAL and applied none of it

What happened

A team scheduled what was meant to be a two-minute switchover of its main Postgres database in August, so the primary's kernel could be patched. According to a postmortem by Sergey Shinder on dev.to, the standby was promoted at seven in the morning and then took 41 minutes to accept its first write. For those 41 minutes, every service that writes anything was down.

The striking detail is that replication never actually broke. The standby had been receiving every change from the primary all night. It simply had not applied any of them.

How replay stalled behind finance reports

The standby was doing double duty as a streaming replica and as a reporting database for the finance team, and reporting from a hot standby has a well-known failure mode. When the primary vacuums away rows that a long query on the standby can still see, applying that cleanup would pull the rows out from under the running query. Postgres arbitrates this with max_standby_streaming_delay: by default the standby pauses replay for 30 seconds and then cancels the offending query with the error "canceling statement due to conflict with recovery."

Finance objected to those cancellations during month-end runs, and in the spring somebody set the value to -1, which the documentation describes plainly as waiting indefinitely. It did exactly that. On the evening before the switchover, finance ran its month-end pack of long queries. Replay stopped behind the first of them and stayed behind the rest. The standby kept receiving WAL from the primary and writing it to disk, but by morning it was five hours behind in applied changes.

Promotion has to finish replaying everything the standby has already received before it accepts a single write, and five hours of accumulated WAL took 41 minutes to apply.

Why the pre-switchover check looked fine

The runbook did include a replication check before any switchover. It compared the primary's current position against the position the standby had flushed to disk, which sat a few kilobytes behind and looked perfect. The number that actually mattered, the replay position, lives in a column further along in pg_stat_replication, and nothing was watching it.

What the team changed

The fixes Shinder describes address both the workload mix and the monitoring gap:

• Finance now has a replica of its own, configured to allow long delays, with no role in failover.
• The promotable standby is back to a 30-second delay.
• The lag alert measures replay specifically, using both the replay_lag column and the byte difference to replay_lsn, and fires after one minute.
• The switchover script refuses to start unless replay lag is within a few seconds.
• A switchover is rehearsed in staging every month, timed from promotion to the first successful write.

Why it matters

The lesson Shinder draws generalises well beyond this one incident: a standby that has your changes safely on disk is not the same as a standby ready to take over. His checks measured receipt; the failover depended on application. Flush lag and replay lag can diverge by hours while dashboards stay green, and a single parameter tuned for query comfort can quietly become a failover time bomb. Anyone running hot standbys alongside reporting workloads should be alerting on replay lag rather than flush lag, and rehearsing failovers end to end instead of trusting metrics that only prove the data arrived.