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


--QvkzFDse7UbGiC8D
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Greetings,

* tsuraan (tsuraan@gmail.com) wrote:
> I have several streaming replication pairs running under postgres 10,
> and I'm looking at upgrading them to postgres 13. Doing that on the
> active side is pretty quick and easy: just run pg_upgrade once for
> each of versions 11, 12, and 13, then start things up again. That's
> tested (on non-replicated instances) and seems to be working great.
> The standby side looks a little bit more interesting, so I wanted some
> feedback about whether my approach looks safe.

You don't need to go to 11 or 12 if you want to upgrade to 13- you can
just pg_upgrade directly to 13.

> 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
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.

Thanks,

Stephen

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

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

iQIcBAEBCgAGBQJgSrrIAAoJEO1sijiDR2RVIwYQAMRzzh6C5ICbxSEdjrmuJ2gi
qqYnG5+4kEGvr9xwymavrB+GBf5rt9AW+oTFV1osAybSsfY19wpO6PIIT1p6QDZz
mAs9R9rp90I0cS20DRJ2XE08S2M0M5WTGwrA2Sm0GjyCQkm6PBKaRvhX3/7tRr0S
WMeg3KzVzQMQe+c8sgdztNHY4BPBK0Wvlu+Q+i6YqVztccrbQWMWvfvU5BU3IVQZ
zGz+bdvD3us/jfhGaMzfl4F0axEP4L8mieygGgh50YDeAtgsMOC9jsntOhNkjIXj
9tFdUcoqJj+IRQMSiyiRAYGFV6s9LOcWrwVORUWKdMaZUomCdI+UM+jugST/x7w7
h2OySlt1MylXClJrhlgz4duG2Tyi0ZQNTVS/6O5SkVwKXbFygi1CcEFcf5/7z6Kv
4p2vjh7UikCEC1YHO6/ViGJQ1XTitGCpZb+Bfe3xlhJlgKm4Ql7ElehlYNgpK/om
t5lMmoLLZlj0I71REmdgrnvMqWX85wJrMEIkuuzwysnO/tGp39UKxsTd64KTtCBH
yGHWrzWon1OQnPkKI+5oDdVpmbmKfpjc93kudHjmfF8aV4s5rTXvhBg1nyC54bu2
up0tIWq5zkFoPPLCK0HgvvQBkWcoVYNsAdZN3mosRRxJjSIOrsbuCPrcrIZQje1P
jtpaIm39ZV3xhU5uMo7n
=uat5
-----END PGP SIGNATURE-----

--QvkzFDse7UbGiC8D--