[postgresql] PostgreSQL: Why psql can't connect to server?

I typed psql and I get this:

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

I used sudo netstat -nlp | grep 5432 to see the status but nothing showed. And I searched online, somebody told me to modify pg_hba.conf but I can't locate this file. And I also tried this command sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432. It can't work.

I have encountered a similar issue a couple of times. Normally I just do a fresh installation of PostgreSQL following this tutorial and that solves the problem at the expense of losing data.

I was determined on getting real fix today. Restarting PostgreSQL resolved it on ubuntu. sudo /etc/init.d/postgresql restart

So for me and my pals working on a Node.js app (with Postgres and Sequelize), we had to

1. brew install postgresql (one of us was missing postgres, one of us was not, and yet we were getting the same error msg as listed above)

2. brew services start postgresql **** (utilize Homebrew to start postgres)

3. createdb <name of database in config.json file>

4. node_modules/.bin/sequelize db:migrate

5. npm start

The error means that the Postgres server is not running. Try starting it:

sudo systemctl start postgresql

Make sure that the server starts on boot:

sudo systemctl enable postgresql

I was facing same problem and

sudo su - postgres
initdb --locale en_US.UTF-8 -D /var/lib/postgres/data
exit
sudo systemctl start postgresql
sudo systemctl status postgresql

This worked for me.

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 [email protected] failed because the service did not take the steps required by its unit configuration.
See "systemctl status [email protected]" 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

Restarting the instance worked for me. Also, as mentioned in some other post psql -h '/tmp' worked as well before the restart. But post restart psql directly started working. So, probably some file ownership issues that got reset with the restart is what I am thinking.

In my case, the service was running but the cluster was down and psql wouldn't start. My configuration files looked perfect but it kept throwing configuration errors and seemed to ignore the changes I was making.

It turns out that whenever you use ALTER SYSTEM SET ... syntax, PostgreSQL writes to a file called postgresql.auto.conf. That file is read in addition to the regular postgresql.conf and pg_hba.conf files. In my distribution of Ubuntu (18.04), they are in different folders(!):
- pg_hba.conf and postgresql.conf are both in /etc/postgresql/12/main
- The auto-generated file is /var/lib/postgresql/12/main/postgresql.auto.conf

I had tried to change the configuration using ALTER SYSTEM SET listen_addresses = <my-ip>, but had made a mistake and that created a broken "ghost" configuration that I couldn't find. As soon as I erased the offending line in postgresql.auto.conf, it fixed everything.

My issue with this error message was in wrong permissions on key and pem certificates, which I have manipulated. What helped me a lot was: /var/log/postgresql/postgresql-9.5-main.log where are all the errors.

If your service is not secure, this may be the reason

vi /etc/postgresql/11/main/pg_hba.conf

1. open hba config file, this config file usualy located in the etc directory.

host    all   all    localhost trust   md5

2. you can remove the trust keyword

3. save pg_hba.conf

4. sudo service postgresql restart.

if you are using windows subsystem for linux and Ruby on Rails then check your postgres is running in which port using this command sudo nano /etc/postgresql/12/main/postgresql.conf if it is in port 5433 then go to database.yml file and add port:5433 in there and then run command sudo service postgresql start i have resolved my issue like this

In my case I had this error, /var/run/postgresql/.s.PGSQL.5433 (note, one number up from the file it was looking for, .s.PGSQL.5432) was present. Tried the instructions at the top of this page but nothing worked.

Turns out there was an old directory for PostGreSQL 12 config files in /etc/postgresql/12, which I deleted, which solved the issue.

I had the same issue on Devuan ascii (maybe Debian, too?). The config file /etc/postgresql/9.6/main/postgresql.conf contains a directive unix_socket_directories which points to /var/run/postgresql by default. Changing it to /tmp, where most clients look by default, fixed it for me.

I resolved this problem by checking my file system the disk was completely full, and so database could not start up

connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432" ?

I tried series of troubleshooting, until when i checked my disk usage and found that it was full, 100% usage,

df -h
cd /var/log/odoo/
cat /dev/null > odoo-server.log
reboot

I had the similar issue and the problem was in the config file pg_hba.conf. I had earlier made some changes which was causing the server to error out while trying to start it. Commenting out the extra additions solved the problem.

Just want to make a small addition: if your instance is complaining on a socket, you can also check unix_socket_directories at /data/postgresql.conf file which could have been set to /tmp, for example, if you have used a 3rd party distribution. You can change it to /var/run/postgresql and restart the service. That may also require creating a postgresql dir at /var/run and subsys/postgresql-9.6 at /var/lock if those doesn't already exist (worked for me with postgresql 9.6).

Verify that Postgres is running using:

ps -ef | grep postgres

root@959dca34cc6d:/var/lib/edb# ps -ef|grep postgres
enterpr+    476  1  0 06:38 ?        00:00:00 /usr/lib/edb-as/11/bin/edb-postgres -D /var/lib/edb-as/11/main2 -c config_file=/etc/edb-as/11/main2/postgresql.conf

Check for data directory and postgresql.conf.

In my case data directory in -D was different than that in postgresql.conf

So I changed the data directory in postgresql.conf and it worked.

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

Solved it! Although I don't know what happened, but I just deleted all the stuff and reinstalled it. This is the command I used to delete it sudo apt-get --purge remove postgresql\* and dpkg -l | grep postgres. The latter one is to find all the packets in case it is not clean.

In my case I saw this error and postgres was not running.

The problem was that the instalation failed to create the required cluster.

The solution was to create the folder /etc/postgres/{postgresql-version}/main

and then create the cluster with:

pg_createcluster {postgresql-version} main

After that wiht just restarting the postgresql service everything should work.

It can cause anything for example, my issue was caused for typo error on configuration files. Some of people says caused by certificate files, another group says caused by unmatched locals.

If you cant find any solution about your issue, remove postgres and reinstall it.This is the best solution.

During fresh installation of postgresql. By default, user name and password is assigned as "postgres". The feature this RDBMS provides is to add role for new user and create database. If you are getting such errors:

1. login in by default username:

   root@kalilinux:~# sudo -i -u postgres

2. ype psql for interactive prompt

   postgres@kalilinux:~$ psql

3. To quit from prompt use

   \q

4. To create new user role

   postgres@kalilinux:~$ createuser --interactive

Now you are in interacive psql shell. Enjoy. Dont forget to login in from your username and type psql for shell.

I had the same problem. It seems that there is no socket when there is no cluster.

The default cluster creation failed during the installation because no default locale was set.

I could resolve this by setting the right permissions to datadir. It should be

chmod 700 /var/lib/postgresql/10/main
chown postgres.postgres /var/lib/postgresql/10/main

quick howto on debian to remotely access postgres database on server from the psql client: (the changed config is doc'd in the files):

1. edit /etc/postgresql/10/main/postgresql.conf with listen_address *
2. edit /etc/postgresql/10/main/pg_hba.conf and add line in the end with host all all 0/0 md5
3. create login role postgres=# CREATE ROLE remoteuser LOGIN WITH PASSWORD 'foo'

4. sudo /etc/init.d/postgresql restart changes take effect

5. login from clientside with psql --host=ipofserver --port=5432 --username=remoteuser --password --dbname=mydb

6. the password is interactivly asked which in this case is foo