From: Stephen Frost <sfrost@snowman.net>
To: tsuraan <tsuraan@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Streaming replication upgrade sanity check
Date: Thu, 11 Mar 2021 19:50:16 -0500
Message-ID: <20210312005016.GV20766@tamriel.snowman.net> (raw)
In-Reply-To: <CALKcMwLoZajZ8_8OJefNgaCiaXa0MsbDFiiWN-nYyGD-9gAbiA@mail.gmail.com>
References: <CALKcMwLoZajZ8_8OJefNgaCiaXa0MsbDFiiWN-nYyGD-9gAbiA@mail.gmail.com>
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sfrost@snowman.net, tsuraan@gmail.com
Subject: Re: Streaming replication upgrade sanity check
In-Reply-To: <20210312005016.GV20766@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox