How to Close Idle Connections in Postgresql Automatically

How to close idle connections in PostgreSQL automatically?

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

How to close idle connection on PostgreSQL database

You can safely terminate your own idle backend processes using pg_terminate_backend(pid int). If you try to terminate a process you haven't access to, you'll get an error message and nothing special will happen. Don't try to terminate active processes.

Per the documentation:

pg_terminate_backend(pid int)

Terminate a backend. This is also allowed if the calling role is a member of the role whose backend is being terminated or the calling role has been granted pg_signal_backend, however only superusers can terminate superuser backends.

Is there a timeout for idle PostgreSQL connections?

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.

How to close inactive connections in Postgres

Check out this post Is there a timeout for idle PostgreSQL connections?

And consider of using pgbouncer in front of your database.

I would like to set idle_in_transaction_session_timeout for a Heroku Postgres database

You can change it for your own user:

alter user current_user set idle_in_transaction_session_timeout = '5min';

How long for Postgres to drop idle user

Well, for your actual question:

if a user reaches his connection limit, and without me doing anything, what is the default setting for Postgres 9.1 to drop the connection on its own?

If the connections you are talking about are active connections to real clients (i.e. the client is still around, not crashed and exited, and can read and write to its connection socket), Postgres will not "drop the connection on its own". See this question if you're interested in ways to prune such idle clients.

On the other hand, it is possible for a Postgres backend to be nominally connected to a client which has really crashed or otherwise exited, as you alluded to earlier. In that case, Postgres may not notice the client is gone and not just in an idle state for some time. I believe the exact time would be controlled by a "TCP keepalive time" OS setting, or you could even fiddle with this setting in your client via connection parameters: see the various keepalives-related settings.

---

Related Topics