Why can I not access postgres when running it as a service?

Question

When I try to access postgres via a webapp, or open the postgres shell with psql -d template1, I get an error.

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"?

(*I've read a number of SO threads about this error, but recommended solutions did not fix this for me)

Start postgres as a service:

brew services restart postgresql

Stopping postgresql... (might take a while) ==> Successfully stopped postgresql (label: homebrew.mxcl.postgresql) ==> Successfully started postgresql (label: homebrew.mxcl.postgresql)

Manually run postgres in a terminal:

I can get everything to work by running below in a terminal window, but I would prefer to run it in the background.

postgres -D /usr/local/var/postgres9.6.3

Clusters:

I have 3 db clusters in /usr/local/var, but I would like to use postgres9.6.3/

postgres
postgres9.5/
postgres9.6.3/

Which:

which psql returns /usr/local/bin/psql

Path:

echo $PATH returns /usr/local/bin and /usr/local/var (added)

Other recommended solutions:

A lot of people where able to fix this by removing a /postgres/postmaster.pid, but this file doesn't exist for me.

UPDATE

Running ps -ef | grep postgres, outputs only,

501  2135  1530   0 12:08pm ttys002    0:00.00 grep postgres

After restarting postgres with brew servies, postgres.log in /usr/local/var/log contains,

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

Answer 1

If you want to use your 9.5 data with 9.6 server. you have to run:

• pg_upgrade(here) or
• pg_dump from 9.5 to file and pg_restore | psql depending on backup type on 9.6... (here)

if you choose first option, no further steps needed - your default version is 9.6 and it will work with existing data directory.

before running pg_upgrade, make a cold copy of data_directory

Answer 2

The service definition is still specifying your old 9.5 data directory. If you open ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist you should see a section that looks like this:

<key>ProgramArguments</key>
<array>
  <string>/usr/local/opt/postgresql/bin/postgres</string>
  <string>-D</string>
  <string>/usr/local/var/postgres9.5</string>
</array>

Change the final argument to point to your 9.6.3 data directory and restart the service. If it doesn't take you may need to log out and back in so it re-reads the plist.