96

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 cost $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 auwx|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

Fa11enAngel
  • 4,225
  • 2
  • 35
  • 33
Pamela Cook - LightBe Corp
  • 3,450
  • 5
  • 39
  • 71
  • 22
    Your issue boils down to the fact that the Mac OS X pre-installed version of PostgreSQL's `psql` is on your `PATH` before the version you installed. They look for the unix socket in different places. Either use tcp/ip by specifying `-h localhost` or preferably fix your `PATH` so the correct `psql` is found first. Apple's bizarre decision to not only bundle PostgreSQL, but mess with it so it put things in non-standard places is the root cause of this problem. – Craig Ringer Dec 14 '12 at 00:51
  • 3
    There is an ongoing issue in OSX, where the packagers decided to put the unix-domain socket at a different place than normal. Let me search ... BRB ... http://stackoverflow.com/a/8482546/905902 – wildplasser Dec 14 '12 at 01:09
  • I copied an old PATH in .bashrc. I have not installed another version of postgreSQL. Here is my PATH statement which includes code for RVM. If someone could tell me what to change it to I will do that. When I do which psql it is found in /usr/bin/psql. Thanks so much for all of you for your help. PATH="$PATH:$HOME/.rvm/bin:/usr/bin/psql:/usr/local:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin" I am also searching for the socket file while logged in as root. – Pamela Cook - LightBe Corp Dec 14 '12 at 01:15
  • 1
    For those using `homebrew` and `osx` @CraigRinger might have the right answer for you, confirm with `brew doctor`. – Karthik T Jan 08 '14 at 02:17

20 Answers20

303

I was able to add the following to my .bash_profile to prevent the error:

export PGHOST=localhost

This works because:

If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets.

Your OS supports Unix domain sockets, but PostgreSQL's Unix socket that psql needs either doesn't exist or is in a different location than it expects.

Specifying a hostname explicitly as localhost forces psql to use TCP/IP. Setting an environment variable PGHOST is one of the ways to achieve that. It's documented in psql's manual.

D-side
  • 8,408
  • 3
  • 23
  • 43
mmasters
  • 3,171
  • 1
  • 13
  • 4
  • 37
    For anyone running into this with a Rails app: you can specify the host in database.yml. – dwhalen Jul 18 '13 at 17:20
  • 12
    What kind of sorcery is this? I mean, seriously. Could you explain? – Sreejith Ramakrishnan Dec 02 '13 at 13:51
  • in fish shell `set -x PGHOST $PGHOST "localhost"` – Зелёный Apr 21 '14 at 07:16
  • 1
    I always come back to this answer! Saved my life again. This snippet really belongs on this page, http://postgresapp.com/documentation/cli-tools.html. – sambecker Aug 10 '16 at 14:47
  • 1
    I don't why, but it works for me! I met this problem in rails 5 when I use 'rails db:create', but I have already set the `host: localhost` in database.yml. @dwhalen – Spark.Bao Aug 29 '16 at 10:12
  • It worked for me, but I don't know how. Would love to get some explanations? – Shashank Apr 19 '17 at 07:04
  • 1
    This worked for Rails 5.2 without modifying `database.yml`, when this error appeared after downgrading Postgres to an older Homebrew-managed version. – SexxLuthor Jan 06 '19 at 10:41
  • it's 2020 and after upgrading Postgres.app, I ran into "No such file or directory: /var/pgsql_socket/.s.PGSQL.5432". After hours I found this which solved it. None of all the complicated solutions achieved anything but messing up Terminal.. – Daniela Feb 20 '20 at 21:21
37

Try paste in console this: 

$ mkdir /var/pgsql_socket/ 

$ ln -s /private/tmp/.s.PGSQL.5432 /var/pgsql_socket/
Top Questions
  • 1,842
  • 3
  • 24
  • 38
Bryan Algutria
  • 479
  • 4
  • 3
  • Finally, I got this answer. – Abs Oct 12 '16 at 12:21
  • Good. Even though I think I know why this is working, a little more explanation would be useful. This solution is better than the accepted answer, only the details are missing. – Glutexo Mar 07 '18 at 14:04
23

I was able to solve by simply filling in 127.0.0.1 for the PostgreSQL host address rather than leaving it blank. (Django Example)

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'database_name',
        'USER': 'database_user',
        'PASSWORD': 'pass',
        'HOST': '127.0.0.1',
        'PORT': '',
        }
}
Nick Woodhams
  • 10,553
  • 10
  • 47
  • 51
  • 1
    I feel kind of dumb now, I've been trying to fix this issue for about 2 days. On Windows I was using '127.0.0.1', but then I moved my code to production (Debian 7) and had to change it to ''. It is kind of weird that they give such an error message that leads one to believe that the problem might be somewhere else. – fang_dejavu Dec 29 '14 at 14:15
10

Open 'postgresql.conf' in you favourite editor. Look for the variable 'unix_socket_directories', it will most likely look like this: 

unix_socket_directories = '/private/tmp/'

Change the line to this:

unix_socket_directories = '/var/pgsql_socket/'

Note if you want the socket files in more than one directory comma separate them.

Craig Thomas
  • 101
  • 1
  • 2
  • This had the desired effect of trying to create the socket elsewhere, but the server wouldn't start because it didn't have the permissions to create files in the /var directory. I ended up changing configuration files at a higher level to simply use the socket where it was originally. Pretty much as given in this answer https://stackoverflow.com/a/29511357/1535177. – Eosis Apr 20 '18 at 09:21
9

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!

Jan Gerritsen
  • 171
  • 2
  • 9
7

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

David Battersby
  • 266
  • 3
  • 13
  • I needed to set my host to the directory that I found contained the `.s.PGSQL.5432` file. – Eosis Apr 20 '18 at 09:22
5

Check for the status of the database:

service postgresql status

If the database is not running, start the db:

sudo service postgresql start
Maximilian Ast
  • 2,954
  • 12
  • 33
  • 40
Sivakumar R.J
  • 49
  • 1
  • 2
4

Can you check your postgresql.conf file ??

On what port your postgres is running ??

I think it is not running on port 5432.If not change it to 5432

OR on terminal use

psql -U  postgres -p YOUR_PORT_NUMBER database_name
Hunter
  • 690
  • 7
  • 19
  • 1
    I cannot run the terminal command. I get the same error. I need help on where I could find the postgresql.conf file. I used to have a /var/pgsql folder when running Lion Server. I guess that is gone now. I have just posted my output for the ps grep command. I turned on the Wiki service in order to run PostgreSQL. I was told that I could use the sudo serveradmin commands without turning on any Server app services but they do not work. I am logged in as root right now so I should be able to do anything :) I checked ports in Network Utility. 5432 not used. – Pamela Cook - LightBe Corp Dec 13 '12 at 21:43
2

I had this problem with Django.

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

Ashwin Balamohan
  • 3,126
  • 2
  • 23
  • 47
2

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
mymusise
  • 385
  • 2
  • 8
2

For RubyOnRails app add localhost If you use custom Postgresql version

# config/database.yml
default: &default
  host: localhost
itsnikolay
  • 15,246
  • 3
  • 56
  • 60
  • This is a solution to move from unix socket to network socket. A better solution would be to recompile postrgres gem *pg* to match the changes on postrgesql due to upgrade or whatever change: ```gep pristine pg``` – Fa11enAngel Oct 19 '20 at 09:48
1

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).

Community
  • 1
  • 1
Obromios
  • 11,158
  • 11
  • 58
  • 98
1
apt-get install postgres-xc-client
apt-get install postgres-xc
Lucia
  • 41
  • 5
1
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 kept on getting the above error and none of the above solutions worked for me. Finally the following solution solved my problem on Mac OS X

Install postgres using brew

brew install postgres

Install brew services 

brew tap homebrew/services

To start postgres as a background service

brew services start postgresql

To stop postgres manually

brew services stop postgresql

We can also use brew services to restart Postgres

brew services restart postgresql
Sudharshan
  • 2,771
  • 1
  • 20
  • 22
1

check the postgres server is running with following code

sudo service postgresql status

if the postgres server is inactive, write the following command.

sudo service postgresql start
wailinux
  • 129
  • 4
1

I got this error after my computer froze and rebooted on its own. The solution for me was not found on this page, rather on another very highly rated SO question with the same error psql: could not connect to server: No such file or directory (Mac OS X). The answer: just delete this file /usr/local/var/postgres/postmaster.pid, then brew services restart postgresql did the trick. Heed the warning on the linked answer about killing postgres processes before doing this else you could corrupt your db permanently.

wetjosh
  • 5,880
  • 3
  • 21
  • 32
0

It took me a while but I was able to get this working finally after going through the suggestions offered and additional web searches being done. I used the information in the following YouTube video created by Mactasia:

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

When I did this I saw the file with .lock as the extension. However I still got the error when I tried to start the Rails Server when I resumed working on my Rails application using PostgreSQL. This time I got a permission denied error. This is when I remembered that not only did I have to change listen_addresses in the plist but I also had to change unit_socket_permissions to 0777. I also logged in as root to change the permissions on the var/pgsql_socket folder where I could access it at the user level. Postgres is working fine now. I am in the process of reloading my data from my SQL backup.

What I did not understand was that when I had wiki turned on PostgreSQL was supposedly working when I did a sudo serveradmin fullstatus postgres but I still got the error. Oh well.

sudo bangbang
  • 19,198
  • 7
  • 64
  • 71
Pamela Cook - LightBe Corp
  • 3,450
  • 5
  • 39
  • 71
0

File permissions are restrictive on the Postgres db owned by the Mac OS. These permissions are reset after reboot, or restart of Postgres: e.g. serveradmin start postgres.

So, temporarily reset the permissions or ownership:

sudo chmod o+rwx /var/pgsql_socket/.s.PGSQL.5432
sudo chown "webUser"  /var/pgsql_socket/.s.PGSQL.5432

Permissions resetting is not secure, so install a version of the db that you own for a solution.

Arup Rakshit
  • 109,389
  • 25
  • 234
  • 293
clewis
  • 1
0

First remove the installed postgres:

sudo apt-get purge postgr*
sudo apt-get autoremove

Then install 'synaptic':

sudo apt-get install synaptic
sudo apt-get update

Then install Postgres

sudo apt-get install postgresql postgresql-contrib
a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
Gurudath BN
  • 1,242
  • 19
  • 21
0

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
Floern
  • 31,495
  • 23
  • 98
  • 115
Bruno Filgueiras
  • 35
  • 4