# 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: ```bash FATAL: could not load locale "sr_YU.ISO8859-5" ``` This seems to be caused by a [remaining deprecated locale](https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=274718#c12). 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): ```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. ```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. ```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 ```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 ```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) ```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 ```