Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster

Hello,

Could you first clarify the reason for using 'pg_dump' in the upgrade
method?

In a PostgreSQL database environment managed by a Patroni cluster, if there
aren't overly complex extensions in use, you can replicate the database to
the new environment and then perform a failover during the upgrade process.
By using the 'pg_upgrade' method, you can first run it with the '--check'
flag to ensure compatibility, and then proceed with the upgrade.

Since the PostgreSQL cluster will be initialized with Patroni, there should
be no issues, and this approach will likely reduce your workload.

If your database is small or involves a highly complex structure, 'pg_dump'
is indeed an option. However, is it truly necessary? Testing this approach
would provide more clarity.

Dischner, Anton <Anton(dot)Dischner(at)med(dot)uni-muenchen(dot)de>, 7 Oca 2025 Sal, 18:41
tarihinde şunu yazdı:

> Hi,
>
> if you are using the same slow internet connection for WAL and
> data-transfer you might considering to use rsync which has a full set of
> throttling, resuming/ibcremential and as mentioned data-compression options
> so that you do not saturate you connection,
>
> BTW nice challenge,
>
> Best,
>
> Anton
>
> -----Ursprüngliche Nachricht-----
> Von: Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
> Gesendet: Dienstag, 7. Januar 2025 15:42
> An: kasem adel <kasemadel8(at)gmail(dot)com>
> Cc: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
> Betreff: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11
> Cluster
>
> > Are you mean do the following step:
> >
> > 1- upgrade primary node
> > 2- take base backup from new version in primary node and keep wal file
> for 3 days
> > 3- move backup by external hard disk to the replica node
> > 4- restore base backup to replica
> > 5- start replication to replicate delta from primary node.
> >
> > Please confirm if this the best approach and it will work without risk.
>
> Yes, that's what I meant. It will work, nothing is completely without risk
> ;-) Main thing is to make absolutely sure you don't lose WAL during that
> time period. If you could set up WAL archiving to push to the remote site,
> that would be great as then you could configure the remote to pull
> accumulated WAL locally instead of across the slow network link.
>
> But, just thought of this:
>
> - pg_upgrade both sides
> - with neither side running, rsync the data directories (bonus points for
> being paranoid and using -c)
> - fix up the standby flag on the standby
> - fix up the postgresql.conf -- for instance, standby config has been
> moved out of a separate file into the main one, so that you can have common
> config both sides now, with the only necessary difference being the standby
> flag
> - bring them up
>
> You could even try to figure out where the catalog tables are stored and
> only rsync those, since pg_)upgrade doesn't change the format of your data
> files. But personally, I wouldn't. I wouldn't want to introduce the
> possibility of error on my part, and the rsync checksum is a nice check
> that nothing has gotten corrupted over time from network or disk glitch.
> (Excluding disk glitch on the primary...)
>
> You may be wondering why not just pg_upgrade both sides? Well, pg_upgrade
> ourput should be deterministic, right? So if you make sure that clients are
> disconnected and standby is completely in sync before starting, why not?
> Maybe you could. But because it's not designed nor documented for that use,
> so although you likely could make it work, that's a dangerous path. The
> last thing you want to do is take your server down for this scheduled
> operation, and wind up at the end with an unusable standby.
>
>
>
>
>
>