Agfdhyk

1 S postgres  5038   876  0  80   0 - 11962 sk_wai 09:57 ?        00:00:00 postgres: postgres my_app ::1(45035) idle                                                                                 

1 S postgres  9796   876  0  80   0 - 11964 sk_wai 11:01 ?        00:00:00 postgres: postgres my_app ::1(43084) idle             

I see a lot of them. We are trying to fix our connection leak. But meanwhile, we want to set a timeout for these idle connections, maybe max to 5 minute.

It sounds like you have a connection leak in your application because it fails to close pooled connections. You aren't having issues just with <idle> in transaction sessions, but with too many connections overall.

Killing connections is not the right answer for that, but it's an OK-ish temporary workaround.

Rather than re-starting PostgreSQL to boot all other connections off a PostgreSQL database, see: How do I detach all other users from a postgres database? and How to drop a PostgreSQL database if there are active connections to it? . The latter shows a better query.

For setting timeouts, as @Doon suggested see How to close idle connections in PostgreSQL automatically?, which advises you to use PgBouncer to proxy for PostgreSQL and manage idle connections. This is a very good idea if you have a buggy application that leaks connections anyway; I very strongly recommend configuring PgBouncer.

A TCP keepalive won't do the job here, because the app is still connected and alive, it just shouldn't be.

In PostgreSQL 9.2 and above, you can use the new state_change timestamp column and the state field of pg_stat_activity to implement an idle connection reaper. Have a cron job run something like this:

SELECT pg_terminate_backend(pid)

    FROM pg_stat_activity

    WHERE datname = 'regress'

      AND pid <> pg_backend_pid()

      AND state = 'idle'

      AND state_change < current_timestamp - INTERVAL '5' MINUTE;

In older versions you need to implement complicated schemes that keep track of when the connection went idle. Do not bother; just use pgbouncer.

In PostgreSQL 9.6, there's a new option idle_in_transaction_session_timeout which should accomplish what you describe. You can set it using the SET command, e.g.:

SET SESSION idle_in_transaction_session_timeout = '5min';

In PostgreSQL 9.1, the idle connections with following query. It helped me to ward off the situation which warranted in restarting the database. This happens mostly with JDBC connections opened and not closed properly.

SELECT

   pg_terminate_backend(procpid)

FROM

   pg_stat_activity

WHERE

   current_query = '<IDLE>'

AND

   now() - query_start > '00:10:00';

if you are using postgresql 9.6+, then in your postgresql.conf you can set

idle_in_transaction_session_timeout = 30000 (msec)