Upgrading from Postgres 9.3 on OSX

One day I upgraded XCode, and I'm not sure how, but my Postgres version upgraded. This is how I discovered it:

user@~/ $ pstart  
server starting

user@~/ $ psql  
psql: could not connect to server: No such file or directory  
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Say what? Why didn't my server start? I didn't have any stale PID files...

user@~/ $ rm /usr/local/var/postgres/postmaster.pid  
rm: /usr/local/var/postgres/postmaster.pid: No such file or directory  

Here's what my pstart command is:

alias pstart="pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start"  

So let's read /usr/local/var/postgres/server.log:

DETAIL:  The data directory was initialized by PostgreSQL version 9.3, which is not compatible with this version 9.6.3  

Oh boy.

user@~/ $ psql --version  
psql (PostgreSQL) 9.6.3  

I'm running 9.6.3 now! Great! Except that...

user@~/ $ cat /usr/local/var/postgres/PG_VERSION  
9.3  

Ok, now I have no idea what's going on. Maybe I lost a brew link to my old Postgres version somehow? That's happened to me before...

user@~/ $ brew link postgres  
Warning: Already linked: /usr/local/Cellar/postgresql/9.6.3  

Nope! Ok, looks like I need to upgrade my database. There are instructions on how to do this, except it requires having the original Postgres binary installed. Is there a postgres 9.3 Homebrew package?

user@~/ $ brew search postgres  
==> Searching local taps...
check_postgres                postgres-xc                   postgresql ✔                  postgresql@9.4                postgresql@9.5  

Nope! Great! Let's try it without the Postgres 9.3 binary and just point it to the newer one:

user@~/ $ pg_upgrade -b /usr/local/Cellar/postgresql/9.6.3/bin/ -B /usr/local/Cellar/postgresql/9.6.3/bin/ -d /usr/local/var/postgres.9.3.backup/ -D /usr/local/var/postgres  
Performing Consistency Checks  
-----------------------------
Checking cluster versions  
Old cluster data and binary directories are from different major versions.  
Failure, exiting  

Great!

1. Install postgres-9.3

brew tap petere/postgresql  
brew install postgresql-9.3  

Then follow these instructions. On the pg_upgrade step, use your postgres-9.3 install location:

pg_upgrade -b /usr/local/opt/postgresql\@9.3/bin -B /usr/local/Cellar/postgresql/9.6.3/bin/ -d /usr/local/var/postgres.9.3.backup/ -D /usr/local/var/postgres  

And hopefully you see:

Upgrade Complete  
----------------
comments powered by Disqus