Unable to connect to Postgres - error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed

Question

I did reinstall Postgres multiple times and each time when I try to connect postgres it shows below error:

root@mwdbsrv01:/home/thomson# sudo -i -u bitbucket
bitbucket@mwdbsrv01:~$ 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 know this has been posted few times but none of the solutions worked for me. My Postgres is running on a VM on Ubuntu 20.04 server. My Goal is to connect to this VM from other VMs and I have modified postgresql.conf and pg_hba.conf as below:

postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation

pg_hba.conf

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5
host    all             all             :/0                     md5
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I did open the firewall for port 5432 but netstat -nlt i snot showing the port isnt listening either.

root@mwdbsrv01:/home/thomson# netstat -nlt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 127.0.0.1:5433          0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp6       0      0 :::22                   :::*                    LISTEN

I did check my sever status and its running:

root@mwdbsrv01:/home/thomson# service postgresql status
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2022-02-24 02:20:38 UTC; 35min ago
    Process: 8259 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 8259 (code=exited, status=0/SUCCESS)

Feb 24 02:20:38 mwdbsrv01 systemd[1]: Starting PostgreSQL RDBMS...
Feb 24 02:20:38 mwdbsrv01 systemd[1]: Finished PostgreSQL RDBMS.

Not sure what else I can do. A help would be greatly appreciated! Thank you!

systemctl won't show if PostgreSQL is running. Use ps -f -u postgres. — Laurenz Albe
– Laurenz Albe, Commented Feb 24, 2022 at 6:40

Suraj Rao · Accepted Answer · 2023-04-05 05:42:24Z

2

If you are still facing this issue and have a backup of the databases inside postgres then you could hard reset it and reinstall

First of remove all the configuration:

sudo rm -rf /var/lib/postgresql/
sudo rm -rf /var/log/postgresql/
sudo rm -rf /etc/postgresql/

Next get all the related packages installed for postgres using:
```
dpkg -l | grep postgres
```

Now you can remove those using the command:

sudo apt-get --purge remove package1 package2 package

Also delete the postgres user using the command:
```
sudo userdel -f postgres
```

Finally Install Postgres:

sudo apt update

sudo apt install libpq-dev postgresql postgresql-contrib curl

edited Apr 5, 2023 at 5:42

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Apr 5, 2023 at 5:23

Usama Zafar

464 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jlb333333 Over a year ago

Thank you. Helped me a lot. I was completely stuck before doing this.

Xavier J · Accepted Answer · 2022-02-24 02:52:50Z

1

You need to make sure the service is actually running.

https://www.dark-hamster.com/operating-system/linux/ubuntu/check-postgresql-service-status/

If the service isn't running, it may be (a) misconfigured (b) you haven't set up default user (c) it's trying to do what it can, but you are out of disk space

You can get more clues by checking the end of the system error log.

answered Feb 24, 2022 at 2:52

Xavier J

4,7911 gold badge16 silver badges29 bronze badges

4 Comments

Thomson Mathew Over a year ago

I am not sure what could have been miss configured. I did try this on multiple VM and I have the same issue everywhere. And I did check the status and it shows its running. Btw did try by latest as well as specific versions too but same result

Xavier J Over a year ago

Look here and see if there's anything you missed. digitalocean.com/community/tutorials/…

Thomson Mathew Over a year ago

Well that's exactly the website I was following. I did create new users and all looks well until I restart. Btw I could never connect from other VM.

Xavier J Over a year ago

Then your next move might be here mydbanotebook.org/post/troubleshooting-01 It might not be auto starting after a reboot. Again, check those logs

Collectives™ on Stack Overflow

Unable to connect to Postgres - error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related