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
DETAIL: The data directory was initialized by PostgreSQL version 9.3, which is not compatible with this version 9.6.3
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 ✔ email@example.com firstname.lastname@example.org
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
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 ----------------