PostgreSQL闲置连接5分钟后断开，请求排查参数原因

问题现象 #

我们未使用连接池连接数据库，发现会话每次在闲置约5分钟（±3~4秒）后断开。

告警日志断开记录 #

2024-11-11 17:51:28.627 UTC,"test30","test30",479460,"172.16.83.81:1083",673242f0.750e4,4,"idle",2024-11-11 17:46:24 UTC,7/0,0,LOG,08006,"could not receive data from client: Connection reset by peer",,,,,,,,,"psql","client backend",,0
2024-11-11 17:51:28.627 UTC,"test30","test30",479460,"172.16.83.81:1083",673242f0.750e4,5,"idle",2024-11-11 17:46:24 UTC,,0,LOG,00000,"disconnection: session time: 0:05:04.424 user=test30 database=test30 host=172.16.83.81 port=1083",,,,,,,,,"psql","client backend",,0

2024-11-11 18:00:16.890 UTC,"test30","test30",483965,"10.22.84.0:39903",67324501.7627d,4,"idle",2024-11-11 17:55:13 UTC,7/0,0,LOG,08006,"could not receive data from client: Connection reset by peer",,,,,,,,,"psql","client backend",,0
2024-11-11 18:00:16.890 UTC,"test30","test30",483965,"10.22.84.0:39903",67324501.7627d,5,"idle",2024-11-11 17:55:13 UTC,,0,LOG,00000,"disconnection: session time: 0:05:03.791 user=test30 database=test30 host=10.22.84.0 port=39903",,,,,,,,,"psql","client backend",,0

Postgres超时参数设置 #

postgres=# select name,setting,context from pg_settings where name like '%timeout%';
                name                 | setting |  context
-------------------------------------+---------+-----------
 archive_timeout                     | 1800    | sighup
 authentication_timeout              | 60      | sighup
 checkpoint_timeout                  | 300     | sighup
 deadlock_timeout                    | 1000    | superuser
 idle_in_transaction_session_timeout | 0       | user
 idle_session_timeout                | 0       | user
 lock_timeout                        | 0       | user
 statement_timeout                   | 0       | user
 tcp_user_timeout                    | 0       | user
 wal_receiver_timeout                | 60000   | sighup
 wal_sender_timeout                  | 60000   | user
(11 rows)

操作系统netfilter参数设置 #

net.netfilter.nf_conntrack_acct = 0
net.netfilter.nf_conntrack_buckets = 262144
net.netfilter.nf_conntrack_checksum = 1
net.netfilter.nf_conntrack_count = 0
net.netfilter.nf_conntrack_dccp_loose = 1
net.netfilter.nf_conntrack_dccp_timeout_closereq = 64
net.netfilter.nf_conntrack_dccp_timeout_closing = 64
net.netfilter.nf_conntrack_dccp_timeout_open = 43200
net.netfilter.nf_conntrack_dccp_timeout_partopen = 480
net.netfilter.nf_conntrack_dccp_timeout_request = 240
net.netfilter.nf_conntrack_dccp_timeout_respond = 480
net.netfilter.nf_conntrack_dccp_timeout_timewait = 240
net.netfilter.nf_conntrack_events = 2
net.netfilter.nf_conntrack_expect_max = 4096
net.netfilter.nf_conntrack_frag6_high_thresh = 4194304
net.netfilter.nf_conntrack_frag6_low_thresh = 3145728
net.netfilter.nf_conntrack_frag6_timeout = 60
net.netfilter.nf_conntrack_generic_timeout = 600
net.netfilter.nf_conntrack_gre_timeout = 30
net.netfilter.nf_conntrack_gre_timeout_stream = 180
net.netfilter.nf_conntrack_helper = 0
net.netfilter.nf_conntrack_icmp_timeout = 30
net.netfilter.nf_conntrack_icmpv6_timeout = 30
net.netfilter.nf_conntrack_log_invalid = 0
net.netfilter.nf_conntrack_max = 524288
net.netfilter.nf_conntrack_sctp_timeout_closed = 10
net.netfilter.nf_conntrack_sctp_timeout_cookie_echoed = 3
net.netfilter.nf_conntrack_sctp_timeout_cookie_wait = 3
net.netfilter.nf_conntrack_sctp_timeout_established = 210
net.netfilter.nf_conntrack_sctp_timeout_heartbeat_sent = 30
net.netfilter.nf_conntrack_sctp_timeout_shutdown_ack_sent = 3
net.netfilter.nf_conntrack_sctp_timeout_shutdown_recd = 0
net.netfilter.nf_conntrack_sctp_timeout_shutdown_sent = 0
net.netfilter.nf_conntrack_tcp_be_liberal = 0
net.netfilter.nf_conntrack_tcp_ignore_invalid_rst = 0
net.netfilter.nf_conntrack_tcp_loose = 1
net.netfilter.nf_conntrack_tcp_max_retrans = 3
net.netfilter.nf_conntrack_tcp_timeout_close = 10
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 60
net.netfilter.nf_conntrack_tcp_timeout_established = 432000
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_last_ack = 30
net.netfilter.nf_conntrack_tcp_timeout_max_retrans = 300
net.netfilter.nf_conntrack_tcp_timeout_syn_recv = 60
net.netfilter.nf_conntrack_tcp_timeout_syn_sent = 120
net.netfilter.nf_conntrack_tcp_timeout_time_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_unacknowledged = 300
net.netfilter.nf_conntrack_timestamp = 0
net.netfilter.nf_conntrack_udp_timeout = 30
net.netfilter.nf_conntrack_udp_timeout_stream = 120

测试验证结果 #

当Postgres参数tcp_keepalives_idle≤285时，连接保持正常；当该参数≥286时，连接会在5分钟后断开。

疑问与怀疑对象 #

请问上述哪个参数可能导致该问题？或者是否存在其他原因？
我的怀疑对象包括：

• net.netfilter.nf_conntrack_tcp_timeout_unacknowledged = 300：300秒的值看起来可疑，但连接已建立，我认为该参数不是断开原因
• net.netfilter.nf_conntrack_tcp_timeout_established = 432000：名称符合但值过高

问题根源分析 #

从测试结果和参数配置来看，net.netfilter.nf_conntrack_tcp_timeout_unacknowledged = 300 就是导致连接断开的核心原因，你最初的怀疑方向正确，但对该参数的作用理解有误：

1. 参数作用澄清：
   该参数并非只针对未建立的连接，而是控制TCP连接在已建立状态下，若某一端发送的数据包长时间未收到ACK确认时的超时时间。当Postgres的tcp_keepalives_idle设置≥286秒时，首次发送Keepalive探测包的时间点会晚于300秒（5分钟）的窗口——从最后一次有ACK交互的数据包开始，conntrack会启动300秒的计时器，若期间没有任何确认类数据包（包括Keepalive的ACK），就会将这条连接标记为无效，进而触发连接重置。

   当tcp_keepalives_idle≤285时，Keepalive包会在285秒时发出，此时还在300秒的超时窗口内，客户端收到探测包后会回复ACK，这个ACK会刷新conntrack的超时计时器，从而避免连接被清理。而当参数≥286时，首次Keepalive发送时，conntrack已经因为300秒无确认数据包而完成了连接清理，导致连接被重置。

2. 排除其他参数：

   • net.netfilter.nf_conntrack_tcp_timeout_established = 432000（对应120小时），远大于5分钟，完全不可能触发5分钟的断开逻辑。
   • Postgres自身的idle_session_timeout和idle_in_transaction_session_timeout均设置为0（即禁用自动断开），因此不是数据库主动发起的断开。

验证建议 #

可以通过以下方式验证结论：

• 临时修改net.netfilter.nf_conntrack_tcp_timeout_unacknowledged为600秒，然后设置tcp_keepalives_idle=300，观察连接是否还会在5分钟后断开；
• 保持net.netfilter.nf_conntrack_tcp_timeout_unacknowledged不变，设置tcp_keepalives_idle=280，确认连接保持稳定。

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