In my previous post, we upgraded a PostgreSQL 15 cluster to version 18 using pg_upgrade. In this post, we’ll contrast that with using pg_upgradecluster.
As before, the upgrade process is:
- Install new version of PostgreSQL
- Upgrade the existing cluster to the new version
- Remove the old cluster
Note that we have one less step than the pg_upgrade version, that step being creating the new cluster.
The environment is the same as before:
- Debian 12 VM
- PostgreSQL 15 installed
- Version 15 cluster up and running with databases attached
- Existing cluster created using pg_createcluster as part of the PostgreSQL Debian installer
The upgrade will:
- Install PostgreSQL 18
- Migrate the existing version 15 cluster to version 18
- Use Debian-specific PostgreSQL tools rather than the tooling from the standard PostgreSQL repo
Given we are doing an upgrade, we’ll need to ensure we have backups and a recovery plan in case something goes wrong, not to mention reviewing release notes and doing some testing, but that is outside the scope of this article.
What is pg_upgradecluster?
pg_upgradecluster is a Perl script that is part of the postgresql-common package. Pg_upgradecluster is a Debian (and derivative) specific tool, rather than something in the official PostgreSQL repos. pg_upgradecluster is a wrapper script for the PostgreSQL native pg_upgrade tool. It does more than pg_upgrade, such as starting/stopping the new and old clusters, swapping the ports and rolling back on failure, etc. As such, to use pg_upgradecluster, you will need to be using a Debian-based OS (Debian, Ubuntu etc.). If you are using something else (RHEL, Fedora, Windows), then you will use pg_upgrade directly.
Verifying the Current Version
First up, we’ll verify what version of PostgreSQL we have installed. There are a few different ways we can do this. Rather than repeat them, I will refer back to the pg_upgrade post where this is detailed.
Installing the New Version
Again, not wanting to re-invent the wheel, installation of version 18 is also covered in the pg_upgrade post.
Method 1: Upgrade via the Installer
The easiest way to upgrade a cluster using pg_upgradecluster is to do so via the PostgreSQL apt installer. When we run the install command below, apt installs the package and as part of the postinst script, a debconf prompt pops up asking us if we want to upgrade the existing version 15 cluster to version 18:
sudo apt install postgresql-18
During the installation, the following debconf screen pops up. At this point, postgresql-common has detected that we are running a cluster on an older version and is offering to upgrade it using pg_upgradecluster:
If we select “Yes”, the configuration tool will migrate the version 15 cluster to version 18.
The upgrade starts migrating data from the version 15 cluster to the version 18 cluster using pg_dump – it creates a dump of all the databases on the old cluster and creates them on the new one. This is the default behaviour of pg_upgradecluster:
If we background the process (CTRL+Z) at this point and look to see which clusters we have installed:
sudo find / -type f -name PG_VERSION ! -path '*/base/*' 2> /dev/null
We can see that a cluster directory has been created for our version 18 cluster.
Since we are using Debian, we can also use pg_lsclusters to show the clusters on the system. This utility uses the postgresql.conf files in /etc/postgresql/<version>/<clustername> to determine which clusters are on the system:
We can see there are two active clusters and that the new version 18 cluster is using port 5433, whereas version 15 is using the default port of 5432.
Eventually, my upgrade hit an error:
The error is further detailed below:
It’s fairly clear what the issue is – we have run out of disk space. This is because pg_upgradecluster is making a copy of a 116 GB database to the new cluster directory so we have two copies and not enough disk space for those two copies. We can also see that due to the failure, the new cluster has been removed and we can verify this by showing the clusters in the same way we did before:
sudo find / -type f -name PG_VERSION ! -path '*/base/*' 2> /dev/null
Method 2: Manually using pg_upgradecluster After Rolled-Back Installer Upgrade
At this point, we have installed the version 18 binaries but failed to upgrade our cluster and our version 15 cluster is still online. Let’s use pg_upgradecluster manually to have another go. This approach is one we would also take if we installed version 18 and replied “No” to the upgrade prompt in the debconf window that appeared as part of the install process.
The man page tells us we can change the behaviour and use hard links rather than pg_dump, which means we won’t be creating the duplicate copy of our database which causes the disk to fill. The syntax is to use the –method=link option which uses hard links rather than dumps:
sudo pg_upgradecluster --method=link 15 main -v 18
Now we have success and pg_upgradecluster shows us that we have an online version 18 cluster called main and an offline version 15 cluster, the old cluster has been moved to port 5433 so existing connections should seamlessly connect to version 18. As an aside, the output above that shows the clusters is from pg_lsclusters – another Debian-specific tool, one which lists the clusters installed on the system.
Verifying Install Success
Let’s verify we have a version 18 cluster directory now:
sudo find / -type f -name PG_VERSION ! -path '*/base/*' 2> /dev/null
pg_lsclusters
We can check both cluster directories and see that the actual files are pointing to the same inode (the first column) which verifies what pg_upgradecluster has done (hard linked the files):
sudo ls -liS /var/lib/postgresql/18/main/base/2072592 | head
sudo ls -liS /var/lib/postgresql/15/main/base/2072592 | head
We can also run the same version checks described in the pg_upgrade post to verify that the version has indeed been upgraded to 18:
psql -d postgres
SELECT version();
dpkg -l | grep -i postgresql
Method 3: Manually using pg_upgradecluster with Hard Links After Upgrade Prompt Rejection
Now let’s see what happens if we choose not to upgrade the cluster from the installer and upgrade it later ourselves. The installer suggested that after the install process we could run the cluster upgrade manually using pg_upgradecluster, so let’s test how that goes.
I restore my VM to a snapshot I took before the previous upgrade so I can attempt the upgrade again.
As before, we install the repository and PostgreSQL 18:
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install postgresql-18
This time, select No at the prompt:
Once complete, check our cluster directories:
sudo find / -type f -name PG_VERSION ! -path '*/base/*' 2> /dev/null
pg_lsclusters
As expected, there is no version 18 cluster directory.
Let’s run pg_upgradecluster. I’ll use the hard link version first as I know we are low on disk space.
sudo pg_upgradecluster --method=link 15 main -v 18
Success! We can see the cluster was upgraded and the old one is offline as before. The outcome is the same as what we got when we performed Method 1 (which failed due to disk space and then rolled back) followed by Method 2 (where we ran pg_upgradecluster following the rollback)
Method 4: Manually using pg_upgradecluster with pg_dump After Upgrade Prompt Rejection
This time, we’ll upgrade from 15 again, reject the upgrade offer and use pg_upgradecluster. We won’t use the hard link version, we’ll use the default pg_dump option. This is the same as Method 1, but we are performing the upgrade ourselves after installation, rather than getting the installer to do it. We are expecting this to fail as we don’t have enough disk space.
I’ll restore the VM snapshot again so it’s as if we’ve done nothing yet.
At this prompt, select “No”:
Now we’ll run pg_upgradecluster manually. The command below does not provide a method option and as such it will use pg_dump in the same way as we saw in Method 1:
sudo pg_upgradecluster 15 main -v 18
Sure enough, we get the disk space error we saw before, and again, let’s confirm it has rolled back in the same way as before.
sudo find / -type f -name PG_VERSION ! -path '*/base/*' 2> /dev/null
pg_lsclusters
A Note About Rollback
OK, time to confess, I ran the above tests a number of times and got varying results. The rollbacks that occurred above as a result of the disk space failure happened in about 60% of the times the upgrade failed (I tried A LOT!). The remainder of the time, the new cluster was not removed and manual cleanup was done. I am not sure what the cause of this inconsistency is. For now I will continue to wonder why, though I will endeavour to get to the bottom of this in the future.
Conclusion
We have looked at how to upgrade a PostgreSQL version 15 cluster on Debian to version 18. We looked at a number of options in doing so and also observed what happens when failure occurs. We contrasted this process with the previous post which uses the native pg_upgrade binary.
References / Further Reading
DualCoreDBA – Upgrading PostgreSQL on Linux with pg_upgrade
Martin Pitt / Christoph Berg – PgCommon.pm Source Code
Martin Pitt / Christoph Berg – pg_upgradecluster
Oliver Elphick / Martin Pitt – postgresql-common Github repository