1

I run:

select *
from pg_stat_activity

And there are some old queries (which still run on the DB background) which theirs python application doesn't exists (The apps crash or stopped without calling connection close command)

State          wait_event   backend_type
Active         null         parallel_backend

Is there a way to close all the queries which theirs processes are no longer exists ?

I saw this post:

Kill a postgresql session/connection

but I don't want to kill all sessions or connections, because there are some connections which gather and update important data.

I just want to close sessions (and stop queries) which theirs processes are no longer exists.

Boom
  • 1,145
  • 18
  • 44

1 Answers1

3

If you know or able to figure it out which are not neccessary, then you can fetch their pids from here.

SELECT pid,* FROM pg_stat_activity;

And use those pids over here along with dbname to kill those connections.

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
where datname in (<put dbname over here>) 
  and pid in(<put pid over here>);

Or simply use the below query to kill idle connections, which are not active.

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
where datname in (<put dbname over here>') 
  and state = 'idle';
SOHAM N.
  • 110
  • 8