Table of Contents
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