MIME-Version: 1.0
References: <CAAu_hGvBJXOkrjydkOXuDTX8eNMQn9+OMzPT_bcBcT-C9QsPVg@mail.gmail.com>
In-Reply-To: <CAAu_hGvBJXOkrjydkOXuDTX8eNMQn9+OMzPT_bcBcT-C9QsPVg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 24 Feb 2025 18:12:13 -0500
Message-ID: <CANzqJaDVz6H=wUqXi72ZieNs3ivTSuYxnM6AYxEKKOMQigU_iw@mail.gmail.com>
Subject: Re: Deleting idle connections
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000166c45062eeb7982"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDVz6H%3DwUqXi72ZieNs3ivTSuYxnM6AYxEKKOMQigU_iw%40mail.gmail.com>
Precedence: bulk

--000000000000166c45062eeb7982
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Feb 24, 2025 at 5:50=E2=80=AFPM Yongye Serkfem <yserkfem@gmail.com>=
 wrote:

> Hi Everyone!
> I am having a series of idle connections and unable to delete them with a
> single command. Any help in realizing this would be greatly appreciated.
>

This will kill idle connections older than two hours:
select pid, pg_terminate_backend(pid)
from pg_stat_activity
where state =3D 'idle'
  and (EXTRACT(epoch FROM now() - backend_start))/3600.0 > 2;

Be warned that it might kill more than you want.  Add more WHERE predicates
as filter.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000166c45062eeb7982
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Feb 24, 2025 at 5:50=E2=80=AFPM Y=
ongye Serkfem &lt;<a href=3D"mailto:yserkfem@gmail.com">yserkfem@gmail.com<=
/a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hi Everyone!<d=
iv>I am having a series of idle connections and unable to delete them with =
a single command. Any help in realizing this would be greatly appreciated.<=
/div></div></blockquote><div><br></div><div>This will kill idle connections=
 older than two hours:</div><div><font face=3D"monospace">select pid, pg_te=
rminate_backend(pid) <br>from pg_stat_activity <br>where state =3D &#39;idl=
e&#39;<br>=C2=A0 and (EXTRACT(epoch FROM now() - backend_start))/3600.0 &gt=
; 2;</font><br></div><div><br></div><div>Be warned that it might kill more =
than you want.=C2=A0 Add more WHERE predicates as filter.</div><div><br></d=
iv></div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"l=
tr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, a=
nd butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&=
lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--000000000000166c45062eeb7982--