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:

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

The only workaround I found was to disable locale detection in the source code:

--- /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 @@
-               //      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.

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.

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

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

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

Clean ports (optional)

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