Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1murIk-00064t-73 for pgsql-admin@arkaria.postgresql.org; Wed, 08 Dec 2021 07:23:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1murIj-0008D1-31 for pgsql-admin@arkaria.postgresql.org; Wed, 08 Dec 2021 07:23:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1murIi-0008Cs-J9 for pgsql-admin@lists.postgresql.org; Wed, 08 Dec 2021 07:23:40 +0000 Received: from mail-ot1-x329.google.com ([2607:f8b0:4864:20::329]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1murId-00020S-KF for pgsql-admin@lists.postgresql.org; Wed, 08 Dec 2021 07:23:39 +0000 Received: by mail-ot1-x329.google.com with SMTP id h19-20020a9d3e53000000b0056547b797b2so1842414otg.4 for ; Tue, 07 Dec 2021 23:23:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=PDxhJ/2i7U+y1ithykRU0EnAA7/dKrdfxn8TG/bdtPo=; b=V1i3/Iylc3V5fgkvpV682CaDFFgHNUruYZz761AIip0aq5r3/K0jtXg410P0dtCzWq fAO1u/ff8Nx3+pflBDtx4SSCreB1qOL7K2h+hkk4ZOXe7bWonIakgXDqCUlU0te/S2Pn +IiZLuaXOg6ro5ZgDyBAO+yB1XjRC7A/dj3MahmRdGF42zoVUOxK0CGRaMOYi7P/hFEy GnocjxGDFCc0CHpaQz1863mxzrzIAcxx2kA/kehMFSB9BPRBQI1d3MdxtxyMyk5nB8xv PpzYtFC/qc4pWTSDIb6t/7juVevkecExb0C7hj2haq5UuJHTf+Ras9DfSmaD2khA4NAF ERfA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=PDxhJ/2i7U+y1ithykRU0EnAA7/dKrdfxn8TG/bdtPo=; b=E0HTsMT4hWS84y0U1FEiJ1CkppgFOxfeRn+TXlzTucKgyzpyHJ1jDbwhBY+YleNV8H lJ9rvQiStkCMxChyjhcizTDGi9xj31Gm2QRX8NhxS+oKDGD3kKmwbLFCbnYUdImNNE1E jTTJeXiMFEPAr3ts4PV65Q6qLNZtd/t9SwYm1jdqORNqTWMQv16KfdYY1ErY/vtSv7He jOOgjqZijqpHe10qp2IXQxRpmLrG+S0EQ/c56W7/k+fHAqdwcywb98M7axKeGKUCLcgi oE4uMn6IDh0ytFsZ184esYWepLvneu9rbda2C5uGCMqJzWPHQXG/GhU2N97uP9HT+DHC /yOw== X-Gm-Message-State: AOAM530pu1aJJatvARReKOVXNq+4qRkeIz2igtl03ye4XeIZq3LefdrD j4HSoTRpVrXH+qg7inrGx5dtXx3kPKs= X-Google-Smtp-Source: ABdhPJwBdZSQCRYy4t+MbLAxePZfhiwYQr37LgK5VY8B1MBElwt0sIMPVThbs1aZM1Akzs4qkv+L4g== X-Received: by 2002:a9d:628f:: with SMTP id x15mr28130715otk.348.1638948214484; Tue, 07 Dec 2021 23:23:34 -0800 (PST) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id m22sm480238ooj.8.2021.12.07.23.23.33 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 07 Dec 2021 23:23:33 -0800 (PST) Subject: Re: PostgreSQL(v9.6.5) not releasing old/inactive IDLE connections To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: <7ab7add9-9c1b-3842-e4eb-f78107964db3@gmail.com> Date: Wed, 8 Dec 2021 01:23:32 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.14.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A63EDAAF40CC23316990C6DB" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------A63EDAAF40CC23316990C6DB Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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  ), 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. --------------A63EDAAF40CC23316990C6DB Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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 ), 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.
--------------A63EDAAF40CC23316990C6DB--