Home 2016 August PostgreSQL: Script to kill all idle sessions and connections of a Database

PostgreSQL: Script to kill all idle sessions and connections of a Database

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing one of the important script to kill all running idle connections and sessions of the PostgreSQL Database.

I have prepared this script such a way that you can also filter idle connections base on a particular time interval.

We are Database Administrator, and this is our responsibility to check idle connection periodically and if it requires to kill, we should do it.

To kill the connections is always not advisable, but in large systems where lots of transactions are going on in that situation we should kill idle connections base on a particular time interval.

Anvesh Patel
PostgreSQL: Create an auto incrementing Primary Key using a Custom SequencePostgreSQL: How to convert Table Data into JSON formatted Data?
Comments: 6
  1. Aksh
    September 25, 2018 at 9:48 am

    How do I use this script? Just call it from psql?

    • Anvesh Patel
      October 3, 2018 at 7:18 pm

      did you try to use via psql?

  2. Azim
    October 3, 2018 at 7:25 am

    Hi Anvesh,

    First of all thanks for this script. Its really works well though there are some sessions in idle state running on the database. Can you please share any advance or better where clause for the below part

    “state_change < current_timestamp – INTERVAL '15' MINUTE;"
    It will be a great help from your end.

    Cheers,
    Azim A.

    • Anvesh Patel
      October 3, 2018 at 7:12 pm

      Sure will check and share

  3. San
    September 10, 2019 at 5:47 am

    This helped!

  4. Manoj kumar matang
    January 22, 2020 at 9:18 am

    how to clean up old ideal connection approx 1 days ago in postgresql. and set in cronjob.

Anvesh Patel
Anvesh Patel

Database Engineer

August 4, 2016 PostgreSQL, PostgreSQL DBA Script, , , , , , , , , , , ,