Date: Wed, 9 Nov 2022 17:13:42 +0000
From: Sad Clouds <cryintothebluesky@gmail.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: pg_upgrade questions
Message-Id: <20221109171342.2d5f1d365efa709c5200d090@gmail.com>
In-Reply-To: 
 <CAKFQuwYzJXo5rgm1QRsfhxnym0Vie0_k+dD0YR6gwkJW7eA4RA@mail.gmail.com>
References: <20221109142510.2b528bd331c5d9482c59291d@gmail.com>
	<CAKFQuwYzJXo5rgm1QRsfhxnym0Vie0_k+dD0YR6gwkJW7eA4RA@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20221109171342.2d5f1d365efa709c5200d090%40gmail.com>
Precedence: bulk

On Wed, 9 Nov 2022 09:21:57 -0700
"David G. Johnston" <david.g.johnston@gmail.com> wrote:

> I cannot tell if you are talking about physical or logical
> replication...but only the former matters for this purpose.  I don't know
> why you'd want to disable replication, what you need to do is ensure that
> there is zero activity on the primary and that the standby has caught up
> applying all WAL that has already been generated (and no WAL is pending).
> Hence the requirement to check pg_controldata.
> 
> Step 11 covers your options.  I don't suggest you invent your own.
> 
> rsync is the means by which you avoid "large data transfers".
> 
> David J.

Hi David, thanks for your reply. Yes I was referring to physical
replication. Step 11 in the pg_upgrade guide implies that the primary
server has to be upgraded first, then rsync copies upgraded files and
links to the standby server. For various reasons, it would be
desirable for me to upgrade the standby server first, while the primary
database is fully up and running. I don't have much control over the
applications using the primary data base and cannot make them wait
unspecified amount of time until no WAL is pending on the standby. The
outage during database upgrade needs to be absolutely minimal, hence
the use of --link option.

I'm not an expert on PostgreSQL replication, so was hoping there was a
way to upgrade standby, while primary is modifying data, then later
upgrade primary and tell it to synchronize any new data to standby.
Maybe there is a way to pause replication and leave both servers in a
consistent state? Can any outstanding WAL on primary be automatically
replayed to standby?

But, if I understand you correctly, then this is not going to work at
all. I may need to discard all data on the standby and restart
replication from empty.