Error with Postgresql Datababse:Is the Server Running Locally and Accepting Connections on Unix Domain Socket "/Var/Run/Postgresql/.S.Pgsql.5432"

error with postgresql datababse : Is the server running locally and accepting connections on Unix domain socket /var/run/postgresql/.s.PGSQL.5432?

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.

PostgreSQL: Why psql can't connect to server?

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.

psql: error: connection to server on socket /var/run/postgresql/.s.PGSQL.5432 failed: FATAL: Peer authentication failed for user postgres (Ubuntu)

You can edit your .conf files with privileges using an editor, for my case it is nano.

$sudo nano /etc/postgresql/14/main/pg_ident.conf

Map your user by adding this line

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
user1             <computer-username>               postgres

Replace the <computer-username> with the System-Username, which can be found using the whoami command. Type in your terminal:

$whoami

Then go ahead to the pg_hba.conf

$sudo nano /etc/postgresql/14/main/pg_hba.conf

Add your postgre user, with method=peer, as shown below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer

This worked for me.

Postgres could not connect to server

Had a similar problem; a pid file was blocking postgres from starting up. To fix it:

$ rm /usr/local/var/postgres/postmaster.pid
$ brew services restart postgresql

and then all is well.

---

UPDATE:

For Apple M1 (Big Sur) users, do this instead:

$ rm /opt/homebrew/var/postgres/postmaster.pid
$ brew services restart postgresql

---

UPDATE (Nov 15 2022):

For Apple M1 / M2 (Ventura) users, just do this:

$ bundle install (just to make sure pg is installed)
$ brew services restart postgresql

Psql could not connect to server: No such file or directory, 5432 error?

I've had this same issue, related to the configuration of my pg_hba.conf file (located in /etc/postgresql/9.6/main). Please note that 9.6 is the postgresql version I am using.

The error itself is related to a misconfiguration of postgresql, which causes the server to crash before it starts.

I would suggest following these instructions:

1. Certify that postgresql service is running, using sudo service postgresql start
2. Run pg_lsclusters from your terminal

3. Check what is the cluster you are running, the output should be something like:

   Version - Cluster Port Status Owner Data directory

   9.6 ------- main -- 5432 online postgres /var/lib/postgresql/9.6/main

   Disregard the '---' signs, as they are being used there only for alignment.
   The important information are the version and the cluster. You can also check whether the server is running or not in the status column.

4. Copy the info from the version and the cluster, and use like so:
   pg_ctlcluster <version> <cluster> start, so in my case, using version 9.6 and cluster 'main', it would be pg_ctlcluster 9.6 main start
5. If something is wrong, then postgresql will generate a log, that can be accessed on /var/log/postgresql/postgresql-<version>-main.log, so in my case, the full command would be sudo nano /var/log/postgresql/postgresql-9.6-main.log.
6. The output should show what is the error.
   
   

   2017-07-13 16:53:04 BRT [32176-1] LOG: invalid authentication method "all"
   
   2017-07-13 16:53:04 BRT [32176-2] CONTEXT: line 90 of configuration file "/etc/postgresql/9.5/main/pg_hba.conf"
   
   2017-07-13 16:53:04 BRT [32176-3] FATAL: could not load pg_hba.conf

7. Fix the errors and restart postgresql service through sudo service postgresql restart and it should be fine.

I have searched a lot to find this, credit goes to this post.

Best of luck!

---

Related Topics