Cancel Postgres query

Do you need to cancel / abort / terminate a long running query in PostgreSql? Perhaps you ran a very nasty select or kicked off an index creation that’s blocking inserts. Either way, stopping a long running query is easy.

1. Log into postgres as the super user: psql -U postgres
2. Find your query

postgres=> select procpid, current_query from pg_stat_activity;
 procpid |                                                                                                                                   current_query                                                                                                                                    
---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    8093 | <IDLE>
    8172 | <IDLE>
    9357 | <IDLE>
    9358 | <IDLE>
   17360 | select procpid, current_query from pg_stat_activity;
    9359 | <IDLE>
   22429 | drop index fooindex;
   20363 | <IDLE>
   29098 | <IDLE>
    1083 | <IDLE>
   10812 | <IDLE>
   10855 | <IDLE>
(13 rows)

1. Use pg cancel backend to stop the query

postgres=# select pg_cancel_backend(22429);
 pg_cancel_backend 
-------------------
 t
(1 row)

All done. Your query was cancled. If you were creating an index you may have an invalid index which will require clean up later.