Important Notes
Important: • This guide assumes an Enterprise Linux 8 distribution (RHEL 8, Oracle Linux 8, AlmaLinux 8, or Rocky Linux 8). • This guide assumes an upgrade from PostgreSQL 14. Check your current version and adjust commands accordingly: psql --version • If your PostgreSQL installation uses a custom mount point, update all data directory paths in the commands accordingly. • Verify your PostgreSQL user and database name in intigua.properties. This guide uses the defaults: user = jetpatch, database = jetpatchdb. To obtain DB details, run: cd /usr/share/tomcat/default/conf/ cat intigua.properties | grep db |
Why Upgrade to PostgreSQL 16?
| Benefit | Details |
| Performance Gains | Significantly reduced query times, higher throughput, and improved parallelism - especially compared to PostgreSQL 14 and earlier. |
| Security & Stability | Latest security patches plus stricter defaults introduced in PostgreSQL 15+. |
| Feature Enhancements | Streamlined replication, extended SQL features (e.g., better JSON handling), and optimized memory/I/O. |
Step 1: Pre-Upgrade Preparation
Read and understand all Important Notes above before proceeding.
Back Up Your Data
These commands assume the default: user = jetpatch, database = jetpatchdb.
Single database backup:
| pg_dump -U jetpatch -d jetpatchdb > /tmp/jetpatchdb_backup.sql |
All databases are backed up:
| pg_dumpall > /tmp/all_postgres_backup.sql |
Stop Services
- Stop the JetPatch application (e.g., stop Tomcat services).
- Stop the current PostgreSQL service:
| systemctl stop postgresql-14 |
Step 2: Install PostgreSQL 16 on EL8
Add the PGDG repository (works for RHEL 8 and derivatives: Oracle Linux 8, AlmaLinux 8, 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 |
| Note: The postgresql16-contrib package is optional. |
Initialize the new cluster:
| /usr/pgsql-16/bin/postgresql-16-setup initdb |
| Important: Confirm that the PostgreSQL 16 service is NOT running before proceeding to the next step. |
Step 3: Migrate Data with pg_upgrade
Note: The following commands assume default data directory locations. If your directories are on a custom mount, update --old-datadir and --new-datadir accordingly. |
(Optional) Start the Old Cluster for Pre-Check
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
/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 (postgresql.conf, pg_hba.conf) with any custom settings from the old cluster.
Step 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 and confirm the PostgreSQL version:
| psql -U postgres -c "SELECT version();" |
Clean Up Old Installation
Once verified, remove the old PostgreSQL version and data:
dnf remove -y postgresql14-* rm -rf /var/lib/pgsql/14/data |
Step 5: Adjust Schema Privileges (PostgreSQL 15+)
PostgreSQL 15+ restricts object creation in the public schema by default.
If the JetPatch user needs to create objects there, run the following:
Grant privileges on the 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
| Issue | Resolution |
| pg_upgrade Errors | Use the --verbose flag for detailed output. Check the pg_upgrade log files for specific error messages. |
| Permission Problems | Ensure all pg_upgrade commands are executed as the postgres user and that file permissions on data directories are correct. |
| Post-Upgrade Performance | Run vacuumdb to ensure proper optimizer statistics: vacuumdb --all --analyze-in-stages |
Comments
0 comments
Please sign in to leave a comment.