On Sun, Nov 24, 2024 at 09:35:15PM +0530, Subhash Udata wrote: > Dear PostgreSQL Community, > > I have a production database setup with a primary server and a standby > server. The database is currently running on PostgreSQL 15.0, and I plan > to upgrade both servers to 15.9. > > I have the following questions regarding the upgrade and replication > process: > > 1. Upgrade and Replication Compatibility: > > * My plan is to perform a failover, promote the standby server > (currently 15.0) to primary, and then upgrade the old primary > server to version 15.9.
1) Why do you want to use a switchover first? You can upgrade the standby, then switchover to it. (You could even don't switchover back, when the old primary would be upgraded and synchonized).
> * After upgrading the old primary server to version 15.9, I want to > configure it as a standby server and set up streaming replication > with the new primary server, which will still be running version > 15.0. > * Is it possible to establish streaming replication between these > two versions (15.0 as primary and 15.9 as standby)? > 2. Efficient Replication Setup: > > * The production database is around 1TB in size, and creating > replication using pg_basebackup is taking more than 2-3 hours to > complete. > * Is there an alternative method to set up replication without > taking a full backup of the entire cluster but instead using only > the WAL files that have changed on both servers?
Well, there are some.
pg_rewind is one of those (you should keep all the WAL files be- tween switchover point and now on both servers. Also, maximum one switchover/failover AFAIK. Also, it's a bit fragile nevertheless, bad things could happen if you mix timelines from the very straight scenario of one switchover+pg_rewind on the old prima- ry).
This requires some manual commands, writing backup_label and so on -- but looks more straightforward to me. (And yes, rsync uses block-level comparision and transfers only change blocks. setting block-size to 8k in rsync could be beneficial).
> > Your guidance and recommendations on these questions will be greatly > appreciated. > > Thank you for your time and support! > > Best regards, > > Subhash
