Re: PostgreSQL(v9.6.5) not releasing old/inactive IDLE connections

On 12/8/21 12:45 AM, Md Arqum Farooqui wrote:
>
> Hi,
>
> I'm facing "Too many clients already" error frequently with my PostgreSQL
> (v9.6.5)
>

You really need to update to 9.6.24.

> container (i.e. being used along with CKAN application i.e. A open source
> data management system <https://ckan.org/> ), I have observed that
> PostgreSQL is not releasing IDLE connections from their end. I have set
> "max_connetions = 100" in postgresql.
>
> Please provide your suggestions on below queries:
>
> 1. Why PostgreSQL is not releasing IDLE connection?
>

It is the application's job to close connections when they are no longer of use.

> (If we leave the setup then postgres keep IDLE  connection even for 1
> month, after that I have to restart the Postgres)
> 2. Is there any parameter or any solution by which i can remove unused old
> IDLE connection regularly?
>

I set up a cron job that regularly kills old idle connections.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'databasename'
  AND pid <> pg_backend_pid()
  AND state = 'idle'
  and extract(epoch from (current_timestamp - query_start)) > 20*60 -- 20
minutes
;

--
Angular momentum makes the world go 'round.