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