In August we planned a two minute switchover of our main Postgres database, to patch the primary's kernel. The standby was promoted at seven in the morning and took forty one minutes to accept writes. For forty one minutes every service that writes anything was down.
The standby is a streaming replica that also serves reports for our finance team, and reports on a replica have a known problem. When the primary vacuums away rows that a long query on the standby can still see, replaying that change would pull the rows out from under the query. Postgres resolves the conflict with max_standby_streaming_delay. By default the standby waits thirty seconds, then cancels the query with canceling statement due to conflict with recovery. Finance hated those errors during month end, and in the spring somebody set the value to minus one, which the documentation describes plainly: wait forever.
So it did. On the evening before the switchover finance ran their month end pack, a series of long queries, and replay stopped behind the first of them and stayed behind the ones that followed. The standby kept receiving WAL from the primary and writing it to disk. It just did not apply it. By morning it was five hours behind in replay. Promotion finishes replaying everything the standby has received before it accepts a single write, and five hours of WAL took forty one minutes.
Our runbook did check replication before a switchover. It compared the primary's current position with the position the standby had flushed to disk, which was a few kilobytes behind and looked perfect. The replay position, a column further along in pg_stat_replication, was the one that mattered.
Finance now have a replica of their own, with long delays allowed and no role in failover. The standby that can be promoted is back to a thirty second delay. Our lag alert measures replay, both the replay_lag column and the byte difference to replay_lsn, and fires at one minute. The switchover script refuses to start unless replay is within a few seconds. And we run a switchover in staging every month, timed from promotion to the first successful write.
A replica that has received your data and a replica that can take over from you are different things. Our checks measured the first, and the failover depended on the second.
– Sergey Shinder
Top comments (0)