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

[Subhash Udata]

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

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

[Christophe Pettus]

> On Nov 24, 2024, at 08:51, Subhash Udata  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.

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

[Subhash Udata]

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

[Subhash Udata]

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

[Ron Johnson]

On Sun, Nov 24, 2024 at 12:06 PM 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.


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

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

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

[Christophe Pettus]

> 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

[Adrian Klaver]

On 11/24/24 13:00, Ron Johnson wrote:
On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus > wrote:


 > On Nov 24, 2024, at 09:15, Ron Johnson mailto:ronljohnso...@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 , and butter sauce.
Don't boil me, I'm still alive.
 lobster!


--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Ron Johnson]

On Sun, Nov 24, 2024 at 4:58 PM Adrian Klaver 
wrote:

> On 11/24/24 13:00, Ron Johnson wrote:
> > On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus  > > wrote:
> >
> >  > On Nov 24, 2024, at 09:15, Ron Johnson  > > 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 , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

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

[Ron Johnson]

On Sun, Nov 24, 2024 at 11:05 AM 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.
>
>
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 , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

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

[Ron Johnson]

On Sun, Nov 24, 2024 at 11:41 AM 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/
> > 
> >
> > 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 , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

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

[Subhash Udata]

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/
> > 
> >
> > 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  > > 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 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: JDBC using REFERENCES

[David G. Johnston]

On Sunday, November 24, 2024, Arbol One  wrote:

>
> * sql.append("CONSTRAIN id PRIMARY KEY (id)"); **<==
> Error*
>
>
>
The word constraint ends with a “T”

David J.

Re: JDBC using REFERENCES

[Rob Sargent]

> On Nov 24, 2024, at 7:15 PM, Arbol One  wrote:
> 
> 
> In an application that uses JDBC I am trying to use the following statement
> 
>// Table parameters
> var sql = new MyString();
> sql.setData("CREATE TABLE IF NOT EXISTS ");
> sql.append(this.tableName);
> sql.append(" (");
> sql.append("index SERIAL NOT NULL, ");
> sql.append("id TEXT PRIMARY KEY, ");
> sql.append("day TEXT NOT NULL, ");
> sql.append("month TEXT NOT NULL, ");
> sql.append("year TEXT NOT NULL, ");
> sql.append("hour TEXT NOT NULL, ");
> sql.append("minute TEXT NOT NULL, ");
> sql.append("second TEXT NOT NULL, ");
> sql.append("millisecond TEXT NOT NULL, ");
> sql.append("am_pm TEXT NOT NULL, ");
> sql.append("admin boolean NOT NULL DEFAULT false, ");
> sql.append("CONSTRAIN id PRIMARY KEY (id)"); <== Error
> sql.append(");");
> 
> but I get a message saying :

> 
> Exception in thread "main" java.sql.SQLException: In 
> company.contact.createTable()
> ERROR: syntax error at or near "("
>   Position: 315
> 

 Collapse all the append()s after rable e and see if parentheses are balanced.
> Does JDBC allow us to do this?
> What am I doing wrong?
> How to fix this
> 
> --
> ArbolOne ™
> Using Fire Fox and Thunderbird.
> ArbolOne is composed of students and volunteers dedicated to providing free 
> services to charitable organizations.
> ArbolOne's development on Java, PostgreSQL, HTML and Jakarta EE is in 
> progress [ í ]

Re: JDBC using REFERENCES

[David G. Johnston]

On Sunday, November 24, 2024, Arbol One  wrote:

>
>
>
>
> * sql.append("index SERIAL NOT NULL, ");
> sql.append("id TEXT PRIMARY KEY, "); sql.append("CONSTRAIN
> id PRIMARY KEY (id)"); **<== Error*
>
>
>
This is quite confusing, having the index column be serial but not PK (or
even unique…) while the id column is the PK, which you’ve redundantly
specified.

David J.

JDBC using REFERENCES

[Arbol One]

In an application that uses JDBC I am trying to use the following statement

*   // Table parameters
    var sql = new MyString();
    sql.setData("CREATE TABLE IF NOT EXISTS ");
    sql.append(this.tableName);
    sql.append(" (");
    sql.append("index SERIAL NOT NULL, ");
    sql.append("id TEXT PRIMARY KEY, ");
    sql.append("day TEXT NOT NULL, ");
    sql.append("month TEXT NOT NULL, ");
    sql.append("year TEXT NOT NULL, ");
    sql.append("hour TEXT NOT NULL, ");
    sql.append("minute TEXT NOT NULL, ");
    sql.append("second TEXT NOT NULL, ");
    sql.append("millisecond TEXT NOT NULL, ");
    sql.append("am_pm TEXT NOT NULL, ");
    sql.append("admin boolean NOT NULL DEFAULT false, ");
    sql.append("CONSTRAIN id PRIMARY KEY (id)"); *_<== Error_*
    sql.append(");");*

but I get a message saying :

Exception in thread "main" java.sql.SQLException: In 
company.contact.createTable()

ERROR: syntax error at or near "("
  Position: 315

Does JDBC allow us to do this?
What am I doing wrong?
How to fix this

--
*/ArbolOne ™/*
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing 
free services to charitable organizations.
ArbolOne's development on Java, PostgreSQL, HTML and Jakarta EE is in 
progress [ í ]

JDBC using REFERENCES

[Arbol One]

In an application that uses JDBC I am trying to use the following statement

*   // Table parameters
    var sql = new MyString();
    sql.setData("CREATE TABLE IF NOT EXISTS ");
    sql.append(this.tableName);
    sql.append(" (");
    sql.append("index SERIAL NOT NULL, ");
    sql.append("id TEXT PRIMARY KEY, ");
    sql.append("day TEXT NOT NULL, ");
    sql.append("month TEXT NOT NULL, ");
    sql.append("year TEXT NOT NULL, ");
    sql.append("hour TEXT NOT NULL, ");
    sql.append("minute TEXT NOT NULL, ");
    sql.append("second TEXT NOT NULL, ");
    sql.append("millisecond TEXT NOT NULL, ");
    sql.append("am_pm TEXT NOT NULL, ");
    sql.append("admin boolean NOT NULL DEFAULT false, ");
    sql.append("CONSTRAIN id PRIMARY KEY (id)"); *_<== Error_*
    sql.append(");");*

but I get a message saying :

Exception in thread "main" java.sql.SQLException: In 
company.contact.createTable()

ERROR: syntax error at or near "("
  Position: 315

Does JDBC allow us to do this?
What am I doing wrong?
How to fix this

--
*/ArbolOne ™/*
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing 
free services to charitable organizations.
ArbolOne's development on Java, PostgreSQL, HTML and Jakarta EE is in 
progress [ í ]

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

[Christophe Pettus]

> On Nov 24, 2024, at 09:15, Ron Johnson  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.

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

[Ron Johnson]

On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus  wrote:

> > On Nov 24, 2024, at 09:15, Ron Johnson  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 , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

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

[Adrian Klaver]

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

[Subhash Udata]

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

[Adrian Klaver]

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/ 



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



--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Ron Johnson]

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!

Re: Unique key constraint Issue

[ajit wangkhem]

  Output should be consistent across servers below query o/p

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'your table name'::regclass AND contype = 'u';
(different datatype combine in UK is not an issue). This issue may not
happen in asynchronous, log shipping or streaming replication. In logical
replication it may create problem.

On Mon, Nov 25, 2024 at 12:01 PM shashidhar Reddy <
shashidharreddy...@gmail.com> wrote:

> Hello,
>
> We have postgresql servers with PostgreSQL 13.10   on Ubuntu release 22.04.
>
> The issue is a unique key constraint with two columns one is character
> another is integer. At some point the unique key did not work as I see
> duplicate values with these two columns combination and it happened on
> multiple servers on multiple databases on same table with same unique key.
> I tried db dump and restore but the key is failing to create when restore
> with duplicate value errors.
>
>
>
>

Error creating materialized view

[Shaun Robinson]

Hi,

I'm currently testing an application with Postgres 17.2 and am getting an
error when creating a materialized view which works in version 16 and
below. The sql works fine running as a query, but adding the
create materialized view breaks it.

The error comes when calling a custom function and the error is that a
relation doesn't exist (which it does as it works within the same query
when not creating a view).

Is this a known issue in the version 17.2?

Thanks
Shaun

Pg_basebackup failed or completed?

[jayesh thakare]

Hi Team,

We have got below error while doing basebackup


pg_basebackup: could not close file "0002022E00027": No error


Can we assume that backup is completed successfully?


Regards,
Jayeshthakare
Postgresql DBA
8828986182

Unique key constraint Issue

[shashidhar Reddy]

Hello,

We have postgresql servers with PostgreSQL 13.10   on Ubuntu release 22.04.

The issue is a unique key constraint with two columns one is character
another is integer. At some point the unique key did not work as I see
duplicate values with these two columns combination and it happened on
multiple servers on multiple databases on same table with same unique key.
I tried db dump and restore but the key is failing to create when restore
with duplicate value errors.