Re: Streaming replication upgrade sanity check

Greetings,

* Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> On Thu, Mar 11, 2021 at 07:50:16PM -0500, Stephen Frost wrote:
> > > So, my thought is to just upgrade the active side as normal, and once
> > > that's done and the master is back in use, stop the standby's
> > > postgres, call "pg_start_backup(...)" on the master, rsync the changed
> > > files from the running master's data dir to the standby's 10.x data
> > > directory, call "pg_stop_backup()", migrate the recovery.conf into a
> > > postgresql.auto.conf, touch the standby.signal file, and then start
> > > the new postgres 13 on the standby. That seems to work, but I want to
> > > be sure I'm not just having good luck due to the relatively low
> > > activity on my test systems.
> >
> > Exactly how are you doing this rsync..? A simplistic rsync would end up
> > just copying everything over and, as Bruce says later, if you're doing
> > that then you might as well just use pg_basebackup or a similar tool to
> > do it cleanly. In other words, I don't think that you're actually
> > getting the benefit that you think you are with this.. Note that the
> > file names are not kept the same after the pg_upgrade...
> >
> > Also- I don't think you realize how fast the rsync process to update the
> > standbys will be. Done correctly, it should be faster than the
> > pg_upgrade.. Note that you'll want to make sure you TRUNCATE and
>
> He/she wants to run the standby, I guess in read-only mode, while
> pg_upgrade is running, which is why I didn't even bother to mention
> that. I think if downtime is the critical for this person, he/she
> should be using logical replication for the upgrade. pg_upgrade really
> wants full control of the primary/standbys during its operation, and if
> you can't do that, pg_upgrade is not the right tool to use.

What I typically do in that case is just spin up more replicas and let
those handle the read load while the upgrade happens, and then throw
them away after the upgrade of the primary plus the other standbys is
done. I do agree that logical replication could be an alternative but
that takes a lot longer and puts a number of constraints on what you can
do while it's going on (DDL changes and such have to be done carefully,
etc).

> > unlogged tables before the pg_upgrade, et al, otherwise you'll end up
> > rsync'ing those over. Also make sure you haven't got any stray or
> > forgotten temp files or other things. Again, done properly, the rsync
> > to upgrade the standbys should only be copying the catalog tables
> > themselves and it should be quite fast.
>
> Yes, good points.

Yeah.. Would really be nice if we had a custom written tool which knew
how to detect unlogged tables and not try to copy them over and such.
Might be able to make something that's faster and less error-prone than
the rsync approach since we know a lot more about the PG data dir than
rsync does.

Thanks,

Stephen