osx snow leopard - PostgreSQL error 'Could not connect to server: No such file or directory'

Question

Like some others I am getting this error when I run rake db:migrate in my project or even try most database tasks for my Ruby on Rails 3.2 applications.

PGError (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 installed PostgreSQL with Homebrew a long time ago and following an attempted installation of MongoDB recently my PostgreSQL install has never been the same. I'm running OS X v10.6 Snow Leopard.

What's wrong and how do I better understand how PostgreSQL is and should be setup on my Mac?

So far (I think) this tells me that PostgreSQL is not running(?).

ps -aef|grep postgres                                                                                                   (ruby-1.9.2-p320@jct-ana) (develop) ?
  501 17604 11329   0   0:00.00 ttys001    0:00.00 grep postgres

But does this tell me that PostgreSQL is running?

? launchctl load -w /usr/local/Cellar/postgresql/9.1.4/homebrew.mxcl.postgresql.plist                                                        (ruby-1.9.2-p136) 
homebrew.mxcl.postgresql: Already loaded

How do I fix this? what am I not seeing?

PS: ~/Library/LaunchAgents includes two PostgreSQL .plist files. I am not sure if that's relevant.

org.postgresql.postgres.plist
homebrew.mxcl.postgresql.plist

I tried the following and got a result as below.

$ psql -p 5432 -h localhost

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

I've read since that this is occuring because OS X installs its own version of PostgreSQL and Homebrew installs a different version in a different place and the PostgreSQL commands are looking in the /tmp/ directory. You'll need to search more on Stack Overflow, but basically you symlink PostgreSQL so that anything looking in that tmp path actually finds the real path, if that makes sense.

This is the link where I found a few more things to try, specifically doing the symlink as per above, Mac OSX Lion Postgres does not accept connections on /tmp/.s.PGSQL.5432. I still wish someone would put together a decent explanation of the concepts behind installing PostgreSQL on OS X and why it's all so difficult.

Latest insights to help with troubleshooting:

$ which psql // This tells you which PostgreSQL you are using when you run $ psql. 

Then run:

$ echo $PATH

The key thing to take into account is this:

Ensure that the path entry for the copy of PostgreSQL you want to run COMES BEFORE the path to the OS X system's PostgreSQL.

This is a core requirement which decides which PostgreSQL gets run and is what I'm told leads to most of these issues.

Answer 1

Check there is no postmaster.pid in your postgres directory, probably /usr/local/var/postgres/

remove this and start server.

Check - https://github.com/mperham/lunchy is a great wrapper for launchctl.