Subject: Upgrade from 12.22 to 16

From: Wasim Devale <[email-masked]>
Date: Wed, 11 Jun 2025 11:51:42 +0530

Hi All

Please suggest me to upgrade postgresql: 400 GB data with minimal downtime
apart from in place upgrade. We have it on red hat 9.

Thanks,
Wasim

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 11 Jun 2025 08:43:05 +0200

On Wed, 2025-06-11 at 11:51 +0530, Wasim Devale wrote:
> Please suggest me to upgrade postgresql: 400 GB data with minimal downtime apart
> from in place upgrade. We have it on red hat 9.

What do you mean with "apart from"?

Yours,
Laurenz Albe

---

From: Wasim Devale <[email-masked]>
Date: Wed, 11 Jun 2025 12:23:34 +0530

I mean to say that we do not want to do the in place upgrade also the other
option of taking dump and restoration of it. If I can have any robust plan
who all have worked on that then please suggest me.

Thanks,
Wasim

---------- Forwarded message ---------
From: Laurenz Albe <[email-masked]>
Date: Wed, 11 Jun, 2025, 12:13 pm
Subject: Re: Upgrade from 12.22 to 16
To: Wasim Devale <[email-masked]>, Pgsql-admin <
[email-masked]>

Yours,
Laurenz Albe

---

From: Domen Šetar <[email-masked]>
Date: Wed, 11 Jun 2025 06:57:08 +0000

You can use logical replication if new version of your postgres is on other host.

Best regards!
[izum]
Domen Šetar
Computer Systems Support
IZUM – Institute of Information Science | Prešernova ulica 17 | 2000 Maribor | Slovenia
T: +386 2 25 20 339 | M: +386 41 676 342 | www.izum.si<http://www.izum.si/> | [email-masked]<mailto:[email-masked]>

From: Wasim Devale <[email-masked]>
Sent: Wednesday, June 11, 2025 8:54 AM
To: Laurenz Albe <[email-masked]>
Cc: Pgsql-admin <[email-masked]>
Subject: Re: Upgrade from 12.22 to 16

I mean to say that we do not want to do the in place upgrade also the other option of taking dump and restoration of it. If I can have any robust plan who all have worked on that then please suggest me.

Thanks,
Wasim

---------- Forwarded message ---------
From: Laurenz Albe <[email-masked]<mailto:[email-masked]>>
Date: Wed, 11 Jun, 2025, 12:13 pm
Subject: Re: Upgrade from 12.22 to 16
To: Wasim Devale <[email-masked]<mailto:[email-masked]>>, Pgsql-admin <[email-masked]<mailto:[email-masked]>>

Yours,
Laurenz Albe

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 11 Jun 2025 09:38:47 +0200

On Wed, 2025-06-11 at 12:23 +0530, Wasim Devale wrote:
> I mean to say that we do not want to do the in place upgrade also the other option
> of taking dump and restoration of it. If I can have any robust plan who all have
> worked on that then please suggest me.

"I want to upgrade with minimal downtime, but I don't want the natural, simple
 solution for upgrade with minimal downtime."

Strange requirement, but if you feel you want to make your life more difficult, you
can use logical replication for an upgrade with very short downtime.

Yours,
Laurenz Albe

---

From: Ron Johnson <[email-masked]>
Date: Wed, 11 Jun 2025 09:38:53 -0400

On Wed, Jun 11, 2025 at 2:54 AM Wasim Devale <[email-masked]> wrote:

> I mean to say that we do not want to do the in place upgrade
>
Why not?  Using the default --copy option, there's minimal downtime, and
the old database is still there in case you need to fall back, etc.

> also the other option of taking dump and restoration of it. If I can have
> any robust plan who all have worked on that then please suggest me.
>
> Thanks,
> Wasim
>

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

---

From: Zaid Shabbir <[email-masked]>
Date: Wed, 11 Jun 2025 20:15:16 +0500

Hello Wasim,

Minimum downtime can be achieved in a couple of ways, depending entirely on
the specific requirements. If you're considering using logical replication
to achieve this, the following article will definitely help you.

https://www.pgedge.com/blog/always-online-or-bust-zero-downtime-major-version-postgres-upgrades

Thanks & Regards
Zaid

---

From: Ron Johnson <[email-masked]>
Date: Wed, 11 Jun 2025 11:29:01 -0400

> You can use logical replication if new version of your postgres is on

Are you sure?  There _shouldn't_ be any problem with replicating to a new
instance on the same machine.

---

From: Wasim Devale <[email-masked]>
Date: Sat, 14 Jun 2025 20:23:39 +0530

Thanks everyone.

Is there the restriction in logical replication because the database we
have has all of it shown in the screenshot.

---

From: Frank Lanitz <[email-masked]>
Date: Sat, 14 Jun 2025 21:49:12 +0200

Hi,

> have has all of it shown in the screenshot.

The manual is having a good list of restrictions.
-> 
https://www.postgresql.org/docs/current/logical-replication-restrictions.html

Cheers,
Frank

---

From: "DINESH NAIR" <[email-masked]>
Date: Mon, 16 Jun 2025 04:43:00 +0000

Hope this  below  mentionsed on the restrictions  to be useful on the database schema and DDL commands are not replicated

https://www.postgresql.org/docs/current/logical-replication-restrictions.html
[https://www.postgresql.org/media/img/about/press/elephant.png]<https://www.postgresql.org/docs/current/logical-replication-restrictions.html>
PostgreSQL: Documentation: 17: 29.7. Restrictions<https://www.postgresql.org/docs/current/logical-replication-restrictions.html>
29.7.&nbsp;Restrictions # Logical replication currently has the following restrictions or missing functionality. These might be addressed in future releases. The …
www.postgresql.org
Another useful link on  enabling replication

https://dev.to/raselmahmuddev/how-to-set-up-postgresql-logical-replication-use-cases-and-step-by-step-guide-536b
[https://media2.dev.to/dynamic/image/width=1000,height=500,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2F6xwqgp7n6za30dhas79o.png]<https://dev.to/raselmahmuddev/how-to-set-up-postgresql-logical-replication-use-cases-and-step-by-step-guide-536b>
How to Set Up PostgreSQL Logical Replication: Use Cases and Step-by-Step Guide.<https://dev.to/raselmahmuddev/how-to-set-up-postgresql-logical-replication-use-cases-and-step-by-step-guide-536b>
Real-World Use Case 2: E-commerce Platforms Use Case: An e-commerce platform uses logical replication to keep the data in sync between the main database (handling customer transactions) and a separate reporting database (used for analytics).This ensures that the system can generate real-time reports and insights without affecting the performance of the website or app.
dev.to

________________________________
From: Wasim Devale <[email-masked]>
Sent: Saturday, June 14, 2025 8:23 PM
To: Ron Johnson <[email-masked]>
Cc: pgsql-admin <[email-masked]>; Pgsql-admin <[email-masked]>
Subject: Re: Upgrade from 12.22 to 16

You don't often get email from [email-masked]. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments.

Thanks everyone.

Is there the restriction in logical replication because the database we have has all of it shown in the screenshot.

On Wed, 11 Jun, 2025, 8:59 pm Ron Johnson, <[email-masked]<mailto:[email-masked]>> wrote:
On Wed, Jun 11, 2025 at 2:57 AM Domen Šetar <[email-masked]<mailto:[email-masked]>> wrote:

Are you sure?  There _shouldn't_ be any problem with replicating to a new instance on the same machine.

---

From: "Gaspare Boscarino, P.Eng." <[email-masked]>
Date: Mon, 16 Jun 2025 11:10:22 -0700

Hello Wasim,

I suggest the logical replication approach. As mentioned before in this
thread, there are some limitations like DDL propagation, the need for
primary keys, etc. However, those constraints are not problematic and if
planned properly the result is excellent. I have used logical replication
many times for databases with TB of data.

Thank you.

Regards,

Gaspare

On Sun, Jun 15, 2025 at 9:43 PM DINESH NAIR <[email-masked]>
wrote:

> Hope this  below  mentionsed on the restrictions  to be useful on the
> database schema and DDL commands are not replicated
>
>
> <https://www.postgresql.org/docs/current/logical-replication-restrictions.html>
> PostgreSQL: Documentation: 17: 29.7. Restrictions
> <https://www.postgresql.org/docs/current/logical-replication-restrictions.html>
> 29.7.&nbsp;Restrictions # Logical replication currently has the following
> restrictions or missing functionality. These might be addressed in future
> releases. The …
> www.postgresql.org
> <https://dev.to/raselmahmuddev/how-to-set-up-postgresql-logical-replication-use-cases-and-step-by-step-guide-536b>
> How to Set Up PostgreSQL Logical Replication: Use Cases and Step-by-Step
> Guide.
> <https://dev.to/raselmahmuddev/how-to-set-up-postgresql-logical-replication-use-cases-and-step-by-step-guide-536b>
> Real-World Use Case 2: E-commerce Platforms Use Case: An e-commerce
> platform uses logical replication to keep the data in sync between the main
> database (handling customer transactions) and a separate reporting database
> (used for analytics).This ensures that the system can generate real-time
> reports and insights without affecting the performance of the website or
> app.
> dev.to
>
>
>
>
> ------------------------------
> *From:* Wasim Devale <[email-masked]>
> *Sent:* Saturday, June 14, 2025 8:23 PM
> *To:* Ron Johnson <[email-masked]>
> *Cc:* pgsql-admin <[email-masked]>; Pgsql-admin <
> *Subject:* Re: Upgrade from 12.22 to 16
>
> You don't often get email from [email-masked]. Learn why this is
> important <https://aka.ms/LearnAboutSenderIdentification>
> Caution: This email was sent from an external source. Please verify the
> sender’s identity before clicking links or opening attachments.
>
> Thanks everyone.
>

-- 
Gaspare Boscarino, P.Eng., M.Eng., MASc.
Founder and CEO
*Theorema Systems Inc.*
www.theoremasystems.com | +1 604-765-0121

---

From: David Okeamah <[email-masked]>
Date: Mon, 16 Jun 2025 18:12:30 +0000

Can you help out

DAVID OKEAMAH,DEVELOPER
________________________________
From: Gaspare Boscarino, P.Eng. <[email-masked]>
Sent: Monday, June 16, 2025 7:10:22 PM
To: DINESH NAIR <[email-masked]>
Cc: Wasim Devale <[email-masked]>; Ron Johnson <[email-masked]>; pgsql-admin <[email-masked]>; Pgsql-admin <[email-masked]>
Subject: Re: Upgrade from 12.22 to 16

Hello Wasim,

I suggest the logical replication approach. As mentioned before in this thread, there are some limitations like DDL propagation, the need for primary keys, etc. However, those constraints are not problematic and if planned properly the result is excellent. I have used logical replication many times for databases with TB of data.

Thank you.

Regards,

Gaspare

On Sun, Jun 15, 2025 at 9:43 PM DINESH NAIR <[email-masked]<mailto:[email-masked]>> wrote:
Hope this  below  mentionsed on the restrictions  to be useful on the database schema and DDL commands are not replicated

www.postgresql.org<http://www.postgresql.org>

dev.to<http://dev.to>

________________________________
From: Wasim Devale <[email-masked]<mailto:[email-masked]>>
Sent: Saturday, June 14, 2025 8:23 PM
To: Ron Johnson <[email-masked]<mailto:[email-masked]>>
Cc: pgsql-admin <[email-masked]<mailto:[email-masked]>>; Pgsql-admin <[email-masked]<mailto:[email-masked]>>
Subject: Re: Upgrade from 12.22 to 16

You don't often get email from [email-masked]<mailto:[email-masked]>. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments.

Thanks everyone.

--
Gaspare Boscarino, P.Eng., M.Eng., MASc.
Founder and CEO
Theorema Systems Inc.
www.theoremasystems.com<http://www.theoremasystems.com> | +1 604-765-0121

---

From: "Gaspare Boscarino, P.Eng." <[email-masked]>
Date: Mon, 16 Jun 2025 11:30:58 -0700

Hello David,

I can help but I am not sure if this is the right channel for further
communication.

Thank you.

Regards,

Gaspare

On Mon, Jun 16, 2025 at 11:12 AM David Okeamah <[email-masked]>
wrote:

> *From:* Gaspare Boscarino, P.Eng. <[email-masked]>
> *Sent:* Monday, June 16, 2025 7:10:22 PM
> *To:* DINESH NAIR <[email-masked]>
> *Cc:* Wasim Devale <[email-masked]>; Ron Johnson <
> [email-masked]>; pgsql-admin <[email-masked]>;
> Pgsql-admin <[email-masked]>