Upgrading A PostgreSQL Jail

I had an older PostgreSQL 12 install using FreeBSD jails and ZFS. It was setup using iocage on FreeBSD 12.1, and I wanted to upgrade to FreeBSD 13.0 and PostgreSQL 14. I could have done a normal freebsd-update but that would end up wasting a bunch of space on top of the clone that iocage creates. Doing that is fine for minor updates but in this case it is better to delete the jail and recreate it using the new FreeBSD release and PostgreSQL version.

After a bit of research, which included reading two of Michael Lucas’s books on ZFS and jails , as well has this helpful post I assembled a vague plan using snapshots.

The data for my PostgreSQL database is in a separate ZFS dataset, not part of the jail, so preserving that was easy. The hard part was going to be that <code>pg_upgrade</code> requires access to the old installation of PostgreSQL binaries, but you cannot install the PostgreSQL versions in parallel on FreeBSD. If I upgraded to PostgreSQL 14 in the existing jail I’d have to make a new PostgreSQL 12 install in a temporary jail and that seemed like a lot more work than it ought to be.

I already had an existing PostgreSQL 12, if I could mount that installation in the new jail then I’d be able to access the binaries. But I also wanted to reuse the jail name for the new one, which meant destroying the old jail before creating the new one. I could have just named it something new, but that would mess with my jail naming scheme. I didn’t need the whole jail though, just the root filesystem which iocage places under zstorage/iocage/jails/pgsql.brianlane.com/root.

Please note that this worked for me, but is likely not the most efficient way to accomplish this…

ZFS snapshots are basically a filesystem bookmark, saving the state at a certain point in time, and allowing you to return to that state if anything goes wrong. I first made a snapshot of my PostgreSQL data directory so that I could recover if anything went wrong. Luckily PostgreSQL names the data directory using the major version so the existing directory is data12 and the new one will be data14.

iocage stop pgsql.brianlane.com
zfs snapshot zstorage/pgsql@pgsql12
zfs list -t snapshot zstorage/pgsql

I then made a snapshot of the old jail’s root directory tree:

zfs snapshot zstorage/iocage/jails/pgsql.brianlane.com/root@pgsql12
zfs list -t snapshot zstorage/iocage/jails/pgsql.brianlane.com/root

I had thought I could then destroy the jail and make a new one, but iocage won’t let you destroy the jail when it has an existing snapshot. The way to solve this is to clone the snapshot, and promote the clone which disconnects it from the jail.

zfs clone zstorage/iocage/jails/pgsql.brianlane.com/root@pgsql12 zstorage/pgsql12-pg_upgrade
zfs promote zstorage/pgsql12-pg_upgrade

Now I can destroy the old jail and create my new one with PostgreSQL 14.

iocage destroy pgsql.brianlane.com

My jail setup uses an Ansible playbook. I adjusted it for the latest FreeBSD release, and for the new version of PostgreSQL. I also added a line to the playbook to mount the old version 12 filesystem on /mnt/:

iocage fstab -a {{ jail_name }} /zstorage/pgsql12-pg_upgrade /mnt nullfs rw 0 0

And created the new jail using ansible-playbook. This also starts up the jail and the new version of PostgreSQL 14 which initializes a new empty database named data14. I think I could have mounted the old jail manually instead of adding it to Ansible, but it worked.

Next I needed to run pg_upgrade while passing it the paths to the old and new databases, and to the old and new binaries. Not surprisingly version 12 and 14 are linked to different library versions so you need to use LD_LIBRARY_PATH to list all the possible paths. I chose to use the latter, so I connected to the jail console and ran:

/usr/local/etc/rc.d/postgresql stop
su - postgres
export LD_LIBRARY_PATH=/lib:/usr/lib:/usr/local/lib:/mnt/lib:/mnt/usr/lib:/mnt/usr/local/lib
/usr/local/bin/pg_upgrade -b /mnt/usr/local/bin -B /usr/local/bin -d /var/db/postgres/data12 -D /var/db/postgres/data14 -j 8 -k

This uses 8 cores (-j 8) and hardlinks the files (-k) to the old database files for a faster upgrade. This is why a snapshot of the version 12 data is important, you cannot go back to the old database once this is done since it has changed the old files. If there had been a problem I could have stopped the jail and restored the data from the snapshot, allowing me to try again. In my case everything ran perfectly.

The LD_LIBRARY_PATH is needed so that the old version 12 binaries can find the old versions of the libraries it was linked with. Otherwise you will see pg_upgrade return an error telling you it cannot run the old version.

pg_upgrade will output some instructions when it is finished running. Follow them :) In my case I started the server and updated the indexes with:

/usr/local/etc/rc.d/postgresql start
/usr/local/bin/vacuumdb --all --analyze-in-stages

And I deleted the data12 directory which has now been upgraded to files in the data14 directory and then removed the mount of the old jail’s filesystem using iocage:

iocage fstab -r pgsql.brianlane.com 1

Make sure to check what index the mount is using before doing that, otherwise you might accidentally remove the database file mount instead.

It is now safe to remove the old jail’s promoted filesystem:

zfs destroy -r zstorage/pgsql12-pg_upgrade

Make sure the database is operating correctly. Connect to it with whatever applications were using it and make sure they are functioning. Eventually you can delete the snapshot of the old data when you are confident that everything is running as expected.