远程PostgreSQL 12连接故障解决及周期性查询变慢原因排查

这个周期性变慢的问题我之前帮不少用户排查过，大概率和闲置连接被网络设备断开有关，结合你的PostgreSQL 12和Ubuntu 18 Server环境，给你拆解下核心原因和对应的解决步骤：

可能的原因 #

• NAT路由器的会话回收机制：家里的网络一般是通过NAT（网络地址转换）访问服务器，大部分路由器会自动回收闲置1-2分钟的TCP会话。当你的PostgreSQL连接闲置超过这个时间，路由器就会把对应的端口映射会话删掉，此时再执行查询，需要重新建立TCP连接或触发连接检测，这就导致了15-20秒的延迟，后续重新建立的连接又能快速响应，直到下一次闲置超时。
• PostgreSQL默认的TCP Keepalive配置不足：PostgreSQL默认的tcp_keepalives_idle参数是7200秒（2小时），也就是说连接闲置2小时才会发送Keepalive包，远大于你遇到的1-2分钟，无法及时维持连接，导致中间设备断开会话。
• Ubuntu系统级TCP参数默认值过大：Ubuntu默认的net.ipv4.tcp_keepalive_time也是7200秒，系统不会主动发送Keepalive包来维持闲置连接，进一步加剧了会话被回收的概率。

解决步骤 #

1. 调整PostgreSQL的TCP Keepalive参数 #

首先修改PostgreSQL的主配置文件postgresql.conf（路径通常是/etc/postgresql/12/main/postgresql.conf），找到或添加以下参数：

# 闲置60秒后开始发送Keepalive包，主动维持连接
tcp_keepalives_idle = 60
# 每10秒发送一次Keepalive探测包
tcp_keepalives_interval = 10
# 连续3次探测无响应则断开连接
tcp_keepalives_count = 3

修改完成后，重启PostgreSQL服务使配置生效：

sudo systemctl restart postgresql@12-main

2. 补充调整Ubuntu系统的TCP参数（可选） #

为了确保系统层面也能配合维持连接，编辑/etc/sysctl.conf文件，添加或修改以下内容：

net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_keepalive_intvl = 10
net.ipv4.tcp_keepalive_probes = 3

执行以下命令使配置立即生效：

sudo sysctl -p

3. 检查并调整路由器的NAT超时设置 #

如果上面的配置后问题依然存在，登录你的家庭路由器管理界面，查找NAT会话超时或TCP端口映射超时的选项，将闲置超时时间调整为5分钟以上（比如300秒），部分路由器还支持关闭闲置会话自动回收功能，开启后也能缓解这个问题。

4. 验证连接状态（用于排查） #

你可以在服务器上用ss命令查看PostgreSQL连接的状态，闲置一段时间后执行：

ss -t state established '( dport = :5432 or sport = :5432 )'

如果闲置后连接状态变成TIME_WAIT或直接消失，说明确实是会话被回收，上面的Keepalive配置应该能解决问题。

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