Can not connect to local PostgreSQL

Question

I've managed to bork my local development environment.

All my local Rails apps are now giving the error:

PGError
could not connect to server: Permission denied
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

I've no idea what's caused this.

While searching for a solution I've updated all bundled gems, updated system gems, updated MacPorts. No joy.

Others have reported this issue when upgrading from OSX Leopard to Lion, due to confusion over which version of Postgres should be used (i.e., OSX version or MacPorts version). I've been running Lion for several months, so it seems strange that this should happen now.

I'm reluctant to mess around too much without first understanding what the problem is. How can I debug this methodically?

How can I determine how many versions of PostgreSQL are on my system, which one is being accessed, and where it is located? How do I fix this if the wrong PostgreSQL is being used?

Sorry for the noob questions. I'm still learning how this works! Thanks for any pointers.

EDIT

Some updates based on suggestions and comments below.

I tried to run pg_lsclusters which returned a command not found error.

I then tried to local my pg_hba.conf file and found these three sample files:

/opt/local/share/postgresql84/pg_hba.conf.sample
/opt/local/var/macports/software/postgresql84/8.4.7_0/opt/local/share/postgresql84/pg_hba.conf.sample
/usr/share/postgresql/pg_hba.conf.sample

So I assume 3 versions of PSQL are installed? Macports, OSX default and ???.

I then did a search for the launchctl startup script ps -ef | grep postgres which returned

0    56     1   0 11:41AM ??         0:00.02 /opt/local/bin/daemondo --label=postgresql84-server --start-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper start ; --stop-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper stop ; --restart-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper restart ; --pid=none
  500   372     1   0 11:42AM ??         0:00.17 /opt/local/lib/postgresql84/bin/postgres -D /opt/local/var/db/postgresql84/defaultdb
  500   766   372   0 11:43AM ??         0:00.37 postgres: writer process                                                                                                                                                                                                                                                                                                                   
  500   767   372   0 11:43AM ??         0:00.24 postgres: wal writer process                                                                                                                                                                                                                                                                                                               
  500   768   372   0 11:43AM ??         0:00.16 postgres: autovacuum launcher process                                                                                                                                                                                                                                                                                                      
  500   769   372   0 11:43AM ??         0:00.08 postgres: stats collector process                                                                                                                                                                                                                                                                                                          
  501  4497  1016   0 12:36PM ttys000    0:00.00 grep postgres

I've posted the contents of postgresql84-server.wrapper at http://pastebin.com/Gj5TpP62.

I tried to run port load postgresql184-server but received an error Error: Port postgresql184-server not found.

I'm still very confused how to fix this, and appreciate any "for dummies" pointers.

Thanks!

EDIT2

This issue began after I had some problems with daemondo. My local Rails apps were crashing with an application error along the lines of "daemondo gem can not be found". I then went through a series of bundle updates, gem updates, port updates and brew updates to try and find the issue.

Could this error be an issue with daemondo?

Forereach

Note that when your app connects, it connects through the tcp port instead of the unix domain socket. So you need to config postgres to accept network connections, at least from localhost. – Fireman

Why do you believe so? Obviously the drivers are trying to connect through Unix socket. Though they look for the socket at the wrong place. – Sosthenna

possible duplicate of How to resolve this PostgreSQL error on OS 10.6 (Snow Leopard) – Pak

Exactly. It appears to be a mac-thing. OP could use the -h flag, after verifying that the postmaster is running. – Grassland

Please post the output of pg_lsclusters and your pg_hba.conf file. – Mead

Thanks for all these suggestions. I've edited the original question with some results. I really appreciate your help to understand and fox this issue. – Forereach

It could be a PATH issue See similar question #6771149 – Antirrhinum

Answer 1

This really looks like a file permissions error. Unix domain sockets are files and have user permissions just like any other. It looks as though the OSX user attempting to access the database does not have file permissions to access the socket file. To confirm this I've done some tests on Ubuntu and psql to try to generate the same error (included below).

You need to check the permissions on the socket file and its directories /var and /var/pgsql_socket. Your Rails app (OSX user) must have execute (x) permissions on these directories (preferably grant everyone permissions) and the socket should have full permissions (wrx). You can use ls -lAd <file> to check these, and if any of them are a symlink you need to check the file or dir the link points to.

You can change the permissions on the dir for youself, but the socket is configured by postgres in postgresql.conf. This can be found in the same directory as pg_hba.conf (You'll have to figure out which one). Once you've set the permissions you will need to restart postgresql.

# postgresql.conf should contain...
unix_socket_directory = '/var/run/postgresql'       # dont worry if yours is different
#unix_socket_group = ''                             # default is fine here
#unix_socket_permissions = 0777                     # check this one and uncomment if necessary.

---

EDIT:

I've done a quick search on google which you may wish to look into to see if it is relavent. This might well result in any attempt to find your config file failing.

http://www.postgresqlformac.com/server/howto_edit_postgresql_confi.html

---

Error messages:

User not found in pg_hba.conf

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "couling", database "main", SSL off

User failed password auth:

psql: FATAL:  password authentication failed for user "couling"

Missing unix socket file:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Unix socket exists, but server not listening to it.

psql: could not connect to server: Connection refused
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Bad file permissions on unix socket file:

psql: could not connect to server: Permission denied
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Tm

thanks for these ideas @couling. This feels like the right direction. But there is something very strange with my setup. Bear in mind that this has been working perfectly until recently. I've looked through the three postgres directories on my system, they all contain conf.sample files (pg_hba, pg_ident, pg_service, etc) but no .conf files. This should not be the case, correct? Also the directory that should contain .s.PGSQL.5432 is empty. Instead I have a file located at /private/tmp/.s.PGSQL.5432.lock. PS returns postgres processes so it is running. Confused! – Forereach

I've added an extra link. As far as I know you can't really have postgresql without the postgresql.conf (even if it's been renamed). Your first task must be to find this. – Tm

its taken some time to trawl through my directories and find the right files, but this was indeed the issue. Thanks for the very detailed answer. Once I'd found the correct directory, I needed to set the socket directory, data directory, hba_file and ident_file in postgresql.conf. Thank you! – Forereach

Might help some people: I used brew to install postgres under my account. In my case it turned out that the folder /var/pgsql_socket/ was owned by the user _postgres and changing the ownership to my account (darren) fixed this issue. Not sure why brew did not set the ownership of the this folder correctly in the first place...? – Dillard

Thank you couling. I wasn't able to connect and setting up the unix_socket_directory was the right thing to do. – Stereoisomerism

This answer in combination with #13411186 really helped. – Hanus

I actually found that the socket was /private/tmp/.s.PGSQL.5432 from the answer below, setting host to "localhost" fixed it, weirdly enough – Moussorgsky

Answer 2

My gut feeling is that this is (again) a mac/OSX-thing: the front end and the back end assume a different location for the unix-domain socket (which functions as a rendezvous point).

Checklist:

• Is postgres running: ps aux | grep postgres | grep -v grep should do the trick
• Where is the socket located: find / -name .s.PGSQL.5432 -ls (the socket used to be in /tmp; you could start looking there)
• even if you locate the (unix-domain) socket, the client could use a different location. (this happens if you mix distributions, or of you have a distribution installed someplace and have another (eg from source) installation elsewhere), with client and server using different rendez-vous addresses.

If postgres is running, and the socket actually exists, you could use:

• psql -h /the/directory/where/the/socket/was/found mydbname

(which attempts to connect to the unix-domain socket)

; you should now get the psql prompt: try \d and then \q to quit. You could also try:

• psql -h localhost mydbname.

(which attempts to connect to localhost (127.0.0.1)

If these attempts fail because of insufficient authorisation, you could alter pg_hba.conf (and SIGHUP or restart) In this case: also check the logs.

A similar question: Can't get Postgres started

Note: If you can get to the psql prompt, the quick fix to this problem is just to change your config/database.yml, add:

host: localhost

or you could try adding:

host: /the/directory/where/the/socket/was/found

In my case, host: /tmp

Grassland

As I indicated in my answer below, the pg_hba.conf generates a different error. Otherwise yes, something is wrong with the name placement and usage of the socket. – Tm

@Grassland thanks for your answer. This got me started in the right direction, and couling's detailed answer gave the solution. – Forereach

Adding the host: localhost to my database.yml fixed my problem. Thanks :) – Fidel

mine is in /private/.... weirdness. I brew installed it, but this laptop was having issues too previously. Weird. that database.yml was key! – Spurrier

Weird. Adding "localhost" to my database.yml also worked but why so? I didn't had that before and everything was working fine. But I don't think I added anything of late and suddenly it was like that already. >_< – Trachoma

@index: the framework probably allows both connection methods, and the unix-domain socket method apparently did not work in your case. The localhost IP connection did work (after you told the framework to use/prefer it) – Grassland

Hmn. I see. But I was wondering why suddenly then it needed it as for before I did not have the need to specify it. – Trachoma

I couldn't find the socket anywhere, but -h localhost solved my problem. it works for createdb as well as psql. – Beater

I started having this issue when I updated abunch of gems but also rails itself (from 3.2.3 to 3.2.16). Maybe the default method of connecting changed from using tcp/ip to using a socket? – Hyperpituitarism

@Hyperpituitarism : you are wrong. an unixdomain socket is not defferent from a TCP/IP socket; it is only the naming/adressing method that differs. – Grassland

Haha, to the point. Misnomer aside, I just meant to give a clue as to when the problem started happening (ie. when you'd have to specify a host or else it would go looking for a socket on some arbitrary place on the filesystem). – Hyperpituitarism

the trick for me here was the socket path -- it was in /run not /var/run -- thanks! – Sporadic

Answer 3

Try uninstalling the pg gem (gem uninstall pg) then reinstalling -- if you use bundler, then bundle install, else gem install pg. Also, make sure path picks up the right version: Lion has a version of posgresql (prior versions didn't) and it may be in the path before your locally installed version (e.g. MacPorts, homebrew).

In my case: homebrew install of postgresql, updated postgresql, rails, etc. and then got this error. Uninstalling and reinstalling the pg gem did it for me.

Boise

bundle exec gem pristine pg <== will also do the trick – Bullion

I have no doubt the OP's error can be caused by any number of things, but this is the solution that worked for me. – Beaumarchais

Answer 4

The location of the socket file is baked into the gem at compile time. Thus you need to rebuild your pg gem.

gem pristine pg
# or
bundle exec gem pristine pg

This should resolve that particular issue.

Bullion

I started getting the problem described in this question after brew upgrading Postgres from 9.1x to 9.3x. This answer fixed it for me (so did adding 'localhost' to my database.yml, but I like this approach better). – Crumpled

Answer 5

If you're getting a similar error:

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

This might do the trick (it did for me):

initdb /usr/local/var/postgres -E utf8

The directory specified should be different if you're not using OSX/Brew.

Note: This is not the exact error message seen above, but this thread is the first result for that error message.

Armyworm

I always end up having this issue every now and then, but this is the first time I solve it with THIS solution. Also, had to rm -fr /usr/local/var/postgres before running initb – Hennebery

After I did this (both Neal's and Raf's), I got "Fatal" database does not exist error. So then ran "rake db:create:all" and "rake db:migrate" and finally everything worked again. – Sunup

I also had to do rm -rf /usr/local/var/postgres then initdb /usr/local/var/postgres -E utf8. But make sure postgres isn't running when you do this, otherwise the directory gets recreated nearly immediately. – Morrie

Answer 6

what resolved this error for me was deleting a file called postmaster.pid in the postgres directory. please see my question/answer using the following link for step by step instructions. my issue was not related to file permissions:

psql: could not connect to server: No such file or directory (Mac OS X)

the people answering this question dropped a lot of game though, thanks for that! i upvoted all i could

Godart

Since the accepted answer here notes a file permissions issue, it seems apparent that your post and this one are not related. – Bennybenoit

Answer 7

This is how I solved that error message, based partly on wildplasser's answer.

find / -name .s.PGSQL.5432 -ls 2> /dev/null
  => ... /tmp/.s.PGSQL.5432

So, there's my socket or whatever, but the client looks for it at:

/var/run/postgresql/.s.PGSQL.5432

So quite simply make a symbolic link to the /tmp/.s.PGSQL.5432:

sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

Hope this helps to anyone. The seems kind of wrong, but hey, it works!

Disrate

This worked for me: sudo ln -s /tmp/.s.PGSQL.5432 /var/pgsql_socket/.s.PGSQL.5432 – Baughman

Thanx :) This helped. But before making symlink do not forget to create sudo mkdir /var/run/postgresql folder (in my case id did not exist and was not created by installer for pg 9.3.X). – Doykos

Oh, this solution turned out to be 'one-time' solution that I had to redo after each reboot. For Rails I've found other solution - modify database config file (added answer below). – Doykos

Answer 8

I started getting this after upgrading to a new postgres - I didn't realize I had hold data files.

First I tried to start the postgres server:

postgres -D /usr/local/var/postgres

which is how I saw this error

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.0, which is not compatible with this version 9.3.5.

So then I found this answer on SO - related to an incompatibility error: https://serverfault.com/questions/342626/how-do-i-upgrade-postgresl-database-incompatibility-error

This is what fixed it

mv /usr/local/var/postgres /usr/local/var/postgres.old
initdb -D /usr/local/var/postgres

Eric

Answer 9

Just confirming I had a similar issue on PSQL and Django,

Looked like because my psql server was not shut down correctly and the postmaster.pid file was still present (should be deleted on proper shutdown automatically) in my postgres folder.

Deleted this and all good

Mahla

This is what solved the problem for me. My computer froze and I had to do a hard shutdown. When I started it back up, postgres was saying psql: could not connect to server: No such file or directory. Removing postmaster.pid from /usr/local/var/postgres made everything work again. – Blum

This was also what happened to me. My mac restarted because of a kernel panic and I had to deleted postmaster.pid before getting back to work. – Sallee

Answer 10

I was getting this same error (it turns out it was an error with postmaster.pid. Here's how I got postgres up and running again (credit to Ricardo Burillo for the fix):

$ rm /usr/local/var/postgres/postmaster.pid 
$ pg_resetxlog -f /usr/local/var/postgres

Dunlap

This worked for me, although pg_resetxlog returned `lock file "postmaster.pid" exists. – Mellar

Answer 11

I had similar problem when trying to use postgresql with rails. Updating my Gemfile to use new version of gem pg solve this problem for me. (gem pg version 0.16.0 works). In the Gemfile use:

gem 'pg', '0.16.0'

then run the following to update the gem

bundle install --without production
bundle update
bundle install

Terrence

This fixed the issue for me as well. I was on 0.15.1 from going through Rails 4.0 beta, and now using Ruby 2.0.0. Once I upgraded to pg 0.16.0 the problem was fixed. – Dawndawna

Solved the problem for me on 4.1.0.beta1 – Bargeman

Answer 12

This happened to me today after my Macbook's battery died. I think this can be caused by improper shutdown. All you have to do in cases such as mine is delete postmaster.pid

Navigate to the folder

cd /usr/local/var/postgres

Check to see if postmaster.pid is present

ls

Remove postmaster.pid

rm postmaster.pid

Iceblink

Answer 13

psql: 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 searching the solution for a while. So, this one fixed the issue for me as well (reinit db):

rm -r /usr/local/var/postgres  
initdb /usr/local/var/postgres -E utf8  
pg_ctl -D /usr/local/var/postgres -l logfile start

I use OS X 10.11.3 with brew.

Marasco

Answer 14

I read many topics about this error and the solution to me was to simply restart the postgres with:

sudo service postgresql restart

Which is not mentioned here.

Highgrade

Answer 15

In my case none of previous solutions was good. Instead of using socket, you can use TCP host + port number in Rails config file. So in database.yml file just add two lines like here:

...
adapter: postgresql
encoding: unicode
pool: 5
host: localhost
port: 5432

This solved my problem :)

Before I used this fix:

sudo mkdir /var/run/postgresql
sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

But after each reboot /tmp/.s.PGSQL.5432 was deleted and I had to repeat these commands. Solution works, but it is horrible, so better just modify Rails database config file :)

Doykos

Answer 16

Got this error when I was setting up Posgtres with Django, I'm using Back Track and it comes with Postgres installed. I assume the settings are the issue. I fixed it by removing it completely then reinstalling like so.

sudo apt-get remove postgresql
sudo apt-get purge postgresql

Now run:

apt-get --purge remove postgresql\*

to remove everything PostgreSQL from your system. Just purging the postgres package isn't enough since it's just an empty meta-package.

Once all PostgreSQL packages have been removed, run:

rm -r /etc/postgresql/
rm -r /etc/postgresql-common/
rm -r /var/lib/postgresql/
userdel -r postgres
groupdel postgres

You should now be able to:

apt-get install postgresql

Tito

Answer 17

MacOSX here. I had the same problem after upgrading my postresql install from a pre-9.1 to 9.1.2 using homebrew. (By the way, remember to dump databases before your upgrade with pg_dump, pre-9.1 databases are incompatible.) Same problem, same error messages.

Uninstalling the pg gem did the trick for me. I actually had to do quite a bit of dancing to discover the issue. First I did a global gem uninstall, clearing the deck of all the old gems (there were a few). Then I removed pg from my Gemfile, rebundled, restored the pg reference, and rebounded once more.

After that, it worked like a charm.

Glochidium

Answer 18

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

It's a PATH to psql from Postgres93.app. The row (command) runs every time console is started.

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.

Anzus

Answer 19

gem uninstall pg

On OS X with Homebrew:

gem install pg -- --with-pg-config=/usr/local/bin/pg_config

Moluccas

Answer 20

I had this problem plaguing me, and upon further investigation (running rake db:setup), I saw that rails was trying to connect to a previously used postgres instance - one which was stored in env variables as DATABASE_URL.

The fix: unset DATABASE_URL

source: https://mcmap.net/q/49284/-rails-activerecord-connecting-to-wrong-postgres-database

Gemmule

Answer 21

I tried most of the solutions to this problem but couldn't get any to work.

I ran lsof -P | grep ':5432' | awk '{print $2}' which showed the PID of the process running. However I couldn't kill it with kill -9 <pid>.

When I ran pkill postgresql the process finally stopped. Hope this helps.

Monoatomic