Upgrading Postgres Versions with your sanity intact

(and with a little mailing list drama)

(Last Updated: 2022-08-25 12:41:38 -0400 EDT)

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

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

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.

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

/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/

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

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.

Notes/Links/Things to think about