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!

Answer 1

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

1. First of remove all the configuration:

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

2. Next get all the related packages installed for postgres using:

   dpkg -l | grep postgres
   

3. Now you can remove those using the command:

   sudo apt-get --purge remove package1 package2 package
   

4. Also delete the postgres user using the command:

   sudo userdel -f postgres
   

5. Finally Install Postgres:

   sudo apt update
   
   sudo apt install libpq-dev postgresql postgresql-contrib curl
   

Answer 2

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.