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.

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

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 

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}

snippet.bash

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

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

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