Upgrading PostgreSQL on Linux with pg_upgrade

In this post, we’ll look at how to upgrade a PostgreSQL installation on Linux. In this example, we’ll look at an in-place upgrade on Debian from PostgreSQL version 15 to 18. The version 15 cluster will already be up and running and we’re going to use pg_upgrade to do it.

The upgrade process is:

Install new version of PostgreSQL
Create new cluster for the new version
Upgrade the existing cluster to the new version
Remove the old cluster

The environment we’ll be working on is:

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 only tools from the standard PostgreSQL repository rather than any Debian-specific tooling (pg_createcluster, pg_lsclusters etc.)

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, though that is outside the scope of this article.

What is pg_upgrade?

pg_upgrade is an application that ships with PostgreSQL; it is a command-line tool that migrates PostgreSQL data files in a cluster to a cluster directory of a higher PostgreSQL version. pg_upgrade is not to be confused with pg_upgradecluster, which is a Debian-specific wrapper utility.

Verifying the Current Version

First up, we’ll verify what our current version of PostgreSQL is. There are a few different ways we can do this, so let’s look at them.

Firstly, we can get the version number from the PostgreSQL server by running the following with psql (or any other client):

SELECT version();

We could query the installed packages:

dpkg -l | grep -i postgresql

Using the version flag on the postgres binary is another option:

postgres --version

Installing the New Binaries

Now we’re confident about which version we’re on, let’s do the upgrade. At the time of writing, the latest and greatest is version 18. Let’s see if our repositories have any newer version than what we have installed:

apt search postgresql-1[6-8]

Apt hasn’t found any newer version than 15. As this server is running Debian stable, the packages are a little out of date and therefore we’re going to need to get the latest version from the PostgreSQL repository. The instructions on the Linux downloads page on the PostgreSQL site are fairly simple:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

This installs the postgresql-common package (or upgrades it if we already have it), which includes the apt.postgresql.org.sh script. The script installs the PostgreSQL repository to sources.list.d. We can inspect the sources file with the command:

cat /etc/apt/sources.list.d/pgdg.sources

Now that apt has the PostgreSQL repository in its sources, we can install the version we are interested in:

sudo apt install postgresql-18

During the installation, the following debconf screen pops up. At this point, the installer has detected that we are running a cluster on an older version and is offering to update it using pg_upgradecluster:

As this article is specifically looking to upgrade using pg_upgrade, we will say no to the installer’s offer to upgrade using pg_upgradecluster and therefore will just install the binaries for our new version, version 18.

At this point, we now have both PostgreSQL 15 and PostgreSQL 18 installed, but we only have a version 15 cluster. To confirm both versions are installed, list the installed packages:

dpkg -l | grep postgresql

We can list installed clusters using the command below. As we’re using pg_upgrade here, I’m refraining from using Debian-specific tooling, such as pg_lsclusters, which would also list installed clusters on the system. Here I am using a more universal method. As each installed cluster will have a PG_VERSION file in the root directory, we can search for files of that name. This file also exists in every database sub-directory within the base directory, so we’ll exclude those.

sudo find / -type f -name PG_VERSION ! -path '*/base/*' 2> /dev/null

Creating the New Cluster

Now we need to create the new version 18 cluster; this is what we are going to upgrade into. To create it, we need to run initdb, which creates a new cluster. We need to ensure we run the correct version of initdb as we currently have a version 15 and a version 18. If, for example, we have the binary path of version 15 in our $PATH variable but not version 18’s, we might accidentally execute the wrong one. For this reason, and for the sake of clarity, I like to run initdb with the full path to the executable, although –version will confirm the version for us. The following shows that there is an initdb binary per version:

/usr/lib/postgresql/15/bin/initdb --version

/usr/lib/postgresql/18/bin/initdb --version

As we’re upgrading to version 18, we need to run the version 18 initdb binary, and we need to specify where we want our cluster directory using the -D flag.

/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main

We have an issue in that the user I am running as does not have permissions to create in the directory. Let’s put on the sudo power glove:

sudo /usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main

We get flat out denied, telling us we can’t run as root. The solution is to run as the postgres user, which ensures any directories or files created will have the correct ownership:

sudo su postgres

In the resulting prompt, we’ll run the original command again:

/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main

Now we have a cluster, so let’s start it to verify we initialized it correctly. This isn’t required for the upgrade as we’ll stop it again later. We’re just verifying our cluster was created successfully.

/usr/lib/postgresql/18/bin/pg_ctl -D /var/lib/postgresql/18/main -l /var/log/postgresql/postgresql-18-main.log -o "-p 5433" start

Now let’s verify that we can log in OK:

psql -d postgres -p 5433

Here we see the psql prompt, meaning all is installed OK and we can connect.

Performing the Migration

Now we’ll need both clusters to be stopped, so we can do the migration from the version 15 cluster to the version 18 cluster:

/usr/lib/postgresql/18/bin/pg_ctl stop -D /var/lib/postgresql/18/main
/usr/lib/postgresql/15/bin/pg_ctl stop -D /var/lib/postgresql/15/main

Now we can do the upgrade, though I’ll run with –check first which, as the name suggests, checks if everything is OK, rather than doing the work for real:

/usr/lib/postgresql/18/bin/pg_upgrade --old-datadir /var/lib/postgresql/15/main --new-datadir /var/lib/postgresql/18/main --old-bindir /usr/lib/postgresql/15/bin/ --new-bindir /usr/lib/postgresql/18/bin --check

I get an error that the command needs to be run from a directory in which the current account has read/write access. As we were in the dualcoredba user’s home directory when we switched to the postgres user, that is still our working directory, so let’s change to the postgres user’s home directory, /var/lib/postgresql:

cd ~

Now we run the pg_upgrade command again:

/usr/lib/postgresql/18/bin/pg_upgrade --old-datadir /var/lib/postgresql/15/main --new-datadir /var/lib/postgresql/18/main --old-bindir /usr/lib/postgresql/15/bin/ --new-bindir /usr/lib/postgresql/18/bin --check

We can see there is an issue preventing us from upgrading: our new cluster uses checksums but the old one doesn’t. This is because checksums are enabled by default since PostgreSQL version 18. I’ll bring the new cluster in line by disabling checksums:

/usr/lib/postgresql/18/bin/pg_checksums --disable -D /var/lib/postgresql/18/main

Now we can re-try the upgrade check:

/usr/lib/postgresql/18/bin/pg_upgrade --old-datadir /var/lib/postgresql/15/main --new-datadir /var/lib/postgresql/18/main --old-bindir /usr/lib/postgresql/15/bin/ --new-bindir /usr/lib/postgresql/18/bin --check

We can see our clusters are compatible, so let’s do the upgrade for real by removing the –check flag:

/usr/lib/postgresql/18/bin/pg_upgrade --old-datadir /var/lib/postgresql/15/main --new-datadir /var/lib/postgresql/18/main --old-bindir /usr/lib/postgresql/15/bin/ --new-bindir /usr/lib/postgresql/18/bin

We get a bit of a dubious error message, so let’s check the log file which pg_upgrade created in the location in the error message:

tail /var/lib/postgresql/18/main/pg_upgrade_output.d/20260131T234138.860/log/pg_upgrade_server.log

The log tells us that it can’t find the postgresql.conf file in /var/lib for the old version 15 cluster. The reason for this is that the version 15 cluster was created with pg_createcluster, which creates the config files in the /etc directory, and it seems that pg_upgrade defaults to assuming the config is in /var/lib. To get round this, we can use the –old-options option on pg_upgrade, which passes options to the old cluster. When pg_upgrade uses the postgres command for the old cluster, we can pass the config file using the -c option. This means our revised command is:

/usr/lib/postgresql/18/bin/pg_upgrade --old-datadir /var/lib/postgresql/15/main --new-datadir /var/lib/postgresql/18/main --old-bindir /usr/lib/postgresql/15/bin/ --new-bindir /usr/lib/postgresql/18/bin --old-options "-c config_file=/etc/postgresql/15/main/postgresql.conf" --check

Now we get the message that we are finally ready to upgrade as our old and new clusters are compatible.

We can run pg_upgrade again for real:

/usr/lib/postgresql/18/bin/pg_upgrade --old-datadir /var/lib/postgresql/15/main --new-datadir /var/lib/postgresql/18/main --old-bindir /usr/lib/postgresql/15/bin/ --new-bindir /usr/lib/postgresql/18/bin --old-options "-c config_file=/etc/postgresql/15/main/postgresql.conf"

We have a failure and the reason is fairly clear: pg_upgrade was copying a database from the old cluster to the new cluster, resulting in two copies, which have filled the disk. The files in question relate to a 116 GB database, hence the issue. By default, pg_upgrade copies the files to the new cluster. We can change this behaviour by getting pg_upgrade to use a hard link, i.e. a new file which points to the same inode on the file system. To do this, we need to use the –link option with pg_upgrade. Note that if we are using a hard link and the version 18 cluster has started, we can no longer safely use the version 15 cluster as the files have been upgraded to version 18.

However, we now have half a database and the cluster has metadata that is expecting those database files to be there. To recover, I’m going to drop the half-built version 18 cluster, recreate it, and run the migration again:

rm -rf /var/lib/postgresql/18/main
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main
/usr/lib/postgresql/18/bin/pg_checksums --disable -D /var/lib/postgresql/18/main
/usr/lib/postgresql/18/bin/pg_upgrade --old-datadir /var/lib/postgresql/15/main --new-datadir /var/lib/postgresql/18/main --old-bindir /usr/lib/postgresql/15/bin/ --new-bindir /usr/lib/postgresql/18/bin --old-options "-c config_file=/etc/postgresql/15/main/postgresql.conf" --link

And with this…finally…

Now we can start the new cluster:

/usr/lib/postgresql/18/bin/pg_ctl start -D /var/lib/postgresql/18/main

Let’s log into psql to confirm everything is as we expect at this point:

We can see we’re now running version 18 and the databases are present.

Final Cleanup

There are a few final bits we need to do to complete our upgrade. First, pg_upgrade told us there are some extensions that require upgrading and it told us how to upgrade them. There is a file called update_extensions.sql in the directory we ran the upgrade from. If we exit psql, we can read it:

As we can see, it’s a single ALTER EXTENSION command in my case. I’ll run the SQL file:

psql -f update_extensions.sql

Now for the final thing: the postgresql.conf file. We can see the differences between the old file and the new using the diff command:

sudo diff -d /var/lib/postgresql/18/main/postgresql.conf /etc/postgresql/15/main/postgresql.conf

We can see this returns an awful lot of changes:

This is somewhat expected as defaults can change between versions, though in this case we have a fair few differences showing up, again due to differences in how settings for a cluster created by pg_createcluster vs initdb look. Given the number of changes between the config file and also that some of the values in the version 15 file point to version 15 directories, blindly copying the version 15 file to the version 18 cluster isn’t going to work, and so I’ll go through and manually change the settings in the version 18 config file.

The same is true for the pg_hba file, although this file is a lot simpler than postgresql.conf and therefore easier to diff.

The Full Script

Below is the script that was run to upgrade from the version 15 cluster from the Debian repos to the version 18 cluster from the PostgreSQL repos. As we have worked through a number of errors and issues above, the script below is intended to be a succinct round-up of what was required for a successful upgrade and excludes the things that caused errors. Note that this doesn’t include checks, which should always be performed prior to any upgrade. This script doesn’t include any extension upgrade or configuration changes as they will differ wildly between one installation and another.

# add the postgres repo
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
# install the new version
sudo apt install postgresql-18
# log in as the postgres user and change to the home directory
sudo su postgres
cd ~
# create a new v18 cluster
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main
# stop the v15 cluster
/usr/lib/postgresql/15/bin/pg_ctl stop -D /var/lib/postgresql/15/main
# disable checksums on the v18 cluster
/usr/lib/postgresql/18/bin/pg_checksums --disable -D /var/lib/postgresql/18/main
# perform the upgrade
/usr/lib/postgresql/18/bin/pg_upgrade --old-datadir /var/lib/postgresql/15/main --new-datadir /var/lib/postgresql/18/main --old-bindir /usr/lib/postgresql/15/bin/ --new-bindir /usr/lib/postgresql/18/bin --old-options "-c config_file=/etc/postgresql/15/main/postgresql.conf" --link
# start the new cluster
/usr/lib/postgresql/18/bin/pg_ctl start -D /var/lib/postgresql/18/main

Conclusion

In this post, we looked at how to migrate from a PostgreSQL version 15 cluster to a version 18 cluster using pg_upgrade: the standard PostgreSQL tooling. We saw some issues that were caused by the source cluster having been created by the Debian-specific pg_createcluster utility and the target having been created by the PostgreSQL standard initdb. We also overcame an issue brought about by the checksum feature being enabled by default on version 18.