kill a running query in postgres

There are queries which takes more times to execute. Sometimes you need to stop the queries but you can’t do it simply(Ctrl+c) because queries are coming from application. There are mainly two approaches to kill the query. You can find the process corresponding to the query & kill or you can find the procpid of the query and terminate it from PostgreSQL console. Former approach may not be a great idea cause PostgreSQL thinks there is a crash in the system & restarts the whole PostgreSQL database. for the later approach you need to do the following steps. Get the Status of the running queries. To get the details about all the running on the PostgreSQL, ┬áplease execute below query.

SELECT * FROM pg_stat_activity;

or

SELECT procpid,current_query from pg_stat_activity;

Get procid of the query you want to kill / Stop. Stop the query by below query:

SELECT pg_cancel_backend(procpid);

Kill the database connection by below query:

SELECT pg_terminate_backend(procpid);