Re: In-place upgrade with streaming replicas

On 2025-Feb-19, richard(at)kojedz(dot)in wrote:

> With this, I have the question, that after the shutdown of primary, what is
> the guarantee for replicas having the same checkpoint location? Why does the
> order of shutting down the servers matter? What would be the really exact
> and reliable way to ensure that replicas will have the same checkpoint
> location as the primary?

The replicas can't write WAL by themselves, but they will replay
whatever the primary has sent; by shutting down the primary first and
letting the replicas catch up, you ensure that the replicas will
actually receive the shutdown record and replay it. If you shut down
the replicas first, they can obviously never catch up with the shutdown
checkpoint of the primary.

As I recall, if you do shut down the primary first, one potential danger
is that the primary fails to send the checkpoint record before shutting
down, so the replicas won't receive it and obviously will not replay it;
or simply that they are behind enough that they receive it but don't
replay it.

You could use pg_controldata to read the last checkpoint info from all
nodes. You can run it on the primary after shutting it down, and then
on each replica while it's still running to ensure that the correct
restartpoint has been created.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Someone said that it is at least an order of magnitude more work to do
production software than a prototype. I think he is wrong by at least
an order of magnitude." (Brian Kernighan)