Postgresql server closed the connection unexpectedly

Question

Having one of the most unusual problems I ever encountered.

I'm having my PostgreSQL database installed on a windows server, and listening on all ip addresses: listen_addresses = '*'

I can access and send queries without any issues on various client devices, either linux or windows OS base.

I'm having issue only with one certain linux client which for some reason fails to execute queries when query response is a little bit "heavier" if I can put it that way.

I'll try to elaborate by example.

I have psql client on that machine and a users table in my postgresql database on a remote windows server which has about 20 records, so when I run this query:

select "firstName", "createdAt", "updatedAt", username from users limit 13;

I get results normally:

  firstName  |           createdAt           |           updatedAt           |  username
-------------+-------------------------------+-------------------------------+-------------
 User 1       | 2017-01-26 12:48:52.995+01    | 2017-01-26 12:48:52.995+01    | user1
 User 2       | 2019-08-24 10:29:16.16329+02  | 2019-08-24 10:29:16.16329+02  | user2
 User 3       | 2018-10-05 11:45:14.127813+02 | 2018-10-05 11:45:14.127813+02 | user3
 User 4       | 2017-09-27 18:53:56.535867+02 | 2017-09-27 18:53:56.535867+02 | user4
 User 5       | 2017-03-28 11:46:27.03684+02  | 2017-03-28 11:46:27.03684+02  | user5
 User 6       | 2017-03-28 11:46:40.840481+02 | 2017-03-28 11:46:40.840481+02 | user6
 User 7       | 2018-05-22 12:43:08.397247+02 | 2018-05-22 12:43:08.397247+02 | user7
 User 8       | 2017-03-28 11:46:36.24854+02  | 2017-03-28 11:46:36.24854+02  | user8
 User 9       | 2022-04-30 14:04:02.24541+02  | 2022-04-30 14:04:02.24541+02  | user9
 User 10      | 2022-04-30 14:04:02.24541+02  | 2022-04-30 14:04:02.24541+02  | user10
 User 11      | 2022-04-30 14:04:02.24541+02  | 2022-04-30 14:04:02.24541+02  | user11
 User 12      | 2022-04-30 14:04:02.24541+02  | 2022-04-30 14:04:02.24541+02  | user12
 User 13      | 2022-04-30 14:04:02.24541+02  | 2022-04-30 14:04:02.24541+02  | user13
(13 rows)

And any query with limit up to 13 returns data without issues.

But immediately after adding one more row to the results (limit 14 in the query) I get this:

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

And when I do querying other tables, I got same issue too, data with lower limits will return successfully, but when I get higher load increasing limits in the query, it fails.

Looking into postgresql logs on my server I get this:

CEST FATAL:  connection to client lost
CEST LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

Doing same queries in my node app using npm [email protected] or any other version, I'm getting same issue, successes on less data in response, and this when it fails fetching more rows:

Error: read ECONNRESET
    at TCP.onStreamRead (internal/stream_base_commons.js:209:20) {
  errno: -104,
  code: 'ECONNRESET',
  syscall: 'read'
}

I also did some wireshark pcap dumps on client machine, while hitting these queries, and noticed that when I get an error wireshark log looks like this:

3301    2.220496557 25.67.20.168    25.20.186.130   TCP 68  [TCP Dup ACK 2839#1] 45208 → 5432 [ACK] Seq=27 Ack=1 Win=64542 Len=0 SLE=2729 SRE=3143

I don't know much on wireshark and network issues, but it looks something about duplicate acknowledgement "TCP Dup ACK" issue.

All of this is even more wierd because I get this problem only on one linux (ubuntu) client, and other clients work fine without any issues, and there is about 10 of them windows/linux-ubuntu mixed.

It is most likely some network issue I guess.

I'll appreciate any clue on this.

Answer 1

If both client and server think that the other end hung up on them, it is clearly a network problem.

You don't tell us how long these queries take, but it is possible that you hit a timeout in some in-between network component that decides that this seemingly idle connection should be terminated (there are people who don't know that there are other protocols than HTTP). You can prevent that by setting tcp_keepalives_idle on the server. Here is more about that topic.

It might well be a different problem, but it is certainly a network problem.