Showing posts with label postgresql upgrade. Show all posts
Showing posts with label postgresql upgrade. Show all posts

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