Re: pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption?

On Fri, Jun 30, 2023 at 14:33 Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Fri, Jun 30, 2023 at 04:16:31PM -0400, Robert Haas wrote:
> > I'm not quite clear on how Nikolay got into trouble here. I don't
> > think I understand under exactly what conditions the procedure is
> > reliable and under what conditions it isn't. But there is no way in
> > heck I would ever advise anyone to use this procedure on a database
> > they actually care about. This is a great party trick or something to
> > show off in a lightning talk at PGCon, not something you ought to be
> > doing with valuable data that you actually care about.
>
> Well, it does get used, and if we remove it perhaps we can have it on
> our wiki and point to it from our docs.

In my case, we performed some additional writes on the primary before
running "pg_upgrade -k" and we did it *after* we shut down all the
standbys. So those changes were not replicated and then "rsync --size-only"
ignored them. (By the way, that cluster has wal_log_hints=on to allow
Patroni run pg_rewind when needed.)

But this can happen with anyone who follows the procedure from the docs as
is and doesn't do any additional steps, because in step 9 "Prepare for
standby server upgrades":

1) there is no requirement to follow specific order to shut down the nodes
- "Streaming replication and log-shipping standby servers can remain
running until a later step" should probably be changed to a
requirement-like "keep them running"

2) checking the latest checkpoint position with pg_controldata now looks
like a thing that is good to do, but with uncertainty purpose -- it does
not seem to be used to support any decision
- "There will be a mismatch if old standby servers were shut down before
the old primary or if the old standby servers are still running" should
probably be rephrased saying that if there is mismatch, it's a big problem

So following the steps as is, if some writes on the primary are not
replicated (due to whatever reason) before execution of pg_upgrade -k +
rsync --size-only, then those writes are going to be silently lost on
standbys.

I wonder, if we ensure that standbys are fully caught up before upgrading
the primary, if we check the latest checkpoint positions, are we good to
use "rsync --size-only", or there are still some concerns? It seems so to
me, but maybe I'm missing something.

> --

Thanks,
Nikolay Samokhvalov
Founder, Postgres.ai