- 26 Sep, 2025
- 0 Comments
- 4 Mins Read
PostgreSQL Upgrade 14-15
PostgreSQL 14 to PostgreSQL 15 Upgrade Guide (Oracle Linux 7.9)
The PostgreSQL pg_upgrade command is the recommended way to upgrade between major PostgreSQL versions. It provides a fast and reliable method to move from an older version (like PostgreSQL 14) to a newer one (like PostgreSQL 15) without needing to dump and restore the entire database.
1 . What is a PostgreSQL Upgrade?
A PostgreSQL upgrade is the process of moving your database from one version to another — for example, from PostgreSQL 14 to 15 — to access new features, security updates, and performance improvements.
- An upgrade can involve major version changes(e.g., 14 → 15) or minor version changes (e.g., 14.3 → 14.7).
- Upgrades may require careful planning, backups, and testing to ensure data integrity and minimal downtime.
2. Types of PostgreSQL Upgrades
Major Version Upgrade
- Involves a change in the first or second numberof the version (e.g., 14 → 15).
- Introduces new features, performance improvements, and changes in system catalogs.
- Usually requires tools like pg_upgradeor logical dump/restore (pg_dump / pg_restore).
Minor Version Upgrade
- Involves a patch number change(e.g., 14.3 → 14.7).
- Primarily bug fixes and security patches.
- Usually safe to perform in-placewithout major downtime.
3. Tools to Upgrade PostgreSQL
1 . pg_upgrade(most common)
- Fast and efficient.
- Can upgrade in-place with minimal downtime.
- Supports both link mode(reuses files to save space) and copy mode (creates a full copy).
2 . pg_dump / pg_restore
- Logical backup and restore method.
- Useful for migrating between architectures or platforms.
- Can be slower for large databases.
3 . Logical Replication / Slony / Bucardo
- Used for zero-downtime upgrades.
- Replicates data from old version to new version.
- Suitable for large production databases.
4. Why Upgrading PostgreSQL is Important
1 .End of Life (EOL) & Support
- Ensures bug fixes, patches, and community support.
2 . Security Fixes
- Protects against vulnerabilities and exploits.
3 . New Features & Enhancements
- SQL improvements (e.g., MERGEcommand in PostgreSQL 15).
- Better replication, indexing, vacuuming, and monitoring.
4 . Performance Improvements
- Faster queries, optimized sorting, better parallel execution.
- Improved storage efficiency and reduced disk usage.
5 . Compatibility with Tools & Extensions
- Ensures support for PostGIS, TimescaleDB, logical replication tools, etc.
6 . Compliance & Audit Requirements
- Required for regulatory compliancein industries like finance, healthcare, and insurance.
7 . Future-Proofing
- Reduces risk and complexity of skipping multiple versionsin future upgrades.
1) Check whether PostgreSQL 14 is running or not and also find the location of data and bin directory
Example:
Data Location: /var/lib/pgsql/14/data
Bin location: /usr/pgsql-14/bin
2) Backup data (depending on the backup strategy being used)
As postgres user:
pg_basebackup -D /var/lib/pgsql/14/backups OR pg_dumpall > /var/lib/pgsql/14/backups/clusterall.sql
3) Install PostgreSQL 15 (on Oracle Linux 7.9)
As root user:
yum install postgresql15-server.x86_64 postgresql15-contrib.x86_64
4) Check the location of the installed PostgreSQL 15 and old version PostgreSQL 14
You should see both versions (14 and 15) under /var/lib/pgsql/ and /usr/.
5) Initialize the PostgreSQL 15 cluster
Navigate to:
cd /usr/pgsql-15/bin
Run pg_ctl as postgres user:
./pg_ctl -D /var/lib/pgsql/15/data initdb
6) Verify the PostgreSQL 15 data directory
Ensure the new cluster directory is populated:
/var/lib/pgsql/15/data
Run compatibility check:
/usr/pgsql-15/bin/pg_upgrade \ --old-bindir=/usr/pgsql-14/bin \ --new-bindir=/usr/pgsql-15/bin \ --old-datadir=/var/lib/pgsql/14/data \ --new-datadir=/var/lib/pgsql/15/data --check
This test should pass without errors and confirm that clusters are compatible.
7) Prepare for upgrade
– Ensure applications are stopped and no connections exist.
– Optionally block connections in pg_hba.conf.
– Take another backup for safety:
pg_basebackup -D /var/lib/pgsql/14/backups OR pg_dumpall > /var/lib/pgsql/14/backups/clusterall.sql
8) Stop PostgreSQL 14
cd /usr/pgsql-14/bin ./pg_ctl -D /var/lib/pgsql/14/data stop ./pg_ctl -D /var/lib/pgsql/14/data status
Expected:
pg_ctl: no server running
9) Perform the upgrade
Navigate to /tmp and execute:
/usr/pgsql-15/bin/pg_upgrade \ --old-bindir=/usr/pgsql-14/bin \ --new-bindir=/usr/pgsql-15/bin \ --old-datadir=/var/lib/pgsql/14/data \ --new-datadir=/var/lib/pgsql/15/data
10) Configure ports (optional)
– In /var/lib/pgsql/15/data/postgresql.conf, set:
port = 5432
– In /var/lib/pgsql/14/data/postgresql.conf, set:
port = 5432
11) Start PostgreSQL 15
cd /usr/pgsql-15/bin ./pg_ctl -D /var/lib/pgsql/15/data start
Check version:
psql --version
12) Post-upgrade tasks
Two scripts are created in /tmp:
– delete_old_cluster.sh
– analyze_new_cluster.sh
Run:
./analyze_new_cluster.sh
13) Validate upgrade
– Check tables, views, and objects in new PostgreSQL 15 cluster.
– Ensure application connectivity.
14) Cleanup (optional)
– Uninstall PostgreSQL 14 packages if not required.
– Run:
./delete_old_cluster.sh
This removes the old cluster safely.
Conclusion
At Learnomate Technologies, we’re here to support you every step of the way with top-notch training in PostgreSQL DBA and more.
For more detailed insights and tutorials, do check out our YouTube channel: www.youtube.com/@learnomate, where we regularly share practical tips and deep dives into essential database topics. And if you’re serious about mastering PostgreSQL DBA, head over to our website for our full training program: learnomate.org/training/postgresql-training/.
I’d love to connect with you, so don’t forget to follow my LinkedIn: https://www.linkedin.com/in/ankushthavali/. If you’re eager to read more about various technologies, explore our blog page here: https://learnomate.org/blogs/. Happy learning, and remember—tuning and training go hand-in-hand for database success!