Socket File “/var/pgsql_socket/.s.PGSQL.5432” Miss

Question

I just upgraded my MacMini Server from Lion Server to Mountain Lion using OS X Server. I am having the same problem with PostgreSQL that I did last year when I first installed Lion Server.

When I try to do any kind of PostgreSQL terminal command I get the following notorious error message that many have gotten over the years:

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

I was attempting to change the password for _postgres when I got the error. I tried several commands but got the same error. I just rebooted my server but no luck. I logged in as root to look at /var/pgsql_socket and the folder is empty. Folder /var/pgsql_socket_alt is also empty.

I have checked online about this. However just about all of the solutions I have read, including on Stack Overflow, suggest a removal and reinstall of PostgreSQL. I do not know but this does not seem like a plausible option because several options on the Server App use PostgreSQL. I contacted Apple Enterprise Support (no agreement) and I was told that my issue would have to be solved by the developers which would cast $695.

I have a website that is down right now because I cannot rebuild it. I don't know where to turn for help with this at this point. I will continue looking online to see if I can find something. However I hope that someone can give me an answer quick so I can rebuild my database.

Update: 12/13/2012 15:33 GMT-6

Here is my output for ps auwwx|grep postg:

_postgres      28123   0.0  0.1  2479696   7724   ??  Ss    3:01PM   0:00.04 /Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_real -D /Library/Server/PostgreSQL For Server Services/Data -c listen_addresses= -c log_connections=on -c log_directory=/Library/Logs/PostgreSQL -c log_filename=PostgreSQL_Server_Services.log -c log_line_prefix=%t  -c log_lock_waits=on -c log_statement=ddl -c logging_collector=on -c unix_socket_directory=/Library/Server/PostgreSQL For Server Services/Socket -c unix_socket_group=_postgres -c unix_socket_permissions=0770
server1        28216   0.0  0.0  2432768    620 s000  R+    3:02PM   0:00.00 grep postg
_postgres      28138   0.0  0.0  2439388    752   ??  Ss    3:01PM   0:00.01 postgres: stats collector process                           
_postgres      28137   0.0  0.0  2479828   1968   ??  Ss    3:01PM   0:00.00 postgres: autovacuum launcher process                           
_postgres      28136   0.0  0.0  2479696    544   ??  Ss    3:01PM   0:00.00 postgres: wal writer process                           
_postgres      28135   0.0  0.0  2479696    732   ??  Ss    3:01PM   0:00.01 postgres: writer process                           
_postgres      28134   0.0  0.0  2479696    592   ??  Ss    3:01PM   0:00.00 postgres: checkpointer process                           
_postgres      28131   0.0  0.0  2439388    368   ??  Ss    3:01PM   0:00.00 postgres: logger process 

Update: 12/13/2012 18:10 GMT-6

After intense web searching this video was found. I was able to get PostgreSQL working and remove the error. I am able to connect using pgadmin and phppgadmin. I was about to go back to Lion Server because of sheer frustration. Now I will not have to.

http://www.youtube.com/watch?v=y1c7WFMMkZ4

Answer 1

A much more simple solution (thanks to http://daniel.fone.net.nz/blog/2014/12/01/fixing-connection-errors-after-upgrading-postgres/) . I had upgraded to postgres 9.4. In my case, all I needed to do (after a day of googling and not succeeding)

gem uninstall pg
gem uninstall activerecord-postgresql-adapter
bundle install

Restart webrick, and done!

Answer 2

I had this problem with Django.

Fix it by explicitly setting your hostname to "localhost".

Answer 3

i make in word by doing this:

dpkg-reconfigure locales

and choose your preferred locales

pg_createcluster 9.5 main --start

(9.5 is my version of postgresql)

/etc/init.d/postgresql start

and then it word!

sudo su - postgres
psql

Answer 4

If you have the above problem but you have upgraded from Yosemite, then a different approach is needed as the upgrade solution can destroy some files. More details are at `pg_tblspc` missing after installation of latest version of OS X (Yosemite or El Capitan).

Answer 5

I just created a new cluster and that worked for me, I was using (PostgreSQL) 9.3.20:

sudo pg_createcluster 9.3 main --start

Answer 6

As mentioned by others in the comments, a really simple solution to this issue is to declare the database 'host' within the database configuration. Adding this answer just to make it a little more clear for anyone reading this.

In a Ruby on Rails app for example, edit /config/database.yml:

development:
  adapter: postgresql
  encoding: unicode
  database: database_name
  pool: 5
  host: localhost

Note: the last line added to specify the host. Prior to updating to Yosemite I never needed to specify the host in this way.

Hope this helps someone.

Cheers