PostgreSQL error 'Could not connect to server:

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

for what it's worth, I experienced this same error when I had a typo (md4 instead of md5) in my pg_hba.conf file (/etc/postgresql/9.5/main/pg_hba.conf)

If you got here as I did, double-check that file once to make sure there isn't anything ridiculous in there.

Answer 2

It's very simple. Only add host in your database.yaml file.

Answer 3

I had that problem when I upgraded Postgres to 9.3.x. The quick fix for me was to downgrade to whichever 9.2.x version I had before (no need to install a new one).

$ ls /usr/local/Cellar/postgresql/
9.2.4
9.3.2
$ brew switch postgresql 9.2.4
$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
<open a new Terminal tab or window to reload>
$ psql

This answer offers a much more comprehensive explanation along with alternative ways to fix the problem.

Answer 4

One reason you get this error is that your local postgres database shuts down when you restart your computer. In a new terminal window, simply type:

$psql -h localhost 

to restart the server.

Answer 5

One of the reason could be that

unix_socket_directories in postgresql.conf file is not listed with the directory it is looking for.

In the question example it is looking for directory /tmp this has to be provided in the postgresql.conf file

something like this:

unix_socket_directories = '/var/run/postgresql,/tmp'    # comma-separated list of directories

This solution worked for me.

Answer 6

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.