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 locales issue while ensuing initdb
command, like this:
- snippet.bash
FATAL: could not load locale "sr_YU.ISO8859-5"
The only workaround I found was to disable locale detection in the source code:
- snippet.diff
--- /var/ports/basejail/usr/ports/databases/postgresql15-server/work/postgresql-15.2/src/backend/utils/adt/pg_locale.c 2023-08-22 18:55:52.003715000 +0200 +++ /tmp/pg_locale.c 2023-08-22 18:54:40.437733000 +0200 @@ -1727,8 +1727,8 @@ freelocale(loc); } else - // ereport(ERROR, - // (errmsg("could not load locale \"%s\"", collcollate))); + ereport(ERROR, + (errmsg("could not load locale \"%s\"", collcollate))); #elif defined(WIN32) && _WIN32_WINNT >= 0x0600 /* * If we are targeting Windows Vista and above, we can ask for a name
You can apply this patch while compiling new version's port (issue make
, stop it with ctrl+c after download and extract, patch, then issue make
again)
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