I have a VPS server that I just installed Postgres in and set up. When I tried to connect remotely, I get the error: psql: error: could not connect to server: could not connect to server: Operation timed out Is the server running on host "31.187.72.253" and accepting TCP/IP connections on port 5432?
My connection test is:
psql -h 31.187.72.253 -p 5432 -U postgres
Doing the above from the VPS terminal itself works. But elsewhere, it doesn't.
My /etc/postgresql/15/main/postgresql.conf configuration is:
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# 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
My /etc/postgresql/15/main/pg_hba.conf configuration is:
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256
I am using the latest postgres:
root@user:/etc/postgresql/15/main# psql -V
psql (PostgreSQL) 15.3 (Ubuntu 15.3-1.pgdg22.04+1)
Port 5432 is not blocked:
root@user:/etc/postgresql/15/main# sudo ufw status
Status: active
To Action From
-- ------ ----
22 ALLOW Anywhere
80 ALLOW Anywhere
443 ALLOW Anywhere
5433/tcp ALLOW Anywhere
5432/tcp ALLOW Anywhere
5432 ALLOW Anywhere
7080 DENY Anywhere
22 (v6) ALLOW Anywhere (v6)
80 (v6) ALLOW Anywhere (v6)
443 (v6) ALLOW Anywhere (v6)
5432 (v6) ALLOW Anywhere (v6)
5432/tcp (v6) ALLOW Anywhere (v6)
7080 (v6) DENY Anywhere (v6)
With netstat:
root@user:/etc/postgresql/15/main# netstat -tuln | grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
tcp6 0 0 :::5432 :::* LISTEN
When I ping my VPS IP from inside the VPS or from another VPS server, it pings correctly. But trying to connect to postgres from these other VPS or my local system doesn't work.
PS: Pinging the VPS from my local system doesn't work too.
Please, how do I resolve this issue.
