Why can't I access postgres when I start it as a service?

Question

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

psql: cannot connect to server: no such file or directory. does the server work locally and accepts connections in the Unix socket domain "/tmp/.s.PGSQL.5432"?

(* I read several SO threads about this error, but the recommended solutions did not fix it for me)

Start postgres as a service:

 brew services restart postgresql 

Stop postgresql ... (may take some time) ==> Successfully stopped postgresql (label: homebrew.mxcl.postgresql) ==> Successfully started postgresql (label: homebrew.mxcl.postgresql)

Manually run postgres in a terminal:

I can make everything work by working below in the terminal window, but I would prefer to run it in the background.

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

Clusters:

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

 postgres postgres9.5/ postgres9.6.3/ 

What:

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 you can fix this by deleting /postgres/postmaster.pid , but this file does not exist for me.

UPDATE

Running ps -ef | grep postgres ps -ef | grep postgres , only exits,

 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 data 9.5 with server 9.6. you need to run:

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

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

before running pg_upgrade , make a cold copy of data_directory

Answer 2

The service definition still indicates your old data directory 9.5. If you open the file ~ / Library / LaunchAgents / homebrew.mxcl.postgresql.plist, you will 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 data directory 9.6.3 and restart the service. If this is not required, you may need to log out and go back so that it re-reads the plist.