postgres sometimes will not start after reboot

Question

Something weird is happening with my postgres installation after I upgraded to version 9.3.2 homebrew.

Sometimes and not every time, if I enter psql I get this error message:

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

It also appears that postgres is running because if I try and stop postgres with this command:

pg_ctl -D /usr/local/var/postgres stop -s -m fast

I get this response:

pg_ctl: server does not shut down

If I look in the server.log I can see the following entries:

FATAL: lock file "postmaster.pid" already exists HINT: Is another postmaster (PID 208) running in data directory "/usr/local/var/postgres"?

After some frantic googling, I am able to cure this by entering these commands:

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
rm ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

I would like to first of all understand what is happening and second of all I would like to fix it once and for all.

Can anyone explain what might be happening and a cure?

Update. File name changed. But I just went to ~/Library/LaunchAgents/ and deleted com.postgresapp.Postgres2LoginHelper.plist manually and all was well with Postgres. My problem was almost certainly the result of a crash. Didn't need to do the unload, but YMMV. — Greg

Timusan Timusan · Accepted Answer · 2014-05-05T08:26:42

First, try to pull up a log on PostgreSQL, on OSX (and with PG 9.3) this will most likely be here:

/Library/PostgreSQL/9.3/data/pg_log

Check this log and see if there are any entires in it that may explain more of what is happening. Post it here or in a past bin somewhere. This can help the community to debug the issue.

Next, the reason why 'pg_ctl' says the server is not shutdown is because you have a stale socket file and/or a stale .plist file that did not get removed on last shutdown. This may indicate that the PostgreSQl daemon was "crashed" or was shutdown by force and did not have a clean finish.

The commands you used removed the stale files and thus made way for a new socket file to be created.

Also it is important to know if this was a minor or a major upgrade of PostgreSQL. Major upgrades always require a migration of the data directory. Running an old PostgreSQL data directory with a new engine might have unexpected results.

postgres sometimes will not start after reboot

1 Answers