Re: Streaming replication upgrade sanity check

On Thu, Mar 11, 2021 at 03:53:07PM -0600, tsuraan wrote:
> > Did you see the pg_upgrade instructions on upgrading standby servers?
> > Why are you not using that?
>
> Specifically referring to [0], the part I can't do (and that I

Yes, that is what I was referring to.

> unfortunately left out of my initial email) is right at the beginning,
> "Do not start any servers yet". The master needs to get running again
> as soon as it's upgraded, and the standby systems can't stop the
> master for their upgrades. I believe what I've described is
> essentially the documented approach, but with a running master and
> then wrapping the rsync command in pg_start_backup/pg_stop_backup
> calls, but is it still a correct way to upgrade? From the
> pg_start_backup description on the binary replication tutorial [1], it
> looks like it should be, but I'd like to know if I'm missing anything.

Those documented instructions are very specific about what is possible,
e.g.:

What this does is to record the links created by pg_upgrade's link mode
that connect files in the old and new clusters on the primary server. It
then finds matching files in the standby's old cluster and creates links
for them in the standby's new cluster. Files that were not linked on the
primary are copied from the primary to the standby. (They are usually
small.) This provides rapid standby upgrades. Unfortunately, rsync
needlessly copies files associated with temporary and unlogged tables
because these files don't normally exist on standby servers.

Anything that doesn't match this might lead to disaster. Specifically,
you can't use the simplistic rsync arguments listed there since they
asssume we don't need to check if the files are the same. When doing
this on a running primary, you effectively will be copying all of the
system tables over, which is fine, but for the user heap/index files, it
will not be able to use links because they can change on a running
system, so you really are doing a streaming backup, and you just happen
to have a cluster there. I think will just end up copying all the
heap/index files into the empty new cluster directory, so you might as
well just copy the cluster directory in a more simple way.

You really need to backward and look at the file copy requirements for
pg_backup() and see what is possible. We can discuss what is possible,
but I can't think of any shortcuts given your requirements. I wonder if
you just need to use logical replication here.

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

The usefulness of a cup is in its emptiness, Bruce Lee