March 10, 2025
Summary: In this tutorial, you will learn how to perform a major version upgrade using pg_upgrade in PostgreSQL.
Table of Contents
Introduction
Odds are you’ve been tasked with upgrading software from one major release to another at some point. For this use case, we’ll be using pg_upgrade.
This part is critical to a successful and healthy upgrade: read the release notes. Sometimes, even within minor upgrades, additional steps may be required and you won’t know if this is the case unless you read the release notes or risk running into an issue during the upgrade. Usually, you won’t have any extra steps to perform, but sometimes things like rebuilding indexes or changing paths may be needed. Again, be sure to read the release notes to avoid issues when implementing the upgrade.
In this instance, we’ll be using pg_upgrade to upgrade from PostgreSQL 14 to PostgreSQL 15 on CentOS 7. I choose pg_upgrade due to the upgrade speed. It’s literally done in minutes most of the time. The binary of the pg_upgrade can be found in the same default location that you’ll find initdb, pg_ctl, etc in, such as /usr/pgsql-##/bin.
Prerequisites for upgrade
After the appropriate release notes have been read thoroughly, it’s time to get upgrading!
Before we get underway with the actual pg_upgrade command and implementation, we should take a backup using our favorite backup tool.
Now that a backup has been taken, we’ll install the PostgreSQL 15 binaries.
yum -y install postgresql15-server postgresql15-contrib
Let us initialize the new cluster as the postgres user.
/usr/pgsql-15/bin/initdb -D /var/lib/pgsql/15/data
Stop all connections from the application (or elsewhere) to the database. To do so, become the postgres user and run the following command; Note: if you’ve changed the location of the data directory or binaries for PostgreSQL, you’ll need to substitute the paths below for your custom paths.
/usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -mf stop
Check for upgrade
Once the new cluster is initialized, and the old cluster is halted we’ll now run the pg_upgrade command with the -c (check) flag.
time /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 --link --check
Some information regarding the flags used above:
-b (--old-bindir=bindir) is the old cluster executable directory
-B (--new-bindir=bindir) is the new cluster executable directory
-d (--old-datadir=configdir) is the old cluster data directory
-D (--new-datadir=configdir) is the new cluster data directory
-k (--link) links instead of copying files to the new cluster NOTE: remove this option if you need to keep a local copy of the old cluster’s data files. This will increase the time it takes the upgrade to run as well as the size of the cluster on disk. You can NOT go back after using -k. Please be sure that you have enough disk space for 2 whole copies of the cluster if you do NOT use the -k flag.
-c (--check) does a “check” only, and doesn’t change any data. It will run through numerous consistency checks. Always run your pg_upgrade command with this first.
Additional information regarding pg_upgrade can be found in the documentation.
Perform upgrade
We’ve trained for this. Let us remove the -c (--check) flag and run the upgrade!
time /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 --link
After the above is done you should get an output congratulating your success! At this point it will be worthwhile to run the ./analyze_new_cluster.sh script in order to generate stats so the system is usable. You can also wait and vacuum will eventually generate this for you. The former option is typically the best route.
Lastly, if you wish to remove the old cluster’s data files, you can run ./delete_old_cluster.sh as the postgres user.