Killing Idle connections in azure database for postgresql

sql user 86 Reputation points
2021-01-26T18:42:31.48+00:00

Hi Experts,

Looking for some inputs on below requirement.
we have few servers On azure database for postgresql (single server) , the requirement is to configure and act upon an metric (Active connections >600) and run a script to kill idle connections in a database when the metric goes beyond 600 connections. can you pls let me know how this can be achieved ?. I would be really helpful if i can get a script as well and the procedure?

-Kevin

Azure Database for PostgreSQL
{count} votes

2 answers

Sort by: Most helpful
  1. Anurag Sharma 17,576 Reputation points
    2021-01-27T08:28:50.743+00:00

    Hi @sql user , welcome to Microsoft Q&A forum.

    You can run below query to see the count of total connections state-wise:

    SELECT count(*),state FROM pg_stat_activity GROUP BY 2;  
    

    If the count is greater than 600 overall, you can run below query to clean up the idle connections:

    SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'postgres' AND pid <> pg_backend_pid() AND state in ('idle');  
    

    Please note the datname is the database where we want to check the query.

    Please let us know if this helps or else we can discuss further on the same.

    Referenced Article:
    https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/connection-handling-best-practice-with-postgresql/ba-p/790883
    https://stackoverflow.com/questions/30931683/how-to-kill-the-idle-connection-in-postgresql

    ----------

    If answer helps, you can mark it as 'Accept Answer'


  2. KalyanChanumolu-MSFT 8,316 Reputation points
    2021-01-27T13:47:57.617+00:00

    @sql user You can configure the alert to invoke an Azure function whenever it is triggered.

    Whenever the active connections exceed 600, the alert is fired which in turn triggers the Azure function.
    You can write your pgsql commands in the azure function to kill the connections.
    A code sample to help you get started is here

    Create a new Alert rule from the Monitoring blade

    60967-image.png

    Provide a threshold value for the alert.

    61022-image.png

    Create a new Action group and select the Action type as "Azure Function"

    60968-image.png

    Do let us know if you face any issues.

    ----------

    If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

    0 comments No comments