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);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s