public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sándor Daku <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Deleting idle connections
Date: Tue, 25 Feb 2025 10:02:56 +0100
Message-ID: <CAKyoTgZuJe10da9uiG3_V5jZpD_JNw4U3bibn+AiU1=mdOgxOA@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaDVz6H=wUqXi72ZieNs3ivTSuYxnM6AYxEKKOMQigU_iw@mail.gmail.com>
References: <CAAu_hGvBJXOkrjydkOXuDTX8eNMQn9+OMzPT_bcBcT-C9QsPVg@mail.gmail.com>
<CANzqJaDVz6H=wUqXi72ZieNs3ivTSuYxnM6AYxEKKOMQigU_iw@mail.gmail.com>
On Tue, 25 Feb 2025 at 00:12, Ron Johnson <[email protected]> wrote:
> On Mon, Feb 24, 2025 at 5:50 PM Yongye Serkfem <[email protected]> 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 = '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.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
You could use the state_change timestamp to be sure that the idle
connection is in that state for long enough to be considered really idle.
You can catch and terminate otherwise actively working connections
momentarily in the idle state, if you are not careful enough.
Regards,
Sándor
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Deleting idle connections
In-Reply-To: <CAKyoTgZuJe10da9uiG3_V5jZpD_JNw4U3bibn+AiU1=mdOgxOA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox