Upgrading a Postgreql database to the next major version can seem daunting, and the upgrade docs don’t do a very good job of explaining what the actual steps/considerations to make very well. (see links below) SO! I decided to write my own tests/docs on a successful upgrade process going from 12->14, and hopefully this helps someone else wrap their head around this process.

What I wanted to test

  • Upgrading a large DB from Postgres 12 to 14
  • How easily can standbys be upgraded after the primary
  • Do tablespaces in other directories also get upgraded just as easily
  • List any noteable jank

I used 2 blank Ubuntu 20 VMs and installed both Postgresql 12 and 14 from the official repos. I decided to start/use 12 without systemd, and 14 as systemd, just so I don’t have a way to mess up one or the other, because starting the new one too early can totally matter.

Testing Setup/Steps

  • Copied pg_hba.conf and postgresql.conf in the /etc/ dir rules over to new version
  • Start 12 with /usr/lib/postgresql/12/bin/pg_ctl -D /etc/postgresql/12/main -l /var/log/postgresql/postgresql-12.log -w start
  • Created a database called rep_test with create database rep_test; \c rep_test;
  • Make a replication user with a super easy password CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 20 ENCRYPTED PASSWORD 'rep';

I also did a \password change on the postgres user so I could connect to it externally easier to monitor, but thats up to you.

Now to copy some test data into it from another existing DB. I used an existing 50GB table I had.

  • Create the schema for our data to be dumped to create schema {schema name};
  • I copied some data I had in another host already to test, an easy source of ~50GB of data (I’m also stripping out/changing any tablespaces the table might have had)
pg_dump -O -x -h {some host} -U {some name} -W -t {some schema/table} -d {some database} | sed -e' /^CREATE TABLESPACE /  d' -e 's/ *TABLESPACE .*;/;/' -e "s/SET default_tablespace = .*;/SET default_tablespace = '';/" | psql --dbname=rep_test -v ON_ERROR_STOP=1
  • On standby I started a basebackup to get the same data, and then started the server directly after.
/usr/lib/postgresql/12/bin/pg_basebackup -h {primary IP} -D /var/lib/postgresql/12/main/ -U replication -P -Xs -S secondary -R -C && /usr/lib/postgresql/12/bin/pg_ctl -D /etc/postgresql/12/main -l /var/log/postgresql/postgresql-12.log -w start

We now have ~50gb of data synced between 2 VMs.

Let’s get to it and upgrade!

Do NOT attempt to start the new cluster till you are confident all steps are done, including standbys.

The data/cluster is in a sort of metaphysical state that activates/runs some post-processing when the upgraded DB starts again. Once primary is upgraded it’s more or less good to go, but might as well keep it dormant till all others are done.

Stop all servers before continuing!

/usr/lib/postgresql/12/bin/pg_ctl -D /etc/postgresql/12/main -l /var/log/postgresql/postgresql-12.log -w stop

The Primary

The newer version of postgres must have a blank/initdb database before upgrading on primary only.

Copy postgresql.conf and pg_hba.conf settings over to new version.

Start a blank 14 DB with:

/usr/lib/postgresql/14/bin/initdb /var/lib/postgresql/14/main/

Main upgrade command from 12->14:

/usr/lib/postgresql/14/bin/pg_upgrade -j 10 -k -d /etc/postgresql/12/main/ -D /etc/postgresql/14/main/ -b /usr/lib/postgresql/12/bin/ -B /usr/lib/postgresql/14/bin/
  • -j: number of threads/jobs
  • -d/-D: config dirs, lowercase old, uppercase new
  • -b/-B: binary dirs, lowercase old, uppercase new

I’m using -k to create hard links instead of copying data. It’s a little more jank this way but it saves a buttload of time/disk space to not have the files twice, espeically in our case.

Standbys

Worst case we can just resync it all over again, but lets try NOT doing that for sanity sake.

I had to delete the /var/lib/postgresql/14 directory on the standby for this to work and sync correctly.

rsync --archive --delete --hard-links --size-only --no-inc-recursive /var/lib/postgresql/12 /var/lib/postgresql/14 {standby IP}:/var/lib/postgresql

(do a --dry-run first just so you can do a quick look through)

Tablespaces

Sync any related tablespace dirs over to the newer version. Noting we are syncing the contents of the tablespaces, and not the folders like we did the main postgres data directory.

rsync --archive --delete --hard-links --size-only --no-inc-recursive /var/lib/{some tablespace}/* {standby IP}:/var/lib/{some tablespace}/

Very Important things to check

  • Recreate standby.signal file in the 14 data directory on standby, make sure the primary_conninfo and primary_slot_name are set somwhere to what the previous version had.
  • Slots don’t get recreated after pg_upgrade, so lets remake the slots. We can do this with something like SELECT pg_create_physical_replication_slot('secondary'); on the primary once its back up. This will hopefully be enough for the standby to start syncing immediately from the primary.

After confirming above, start standby and hope for the best.

What I learned after many resets/tests

The Postgres docs are all over the place in terms of detail. The thread below I linked was from 2021, and I completely agree that there is a lot of details/steps missing, while also having a lot of janky steps that are not fully realized/explained or given modern examples. It should really be better explained because there is a lot that could go wrong, espeically around upgrading standbys.

  • Their examples are for Postgres 9, which has been EOL for almost a year now
  • Their examples show non-default locations like /opt/PostgreSQL/9.5 rather than the more standard /var/lib/postgresql/12/main (I know this is just the default, but I assume most people are getting postgres from package managers which would probably be this)
  • Recreating standby.signal on standby since it gets removed when you rsync from primary
  • I had to have to remove the 14 directory in the standby before rsync, though I’ve read doing an --relative gets around this, but uh still…

Notes/Links/Things to think about