Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

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