Re: Seeking Guidance on Upgrading from PostgreSQL 12 to 1

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 <ronljohnsonjr(at)gmail(dot)com> wrote:

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