Psql ошибка подключиться к серверу через сокет

I experienced this issue when working with PostgreSQL on Ubuntu 18.04.

I checked my PostgreSQL status and realized that it was running fine using:

sudo systemctl status postgresql

I also tried restarting the PotgreSQL server on the machine using:

sudo systemctl restart postgresql

but the issue persisted:

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

Following Noushad’ answer I did the following:

List all the Postgres clusters running on your device:

pg_lsclusters

this gave me this output in red colour, showing that they were all down and the status also showed down:

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5433 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5434 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

Restart the pg_ctlcluster for one of the server clusters. For me I restarted PG 10:

sudo pg_ctlcluster 10 main start

It however threw the error below, and the same error occurred when I tried restarting other PG clusters:

Job for postgresql@10-main.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@10-main.service" and "journalctl -xe" for details.

Check the log for errors, in this case mine is PG 10:

sudo nano /var/log/postgresql/postgresql-10-main.log

I saw the following error:

2020-09-29 02:27:06.445 WAT [25041] FATAL:  data directory "/var/lib/postgresql/10/main" has group or world access
2020-09-29 02:27:06.445 WAT [25041] DETAIL:  Permissions should be u=rwx (0700).
pg_ctl: could not start server
Examine the log output.

This was caused because I made changes to the file permissions for the PostgreSQL data directory.

I fixed it by running the command below. I ran the command for the 3 PG clusters on my machine:

sudo chmod -R 0700 /var/lib/postgresql/10/main
sudo chmod -R 0700 /var/lib/postgresql/11/main
sudo chmod -R 0700 /var/lib/postgresql/12/main

Afterwhich I restarted each of the PG clusters:

sudo pg_ctlcluster 10 main start
sudo pg_ctlcluster 11 main start
sudo pg_ctlcluster 12 main start

And then finally I checked the health of clusters again:

pg_lsclusters

this time around everything was fine again as the status showed online:

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5434 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

That’s all.

I hope this helps

Resetting PostgreSQL

My original answer only included the troubleshooting steps below, and a workaround. I now decided to properly fix it via brute force by removing all clusters and reinstalling, since I didn’t have any data there to keep. It was something along these lines, on my Ubuntu 21.04 system:

sudo pg_dropcluster --stop 12 main
sudo pg_dropcluster --stop 14 main
sudo apt remove postgresql-14
sudo apt purge postgresql*
sudo apt install postgresql-14

Now I have:

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

And sudo -u postgres psql works fine. The service was started automatically but it can be done manually with sudo systemctl start postgresql.

Incidentally, I can recommend the PostgreSQL docker image, which eliminates the need to bother with a local installation.

Troubleshooting

Although I cannot provide an answer to your specific problem, I thought I’d share my troubleshooting steps, hoping that it might be of some help. It seems that you are on Mac, whereas I am running Ubuntu 21.04, so expect things to be different.

This is a client connection problem, as noted by section 19.3.2 in the docs.

The directory in my error message is different:

$ sudo su postgres -c "psql"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

I checked what unix sockets I had in that directory:

$ ls -lah /var/run/postgresql/
total 8.0K
drwxrwsr-x  4 postgres postgres  160 Oct 29 16:40 .
drwxr-xr-x 36 root     root     1.1K Oct 29 14:08 ..
drwxr-s---  2 postgres postgres   40 Oct 29 14:33 12-main.pg_stat_tmp
drwxr-s---  2 postgres postgres  120 Oct 29 16:59 14-main.pg_stat_tmp
-rw-r--r--  1 postgres postgres    6 Oct 29 16:36 14-main.pid
srwxrwxrwx  1 postgres postgres    0 Oct 29 16:36 .s.PGSQL.5433
-rw-------  1 postgres postgres   70 Oct 29 16:36 .s.PGSQL.5433.lock

Makes sense, there is a socket for 5433 not 5432. I confirmed this by running:

$ pg_lsclusters
Ver Cluster Port Status                Owner    Data directory              Log file
12  main    5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
14  main    5433 online                postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log

This explains how it got into this mess on my system. The default port is 5432, but after I upgraded from version 12 to 14, the server was setup to listen to 5433, presumably because it considered 5432 as already taken. Two alternatives here, get the server to listen on 5432 which is the client’s default, or get the client to use 5433.

Let’s try it by changing the client’s parameters:

$ sudo su postgres -c "psql --port=5433"
psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
Type "help" for help.

postgres=#

It worked! Now, to make it permanent I’m supposed to put this setting on a psqlrc or ~/.psqlrc file. The thin documentation on this (under «Files») was not helpful to me as I was not sure on the syntax and my attempts did not change the client’s default, so I moved on.

To change the server I looked for the postgresql.conf mentioned in the documentation but could not find the file. I did however see /var/lib/postgresql/14/main/postgresql.auto.conf so I created it on the same directory with the content:

port = 5432

Restarted the server: sudo systemctl restart postgresql

But the error persisted because, as the logs confirmed, the port did not change:

$ tail /var/log/postgresql/postgresql-14-main.log
...
2021-10-29 16:36:12.195 UTC [25236] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-10-29 16:36:12.198 UTC [25236] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2021-10-29 16:36:12.204 UTC [25237] LOG:  database system was shut down at 2021-10-29 16:36:12 UTC
2021-10-29 16:36:12.210 UTC [25236] LOG:  database system is ready to accept connections

After other attempts did not succeed, I eventually decided to use a workaround: to redirect the client’s requests on 5432 to 5433:

ln -s /var/run/postgresql/.s.PGSQL.5433 /var/run/postgresql/.s.PGSQL.5432

This is what I have now:

$ ls -lah /var/run/postgresql/
total 8.0K
drwxrwsr-x  4 postgres postgres  160 Oct 29 16:40 .
drwxr-xr-x 36 root     root     1.1K Oct 29 14:08 ..
drwxr-s---  2 postgres postgres   40 Oct 29 14:33 12-main.pg_stat_tmp
drwxr-s---  2 postgres postgres  120 Oct 29 16:59 14-main.pg_stat_tmp
-rw-r--r--  1 postgres postgres    6 Oct 29 16:36 14-main.pid
lrwxrwxrwx  1 postgres postgres   33 Oct 29 16:40 .s.PGSQL.5432 -> /var/run/postgresql/.s.PGSQL.5433
srwxrwxrwx  1 postgres postgres    0 Oct 29 16:36 .s.PGSQL.5433
-rw-------  1 postgres postgres   70 Oct 29 16:36 .s.PGSQL.5433.lock

This means I can now just run psql without having to explicitly set the port to 5433. Now, this is a hack and I would not recommend it. But in my development system I am happy with it for now, because I don’t have more time to spend on this. This is why I shared the steps and the links so that you can find a proper solution for your case.

sudo apt-get remove --purge postgresql-9.1

sudo apt-get install postgresql-9.1

netstat -nlp | grep 5432

sudo nano /etc/postgresql/9.3/main/postgresql.conf

listen_addresses = '*'

sudo service postgresql restart

sudo nano /etc/postgresql/9.3/main/pg_hba.conf

host    all             all             192.168.1.0/24          md5

netstat -lp --protocol=unix | grep postgres

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

dpkg-reconfigure locales

pg_createcluster 9.5 main --start

/etc/init.d/postgresql start

sudo su - postgres
psql

psql -h /var/run/postgresql

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

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

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

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

#restart postgresql service
sudo service postgresql restart

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`

sudo chown postgres -R /var/lib/postgresql/9.6/main/

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

# Fix 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

export LC_ALL="en_US.UTF-8"

sudo pg_createcluster 9.3 main --start

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# Database administrative login by Unix domain socket
local   all             postgres                                MD5

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# ERROR REPORTING AND LOGGING

sudo chmod 700 main

from flask import Flask
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql+psycopg2://<user>:<password>@<container_name_in_docker-compose.yml>/<database_name>"

from sqlalchemy import MetaData
db = SQLAlchemy(app, metadata=MetaData(schema="<schema_name>"))

db.session.execute("SET search_path TO <schema_name>")

#!/bin/bash

# We can find the version number of the newest PostgreSQL with this
VERSION=$(dpkg-query -W -f "\${Version}" 'postgresql' | sed -e's/+.*//')
PGCONF="/etc/postgresql/${VERSION}/main/postgresql.conf"

# Then we can update the port.
sudo sed -ie '/port = /s/.*/port = 5432/' "$PGCONF"

sudo systemctl restart postgresql

listen_addresses = '*'
unix_socket_directories = '/var/run/postgresql'

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

sudo service postgresql restart

sudo -u postgres psql

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

/tmp/.s.PGSQL.5432

su postgres
psql -h /tmp/ yourdatabase

.../postgresql
.../postgresql-9.x 

sudo apt-get update && sudo apt-get -y install python-software-properties && wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'
sudo apt-get update && sudo apt-get install postgresql-9.3 pgadmin3

Creating new cluster 9.3/main ...
config /etc/postgresql/9.3/main
data   /var/lib/postgresql/9.3/main
locale en_US.UTF-8
socket /var/run/postgresql
port   5432

FATAL:  could not create shared memory segment ...
   To reduce the request size (currently 57237504 bytes), reduce PostgreSQL's
   shared memory usage, perhaps by reducing shared_buffers or max_connections.

$ sudo service postgresql start
 * Starting PostgreSQL 9.1 database server                                                                                                                                                               * The PostgreSQL server failed to start. Please check the log output:
2013-06-26 15:05:11 CEST FATAL:  could not create shared memory segment: Invalid argument
2013-06-26 15:05:11 CEST DETAIL:  Failed system call was shmget(key=5432001, size=57237504, 03600).
2013-06-26 15:05:11 CEST HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMMAX.  To reduce the request size (currently 57237504 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
    If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
    The PostgreSQL documentation contains more information about shared memory configuration.

dpkg -l | grep postgres
apt-get --purge remove <package-founded-1> <package-founded-2>
whereis postgres
whereis postgresql
sudo rm -rf <paths-founded>
sudo userdel -f postgres

sudo /opt/bitnami/ctlscript.sh restart postgresql

/var/log/postgresql/postgresql-12-main.log

sudo systemctl restart postgresql.service

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

unix_socket_directories = '/var/run/postgresql,/tmp'

You must log in to answer this question.

Not the answer you’re looking for? Browse other questions tagged

.

Problem Description:

Not really sure what caused this but most likely exiting the terminal while my rails server which was connected to PostgreSQL database was closed (not a good practice I know but lesson learned!)

I’ve already tried the following:

1. Rebooting my machine (using MBA M1 2020)
2. Restarting PostgreSQL using homebrew brew services restart postgresql
3. Re-installing PostgreSQL using Homebrew
4. Updating PostgreSQL using Homebrew
5. I also tried following this link but when I run cd Library/Application Support/Postgres terminal tells me Postgres folder doesn’t exist, so I’m kind of lost already. Although I have a feeling that deleting postmaster.pid would really fix my issue. Any help would be appreciated!

Solution – 1

Resetting PostgreSQL

My original answer only included the troubleshooting steps below, and a workaround. I now decided to properly fix it via brute force by removing all clusters and reinstalling, since I didn’t have any data there to keep. It was something along these lines, on my Ubuntu 21.04 system:

sudo pg_dropcluster --stop 12 main
sudo pg_dropcluster --stop 14 main
sudo apt remove postgresql-14
sudo apt purge postgresql*
sudo apt install postgresql-14

Now I have:

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

And sudo -u postgres psql works fine. The service was started automatically but it can be done manually with sudo systemctl start postgresql.

Incidentally, I can recommend the PostgreSQL docker image, which eliminates the need to bother with a local installation.

Troubleshooting

Although I cannot provide an answer to your specific problem, I thought I’d share my troubleshooting steps, hoping that it might be of some help. It seems that you are on Mac, whereas I am running Ubuntu 21.04, so expect things to be different.

This is a client connection problem, as noted by section 19.3.2 in the docs.

The directory in my error message is different:

$ sudo su postgres -c "psql"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

I checked what unix sockets I had in that directory:

$ ls -lah /var/run/postgresql/
total 8.0K
drwxrwsr-x  4 postgres postgres  160 Oct 29 16:40 .
drwxr-xr-x 36 root     root     1.1K Oct 29 14:08 ..
drwxr-s---  2 postgres postgres   40 Oct 29 14:33 12-main.pg_stat_tmp
drwxr-s---  2 postgres postgres  120 Oct 29 16:59 14-main.pg_stat_tmp
-rw-r--r--  1 postgres postgres    6 Oct 29 16:36 14-main.pid
srwxrwxrwx  1 postgres postgres    0 Oct 29 16:36 .s.PGSQL.5433
-rw-------  1 postgres postgres   70 Oct 29 16:36 .s.PGSQL.5433.lock

Makes sense, there is a socket for 5433 not 5432. I confirmed this by running:

$ pg_lsclusters
Ver Cluster Port Status                Owner    Data directory              Log file
12  main    5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
14  main    5433 online                postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log

This explains how it got into this mess on my system. The default port is 5432, but after I upgraded from version 12 to 14, the server was setup to listen to 5433, presumably because it considered 5432 as already taken. Two alternatives here, get the server to listen on 5432 which is the client’s default, or get the client to use 5433.

Let’s try it by changing the client’s parameters:

$ sudo su postgres -c "psql --port=5433"
psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
Type "help" for help.

postgres=#

It worked! Now, to make it permanent I’m supposed to put this setting on a psqlrc or ~/.psqlrc file. The thin documentation on this (under «Files») was not helpful to me as I was not sure on the syntax and my attempts did not change the client’s default, so I moved on.

To change the server I looked for the postgresql.conf mentioned in the documentation but could not find the file. I did however see /var/lib/postgresql/14/main/postgresql.auto.conf so I created it on the same directory with the content:

port = 5432

Restarted the server: sudo systemctl restart postgresql

But the error persisted because, as the logs confirmed, the port did not change:

$ tail /var/log/postgresql/postgresql-14-main.log
...
2021-10-29 16:36:12.195 UTC [25236] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-10-29 16:36:12.198 UTC [25236] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2021-10-29 16:36:12.204 UTC [25237] LOG:  database system was shut down at 2021-10-29 16:36:12 UTC
2021-10-29 16:36:12.210 UTC [25236] LOG:  database system is ready to accept connections

After other attempts did not succeed, I eventually decided to use a workaround: to redirect the client’s requests on 5432 to 5433:

ln -s /var/run/postgresql/.s.PGSQL.5433 /var/run/postgresql/.s.PGSQL.5432

This is what I have now:

$ ls -lah /var/run/postgresql/
total 8.0K
drwxrwsr-x  4 postgres postgres  160 Oct 29 16:40 .
drwxr-xr-x 36 root     root     1.1K Oct 29 14:08 ..
drwxr-s---  2 postgres postgres   40 Oct 29 14:33 12-main.pg_stat_tmp
drwxr-s---  2 postgres postgres  120 Oct 29 16:59 14-main.pg_stat_tmp
-rw-r--r--  1 postgres postgres    6 Oct 29 16:36 14-main.pid
lrwxrwxrwx  1 postgres postgres   33 Oct 29 16:40 .s.PGSQL.5432 -> /var/run/postgresql/.s.PGSQL.5433
srwxrwxrwx  1 postgres postgres    0 Oct 29 16:36 .s.PGSQL.5433
-rw-------  1 postgres postgres   70 Oct 29 16:36 .s.PGSQL.5433.lock

This means I can now just run psql without having to explicitly set the port to 5433. Now, this is a hack and I would not recommend it. But in my development system I am happy with it for now, because I don’t have more time to spend on this. This is why I shared the steps and the links so that you can find a proper solution for your case.

Solution – 2

Below was the workaround if you prefer to stay in v13.3, if you are on 14.2 you can simply change the port postgresql is listening to. Read their docs about it here.

Note that this solution works for the setup that I have (take a look at the original post for my setup details ie. OS & how what I’d use to install Postgres)

1. open your postgresql.conf file in the following directory and use
   any text editor you want. below uses vscode/vim.

vscode

sudo code . /usr/local/var/postgres/postgresql.conf

vim

sudo vi /usr/local/var/postgres/postgresql.conf

1. change port to 5432 and restart your machine.

v13.3 solution

it appears that the upgrade really messed up my postgres since per Nagev’s answer it’s listening to port 5433 instead of 5432. I downgraded to v13.3 to fix this issue.

brew uninstall postgresql
brew install postgresql@13
brew services start postgresql@13
brew link postgresql@13 --force

Solution – 3

Solution for MacOS M1 Monterey

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

Solution – 4

In my case I received this issue after upgrading my Mac (pg 14.2 was the version). I uninstalled it and reinstalled it a few times but it wouldn’t start. Even when it said it started, it didn’t which was confusing. I ended up needing to run the command below to get everything to work again.

brew postgresql-upgrade-database

Specifically I ran

brew install postgresql
brew postgresql-upgrade-database

It seems to me that the DB needed to be upgraded before it could run, once I did that it all came back.

Solution – 5

if you are using macOS and not M1

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

detail

• try brew info postgres get execute command
• try /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres
• error: FATAL: lock file "postmaster.pid" already exists
• goto /usr/local/var/postgres directory, delete postmaster.pid file
• restart, works

Solution – 6

If you use lunchy with homebrew, resolving this may be as simple as running three commands:

# Instead of uninstalling it, simply stop Postgres
lunchy stop postgres

# Remove the PID file that was left behind from improper shutdown
rm /usr/local/var/postgres/postmaster.pid

# Restart Postgres
lunchy start postgres

Solution – 7

First up check whether Postgress is running

pgrep -l postgres

If it doesn’t give you any result then let’s try starting it using brew services.

brew services start postgresql
pgrep -l postgres

Still not running? Check the logs

tail /usr/local/var/log/postgres.log

You may see an error about a file named postmaster.pid:

FATAL:  lock file "postmaster.pid" already exists

This indicates that Postgres had not been shut down cleanly, for any number of reasons. Remove the stale PID lock then start the server again

rm /usr/local/var/postgres/postmaster.pid
brew services start postgresql
pgrep -l postgres

Solution – 8

If you use MacOS Homebrew, follow the instructions at https://wiki.postgresql.org/wiki/Homebrew
In short, try
psql postgres

Solution – 9

The top answer on this post is what solved the issue for me. Basically run createdb in your terminal. This will create database for your logged in user. Then run psql -h localhost and you will be in!

Not saying this is the solve for all issues, but it solved mine.

Solution – 10

For Mac with M1 use this:

brew services stop postgresql
rm /opt/homebrew/var/postgresql@14/postmaster.pid
brew services start postgresql