The quick and easy way to upgrade Postgres using ‘pg_upgrade’

Why pg_upgrade?

pg_upgrade is a utility provided by PostgreSQL that allows you to upgrade your database cluster from an older version to a newer version without the need for a lengthy dump and restore process.

This utility is not required for minor version upgrades, which means that upgrading your current version of 11.1 to 11.9, this utility is not necessary.

Performing pg_upgrade

To upgrade from PostgreSQL version 11 (current version) to version 15 (a newer version) using pg_upgrade, follow these step-by-step instructions:

Step 1:  Prerequisites 

Note: Make sure you have created a latest backup of your PostgreSQL 11 (i.e.,current version) databases.

Download and install PostgreSQL 15 (i.e., the newer version) on your system.

Step 2: Install PostgreSQL 15 Cluster

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql15-ser

Step 3: Initialize the new PostgreSQL-15 cluster

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

Step 4: Stop both PostgreSQL Clusters

sudo systemctl stop postgresql-15

sudo systemctl stop postgresql-11

Step 5: Perform upgrade check

Before performing pg_upgrade, we have to check if the clusters are compatible or not. So we can use the option –check

[postgres@localhost data]$ /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-11/bin/ -B /usr/pgsql-15/bin/ -d /var/lib/pgsql/11/data/ -D /var/lib/pgsql/15/data/ -o ' -c config_file=/var/lib/pgsql/11/data/postgresql.conf' -O ' -c config_file=/var/lib/pgsql/15/data/postgresql.conf' --check

In this step, if the “–check” command fails, indicating an error, it is necessary to investigate and resolve the issue before proceeding. 

When repmgr is running on a PostgreSQL cluster, the following error message may occur:

Performing Consistency Checks on Old Live Server
—————————————————————————–
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt

and checking the content of the loadable_libraries.txt file, you may see something like following:

[postgres@localhost ~]$ cat loadable_libraries.txt
could not load library "$libdir/repmgr": ERROR: could not access file "$libdir/repmgr": No such file or directory
Database: postgres
Database: repmgr

Resolution

In this case, when upgrading to a new version, it is necessary to install repmgr in the new version as well, i.e., the utilities & extensions that were installed on the source Postgres version would also need to be installed on the target Postgres version to maintain consistency and compatibility.
We will recheck the same command once again and the result will be as follows.

It’s important to perform thorough testing after the upgrade to ensure the compatibility of your applications with the new version of PostgreSQL.

Step 6: Perform actual upgrade

[postgres@localhost data]$ /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-11/bin/ -B /usr/pgsql-15/bin/ -d /var/lib/pgsql/11/data/ -D /var/lib/pgsql/15/data/ -o ' -c config_file=/var/lib/pgsql/11/data/postgresql.conf' -O ' -c config_file=/var/lib/pgsql/15/data/postgresql.conf'

Performing Upgrade
--------------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster ok
Copying user relation files ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to delete old cluster ok
Checking for extension updates ok
Upgrade Complete
-------------------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
./delete_old_cluster.sh

Step 7: Start the PostgreSQL-15 cluster

sudo systemctl start postgresql-15

Step 8: verify PostgreSQL-15 Cluster after upgrade using the command vacuumdb --all --analyze-in-stages

pg_upgrade provides a convenient upgrade method, it’s important to perform thorough testing after the upgrade to ensure the compatibility of your applications with the new version of PostgreSQL.
If you wish to remove the old cluster’s data files, you can run ./delete_old_cluster.sh as the postgres user.

Conclusion

Overall, pg_upgrade is a valuable tool for upgrading PostgreSQL databases, providing a seamless and efficient way to move from an older version to a newer one while preserving your data and minimizing downtime.