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

On 11/24/24 08:05, 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.
>       * 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?

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.

> 
> Your guidance and recommendations on these questions will be greatly 
> appreciated.
> 
> Thank you for your time and support!
> 
> Best regards,
> 
> Subhash
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/24/24 08:36, Subhash Udata wrote:
> The reason to upgrade from 15.0 to 15.9 is this
> https://www.postgresql.org/support/security/CVE-2024-10979/ 
> <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

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:

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

for why that is not necessary.

> 
> On Sun, 24 Nov 2024 at 21:48, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 11/24/24 08:05, 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.
>      >       * 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?
> 
>     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/
>     <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
>     <https://www.postgresql.org/docs/15/release.html>
> 
>     To see what changed.
> 
>      >
>      > Your guidance and recommendations on these questions will be greatly
>      > appreciated.
>      >
>      > Thank you for your time and support!
>      >
>      > Best regards,
>      >
>      > Subhash
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

> On Nov 24, 2024, at 08:51, Subhash Udata <subhashudata@gmail.com> 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
zerodowntime. 

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
interruptionrequired to do a failover.  You can do the primary and secondary in either order, although upgrading the
primaryfirst is probably the safest route.  You don't have to switch the primary / secondary roles in this case, nor
rebuildthe secondary server using pg_basebackup. 

> On Nov 24, 2024, at 09:03, Subhash Udata <subhashudata@gmail.com> wrote:
> When we shut down the standby, upgrade it, and then start it back up, will the replication automatically resume from
theprimary to the standby? 

Assuming that the standby has access to any WAL generated during the shutdown (either still in the primary's WAL
directory,or via an archive using archive_command), yes.  If you are not using a WAL archive using archive_command, you
willwant to make sure your wal_keep_size parameter is set high enough that required WAL segments aren't recycled during
thestandby's downtime. 

> On Nov 24, 2024, at 09:15, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> Doesn't the existence of a replication slot force PG to retain WAL files when replication is broken?

It does.  I don't recall if the OP said that they were using a persistent replication slot or not; it's not as common
withbinary replication as with logical replication. 

On 11/24/24 13:00, Ron Johnson wrote:
> On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus <xof@thebuild.com 
> <mailto:xof@thebuild.com>> wrote:
> 
>      > On Nov 24, 2024, at 09:15, Ron Johnson <ronljohnsonjr@gmail.com
>     <mailto:ronljohnsonjr@gmail.com>> wrote:
>      >
>      > Doesn't the existence of a replication slot force PG to retain
>     WAL files when replication is broken?
> 
>     It does.  I don't recall if the OP said that they were using a
>     persistent replication slot or not; it's not as common with binary
>     replication as with logical replication.
> 
> 
> Really? I wonder why people fight with configuring max_wal_size and 
> wal_keep_size, when replication slots do all the work for you.

https://www.postgresql.org/docs/current/logicaldecoding-explanation.html

"
Caution

Replication slots persist across crashes and know nothing about the 
state of their consumer(s). They will prevent removal of required 
resources even when there is no connection using them. This consumes 
storage because neither required WAL nor required rows from the system 
catalogs can be removed by VACUUM as long as they are required by a 
replication slot. In extreme cases this could cause the database to shut 
down to prevent transaction ID wraparound (see Section 24.1.5). So if a 
slot is no longer required it should be dropped.
"

"
Caution

There is a chance that the old primary is up again during the promotion 
and if subscriptions are not disabled, the logical subscribers may 
continue to receive data from the old primary server even after 
promotion until the connection string is altered. This might result in 
data inconsistency issues, preventing the logical subscribers from being 
able to continue replication from the new primary server.
"

https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION

"
Caution

Beware that replication slots can cause the server to retain so many WAL 
segments that they fill up the space allocated for pg_wal. 
max_slot_wal_keep_size can be used to limit the size of WAL files 
retained by replication slots.
"

They have their issues also, namely they may not do all the work for you.


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

-- 
Adrian Klaver
adrian.klaver@aklaver.com

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