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