Finding and killing stuck or mistyped postgresql queries

I’ve found these useful during development:

Listing running queries:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid();

Listing queries that have run for more than a full minute:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() AND (now() - pg_stat_activity.query_start) > interval '1 minute';

Try to cancel a single query:

SELECT pg_cancel_backend(<pid>);

Force kill a single query:

SELECT pg_terminate_backend(<pid>);

Force kill all queries that have been running for over 10 minutes:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() AND (now() - pg_stat_activity.query_start) > interval '10 minutes';

Force kill all queries using the user_accounts table (or mentioning user_accounts at all, this isn’t limited to table names):

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() AND query LIKE '%user_accounts%';

Hope someone finds this useful.

Leave a Reply

Your email address will not be published.