Horton

Thread

  1. Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

    Subhash Udata <subhashudata@gmail.com> — 2024-11-24T16:05:15Z

    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

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-11-24T16:18:49Z

    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

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

    Subhash Udata <subhashudata@gmail.com> — 2024-11-24T16:36:14Z

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

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-11-24T16:41:29Z

    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

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

    Ron <ronljohnsonjr@gmail.com> — 2024-11-24T16:47:32Z

    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!

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

    Ron <ronljohnsonjr@gmail.com> — 2024-11-24T16:49:15Z

    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/>
> >
> > 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!

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

    Subhash Udata <subhashudata@gmail.com> — 2024-11-24T16:51:37Z

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

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

    Ron <ronljohnsonjr@gmail.com> — 2024-11-24T16:57:08Z

    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!

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

    Subhash Udata <subhashudata@gmail.com> — 2024-11-24T17:03:58Z

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

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

    Christophe Pettus <xof@thebuild.com> — 2024-11-24T17:04:03Z

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

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

    Christophe Pettus <xof@thebuild.com> — 2024-11-24T17:06:09Z

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

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

    Ron <ronljohnsonjr@gmail.com> — 2024-11-24T17:15:27Z

    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!

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

    Subhash Udata <subhashudata@gmail.com> — 2024-11-24T17:16:21Z

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

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

    Christophe Pettus <xof@thebuild.com> — 2024-11-24T19:54:52Z

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

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

    Ron <ronljohnsonjr@gmail.com> — 2024-11-24T21:00:08Z

    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!

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-11-24T21:58:01Z

    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

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

    Ron <ronljohnsonjr@gmail.com> — 2024-11-24T22:15:18Z

    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!

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

    Ilya Anfimov <ilan@tzirechnoy.com> — 2024-11-25T09:48:51Z

    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

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

    Subhash Udata <subhashudata@gmail.com> — 2024-11-26T04:08:26Z

    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:

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