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

From: Subhash Udata <[email-masked]>
Date: Sun, 24 Nov 2024 21:35:15 +0530

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

Your guidance and recommendations on these questions will be greatly
appreciated.

Thank you for your time and support!

Best regards,

Subhash

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 24 Nov 2024 08:18:49 -0800

On 11/24/24 08:05, Subhash Udata wrote:
> server. The database is currently running on *PostgreSQL 15.0*, and I 
> plan to upgrade both servers to *15.9*.
> 
>       * 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.
>       * 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.
> 
>       * 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?

Why?

15.0 --> 15.9(actually you want the latest release 15.10) is a minor 
upgrade it involves shutting down the servers installing the new version 
binaries on each and restarting them.

You should read:

https://www.postgresql.org/support/versioning/

It would be a good idea to go through the Release Notes here:

https://www.postgresql.org/docs/15/release.html

To see what changed.

-- 
Adrian Klaver
[email-masked]

---

From: Subhash Udata <[email-masked]>
Date: Sun, 24 Nov 2024 22:06:14 +0530

The reason to upgrade from 15.0 to 15.9 is this
https://www.postgresql.org/support/security/CVE-2024-10979/

Here it is mentioned that this vulnerability is fixed in 15.9
So our organization wants an upgrade from 15.0 to 15.9

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 24 Nov 2024 08:41:29 -0800

On 11/24/24 08:36, Subhash Udata wrote:
> <https://www.postgresql.org/support/security/CVE-2024-10979/>
> 

Sorry, I was not clear enough. When I said 'Why?' it was not referring 
to reason you wanted to upgrade, it was why go through the whole 
pg_basebackup process. Read this link:

for why that is not necessary.

> 
> On Sun, 24 Nov 2024 at 21:48, Adrian Klaver <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>     <https://www.postgresql.org/support/versioning/>
> 
>     <https://www.postgresql.org/docs/15/release.html>
> 
>     To see what changed.
> 
>      >
>     [email-masked] <mailto:[email-masked]>
>

---

From: Ron Johnson <[email-masked]>
Date: Sun, 24 Nov 2024 11:47:32 -0500

>       (currently 15.0) to primary, and then upgrade the old primary server to
Try to replicate from old->new version, because bug fixes in newer versions
might have broken something in new->old replication.

If you really can't tolerate any downtime, then shutdown and upgrade the
Secondary server from 15.0 to 15.10.  Once you start it back up,
replication from the still-15.0 primary will catch back up to the
now-patched Secondary.

Fail over to the Secondary (now new-Primary), and then patch old-Primary to
15.10.

>       configure it as a standby server and set up streaming replication with the
>       - Is it possible to establish streaming replication between these
>       - 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?
>
>
pg_rewind is probably what you want.  I've never used it, though.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Subhash Udata <[email-masked]>
Date: Sun, 24 Nov 2024 22:21:37 +0530

I understand your point and appreciate the clarification.

I have reviewed the references and now have a better understanding of the
minor upgrade process.

However, my concern lies in the fact that we are working with production
servers, where downtime is not acceptable.

Additionally, if a failover occurs due to a network issue or any other
disaster, setting up replication again requires running the pg_basebackup
command. For large databases, this process becomes a significant challenge,
as running pg_basebackup for the entire cluster can be time-consuming and
resource-intensive.

---

From: Christophe Pettus <[email-masked]>
Date: Sun, 24 Nov 2024 09:04:03 -0800

> On Nov 24, 2024, at 08:51, Subhash Udata <[email-masked]> wrote:
> However, my concern lies in the fact that we are working with production servers, where downtime is not acceptable.

There is no way to upgrade community PostgreSQL, either to a new minor version or a new major version, with absolute zero downtime.

To do a minor version upgrade such as this, the only thing that is required is to restart the server with the new binaries.  While this does require a service interruption, it's quite short, and is not significantly longer than the interruption required to do a failover.  You can do the primary and secondary in either order, although upgrading the primary first is probably the safest route.  You don't have to switch the primary / secondary roles in this case, nor rebuild the secondary server using pg_basebackup.

---

From: Ilya Anfimov <[email-masked]>
Date: Mon, 25 Nov 2024 12:48:51 +0300

On Sun, Nov 24, 2024 at 09:35:15PM +0530, Subhash Udata wrote:
>    server. The database is currently running on PostgreSQL 15.0, and I plan
>    to upgrade both servers to 15.9.
> 
>     1. Upgrade and Replication Compatibility:
> 

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

---

From: Subhash Udata <[email-masked]>
Date: Tue, 26 Nov 2024 09:38:26 +0530

This would help me. I will try out the pg_rewind and rsync options.

On Mon, 25 Nov 2024 at 15:19, Ilya Anfimov <[email-masked]> wrote:

> replication
> only