Recently, I decided to update the dependencies in my local development environment. I develop on macOS and use homebrew for package management. One of the packages I tend to neglect keeping up to date is postgresql because of the data juggling required to make the upgrade (It's actually not too difficult of a process). I've been using 9.4 and the current major version is 9.6 so it seemed like a good time to upgrade before falling even further behind. I followed the instructions on this helpful blog post to upgrade from 9.4 to 9.5, and everything worked exactly as outlined. However, when I tried to follow the procedure for upgrading from 9.5 to 9.6, I was met with this error:
$ FATAL: could not open directory "/usr/local/share/postgresql95/timezonesets": No such file or directory HINT: This may indicate an incomplete PostgreSQL installation, or that the file "/usr/local/Cellar/postgresql95/9.5.5/bin/postgres" has been moved away from its proper location.
The issue appears to be related to having upgraded to a newer version of postgresql before running the
pg_upgrade command which attempts to start up the previous version of the server and fails. I tried lots of different methods switching back to the previous version of postgresql and following the procedure, but that just broke the process in a different place.
After a fair amount of googling I found a different method of upgrading using
pg_dumpall which did the trick, allowing me to bypass having to start up the older version of the server. Using this process you simply create a dump file of your data, upgrade postgres and initialize a new database, then import your dump file into the new database.
Create the dump file:
$ pg_dumpall > /path/to/file C#when performing this step it's recommended to use the latest version of the `pg_dumpall` binary. If you haven't switched versions using `brew link` yet, you can use the absolute path: C#$ /usr/local/Cellar/postgresql/9.6.1/bin/pg_dumpall > /path/to/file
Shut down the current server:
$ brew services stop postgresql95
Move the current data directory:
$ mv /usr/local/var/postgres /usr/local/var/postgresql95
Make a new data directory using the upgraded binary and start the server:
$ initdb /usr/local/var/postgres -E utf8 $ brew services start postgresql
Restore from the dump file:
$ psql -d postgres -f /path/to/file