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

On 12/8/21 12:45 AM, Md Arqum Farooqui wrote:

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;}div.WordSection1 {page:WordSection1;}

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 ), 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
;