Killing/cancelling a long running Postgres query

Posted by Kyle Hankinson August 12, 2020

---

Finding the PID

The first thing you will need to do in order to kill or cancel a PostgreSQL query is to find the PID. This can be found by running the following query:

SELECT * FROM pg_stat_activity WHERE state = 'active';

Now that you have the PID, there are two options for killing the query.

Option #1 (graceful):

SELECT pg_cancel_backend(<PID>);

Option #2 (forceful):

SELECT pg_terminate_backend(<PID>);

Generally, Option #1 should be used as it gracefully terminates the query. Sometimes, however the query still continues to run for a long period of time even after being gracefull terminated. This is when Option #1 should be used.

Terminate all queries

If you want to terminate all running queries, the following statement can be executed:

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active' and pid <> pg_backend_pid();

The above statement will kill all active queries and should only be used in special situations.

---