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

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

On Sun, Nov 24, 2024 at 11:52 AM Subhash Udata <[email-masked]>
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!

---

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

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 <[email-masked]> wrote:

>> 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:06:09 -0800

> On Nov 24, 2024, at 09:03, Subhash Udata <[email-masked]> 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.

---

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

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 <[email-masked]> 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.