Re: Deleting idle connections

On Tue, 25 Feb 2025 at 00:12, Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> On Mon, Feb 24, 2025 at 5:50 PM Yongye Serkfem <yserkfem(at)gmail(dot)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 = '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