Application logs may show intermittent connection errors such as “An I/O error occurred while sending to the backend” or other error messages that indicate a timeout, write failure or a pipe error. Very shortly after the PostgreSQL logs show an error like “could not receive data from client: An existing connection was forcibly closed by the remote host”. These two events are interrelated and are frequently associated with client-side connection handling issues.
These types of errors can be tricky to troubleshoot as it involves different components. In this blog post, I will discuss what can lead to this error and best practices to avoid it.
Let’s say a client application tries to execute a query using a previously opened connection object retrieved from a connection pool. When the application attempts to use the connection object, the connection has gone “stale” and the client application throws an exception from attempting to send query data over the “open” connection. The client-side logic catches the exception and the TCP connection is closed. The PostgreSQL backend detects that the client-side connection was closed and reports the dropped connection as Windows error WSAECONNRESET (10054) Connection reset by peer: An existing connection was forcibly closed by the remote host. The ‘remote host’ here is the client, which is separate from the Postgres server.
Curious to know more?
The only way to understand exactly what is causing the issue is to capture network trace at the client-side at the time when the issue occurrs. Network Traces can be captured by different applications such as NetMon, WireShark, Fiddler, etc.
If you are using a Linux client and the tools mentioned above don't work, you can manually generate network dump as in the following command:
tshark -i any -n -b filesize:204800 -w `date +%y%m%d-%H:%M:%S`.pcap -b files:1000
tcpdump -i any -w `date +%y%m%d-%H:%M:%S`.pcap -G 300 -W 1000
Note: If you enable network capture, monitor your disk space and purge as needed to ensure you don’t run out of space.
Senior Software Engineer - Azure Database for PostgreSQL
Embedded Escalation Engineer - Azure OSS DB
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.