Upgrade PostgreSQL on Arch Linux

The following are my notes for upgrading PostgreSQL on Arch Linux when I have installed PostgreSQL through the official package management system.

Stop the service with the following.

sudo systemctl stop postgresql

The PostgreSQL upgrade process which is done with pg_upgrade requires having the previous version of postgres that created the /var/lib/postgres/data directory as the format is locked to major versions. For example you might be upgrading from PostgreSQL 17 to 18. The /var/lib/postgres/data directory was originally setup with 17.

On Arch Linux the way that pacman works with upgrades is that it upgrades to the latest. So when you upgrade postgresql it will have only the latest version installed. But in order to facilitate upgrade with postgresql you have to have the previous version installed already.

To facilitate this Arch User Package's available through yay provide a package for the previous version to be installed beside the current. The following facilitates installing these packages.

yay -Sy postgresql-old-upgrade
yay -Sy postgis-old-upgrade # if you were using postgis with postgresql in the previous version

Once these are installed we need to move the old data dir to a backup. This can be done as follows.

sudo -iu postgres
sudo -u postgres mv data data.old17
exit

Then we need to create new initialized data directory for the new version of the database. Note: The --no-data-checksums option is something I added because the 17 data was done without data checksums and the pg_upgrade process doesn't allow upgrading from a non-checksum on to checksum one.

sudo -iu postgres
mkdir data
initdb --no-data-checksums -D /var/lib/postgres/data
exit

Once you have the new data directory initialized then you can run the upgrade with the following.

sudo -iu postgres
pg_upgrade -b /opt/pgsql-17/bin -B /usr/bin -d data.backup -D data
exit

Then you can start the service up again.

sudo systemctl start postgresql

Some statistics are not transferred by pg_upgrade. Once we have started the new server we should be able to do the following to handle this.

sudo -iu postgres
/usr/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
/usr/bin/vacuumdb --all --analyze-only
exit