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

Dear Anton

I need the type of backup that I can take from primary to replica regular
backup or base backup or copy data director.

Thanks

في الثلاثاء، ٧ يناير ٢٠٢٥ ٥:٤١ م Dischner, Anton <
Anton(dot)Dischner(at)med(dot)uni-muenchen(dot)de> كتب:

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