Thread: Deleting idle connections
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.
Regards
Yong
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.
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;
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.
You can catch and terminate otherwise actively working connections momentarily in the idle state, if you are not careful enough.
Regards,
Sándor