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