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