VPS上PostgreSQL远程连接超时问题求助

问题描述 #

在VPS上安装配置PostgreSQL 15.3后，远程连接出现超时错误：

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?

使用测试命令：

psql -h 31.187.72.253 -p 5432 -U postgres

VPS本地终端执行该命令可正常连接，但外部设备或其他VPS连接失败；同时本地设备无法ping通该VPS，其他VPS可ping通但无法连接PostgreSQL。

已确认的配置与状态 #

1. postgresql.conf 核心配置 #

# - 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   

2. pg_hba.conf 访问控制配置 #

# 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

3. PostgreSQL版本信息 #

root@user:/etc/postgresql/15/main# psql -V
psql (PostgreSQL) 15.3 (Ubuntu 15.3-1.pgdg22.04+1)

4. VPS本地防火墙（ufw）状态 #

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)

5. 端口监听状态 #

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 

问题定位与解决步骤 #

从现象来看，PostgreSQL本身配置无异常，问题出在VPS网络层面的外部访问限制，按以下顺序排查：

1. 检查VPS服务商的安全组/平台防火墙
   多数云服务商（如DigitalOcean、AWS、阿里云等）会在平台层面提供独立的安全组规则，即使VPS内部ufw开放了端口，平台层面未放行5432端口的话，外部仍无法访问。需登录服务商后台，找到对应实例的安全组配置，添加入站规则：允许指定IP段（或所有IP）访问TCP 5432端口。

2. 排查本地网络的出站限制
   若本地设备无法ping通VPS，可能是本地网络（公司内网、家庭宽带）封禁了ICMP请求，或运营商屏蔽了该VPS IP段。可尝试更换网络（如手机热点）测试连接，若能正常访问，则需联系本地网络管理员调整规则。

3. 验证端口连通性并重启服务
   从外部设备执行以下命令测试端口是否可达：

   # 使用telnet测试
   telnet 31.187.72.253 5432
   # 或使用nc测试
   nc -zv 31.187.72.253 5432
   

   若端口测试成功但PostgreSQL仍无法连接，可重启服务确保配置生效：

   sudo systemctl restart postgresql
   

内容的提问来源于stack exchange，提问作者Benito