Upgrading PostgreSQL on FreeBSD

Here's a short tutorial on how to upgrade your FreeBSD-based PostgreSQL installation from 9.3 to 9.6.

In addition to the standard pg_dumpall/restore procedure when moving from one major PostgreSQL version to another, we also need to take care of 2 changes that happened in the FreeBSD package between 9.3 and 9.6. Namely, the datastore location and postgres username.

Please note that I'm upgrading the pkg-version of postgres 9.3 with all stock options (pgsql user, data store at /usr/local/pgsql/data).

First, stop all apps and services that are using the postgres DB to avoid any changes between the backup and service shutdown

Next, let's back up all our databases:

$ pg_dumpall -U pgsql | gzip > backup.sql.gz

Shut down the service:

# service postgresql stop

Remove 9.3 and install 9.6. I'm uninstalling the client since that also removes the server (and installing the server also installs the client):

# pkg remove postgresql93-client
# pkg install postgresql96-server

Next, we need to run vipw to remove the old pgsql user as well as activate the postgres user the 9.6 pkg just created:

# vipw
# delete this line: pgsql:*:70:70::0:0:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh

Initialize the new datastore:

$ sudo -u postgres initdb -D /var/db/postgres/data96

Start the service:

# service postgresql start

... and restore your backup:

$ gzcat backup.sql.gz | psql -U postgres postgres

Start your DB apps and verify everything is working properly. After that it should be safe to delete the backup and old datastore:

# rm backup.sql.gz /usr/local/pgsql/data

Finally make sure all your admin/backup scripts are using the new postgres user.