How to close idle connections in PostgreSQL automatically?

by

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

More Related Contents:

  1. Run batch file with psql command without password
  2. ClassNotFoundException with PostgreSQL and JDBC
  3. PostgreSQL function for last inserted ID
  4. What are ‘$$’ used for in PL/pgSQL
  5. What’s the fastest way to do a bulk insert into Postgres?
  6. Adding ‘serial’ to existing column in Postgres
  7. Making sense of Postgres row sizes
  8. How to persist data in a dockerized postgres database using volumes
  9. Time zone names with identical properties yield different result when applied to timestamp
  10. Permission denied for relation
  11. CONSTRAINT to check values from a remotely related table (via join etc.)
  12. How do I get a column with consecutive, increasing numbers, without having any numbers missing?
  13. Execute a dynamic crosstab query
  14. Copy a table from one database to another in Postgres
  15. Dynamic SQL (EXECUTE) as condition for IF statement
  16. uWSGI, Flask, sqlalchemy, and postgres: SSL error: decryption failed or bad record mac
  17. serial in postgres is being increased even though I added on conflict do nothing
  18. Can’t connect to Postgresql on port 5432
  19. Spark Dataframes UPSERT to Postgres Table
  20. Optimize Postgres query on timestamp range
  21. Use variable set by psql meta-command inside of DO block
  22. Sorting array elements
  23. pgAdmin III Why query results are shortened?
  24. How do I handle opening/closing Db connection in a Go app?
  25. PostgreSQL disable more output
  26. PostgreSQL array_agg order
  27. Postgres table column name restrictions?
  28. PostgreSQL jsonb, `?` and JDBC
  29. Syntax error at end of input in PostgreSQL
  30. Error when pushing data to Heroku: time zone displacement out of range

Leave a Comment