Upgrading PostgreSQL version on FreeBSD

For minimal downtime during migration, we will use pg_upgrade tool, which needs both old and new PostgreSQL binaries available. To do so, we will use FreeBSD's ports system, without make clean at first to keep compiled binaries.

A note about locales

You might encounter weird locale issue while ensuing initdb command, like this:

snippet.bash
FATAL:  could not load locale "sr_YU.ISO8859-5"

This seems to be caused by a remaining deprecated locale. You can delete it by using the following command, depending on if you're running PostgreSQL in a jail (I didn't find a cleaner way, sorry):

snippet.bash
sudo rm -rf /usr/share/locale/sr_YU.ISO8859-5
sudo rm -rf /basejail/usr/share/locale/sr_YU.ISO8859-5

Compile old and new version from ports

Replace PSQLOLDVERSION with major version of your existing PostgreSQL installation, and PSQLNEWVERSION with the new major version.

snippet.bash
PSQLOLDVERSION=13
PSQLNEWVERSION=15
 
cd /usr/ports/databases/postgresql${PSQLOLDVERSION}-server/ && make
cd /usr/ports/databases/postgresql${PSQLOLDVERSION}-client/ && make deinstall
cd /usr/ports/databases/postgresql${PSQLNEWVERSION}-server/ && make
cd /usr/ports/databases/postgresql${PSQLNEWVERSION}-contrib/ && make
 
# if you disabled locale detection in initdb, you might want to save patched initdb binary:
cp -vrp /var/ports/basejail/usr/ports/databases/postgresql-server${PSQLNEWVERSION}/work/stage/usr/local/bin/initdb /usr/local/bin/initdb-nolocale
 
cd /usr/ports/databases/postgresql${PSQLOLDVERSION}-server/ && make deinstall
cd /usr/ports/databases/postgresql${PSQLOLDVERSION}-contrib/ && make deinstall
cd /usr/ports/databases/postgresql${PSQLNEWVERSION}-server/ && make install
cd /usr/ports/databases/postgresql${PSQLNEWVERSION}-contrib/ && make install
 
service postgresql stop 

Migrate data

I run PostgreSQL inside a FreeBSD jail, so base path is /var/ports/basejail/. Adjust path if you run it outside a jail.

snippet.bash
sudo -u postgres -i
 
# locale and encoding may vary
initdb -D /home/postgres/data${PSQLNEWVERSION} --locale=C --encoding=UTF8
 
# check upgrade
pg_upgrade -b /var/ports/basejail/usr/ports/databases/postgresql${PSQLOLDVERSION}-server/work/stage/usr/local/bin -B /usr/local/bin -d /home/postgres/data${PSQLOLDVERSION} -D /home/postgres/data${PSQLNEWVERSION} -c
# do upgrade
pg_upgrade -b /var/ports/basejail/usr/ports/databases/postgresql${PSQLOLDVERSION}-server/work/stage/usr/local/bin -B /usr/local/bin -d /home/postgres/data${PSQLOLDVERSION} -D /home/postgres/data${PSQLNEWVERSION}

Migrate configuration files

snippet.bash
cp data${PSQLOLDVERSION}/postgresql.conf data${PSQLNEWVERSION}/postgresql.conf
cp data${PSQLOLDVERSION}/pg_hba.conf data${PSQLNEWVERSION}/pg_hba.conf

Go back to root user and finish migration

snippet.bash
exit
service postgresql start
# I'm using pgbouncer on port 5432, so I chose 6432 for "native" port
psql -p 6432 < update_extensions.sql
/usr/local/bin/vacuumdb --port 6432 --all --analyze-in-stages
 
./delete_old_cluster.sh

Clean ports (optional)

snippet.bash
cd /usr/ports/databases/postgresql${PSQLOLDVERSION}-server/ && make clean
cd /usr/ports/databases/postgresql${PSQLOLDVERSION}-client/ && make clean
cd /usr/ports/databases/postgresql${PSQLOLDVERSION}-contrib/ && make clean
 
cd /usr/ports/databases/postgresql${PSQLNEWVERSION}-server/ && make clean
cd /usr/ports/databases/postgresql${PSQLNEWVERSION}-client/ && make clean
cd /usr/ports/databases/postgresql${PSQLNEWVERSION}-contrib/ && make clean