This would help me. I will try out the pg_rewind and rsync options. On Mon, 25 Nov 2024 at 15:19, Ilya Anfimov <i...@tzirechnoy.com> wrote:
> 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). > > Hoewever, I'd usually use rsync+low-level backup protocol > > https://www.postgresql.org/docs/15/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP > > 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 > > >
