Finding and Closing Idle Connections in PostgreSQL

To enumerate all database connections that have been idle for at least 10 minutes:

SELECT
  DATE_TRUNC('second',NOW()-query_start) AS age,
  client_port,
  current_query
FROM
  pg_stat_activity
WHERE
  current_query = '<IDLE>'
  AND NOW() - query_start > '00:10:00'
ORDER BY
  age DESC;

To close all database connections that have been idle for at least 10 minutes:

SELECT
  pg_terminate_backend(procpid)
FROM
  pg_stat_activity
WHERE
 current_query = '<IDLE>'
 AND now() - query_start > '00:10:00';

WARNING Don't be fooled by the SELECT statement used here. The query above will drop database connections.

Published 14 Dec 2015
Tagged database and postgresql.

 

This page was generated at 4:16 PM on 26 Feb 2018.
Copyright © 1999 - 2018 Rodney Waldhoff.