PostgreSQL: Why psql can't connect to server?

Question

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.

Answer 1

For me, this was a storage issue. My EC2 storage (where the Postgres db was installed) was full.

sudo tail -f /var/log/postgresql/postgresql-15-main.log

Solution: I increased the storage of the EC2 instance and restarted the database and it worked.

Answer 2

I was also getting the same error, here is how I've fixed it

1. I've restarted the postgresql service

   systemctl restart postgresql

2. Then checked the status of postgresql

   systemctl status postgresql

I've found that the service was stopped

so there must be some issue with the configuration

I checked the logs of postgresql

For Checking log, first of all find the version using psql -V

For me the version was 14.2

and my log file path was: tail -n 50 /var/log/postgresql/postgresql-14-main.log

2024-07-12 16:43:51.771 UTC [164944] FATAL:  data directory "/var/lib/postgresql/14/main" has invalid permissions
2024-07-12 16:43:51.771 UTC [164944] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
pg_ctl: could not start server
Examine the log output.
2024-07-12 16:45:33.810 UTC [164974] FATAL:  data directory "/var/lib/postgresql/14/main" has invalid permissions
2024-07-12 16:45:33.810 UTC [164974] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
pg_ctl: could not start server
Examine the log output.
2024-07-12 16:52:13.315 UTC [165237] FATAL:  data directory "/var/lib/postgresql/14/main" has invalid permissions
2024-07-12 16:52:13.315 UTC [165237] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
pg_ctl: could not start server
Examine the log output.
2024-07-12 16:53:26.152 UTC [165259] FATAL:  data directory "/var/lib/postgresql/14/main" has invalid permissions
2024-07-12 16:53:26.152 UTC [165259] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
pg_ctl: could not start server

so as per my logs, it was permission error, which I quickly fixed using chmod 750 /var/lib/postgresql/14/main command

Then I've restarted the postgresql service systemctl restart postgresql and it started working

As I was able to run the shell using sudo -u postgres psql command

So you should also do the same instead of removing postgresql completely and reinstalling it from scratch.

Check the logs, figure out the root cause and fix that issue

Answer 3

Debian 12
postgresql 15
Following solved the problem.

apt install postgresql-contrib

It looks that people with mysql do only two things

apt install postgresql postgresql-client

postgresql-contrib is also required

Answer 4

From the command line run: pg_isready

> pg_isready 
/run/postgresql:5432 - accepting connections

This will show if postgresql is connected on a local socket and accepting connections.

A Connection URI of:

"host=/run/postgresql dbname=your_db_name"

should work for clients trying to connect locally.

Answer 5

There are various potential factors that may contribute to the inability of psql to establish a connection with a PostgreSQL server in a WSL 2 Ubuntu environment.

To check your Postgresql is running, use this command

sudo service postgresql status

If it is not running you can start it by using

sudo service postgresql start

And if you made changes in Postgresql config to apply changes by

sudo service postgresql restart

Answer 6

For me, I had to set up the credentials in the config/database.yml under the default database.

username: <%= ENV["PG_USERNAME"] %>
password: <%= ENV["PG_PASSWORD"] %>

Answer 7

on rebooting the server i found these in 2 locations

srwxrwxrwx 1 postgres postgres 0 Nov 10 18:44 .s.PGSQL.5432 -rw------- 1 postgres postgres 48 Nov 10 18:44 .s.PGSQL.5432.lock

both these files were present in below locations and were not present before reboot /var/run/postgresql/ /tmp/

make sure these files with the corresponding permissions are available and start the psql

Answer 8

In Ubuntu 20.04.4 LTS. I was worried about the socket connection because i was using:

psql -U postgres db_omm < db.pgsql

and the error was: Error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"

But the authentication uses the user permissions. So even if you specify the user and you are in root or other one, you have to switch to postgres

su - postgres

Then it worked!

Answer 9

This is problem occurs with me sometime we add some back up folder, log file folder , scripts ,some certain dependency in postgresql.conf file if this no exist they cause may problem

Answer 10

I know my answer is too late, but I think it can help someone.

I did get his issue when I was modifying the pg_hba.conf to recover password. I am running Debian GNU/Linux. Here's what I did:

• backup pg_hba.conf to pg_hba.conf.bk
• edit the file
• restart the server
• everything ok

But, when I want to recover the backup file:

• rename pg_hba.conf to pg_hba.conf_no_pass
• rename pg_hba.conf.bk to pg_hba.conf
• restart the server
• I get the error above.

So, to fix, I just renamed pg_hba.conf_no_pass to pg_hba.conf and edit the file as it was before. After restarting the server, everything was just fine.

Answer 11

This command works for me on Ubuntu machine

sudo su -postgres

it would request for your password to be entered, most likely your default password.

Answer 12

OK in my case, I uninstalled postgres 10 , but not successfully ,some pg10 file/process still remains there.

and then I installed postgres 13, so pg 13 think there is another pg running on port 5432, so it's using 5433.

solution: purge all your pg via: sudo apt-get --purge remove postgresql postgresql-* and then re-install it. ( caution: this will remove all your existing data )

Answer 13

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

Answer 14

I believe I fell into this issue after uninstalling PostgreSQL 11 and 12 while already installed 13 on on Ubuntu 21. I just solved this issue by sudo nano /etc/postgresql/13/main/postgresql.conf, and found that port = 5433 (don't know why). So I changed port = 5432. Then problem solved.

Answer 15

In my case the following worked for starting postgres after running into the error

sudo service postgresql start
sudo su - postgres
psql

Answer 16

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

Answer 17

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.

Answer 18

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.

Answer 19

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.

Answer 20

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

Answer 21

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.

Answer 22

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

Answer 23

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

Answer 24

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.

Answer 25

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.

Answer 26

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.

Answer 27

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.

Answer 28

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

Answer 29

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.

Answer 30

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.