18

When I run the rake db:migrate or run the rails s command, I get the same error: 

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

I get the error in the browser when I try rails s.

This is my database.yml

default: &default
adapter: postgresql
encoding: unicode

pool: 5

development:
<<: *default
database: books_development




test:
<<: *default
database: books_test



production:
<<: *default
database: books_production
username: abd
password: <%= ENV['BOOKS_DATABASE_PASSWORD'] %>

Note : I have the databases books_development; books_test ; and the postresql are running without problems when I try sudo /etc/init.d/postgresql start

I did run: 

create database books_development;
create database books_test;

in the psql console. And it said that it's done successfully

I tried a lot of solutions and I spent yesterday looking for a solution and no solution in the related questions solved my error.

I have postgresql-9.4 (the latest) and xubuntu 14.04

Any Ideas?

Bigfoot11
  • 911
  • 2
  • 11
  • 25
adyouri
  • 243
  • 1
  • 2
  • 7
  • Check this thread - http://ubuntuforums.org/showthread.php?t=869080 - maybe you'll find any hints... – Paweł Dawczak Mar 08 '15 at 11:30
  • I told I spent the whole yesterday looking for a solution , and of course I tried that thread ; it didn't work for me :( – adyouri Mar 08 '15 at 11:50
  • 2
    Thank you I solved It . I Just created a softlink using `sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432` and then edited the `/etc/postgresql/9.4/main/pg_hba.conf` ( If you have another version of postgresql you have to change 9.4 in the path) from `local all postgres peer` To: `local all postgres md5` – adyouri Mar 08 '15 at 12:38
  • It might be worth if you created an answer based on your comment, and mark it as the proper one - just for future references. Best! – Paweł Dawczak Mar 08 '15 at 13:05
  • in my case in /etc there is no postgresql folder...any idea what to do? – TAMIM HAIDER Jan 20 '21 at 14:26

9 Answers9

21

The convention for PostgreSQL packaged for Debian or Debian derivatives such as Ubuntu is to use /var/run/postgresql as the directory for Unix domain sockets. On the other hand the convention for self-compiled postgres client libs is to use /tmp, unless self-configured otherwise.

So the usual root cause of this mismatch between both is a mix of self-compiled client-side stuff with pre-compiled server-side packages (even if client and server are installed on the same machine, client-side and server-side are still distinct and can be out of sync).

Soft-linking from /tmp to this directory as suggested by the asker works except that the link will be lost at every reboot, because in general /tmp is emptied on reboot.

A better option would be to add as an entry in database.yml:

  • either host: /tmp if the real socket path is /tmp (self-compiled server, packaged client)

  • or host: /var/run/postgresql if the real socket path /var/run/postgresql/ (packaged server, self-compiled client).

When the value in the host field starts with a slash character, the postgres library knows that it's the location of a directory for local sockets rather than a hostname. The filename inside the directory .s.PGSQL.portnumber is generated and must not be specified, only the directory.

Another possibility is to configure the self-compiled software packages as closely as possible to Debian, overriding the defaults as they do.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • 1
    Thank you, adding the `host: /tmp` in `database.yml` does work very nice :) ; but I am afraid from the deploying to Heroku . Is it fine ? will the app work fine after deploying to heroku ? – adyouri Mar 09 '15 at 17:44
  • 1
    Yes, you may have to adapt `database.yml` for heroku, but nothing to be afraid of. Just read and follow their documentation: https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-ruby – Daniel Vérité Mar 09 '15 at 18:58
  • Thank you, adding the `host: /tmp` for development in database.yml does work for Rails 6, Postgres 12, macOS 10 Mojave (haven't gone to Catalina yet; waiting for 2 weeks without fixes). Ran into this problem (slight difference: `Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"') upgrading from Postgres 10 to 12. However now I have another problem with the upgrade; but that's another posting. – Greg Nov 01 '19 at 18:50
7

That means your Postgres server is not running.

Check Postgres Service status from Terminal

sudo service postgresql status

Enable Postgres Service, If not started

sudo service postgresql start

OR

sudo service postgresql restart

Now your command should work, If Postgres Service is successfully started.

Sumon Sarker
  • 2,707
  • 1
  • 23
  • 36
6

I had the same Is the server running locally and accepting connections on Unix domain socket “/var/run/postgresql/.s.PGSQL.5432”? error when typing psql into the postgres user in Ubuntu 14.04. I could not find an existing working solution.

The short answer for me was: my install made a var/pgsql_socket directory but no configuration files knew about it.

1) Find the postgres.conf file (it was in etc/postgresql/9.6/main for me)
2) change to listen_addresses = '*'
3) add another unix socket directory
unix_socket_directories = '/var/run/postgresql, /var/pgsql_socket' # comma-separated list of directories
4) at this point, sudo service postgresql start attempted to start but did not have authority to create the lock file.
* The PostgreSQL server failed to start. Please check the log output: 2016-10-05 17:14:55 CEST [28472-1] FATAL: could not create lock file "/var/pgsql_socket/.s.PGSQL.5432.lock": Permission denied 2016-10-05 17:14:55 CEST [28472-2] LOG: database system is shut down
5) Change permissions ( found from Mark Berry's comment here )
$ sudo chown root.postgres /var/pgsql_socket
$ sudo chmod g+wx /var/pgsql_socket
6) sudo service postgresql start
sudo -i -u postgres
psql

That finally worked for me

Community
  • 1
  • 1
descript
  • 99
  • 1
  • 6
4

I solved It . I Just created a softlink using :

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

and then edited the

/etc/postgresql/9.4/main/pg_hba.conf

( If you have another version of postgresql you have to change 9.4 in the path)

From:

local all postgres peer

To:

local all postgres md5

adyouri
  • 243
  • 1
  • 2
  • 7
  • I get `ln: failed to create symbolic link ‘/var/run/postgresql/.s.PGSQL.5432’: File exists` – Jason Kim Dec 21 '15 at 00:19
  • then delete it by `rm /var/run/postgresql/.s.PGSQL.5432` and try again :) – adyouri Dec 21 '15 at 12:13
  • @adyouri hey i tryed this But it give me error sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432 ERROR BELOW ln: failed to access '/var/run/postgresql/.s.PGSQL.5432': Permission denied – Pooja Mokariya Oct 27 '17 at 08:15
3

Solution:

Try this

export LC_ALL="en_US.UTF-8"

and this. (9.3 is my current PostgreSQL version. Write your version!)

sudo pg_createcluster 9.3 main --start
bogdanvlviv
  • 149
  • 2
  • 5
2

The exact same symptom can be caused by a stale lock file /var/run/postgresql/.s.PGSQL.5432.lock. One of the symptoms of this is psql reporting

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

even though there is clearly a socket with this path available as reported by netstat -lp --protocol=unix | grep postgres

The problem can be solved by removing the lock file and restarting postgresql. This is definitely less invasive than a purge and re-install.

sudo rm /var/run/postgresql/.s.PGSQL.5432.lock
sudo service postgresql restart
David Weber
  • 1,965
  • 1
  • 22
  • 32
2

On Mac OS X I usually get this error when my computer shuts down incorrectly, for example, due to power failure.

The solution I use is pretty simple and works 100% of the time:

# Find the postgres config folder
cd /usr/local/var/postgres

# remove file
rm postmaster.pid

# restart postgres
brew services restart postgres
Richard Jarram
  • 899
  • 11
  • 22
1

When I run into this error, my Postgres server was actually listening on a different port (5433) and not 5432. To solve this, add

port: 5433

to your database.yml file to instruct rails to use the same

Quango
  • 12,338
  • 6
  • 48
  • 83
tobasti
  • 11
  • 2
1

Running pg_lsclusters will list all the postgres clusters running on your device eg: 

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

if the status is down run 

#format is pg_ctlcluster <version> <cluster> <action>
sudo pg_ctlcluster 9.6 main start

If this process is not successfull it will throw the error. My error was(You can see the error log on /var/log/postgresql/postgresql-9.6-main.log) 

FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
Try adding `postgres` user to the group `ssl-cert`

make sure that postgres is the owner of /var/lib/postgresql/version_no/main eg: sudo chown postgres -R /var/lib/postgresql/9.6/main/

It happened to me and it turned out that I removed erroneously the Postgres user from "ssl-cert" group. Run the below code to fix the user group issue and fixing the permissions

#set user to group back with
sudo gpasswd -a postgres ssl-cert

# Fixed ownership and mode
sudo chown root:ssl-cert  /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 740 /etc/ssl/private/ssl-cert-snakeoil.key

# now postgresql starts! (and install command doesn't fail anymore)
sudo service postgres restart
Noushad
  • 6,063
  • 3
  • 25
  • 28