PostgreSQL连接池配置后遇connection reset by peer问题求排查

Great question—let’s break down what’s happening here and walk through actionable debugging steps.

First, let’s clarify: the read: connection reset by peer error means the PostgreSQL server (or a network intermediary) abruptly closed the TCP connection while your application was trying to read from it. Since PostgreSQL logs show no exceptions, this doesn’t rule out network stack issues, but there are several other likely culprits to check first.

Is this a network stack issue? #

It could be, but it’s not the only possibility. Network stack problems might come from:

• OS-level connection limits or TCP timeouts
• Firewall/iptables rules that terminate idle connections
• Network intermediaries (like load balancers) with aggressive idle timeouts

But before jumping to network conclusions, let’s rule out PostgreSQL and connection pool configuration issues first—these are far more common in scenarios like yours.

Actionable Debugging Steps #

1. Audit PostgreSQL’s Connection & Logging Settings #

Even if logs look normal, you might be missing critical details:

• Increase log verbosity: Temporarily set log_error_verbosity = verbose, log_connections = on, log_disconnections = on, and log_statement = 'all' in your PostgreSQL postgresql.conf. This will log every connection, disconnection, and statement, which might reveal why connections are being closed.
• Check PostgreSQL connection limits: Verify max_connections in postgresql.conf is higher than your sqlx MaxOpenConnections setting. If the test machine’s PostgreSQL has a lower max_connections than your app’s pool size, it might be silently rejecting/terminating connections without logging (though this usually triggers a different error, but it’s worth checking).
• Idle connection timeouts: Look for idle_in_transaction_session_timeout or idle_session_timeout in PostgreSQL. If these are set too low, PostgreSQL will terminate idle connections (even those in your connection pool) and your app will hit the reset error when trying to reuse them.
• TCP keepalive settings: Check PostgreSQL’s tcp_keepalives_idle, tcp_keepalives_interval, and tcp_keepalives_probes—these control how PostgreSQL sends keepalive packets to maintain idle connections. Misconfiguration here can lead to unexpected terminations.

2. Validate Your sqlx/Go Connection Pool Configuration #

Setting MaxOpenConnections is good, but other pool parameters can cause this issue:

• MaxIdleConnections: Ensure this is set appropriately (usually equal to or less than MaxOpenConnections). If idle connections are kept too long, PostgreSQL might terminate them before your pool cleans them up.
• ConnMaxLifetime: This defines how long a connection can exist in the pool before it’s discarded. Set this lower than PostgreSQL’s idle_in_transaction_session_timeout or tcp_keepalives_idle to avoid reusing connections that have already been terminated by the server.
• ConnMaxIdleTime: This controls how long an idle connection stays in the pool. If you set this to a value shorter than PostgreSQL’s idle timeout, your pool will discard old connections before the server can terminate them.
• Connection leaks: Double-check your code for unclosed connections, transactions, or result sets. For example, missing defer rows.Close() or not properly committing/rolling back transactions can leave connections in an inconsistent state, leading to unexpected terminations.

3. Debug Network & OS-Level Issues #

If PostgreSQL and pool settings check out, dive into the test machine’s network stack:

• Capture network traffic: Use tcpdump to record traffic between your app and PostgreSQL on the test machine:

  tcpdump -i lo port 5432 -w postgres_connection.pcap
  

  Open the .pcap file in Wireshark to analyze who’s sending the RST packet—if it’s PostgreSQL, you’ll need to go back to its logs/settings. If it’s the OS or a firewall, you’ll know it’s a network stack issue.
• Check OS file descriptor limits: Run ulimit -n on the test machine to ensure the open file limit is higher than your MaxOpenConnections (plus other open files your app uses). A low limit can cause silent connection terminations.
• Inspect TIME_WAIT connections: Use netstat -an | grep 5432 | grep TIME_WAIT to see if there’s a buildup of closed connections in the TIME_WAIT state. A large number can exhaust available ports, leading to connection issues.
• Check firewall/iptables rules: Look for rules that terminate idle connections (e.g., iptables conntrack timeouts). Many Linux distros or cloud providers have default timeouts for idle TCP connections (often around 30 minutes) that can trigger this error.
• Verify TCP keepalive settings: On the test machine, check the system-wide TCP keepalive parameters:

  sysctl net.ipv4.tcp_keepalive_time net.ipv4.tcp_keepalive_intvl net.ipv4.tcp_keepalive_probes
  

  If these are set too high, idle connections might be terminated by network intermediaries before keepalive packets are sent.

4. Try to Reproduce the Issue in a Controlled Environment #

Since you can’t reproduce this locally, simulate the test machine’s conditions:

• Add load: Use tools like pgbench or a simple Go script to generate concurrent traffic matching your app’s workload. High CPU/memory load on the test machine might be causing PostgreSQL or the OS to terminate connections.
• Mirror test machine configuration: Clone the test machine’s PostgreSQL settings, OS limits, and network config onto a local VM to see if you can trigger the error.

Final Notes #

The connection reset by peer error is often a symptom of a mismatch between your connection pool’s lifecycle settings and PostgreSQL’s connection management rules. Start with those checks before diving into network stack debugging—you’ll likely find the root cause there.

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