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.

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)

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 

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}

snippet.bash

cp data${PSQLOLDVERSION}/postgresql.conf data${PSQLNEWVERSION}/postgresql.conf
cp data${PSQLOLDVERSION}/pg_hba.conf data${PSQLNEWVERSION}/pg_hba.conf

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

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