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

On Thu, Jun 29, 2023 at 02:38:58PM -0400, Robert Haas wrote:
> On Thu, Jun 29, 2023 at 1:50 PM Nikolay Samokhvalov <nik(at)postgres(dot)ai> wrote:
> > Does this make sense or I'm missing something and the current docs describe a reliable process? (As I said, we have deviated from the process, to involve logical replication, so I'm not 100% sure I'm right suspecting the original procedure in having standby corruption risks.)
>
> I'm very suspicious about this section of the documentation. It
> doesn't explain why --size-only is used or why --no-inc-recursive is
> used.

I think --size-only was chosen only because it is the minimal comparison
option.

> > > 9. Prepare for standby server upgrades
> > > If you are upgrading standby servers using methods outlined in section Step 11, verify that the old standby servers are caught up by running pg_controldata against the old primary and standby clusters. Verify that the “Latest checkpoint location” values match in all clusters. (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.) Also, make sure wal_level is not set to minimal in the postgresql.conf file on the new primary cluster.
> >
> > – admitting that there might be mismatch. But if there is mismatch, rsync --size-only is not going to help synchronize properly, right?
>
> I think the idea is that you shouldn't use the procedure in this case.
> But honestly I don't think it's probably a good idea to use this
> procedure at all. It's not clear enough under what circumstances, if
> any, it's safe to use, and there's not really any way to know if
> you've done it correctly. You couldn't pay me enough to recommend this
> procedure to anyone.

I think it would be good to revisit all the steps outlined in that
procedure and check which ones are still valid or need adjusting. It is
very possible the original steps have bugs or that new Postgres features
added since the steps were created don't work with these steps. I think
we need to bring Stephen Frost into the discussion, so I have CC'ed him.

Frankly, I didn't think the documented procedure would work either, but
people say it does, so it is in the docs. I do think it is overdue for
a re-analysis.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.