# 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
```