When I run a query that takes a long time on my Postgres server (maybe 30 minutes), I get the error. I've verified the query is running with active status on the server using pgAdmin. I've also verified the correctness of the query, as it runs successfully on a smaller dataset. Server configurations are default, I haven't changed anything. Please help!
1 Answer
Look into the PostgreSQL server log.
Either you'll find a crash report there, which would explain the broken connection, or there is something in your network that cuts connections with no activity after a while.
Investigate your firewalls!
Maybe it is a solution to set the configuration parameter tcp_keepalives_idle to a value shorter than the time when the connection is cut. That will cause the server operating system to send keepalive messages on idle connections, which may be enough to prevent the overzealous connection reaper in your environment from disrupting your work.
2 Comments
Anatoly Alekseev
Indeed! from the docs: "On Windows, setting a value of 0 will set this parameter to 2 hours, since Windows does not provide a way to read the system default value." This is exactly what was happening in my case. Every 2 hours connection was broken with errors "could not receive data from client: An existing connection was forcibly closed by the remote host. unexpected EOF on client connection with an open transaction". Default setting of 0 seems to be unwise for windows.
Laurenz Albe
2 hours is fine for normal networks without malicious firewalls and routers.