November 14, 2017

PostgreSQL upgrade from 9.6.5 to 10.1 on Mac OS X

I'm using Homebrew for installing and upgrading new software on Mac OS, which is quite easy to use, but sometimes I have to also upgrade something by hand. For example PostgreSQL. In PostgreSQL server log (/usr/local/var/postgres/server.log) I have found:


FATAL:  database files are incompatible with server

DETAIL:  The data directory was initialized by PostgreSQL version 9.6.5, which is not compatible with this version 10.1.

Homebrew updated it from version 9.6.5 to the last version 10.1, but I have to upgrade (initialize) data directory (upgrade my database files) also for version 10.1.

PostgreSQL is located in /usr/local/var, and homebrew data dir is located in /usr/local/Cellar/postgresql.

Run the following:


#switch to old postgres version
brew switch postgres 9.6.5    # presuming you already installed 10.1  

#backup your database data files 
pg_dumpall > outputfile

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

mv /usr/local/var/postgres /usr/local/var/postgres.old 

#switch to new postgres version
brew switch postgres 10.1

initdb -D /usr/local/var/postgres

pg_ctl -D /usr/local/var/postgres -l logfile start

#load your database data files again
psql -d postgres -f outputfile

1 comment:

  1. Awesome! Directions are clear and straight to the point.
    Helped me upgrade my rails app from pg 6.5.4 to 9.6.2.
    Thank you!

    ReplyDelete