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.

Answer 2

For me, this works

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

Answer 3

"Postgres.app" is a better fix if you are on OS X

---

Here is the fix:

1. Stop the database
2. cd /var
3. sudo rm -r pgsql_socket
4. sudo ln -s /tmp pgsql_socket
5. chown _postgres:_postgres pgsql_socket
6. Restart PostgreSQL (not your computer)

More information is available at "postgresql 9.0.3. on Lion Dev Preview 1".

Answer 4

This worked for me for the error you mentioned. Do one of these:

1. Possibly change your default port specified in the postgres.conf file, if you have used something other than the default port 5432 while installing.

2. Change the port number in postgresql.conf and restart the DB server.

3. Instead of psql type the full command:

   psql -p 5432 -h localhost   
   

   • server name and the port no

Answer 5

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 6

I fixed this by upgrading postgres.

brew update
brew upgrade

Then I had to also upgrade the databases to be compatible with the new major version, because I upgraded from 10 to 11.

brew postgresql-upgrade-database

(source https://github.com/facebook/react-native/issues/18760#issuecomment-410533581)

Answer 7

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

"Homebrew install specific version of formula?" offers a much more comprehensive explanation along with alternative ways to fix the problem.

Answer 8

I encountered this error when running on Mac 10.15.5 using homebrew and seems to be a more updated solution that works where the ones above did not.

There is a file called postmaster.pid which should is automatically deleted when postresql exits.

If it doesn't do the following

• brew services stop postgresql <--- Failing to exit before rm could corrupt db
• sudo rm /usr/local/var/postgres/postmaster.pid

Answer 9

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 10

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 11

I don't really know Mac or Homebrew, but I know PostgreSQL very well.

You want to figure out where the logs are from PostgreSQL trying to start and what the socket directory is for PostgreSQL. By default when you build PG, the socket directory is /tmp/. If you didn't change that when you built PG and then you started PG, you should be able to see a socket file in /tmp if you do: ls -al /tmp

The socket file starts with a ".", so you won't see it with the '-a' to ls.

If you don't see a socket there, and you don't see anything from ps awux | grep postgres, then PG is probably not running, or maybe it is and it's the OSX-installed one. What might be happening is that you might be getting a conflict on listening on port 5432 on localhost- use netstat -anp to see what, if anything, is listening on 5432. If a Mac OSX PG is already listening on that port then that might be the problem.

Hope that helps. I have heard that homebrew can make things a bit ugly and a lot of people I've talked to encourage using a VM instead.

Answer 12

The Cause

Lion comes with a version of postgres already installed and uses those binaries by default. In general you can get around this by using the full path to the homebrew postgres binaries but there may be still issues with other programs.

The Solution

curl http://nextmarvel.net/blog/downloads/fixBrewLionPostgres.sh | sh

Via

http://nextmarvel.net/blog/2011/09/brew-install-postgresql-on-os-x-lion/

Answer 13

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 14

Hello world :)
The best but strange way for me was to do next things.

1) Download postgres93.app or other version. Add this app into /Applications/ folder.

2) Add a row (command) into the file .bash_profile (which is in my home directory):

export PATH=/Applications/Postgres93.app/Contents/MacOS/bin/:$PATH

psqlPostgres93.app

3) Launch Postgres93.app from /Applications/ folder. It starts a local server (port is "5432" and host is "localhost").

4) After all of this manipulations I was glad to run $ createuser -SRDP user_name and other commands and to see that it worked! Postgres93.app can be made to run every time your system starts.

5) Also if you wanna see your databases graphically you should install PG Commander.app. It's good way to see your postgres DB as pretty data-tables

Of, course, it's helpful only for local server. I will be glad if this instructions help others who has faced with this problem.

Answer 15

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.

Answer 16

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 17

Just restart Postgres as soon as error appears by running brew services restart postgresql and then try again

Answer 18

for me command rm /usr/local/var/postgres/postmaster.pid didn't work cause I installed a specific version of postgresql with homebrew.

the right command is rm /usr/local/var/postgres@10/postmaster.pid.

then brew services restart postgresql@10.

Answer 19

There might be different issues with running PostgreSQL locally. I would recommend to clean all versions of postgres installed and start fresh. Once you have it installed, it's very easy to recreate your database if you have your rails project with the up to date db/schema.rb

Here is how I usually install PostgreSQL on a Mac. If you're running your database under the root user locally, you might want to omit the last step that creates a todo user

Answer 20

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 21

If you're on MacOS and using homebrew I found this answer extremely helpful:

https://stackoverflow.com/a/27708774/4062901

For me, my server was running but because of an upgrade I was having issues connecting (note: I use brew services). If Postgres is running, try cat /usr/local/var/postgres/server.log and see what the logs say. My error was a migration, not a connection issue.

Also after the manual migration they propose (which does work but) I found there was no longer a table for my user. Try this command to fix that: createdb (answered via psql: FATAL: database "<user>" does not exist)

Answer 22

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