0

Here is my original error:

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

and in Rails...

PG::ConnectionBad: could not connect to server: Connection refused
  Is the server running on host "localhost" (::1) and accepting
  TCP/IP connections on port 5432?

I've searched around and found about a dozen answers on this -- The underly issue is that when I shut down my computer /usr/local/var/postgres/postmaster.pid does not get removed - implying postgres isn't shutdown properly. I've read that you should NOT delete the pid (as is often recommended) but instead kill it, even with kill -9 – but again, this is a temporary solution and suggests an underlying error.

EDIT: I could run cat /usr/local/var/postgres/postmaster.pid the first line gives me the the process id. I had this wrong originally.

When I run tail -r /usr/local/var/postgres/server.log | less to see the server log (in reverse) I get:

HINT:  Is another postmaster (PID 1167) running in data directory "/usr/local/var/postgres"?
FATAL:  lock file "postmaster.pid" already exists
HINT:  Is another postmaster (PID 1167) running in data directory "/usr/local/var/postgres"?
FATAL:  lock file "postmaster.pid" already exists
LOG:  database system is shut down
LOG:  shutting down
LOG:  autovacuum launcher shutting down
LOG:  received smart shutdown request
LOG:  using stale statistics instead of current ones because stats collector is not responding
LOG:  using stale statistics instead of current ones because stats collector is not responding

...

LOG:  using stale statistics instead of current ones because stats collector is not responding
LOG:  using stale statistics instead of current ones because stats collector is not responding
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system was shut down at 2016-05-25 17:36:26 MDT
LOG:  database system is shut down
LOG:  shutting down
LOG:  autovacuum launcher shutting down
LOG:  received smart shutdown request

It looks like the database shuts down correctly, but if it was I believe the postmaster.pid file would be removed like it is when I run e.g. brew services stop postgres.

ryanmtaylor
  • 101
  • 4

1 Answers1

1

The file /usr/local/var/postgres/postmaster.pid is not a PID in itself, it is a text file that contains a PID. In this case it seems to be 1167. Try cat /usr/local/var/postgres/postmaster.pid. The file is there because for some reason postgres was shut down uncleanly.

If this PID 1167 exists (ps -p $(cat /usr/local/var/postgres/postmaster.pid) should work to check), then you should not delete the file. If you remove the file while you have a postgres running, and then try to restart postgres, you very probably risk severe damage to the database.

You should stop the postgres process, by any means up to and including kill -9 -- but this last only if necessary. Start with the normal commands, escalate to kill and only if that doesn't work to kill -9.

Once (or if) you do not have a process with the PID in the file, then you can remove the file.

Actually, I thought postgres would detect that the process did not exist any more and remove the file itself, but understandably I have not experimented too much with this! Maybe it depends on the version.

Law29
  • 3,557
  • 1
  • 16
  • 28
  • Your answer is very thorough and really good, but it doesn't _quite_ answer my question -- why doesn't postgres shutdown properly when I shut down my computer? Is there any more info I can give you? Thank you! – ryanmtaylor May 26 '16 at 22:59
  • Right; could you provide the logs for when postgres is running correctly and you shut off the machine? – Law29 May 27 '16 at 04:23