pgAdmin keeps throwing up a connection timeout every 10 minutes or so.
I would think that the problem might be related to a router (or, in some cases, a stateful firewall) between your pgAdmin client and the PostgreSQL server.
This is quite common when you are at home (or in the office) and your router is performing NAT (Network Address Translation). After some idle time the connection is closed by the router. It is not something done by neither pgAdmin nor PostgreSQL, this is something that depends on the settings on the router,
If this is the case, you have three options:
Change the connection_timeout setting in your router, if that is feasible and you have the proper documentation. Here is an example for Cisco routers.
Make the router believe the connection is never idle for long enough. To do that: change one server parameter, called
According to the PostgreSQL documentation on Connections:
Specifies the number of seconds of inactivity after which TCP should send a keepalive message to the client. A value of 0 uses the system default. This parameter is supported only on systems that support the TCP_KEEPIDLE or TCP_KEEPALIVE symbols, and on Windows; on other systems, it must be zero. In sessions connected via a Unix-domain socket, this parameter is ignored and always reads as zero.
The keepalive message should avoid the NAT connection begin dropped.
Keep in mind the remark in the documentation:
This parameter is supported only on systems that support the TCP_KEEPIDLE or TCP_KEEPALIVE symbols, and on Windows
I really don't know which systems "support TCP_KEEPIDLE or TCP_KEEPALIVE symbols", but I guess that Linux and Mac should support them, and so does Windows according to the documentation.
If your system disconnects every 10 minutes, it should be prudent to have this setting to something around 9 * 60 = 540 seconds, or less.
This setting should be done in the
postgresql.conf on your server. You can also test before making the change in the server, by either using the commands:
SET tcp_keepalives_idle = 480 ;
which would alter the setting for your current connection, or
ALTER ROLE (the-one-you-use) tcp_keepalives_idle = 480 ;
which would alter the setting only for this role.
- Change the keep_alives on the client. You don't need to change any connection_timeout (that parameter is "how long to wait for a connection before deciding that the other side is not responding). Follow the instructions of the accepted answer of Any way to keep connection alive in pgAdmin without setting it on the server?. It's perfectly explained.
Reference about keep-alives: This video talks about How to Keep Your SSH Sessions Alive. Although this is not exactly the same scenario, it is equivalent (substitute SSH by pgAdmin).