Upgrading Postgresql clusters through 7 versions

I have neglected many VMs and clusters for the past few years and am now in a situation where upgrading has become painful. I have a postgresql v11 cluster and the oldest binary available on OpenBSD 7.9 is Postgresql 17. I don't want to go through all the revisions of OpenBSD 7.3 to 7.9 either. That's 6 versions. So I'll instead nuke my installation and keep the cluster.

Before I do that, I need to make sure I can keep the data. So I've created a VM with OpenBSD 7.9 fresh install and I'll go through upgrading the cluster from PG 11 to 18, likely one step at a time.

Once OpenBSD 7.9 is installed, finding the sources for PG is easy.

curl -O http://https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.gz
tar -x -v -z -f postgresql-11.22.tar.gz

To build, Postgresql documentation says to do the usual configure-make combo.

cd postgresql-11.22
mkdir build && pushd build
../configure
make

You must use GNU make to build PostgreSQL.

Oops!

pkg_add gmake  # for all versions
pkg_add bison  # for v17
pkg_add icu4c  # for v18

Lots of output lines later, we have a binary! Much easier than I thought. Now we just do this for v18 as well so we have both v11 and v18. PostgreSQL upgrades documentation mentions that pg_upgrade supports upgrades from v9.2.x all the way to the current major release, which as of today is v18.

Once both v11 and v18 are built, we'll create a non-previleged user postgres in wheel group and make the upgrade happen.

useradd -m -G wheel postgres
chown -R postgres /home/postgres/pgversions

Now I'll assume a directory structure like /home/postgres/pgversions/{v11.22,v18.4}/{lib,bin,...}. To actually run v18's pg_ctl, we'll need to let ldconfig know about the libraries and increase semmns so pg_ctl can work well (you can also reduce max_connections.

ldconfig -m /home/postgres/pgversions/v18.4/lib
sysctl kern.seminfo.semmns=4096 # default is 60

doas -u postgres /home/pgversions/v18.4/bin/pg_ctl -D /home/postgres/data18/ initdb

And upgrade.

doas -u postgres /home/postgres/pgversions/v18.4/bin/pg_upgrade -b /home/postgres/pgversions/v11.22/bin/ -B /home/postgres/pgversions/v18.4/bin/ -d /home/postgres/databases/data11/ -D /home/postgres/databases/data18/ --check

--check allows a dry run. Fix the configuration issues and upgrade. The only issue I had was my v11 cluster did not have checksums and v18 did, so I temporarily disabled them for the upgrade.

doas -u postgres /home/pgversions/v18.4/bin/pg_checksums -D /home/postgres/databases/data18/ --disable

Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database connection settings                         ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking data type usage                                      ok
Checking for user-defined encoding conversions                ok
Checking for user-defined postfix operators                   ok
Checking for incompatible polymorphic functions               ok
Checking for tables WITH OIDS                                 ok
Checking for not-null constraint inconsistencies              ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

*Clusters are compatible*

And without --check.

Upgrade Complete
----------------
Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
    /home/pgversions/v18.4/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
    /home/pgversions/v18.4/bin/vacuumdb --all --analyze-only
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

The entire process was surprisingly painless and straightforward. Perhaps why proven technologies are so valuable.