PostgreSQL 16 Upgrade Guide for JetPatch on Enterprise Linux 8

1. Pre-Upgrade Preparation (Back up your Data)

• Make sure you read and understand the important note at the top of this article

• Back Up Your Data 

  (Assumes user = jetpatch, database = jetpatchdb)

  • Single Database:
    

  • pg_dump -U jetpatch -d jetpatchdb > /tmp/jetpatchdb_backup.sql

  • All Databases:

  • pg_dumpall > /tmp/all_postgres_backup.sql

• Stop Services:

  • Stop the JetPatch application (e.g., stop Tomcat services).
  • Stop your current PostgreSQL service (assuming version 14):

  • systemctl stop postgresql-14

2. Install PostgreSQL 16 on EL8

Add the PGDG Repository:
(This package works for RHEL8 and its derivatives such as Oracle Linux 8, AlmaLinux 8, and Rocky Linux 8.)

• dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/RHEL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm dnf module disable postgresql -y && dnf clean all

• Install PostgreSQL 16 Packages:

  dnf install -y postgresql16-server postgresql16 postgresql16-contrib

• Initialize the New Cluster:

• /usr/pgsql-16/bin/postgresql-16-setup initdb

• Ensure PostgreSQL 16 is Stopped:
  Confirm that the PostgreSQL 16 service is not running before proceeding.

3. Migrate Data with pg_upgrade

The following commands assume default locations. If your data directories are on a custom mount, update the --old-datadir and --new-datadir paths accordingly.

Optionally Start the Old Cluster for Checks:

su - postgres 
/usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data start

Run a Pre-Check:

/usr/pgsql-16/bin/pg_upgrade \ 
  --old-bindir=/usr/pgsql-14/bin \ 
  --new-bindir=/usr/pgsql-16/bin \ 
  --old-datadir=/var/lib/pgsql/14/data \ 
  --new-datadir=/var/lib/pgsql/16/data \ 
  --user=postgres \ 
  --check

Stop the Old Cluster Completely:

/usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data stop
systemctl stop postgresql-14

Execute the Upgrade:

/usr/pgsql-16/bin/pg_upgrade \ 
  --old-bindir=/usr/pgsql-14/bin \ 
  --new-bindir=/usr/pgsql-16/bin \ 
  --old-datadir=/var/lib/pgsql/14/data \ 
  --new-datadir=/var/lib/pgsql/16/data \ 
  --user=postgres

Merge Custom Configurations:

Update the new cluster’s configuration files (e.g., postgresql.conf, pg_hba.conf) with any necessary settings from your old cluster.

4. Post-Upgrade Tasks

• Start PostgreSQL 16:

• systemctl start postgresql-16

• Optimize the Database:

• /usr/pgsql-16/bin/vacuumdb -U postgres --all --analyze-in-stages

• Verify Functionality:
  Check JetPatch connectivity by verifying the application and running:

• psql -U postgres -c "SELECT version();"

• Clean Up Old Installation:
  Once verified, remove the old PostgreSQL version:

• dnf remove -y postgresql14-* 
  rm -rf /var/lib/pgsql/14/data

5. Adjust Schema Privileges (PostgreSQL 15+)

PostgreSQL 15+ restricts object creation in the public schema by default. If your JetPatch user needs to create objects in public, run:

• Grant Privileges on Public Schema:

• sudo -u postgres psql -d jetpatchdb -c "GRANT ALL ON SCHEMA public TO jetpatch;"

• Set Default Privileges:

• sudo -u postgres psql -d jetpatchdb -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO jetpatch;"

Troubleshooting

• Common Issues:

  • pg_upgrade Errors: Use --verbose and check logs if errors occur.
  • Permission Problems: Ensure commands are executed as the postgres user and file permissions are correct.
  • Post-Upgrade Performance: Running vacuumdb --all --analyze-in-stages ensures proper optimizer statistics.