public inbox for [email protected]
help / color / mirror / Atom feedQuestions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
9+ messages / 5 participants
[nested] [flat]
* Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
@ 2024-11-24 16:05 Subhash Udata <[email protected]>
2024-11-24 16:18 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Adrian Klaver <[email protected]>
2024-11-24 16:47 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ron Johnson <[email protected]>
2024-11-25 09:48 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ilya Anfimov <[email protected]>
0 siblings, 3 replies; 9+ messages in thread
From: Subhash Udata @ 2024-11-24 16:05 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 16:05 Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
@ 2024-11-24 16:18 ` Adrian Klaver <[email protected]>
2024-11-24 16:36 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
2 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2024-11-24 16:18 UTC (permalink / raw)
To: Subhash Udata <[email protected]>; pgsql-general
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
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 16:05 Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
2024-11-24 16:18 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Adrian Klaver <[email protected]>
@ 2024-11-24 16:36 ` Subhash Udata <[email protected]>
2024-11-24 16:41 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Adrian Klaver <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Subhash Udata @ 2024-11-24 16:36 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general
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 <[email protected]>
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
> [email protected]
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 16:05 Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
2024-11-24 16:18 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Adrian Klaver <[email protected]>
2024-11-24 16:36 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
@ 2024-11-24 16:41 ` Adrian Klaver <[email protected]>
2024-11-24 16:51 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2024-11-24 16:41 UTC (permalink / raw)
To: Subhash Udata <[email protected]>; +Cc: pgsql-general
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 <[email protected]
> <mailto:[email protected]>> 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
> [email protected] <mailto:[email protected]>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 16:05 Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
2024-11-24 16:18 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Adrian Klaver <[email protected]>
2024-11-24 16:36 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
2024-11-24 16:41 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Adrian Klaver <[email protected]>
@ 2024-11-24 16:51 ` Subhash Udata <[email protected]>
2024-11-24 17:04 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Christophe Pettus <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Subhash Udata @ 2024-11-24 16:51 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general
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 <[email protected]>
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 <[email protected]
> > <mailto:[email protected]>> 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
> > [email protected] <mailto:[email protected]>
> >
>
> --
> Adrian Klaver
> [email protected]
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 16:05 Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
2024-11-24 16:18 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Adrian Klaver <[email protected]>
2024-11-24 16:36 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
2024-11-24 16:41 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Adrian Klaver <[email protected]>
2024-11-24 16:51 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
@ 2024-11-24 17:04 ` Christophe Pettus <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Christophe Pettus @ 2024-11-24 17:04 UTC (permalink / raw)
To: Subhash Udata <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
> On Nov 24, 2024, at 08:51, Subhash Udata <[email protected]> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 16:05 Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
@ 2024-11-24 16:47 ` Ron Johnson <[email protected]>
2 siblings, 0 replies; 9+ messages in thread
From: Ron Johnson @ 2024-11-24 16:47 UTC (permalink / raw)
To: pgsql-general
On Sun, Nov 24, 2024 at 11:05 AM Subhash Udata <[email protected]>
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!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 16:05 Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
@ 2024-11-25 09:48 ` Ilya Anfimov <[email protected]>
2024-11-26 04:08 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
2 siblings, 1 reply; 9+ messages in thread
From: Ilya Anfimov @ 2024-11-25 09:48 UTC (permalink / raw)
To: [email protected]; pgsql-general
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 16:05 Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
2024-11-25 09:48 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ilya Anfimov <[email protected]>
@ 2024-11-26 04:08 ` Subhash Udata <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Subhash Udata @ 2024-11-26 04:08 UTC (permalink / raw)
To: Ilya Anfimov <[email protected]>; [email protected]; pgsql-general
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 protected]> 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
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-11-26 04:08 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-24 16:05 Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Subhash Udata <[email protected]>
2024-11-24 16:18 ` Adrian Klaver <[email protected]>
2024-11-24 16:36 ` Subhash Udata <[email protected]>
2024-11-24 16:41 ` Adrian Klaver <[email protected]>
2024-11-24 16:51 ` Subhash Udata <[email protected]>
2024-11-24 17:04 ` Christophe Pettus <[email protected]>
2024-11-24 16:47 ` Ron Johnson <[email protected]>
2024-11-25 09:48 ` Ilya Anfimov <[email protected]>
2024-11-26 04:08 ` Subhash Udata <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox