How to close idle connections in PostgreSQL automatically?

Question

Some clients connect to our postgresql database but leave the connections opened. Is it possible to tell Postgresql to close those connection after a certain amount of inactivity ?

TL;DR

IF you're using a Postgresql version >= 9.2
THEN use the solution I came up with

IF you don't want to write any code
THEN use arqnid's solution

IF you don't want to write any code
AND you're using a Postgresql version >= 14
THEN use Laurenz Albe's solution

Answer 1

For those who are interested, here is the solution I came up with, inspired from Craig Ringer's comment:

(...) use a cron job to look at when the connection was last active (see pg_stat_activity) and use pg_terminate_backend to kill old ones.(...)

The chosen solution comes down like this:

• First, we upgrade to Postgresql 9.2.
• Then, we schedule a thread to run every second.
• When the thread runs, it looks for any old inactive connections.

  • A connection is considered inactive if its state is either idle, idle in transaction, idle in transaction (aborted) or disabled.
  • A connection is considered old if its state stayed the same during more than 5 minutes.

• There are additional threads that do the same as above. However, those threads connect to the database with different user.
• We leave at least one connection open for any application connected to our database. (rank() function)

This is the SQL query run by the thread:

WITH inactive_connections AS (     SELECT         pid,         rank() over (partition by client_addr order by backend_start ASC) as rank     FROM          pg_stat_activity     WHERE         -- Exclude the thread owned connection (ie no auto-kill)         pid <> pg_backend_pid( )     AND         -- Exclude known applications connections         application_name !~ '(?:psql)|(?:pgAdmin.+)'     AND         -- Include connections to the same database the thread is connected to         datname = current_database()      AND         -- Include connections using the same thread username connection         usename = current_user      AND         -- Include inactive connections only         state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')      AND         -- Include old connections (found with the state_change field)         current_timestamp - state_change > interval '5 minutes'  ) SELECT     pg_terminate_backend(pid) FROM     inactive_connections  WHERE     rank > 1 -- Leave one connection for each application connected to the database