Our standby received every change for five hours and applied none of them
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.…
In August, a two-minute switchover of the main Postgres database was planned to patch the primary kernel. However, the standby, which serves reports for the finance team, received every change for five hours and applied none of them. This resulted in the standby being five hours behind in replay, causing services that write data to be down for forty-one minutes.
The issue arose because 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. Finance had previously set the max_standby_streaming_delay value to minus one, causing the standby to wait forever. To prevent this from happening again, the standby was given a longer delay of thirty seconds and a lag alert was implemented to measure replay.
Additionally, a switchover was conducted in a staging environment every month to ensure that the standby can take over from the primary.
Brief written by urgent.news from Dev.to's own syndicated text. Machine-written — may contain errors; check the original before relying on it.
