Did you upgrade PostgreSQL on your Mac via Homebrew but now can’t connect to it? I got you covered. The problem is most likely the new version of PostgreSQL not being able to read your old version of PostgreSQL data files. So you need to migrate it.

Step 1

Unload the new version of PostgreSQL if you followed Homebrew instructions and loaded it:

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

brew info postgresql will print the Homebrew instructions again if you cleared your screen or closed the terminal window you used to upgrade.

Step 2

Move the PostgreSQL data directory to a version specific data directory:

mv -n /usr/local/var/postgres /usr/local/var/postgres-<OLD_VERSION>

Example with old version being 9.4.5:

mv -n /usr/local/var/postgres /usr/local/var/postgres-9.4.5

Note: The -n flag on mv means “Do not overwrite an existing file.”

Step 3

Start the old version of PostgreSQL that Homebrew fortunately keeps around in the Cellar:

/usr/local/Cellar/postgresql/<OLD_VERSION>/bin/postgres -D /usr/local/var/postgres-<OLD_VERSION>

Example with old version being 9.4.5:

/usr/local/Cellar/postgresql/9.4.5/bin/postgres -D /usr/local/var/postgres-9.4.5

Step 4

In a seperate terminal window export the data from the old version of PostgreSQL:

pg_dumpall > postgresql-<OLD_VERSION>-dump

Example with old version being 9.4.5:

pg_dumpall > postgresql-9.4.5-dump

Step 5

Stop the old version of PostgreSQL by just hitting ctrl-c in the original terminal window where the server is running.

Step 6

Initialize the new data directory for the new version of PostgreSQL:

initdb -D /usr/local/var/postgres

If you get the error fixing permissions on existing directory /usr/local/var/postgres ... initdb: could not change permissions of directory "/usr/local/var/postgres": Operation not permitted it means the directory already exists and you do not have permission to modify it. This will happen if you didn’t perform Step 1 above. First you need to run sudo chown <your_account_name>:admin /usr/local/var/postgres and then confirm that it’s empty ls -la /usr/local/var/postgres then re-run the initdb command above.

Step 7

If you have any custom configuration from your previous version of PostgreSQL, you’ll need to copy over the pg_hba.conf, pg_ident.conf, and postgresql.conf files from your old installation:

cp /usr/local/var/postgres-<OLD_VERSION>/*.conf /usr/local/var/postgres/.

Example with old version of 9.4.5:

cp /usr/local/var/postgres-9.4.5/*.conf /usr/local/var/postgres/.

Note: the -n flag does the same thing on cp as it does on mv, so if you’re concerned about overwriting files (although that is the point of this step) you can use the -n flag… cp -n ...

Step 8

Load the new version of PostgreSQL into launchd:

launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

You can verify it’s up and running with ps aux | grep postgres or psql postgres

Step 9

Import the dump file into the new version of PostgreSQL:

psql -d postgres -f <LOCATION_OF_DUMP_FILE>

Example with old version being 9.4.5 and the location of the dump file being your home directory:

psql -d postgres -f ~/postgresql-9.4.5-dump

Step 10

Happy dance


Identifying The Problem

Let’s step through how to diagnose this for those that are curious how to identify the problem themselves.

Since we’re on Mac OS X the first thing I do is launch the Console program which watches your computer’s log files. I’m expecting to find some sort of error message related to PostgreSQL and I find this: 1/28/16 2:55:50.200 PM com.apple.xpc.launchd[1]: (homebrew.mxcl.postgresql[19879]) Service exited with abnormal code: 1 Unfortunately this isn’t super informative but it confirms that for some reason the PostgreSQL service is exiting right after starting. To find the actual problem we need to look at PostgreSQL’s log file which is not currently configured to show in the Console app on my computer.

Homebrew installs PostgreSQL data and log files into /usr/local/var/postgres (which you can see in the output of Homebrew after install or after running brew info postgresql). Inside we find a server.log file which we send to tail with tail server.log and we find our error:

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.4, which is not compatible with this version 9.5.0.

With the error in hand we can now head to Google which will hopefully lead you to this article.

The PostgreSQL documentation is widely considered excellent. You can view their documentation for migrating at Upgrading a PostgreSQL Cluster page.