Deleting idle connections

On Mon, Feb 24, 2025 at 3:50 PM Yongye Serkfem <yserkfem@gmail.com> wrote:

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.

"deleting" really isn't the word used to describe this, terminate, kill, or
disconnect would be better choices.

You should be able to just use pg_terminate_backend in a select query to
accomplish your goal.

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

Though usually you are better off fixing the problem at the source; or use
something like pgbouncer.

David J.

On Mon, Feb 24, 2025 at 5:50 PM 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 = '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!

On Tue, 25 Feb 2025 at 00:12, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

On Mon, Feb 24, 2025 at 5:50 PM 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 = '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