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

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

On Sun, 24 Nov 2024 at 21:48, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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/&gt;

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/&gt;

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&gt;

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 Sun, Nov 24, 2024 at 11:05 AM Subhash Udata <subhashudata@gmail.com>
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.

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.

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

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!

On Sun, Nov 24, 2024 at 11:41 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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/&gt;

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:

OP might not be able to tolerate any downtime.

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

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.

On Sun, 24 Nov 2024 at 22:11, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On Sun, Nov 24, 2024 at 11:52 AM Subhash Udata <subhashudata@gmail.com>
wrote:

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.

A comment and a question:
1) pg_basebackup runs just fine from cron. Thus, "time-consuming" (which
you described as 2-3 hours) isn't that critical.
2) What do you mean by resource-intensive? If it means network bandwidth,
then read the pg_basebackup man page.

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

Thank you for your valuable suggestion!

I have a question regarding the process:
When we shut down the standby, upgrade it, and then start it back up, will
the replication automatically resume from the primary to the standby?

Looking forward to your clarification.

2) What do you mean by resource-intensive? If it means network bandwidth,
then read the pg_basebackup man page.
No, it’s not about pg_basebackup consuming resources. What I meant is
that in the event of a failover, if we need to bring the standby back
online, the process of running pg_basebackup takes a significant amount of
time. However, if using a cron job for this purpose is a viable option,
then that would be acceptable.

On Sun, 24 Nov 2024 at 22:27, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

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

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 the primary 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 will want to make sure your wal_keep_size parameter is set high enough that required WAL segments aren't recycled during the standby's downtime.

On Sun, Nov 24, 2024 at 12:06 PM Christophe Pettus <xof@thebuild.com> wrote:

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 the primary 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 will want to make sure your wal_keep_size parameter is
set high enough that required WAL segments aren't recycled during the
standby's downtime.

Doesn't the existence of a replication slot force PG to retain WAL files
when replication is broken?

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

Thank you, everyone, for your valuable clarifications and insights. Your
guidance has been incredibly helpful in addressing my concerns and
understanding the best approach to proceed.

I truly appreciate the time and effort you’ve taken to assist me.

On Sun, 24 Nov 2024 at 22:36, Christophe Pettus <xof@thebuild.com> wrote:

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 with binary replication as with logical replication.

On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus <xof@thebuild.com> wrote:

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

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

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 4:58 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

Nagios has built-in disk space monitoring, and if it doesn't also have
built-in replication monitoring, you can write a plug-in. Or write your
own bash script that periodically runs "SELECT * from
pg_replication_slots;" and "SELECT * FROM pg_stat_replication;" on the
primary and "SELECT * FROM pg_stat_wal_receiver;" on the secondary.

Whichever you do, some monitoring should always be in place.

"

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

Logical replication is off-topic for this problem, no?

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

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

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

On Mon, 25 Nov 2024 at 15:19, Ilya Anfimov <ilan@tzirechnoy.com> wrote: