This is an old revision of the document!
# 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:
```bash FATAL: could not load locale “sr_YU.ISO8859-5” ```
The only workaround I found was to disable locale detection in the source code:
```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.
```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 ```