Subject: Seeking Guidance on Upgrading from PostgreSQL 12 to 1

From: Motog Plus <[email-masked]>
Date: Sat, 10 Aug 2024 13:06:49 +0530

Dear PostgreSQL Admins,

I hope this message finds you well. We are currently running PostgreSQL 12
in dev as well as prod environment . However, with the end of support for
this version scheduled for November 2024, we are considering upgrading to a
higher version, particularly PostgreSQL 16.

I would greatly appreciate your insights on the following points:

1. **Upgrading to PostgreSQL 16**: Would upgrading to version 16 be a
prudent decision, given the advancements and changes since PostgreSQL 12?
Are there any significant concerns or benefits that we should be aware of
before making this decision?

2. **Pre-Upgrade and Post-Upgrade Checks**: Could you please suggest the
critical checks we need to perform prior to the upgrade to ensure it is
successful? Additionally, what post-upgrade checks should be conducted to
confirm everything is functioning as expected?

3. **Upgrade Resources**: Are there any recommended links, videos, or other
resources that can guide us through the upgrade process, especially from
version 12 to 16?

4. **Application Impact**: If we proceed with the upgrade on the same host,
could there be any potential changes or impacts on the application side
that we should be prepared for?

5. **pg_upgrade Functionality**: When using `pg_upgrade`, does it
effectively copy all user-defined functions, extensions, sequences, along
with other database objects like tables, indexes, and schemas? Are there
any specific objects or elements that `pg_upgrade` does not handle, which
we should manually address?

Your advice and guidance will be invaluable as we prepare for this critical
upgrade.

Thank you in advance for your support.

Best regards,
Raman

---

From: Ron Johnson <[email-masked]>
Date: Sat, 10 Aug 2024 06:25:06 -0400

> before making this decision?

It's the latest, and the 4th release just dropped a few days ago.

> successful?

https://www.postgresql.org/docs/16/pgupgrade.html
-c
--check

check clusters only, don't change any data

> Additionally, what post-upgrade checks should be conducted to confirm
> everything is functioning as expected?
>

Comprehensive QA testing never hurts.

> resources that can guide us through the upgrade process, especially from

https://www.postgresql.org/docs/release/
https://www.postgresql.org/docs/release/13.0/
https://www.postgresql.org/docs/release/14.0/
https://www.postgresql.org/docs/release/15.0/
https://www.postgresql.org/docs/release/16.0/

> 4. **Application Impact**: If we proceed with the upgrade on the same
> host, could there be any potential changes or impacts on the application
> side that we should be prepared for?
>

Read the release notes.

You might also have to upgrade drivers on your application servers.  For
example, a change in how JDBC handles strings with single quotes in them
bit us when moving from PG 9.6 to PG 14, and an older JDBC version (don't
know the number) to whatever the latest was a couple of months ago.

> we should manually address?

https://www.postgresql.org/docs/16/pgupgrade.html
"Major PostgreSQL releases regularly add new features that often change the
layout of the system tables, but the internal data storage format rarely
changes. pg_upgrade uses this fact to perform rapid upgrades by creating
new system tables and simply *reusing the old user data files.*"

-- 
Death to America, and butter sauce.
Iraq lobster!

---

From: Motog Plus <[email-masked]>
Date: Sat, 10 Aug 2024 19:51:12 +0530

Thank you so much, Ron. I’ll go through your suggestions and reach out if I
have any further questions.

Regards,
Raman

On Sat, Aug 10, 2024, 15:55 Ron Johnson <[email-masked]> wrote:

>> I hope this message finds you well. We are currently running PostgreSQL
>> 12 in dev as well as prod environment . However, with the end of support
>> for this version scheduled for November 2024, we are considering upgrading
>> to a higher version, particularly PostgreSQL 16.
>>
> 3. **Upgrade Resources**: Are there any recommended links, videos, or
>> other resources that can guide us through the upgrade process, especially
>> from version 12 to 16?
>>
>
> "Major PostgreSQL releases regularly add new features that often change
> the layout of the system tables, but the internal data storage format
> rarely changes. pg_upgrade uses this fact to perform rapid upgrades by
> creating new system tables and simply *reusing the old user data files.*"
>
> --