Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
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 wrote: > On Sun, Nov 24, 2024 at 11:52 AM Subhash Udata > 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 , and butter sauce. > Don't boil me, I'm still alive. > lobster! >
Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
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
Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
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 wrote: > > > > On Nov 24, 2024, at 09:03, Subhash Udata 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.
Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
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 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 > <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Clarification on CVE-2024-10979 and PostgreSQL Upgrade Necessity Without PL/Perl Usage
Dear PostgreSQL Community, I have a query related to the recent security vulnerability, *CVE-2024-10979*, concerning the PL/Perl extension. >From the advisory, it appears the vulnerability impacts systems utilizing the PL/Perl extension. My question is: - If we do not use the PL/Perl extension in our PostgreSQL instance, is it still necessary to upgrade to the patched version of PostgreSQL? Or can we safely continue using our current version without concern? We would like to understand whether this vulnerability has any implications for environments where the PL/Perl extension is not installed or used. Thank you so much for your guidance on this. Best regards, Subhash Udata
Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
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 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.kla...@aklaver.com > >
Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
This would help me. I will try out the pg_rewind and rsync options. On Mon, 25 Nov 2024 at 15:19, Ilya Anfimov 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 > > >
Re: CVE-2024-10979 Vulnerability Impact on PostgreSQL 11.10
Thank you for your detailed response. I would like to clarify my situation further to ensure I take the appropriate steps. Currently, my environment is running *PostgreSQL 15.0*. I understand that version *15.9* contains the fix for CVE-2024-10979, as mentioned in the release notes. Given that I am not using the *PL/Perl* extension in my environment, I wanted to ask: - Is it still mandatory to upgrade specifically to version *15.9*, or would remaining on version *15.0* suffice in this case? I appreciate your guidance on whether this upgrade is necessary, considering the specifics of my setup. Thank you for your time and support. On Fri, 22 Nov 2024 at 09:39, David G. Johnston wrote: > On Thursday, November 21, 2024, Subhash Udata > wrote: >> >> >> Thank you for your response regarding the affected versions of >> PostgreSQL. I have a follow-up question for clarification: >> >> The PostgreSQL documentation mentions that the versions with a fix for >> CVE-2024-10979 are *17.1, 16.5, 15.9, 14.14, 13.17, and 12.21*. However, >> your reply states that any version greater than 13+ should suffice. >> >> Could you please confirm if upgrading to one of the specific versions >> listed above is mandatory, or is it acceptable to upgrade to any version >> higher than 13 >> > > It was literally just reported and fixed. If you are on a supported > release of PostgreSQL you have the fix. If you are not, you don’t. > > At this point only major versions 13+ are supported. > > Upgrading to an unsupported minor release is never recommended. > > The fact you are on version 11 means you should not expect an answer to > the question whether this newly discovered CVE affects you - that would be > expecting support for a long-unsupported version. > > Which of the 5 currently supported releases you should upgrade to is a > decision you need to make given your circumstances. > > David J. > >
Re: CVE-2024-10979 Vulnerability Impact on PostgreSQL 11.10
Hi Adrian, Thank you for your response regarding the affected versions of PostgreSQL. I have a follow-up question for clarification: The PostgreSQL documentation mentions that the versions with a fix for CVE-2024-10979 are *17.1, 16.5, 15.9, 14.14, 13.17, and 12.21*. However, your reply states that any version greater than 13+ should suffice. Could you please confirm if upgrading to one of the specific versions listed above is mandatory, or is it acceptable to upgrade to any version higher than 13? Your guidance will help us determine the appropriate upgrade path for our environment. Thank you for your time and assistance. On Thu, 21 Nov 2024 at 12:24, Adrian Klaver wrote: > On 11/20/24 22:44, 김주연 wrote: > > Hello, I am currently using PostgreSQL 11.10 and would like to know if > > the CVE-2024-10979 vulnerability affects this version. > > Postgres 11 is past EOL, see: > > https://www.postgresql.org/support/versioning/ > > > > If it does impact my version, I would like to know which version I > > should upgrade to. > > Any version from 13+. > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > >
