Kill Stuck (long running) Processes in Postgres

October 21, 2024

Public

PostgreSQL

1 Query to find stuck queries with more than X minutes:

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '60 minutes';

2 - Query to kill the process:

SELECT pg_terminate_backend(__pid__);

note: be carefull not to kill any replication processes (they ususaly say START_REPLICATION)