3

When working with pgAdmin 4 (and actually, several other programs that act as clients to the database), the connection to the server disconnects after some 10 or 15 minutes of inactivity. One coffee and one phone call, and you get a message similar to "Sorry, connection to database is lost. Do you want me to try to reconnect?"

And pgAdmin reconnects always need more than one trial, and the object-tree that was already expanded gets collapsed... So, it's a little bit annoying.

pgAdmin doesn't seem to have any parameter related to that behaviour. (There seems to be ways to change some connection timeouts, but they're related to how pgAdmin behaves with regard to the server taking too long to connect).

What can be done it to avoid pgAdmin from disconnecting from the database?


Disclosure: This is actually a "pseudo-question". It is spin-off of another one, that ended up not being related to loss of connectivity... Given the fact I already had one answer in place, I (not very humbly) think it is worth "asking", in case the answer is helpful to someone.

4

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:

  1. 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.

  2. Make the router believe the connection is never idle for long enough. To do that: change one server parameter, called tcp_keep_alives:

According to the PostgreSQL documentation on Connections:

tcp_keepalives_idle (integer)

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.

  1. 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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.