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

So for a lot of the issues here, it seems that people were already running psql and had to remove postmaster.pid. However, I did not have that issue as I never even had postgres installed in my system properly.

Here's a solution that worked for me in MAC OSX Yosemite

1. I went to http://postgresapp.com/ and downloaded the app.
2. I moved the app to Application/ directory
3. I added it to $PATH by adding this to .bashrc or .bash_profile or .zshrc : export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin
4. I ran the postgres from the Applications directory and ran the command again and it worked.

Hope this helps! Toodles!

Also, this answer helped me the most: https://stackoverflow.com/a/21503349/3173748

Answer 2

When you run: psql -p 5432 -h localhost it does not use the loopback driver but uses a real socket and hence you need to configure postgres to accept socket connections. Hence even though pgadmin and other clients may work, psql will not.

You need to change both the postgresql.conf file and the pg_hba.conf files so allow connection over a real socket. This will also allow connection from a remote ip which is why it is disabled by default.

These files are in the data directory but the actual location can be different depending on how postgres was installed. With postgres running, run the command:

ps -ef | grep postmaster

These two files are the -D directory (maybe /usr/local/pgsql/data).

For the postgresql.conf file, uncomment the listen_address and change it to be:

listen_address = '*'

For the pg_hba.conf add the line:

host all all 0.0.0.0/0 md5

Answer 3

If you are running Homebrew, uninstall Postgresql end pg gems:*

$ gem uninstall pg
$ brew uninstall postgresql

Download and run the following script to fix permission on /usr/local:* https://gist.github.com/rpavlik/768518

$ ruby fix_homebrew.rb

Then install Postgres again and pg gem:*

$ brew install postgresql  
$ initdb /usr/local/var/postgres -E utf8

To have launchd start postgresql at login run:

$ ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents 

Or start manually.

Install pg gem

$ gem install pg

I hope have helped

Answer 4

For me, this works

rm /usr/local/var/postgres/postmaster.pid

Answer 5

UPDATE: HERE IS A BETTER FIX IF YOU ON OSX...

http://postgresapp.com/

---

Old Answer:

Here is the fix, more info here:

https://github.com/mxcl/homebrew/issues/5004

First, stop the db, then

1. cd /var
2. rm -r pgsql_socket
3. ln -s /tmp pgsql_socket
4. chown _postgres:_postgres pgsql_socket
5. restart postgres (not your computer)

Answer 6

I had PostgreSQL 9.3 and got the same error,

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?

I fixed this using:

chmod 777  /var/lib/pgsql/9.3/data/pg_hba.conf 
service postgresql-9.3 restart

It works for me.