On Wed, 9 Nov 2022 09:21:57 -0700 "David G. Johnston" <david.g.johnston@xxxxxxxxx> 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.
