PostgreSQL: Why psql can't connect to server?

Solution 1:

The error states that the psql utility can't find the socket to connect to your database server. Either you don't have the database service running in the background, or the socket is located elsewhere, or perhaps the pg_hba.conf needs to be fixed.

Step 1: Verify that the database is running

The command may vary depending on your operating system. But on most *ix systems the following would work, it will search for postgres among all running processes

ps -ef | grep postgres

On my system, mac osx, this spits out

501   408     1   0  2Jul15 ??         0:21.63 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log

The last column shows the command used to start the server, and the options.

You can look at all the options available to start the postgres server using the following.

man postgres

From there, you'd see that the options -D and -r are respectively the datadir & the logfilename.

Step 2: If the postgres service is running

Use find to search for the location of the socket, which should be somewhere in the /tmp

sudo find /tmp/ -name .s.PGSQL.5432

If postgres is running and accepting socket connections, the above should tell you the location of the socket. On my machine, it turned out to be:

/tmp/.s.PGSQL.5432

Then, try connecting via psql using this file's location explicitly, eg.

psql -h /tmp/ dbname

Step 3: If the service is running but you don't see a socket

If you can't find the socket, but see that the service is running, Verify that the pg_hba.conf file allows local sockets.

Browse to the datadir and you should find the pg_hba.conf file.

By default, near the bottom of the file you should see the following lines:

# "local" is for Unix domain socket connections only
local       all       all       trust

If you don't see it, you can modify the file, and restart the postgres service.

Solution 2:

If there is no error in starting the Postgres service, follow these steps

Step 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

most probably the status will be down in your case. If not restart PostgreSQL service

Step 2: Restart the pg_ctlcluster

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

#restart PostgreSQL service
sudo service postgresql restart

Step 3: Step 2 failed and threw an error

If restarting pg_lsclusters was not successful, it will throw an error. My error was(You can see the errors in the logs /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`

Step 4: check ownership of postgres

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/

Step 5: Check postgres user belongs to ssl-cert user group

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 for 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 postgresql restart