Date: Thu, 11 Mar 2021 19:55:57 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Stephen Frost <sfrost@snowman.net>
Cc: tsuraan <tsuraan@gmail.com>, pgsql-admin@postgresql.org
Subject: Re: Streaming replication upgrade sanity check
Message-ID: <20210312005557.GA2999@momjian.us>
References: 
 <CALKcMwLoZajZ8_8OJefNgaCiaXa0MsbDFiiWN-nYyGD-9gAbiA@mail.gmail.com>
 <20210312005016.GV20766@tamriel.snowman.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20210312005016.GV20766@tamriel.snowman.net>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20210312005557.GA2999%40momjian.us>
Precedence: bulk

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.

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

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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