Re: Seeking Guidance on Upgrading from PostgreSQL 12 to 1

On Sat, Aug 10, 2024 at 3:37 AM Motog Plus <mplus7535(at)gmail(dot)com> wrote:

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

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

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

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.

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

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.

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

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!