Kill a running query - Postgres

Often we notice long running queries in idle or idle in transaction state. This may or may not interfere with other operations that you may want to perform.

For instance, the default configuration in Sqlalchemy wraps all statements in a transaction. So a long running select query may lock the table blocking a database migration running around the same time.

To kill the long running query, we'd use this query on the pg_stat_activity table to identify the pid of the running query. Once we have the pid we cancel the query by running

  
  SELECT pg_cancel_backend(PID);
  

Cancelling the query allows the system to gracefully terminate the qstate. This may (or may not) interfere with other operations that you may want to perform.

  
    SELECT pg_terminate_backend(PID);
  

This terminates the query instantly. So, in order to kill all transstate. This may (or may not) interfere with other operations that you ay want to perform.

  
    SELECT 
        pg_terminate_backend(pid) 
    FROM 
        pg_stat_activity 
    WHERE 
            datname='db_name' 
        AND 
            state = 'idle in transaction' 
        OR 
            state = 'idle' 
        OR 
            state = 'idle in transaction' 
        OR 
            state = 'idle in transaction (aborted)';