16

Clients connect to our PostgreSQL 8.3 database but leave the connections opened. Is it possible to configure PostgreSQL to close those connections after a certain amount of inactivity?

András Váczi
  • 31,778
  • 13
  • 102
  • 151
Stephan
  • 1,513
  • 4
  • 18
  • 27

3 Answers3

10

Finally, I have a solution. I'll use the tool PgBouncer with its parameter server_idle_timeout.

Another approach would consist in using some cron-like tool running a query periodically to find idle connections.

Once those idle connections are found, a simple call to pg_terminate_backend will close them.

See an in depth description of this approach in the anwser of the following question:

How to close idle connections in PostgreSQL automatically?

Stephan
  • 1,513
  • 4
  • 18
  • 27
6

Since Postgres 9.5 you can configure a timeout for connection in the state "idle in transaction" - which is a bit different just "idle" but typically "idle in transaction" connection generate more problems

See the manual for details: idle_in_transaction_session_timeout

3

Since Postgres 14, you can configure a timeout for idle connections using the idle_session_timeout setting:

update pg_settings
set setting = 2147483647
where name = 'idle_session_timeout';

From the docs:

idle_session_timeout (integer)

Terminate any session that has been idle (that is, waiting for a client query), but not within an open transaction, for longer than the specified amount of time. If this value is specified without units, it is taken as milliseconds. A value of zero (the default) disables the timeout.

Unlike the case with an open transaction, an idle session without a transaction imposes no large costs on the server, so there is less need to enable this timeout than idle_in_transaction_session_timeout.

Be wary of enforcing this timeout on connections made through connection-pooling software or other middleware, as such a layer may not react well to unexpected connection closure. It may be helpful to enable this timeout only for interactive sessions, perhaps by applying it only to particular users.

Ben
  • 31
  • 2