Date: Thu, 11 Mar 2021 20:01:24 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Bruce Momjian <bruce@momjian.us>
Cc: tsuraan <tsuraan@gmail.com>, pgsql-admin@postgresql.org
Subject: Re: Streaming replication upgrade sanity check
Message-ID: <20210312010124.GW20766@tamriel.snowman.net>
References: 
 <CALKcMwLoZajZ8_8OJefNgaCiaXa0MsbDFiiWN-nYyGD-9gAbiA@mail.gmail.com>
 <20210312005016.GV20766@tamriel.snowman.net>
 <20210312005557.GA2999@momjian.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="TQUOdt8YtOe3Z2Q7"
Content-Disposition: inline
In-Reply-To: <20210312005557.GA2999@momjian.us>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20210312010124.GW20766%40tamriel.snowman.net>
Precedence: bulk


--TQUOdt8YtOe3Z2Q7
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Bruce Momjian (bruce@momjian.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.
> >=20
> > 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...
> >=20
> > 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
>=20
> 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.
>=20
> 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

--TQUOdt8YtOe3Z2Q7
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJgSr1kAAoJEO1sijiDR2RV2lcP+QFf+FVwoYvJGz8t6/d2Mqg2
xfsEnxDNmmRw96idihmMMjLHNWQlj07LOFM9olV0ojFimo3dj3VJOvaqGBvaGR18
7JB1deCKlWyPWutvjrnsOzFhFu0bIQysHflLTHkJ1AThuts1VqnuleBUU11anhsS
RIno8nNzN2lIdCtOjrRXNRBgbV3bAy/i0V2fI9Wgz+x2eHnLo5kartn/SW+Pi6p0
SrBtkzgnKg4SG6CjfUYipTittUAFt/s/YKAWTf0jx5+bx/KNNETwLZkCsJlG69w4
ILwkkWtSWYiPQ2JHlRK1qKSptAED1thxO+Zfyil3bvyiXKM1sJxiKeTXP5Gabt6n
16xImkl/15I6/fNU/sZVi2KGS4k5Lzsx6c6BsjI0RB/Rgtb7HBpYYoXStBN5s1wd
xK+mw0RIU09rkmQmfm0vejQ1qH8ZLO07VblCcwlHz5Ncfrft+CUoQ8g2UqXQk1VO
dAiOajps3DrJNbnTCofe5zcCMYDUlpamj2GZ54CdxeCCa2oxYNrW3A+Bf3pY8vKb
DPxxCTV5YYPo3INq2py27v3ye42xm5iW0OSnuHvlVJVKjY4u927y6xrhG4KjjUQ/
/kgA39KfxeJiv/sU3umnBuZr+PAwbKgKgOkwVfmqPIWVh5gHE/Comrf++vURXTtn
4g6GE9C2r5pw6gh9J0RP
=n+8i
-----END PGP SIGNATURE-----

--TQUOdt8YtOe3Z2Q7--