[ 常見問題 ] Finding and killing long running queries on PostgreSQL

Source From Here 
Introduction 
From time to time we need to investigate if there is any query running indefinitely on our PostgreSQL database. These long running queries may interfere on the overall database performance and probably they are stuck on some background process. 

How-To 
In order to find them you can use the following query: 

view plaincopy to clipboardprint?

1. SELECT  
2.   pid,  
3.   now() - pg_stat_activity.query_start AS duration,  
4.   query,  
5.   state  
6. FROM pg_stat_activity  
7. WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';  

The first returned column is the process id, the second is duration, following the query and state of this activity. If state is idle you don’t need to worry about it, but active queries may be the reason behind low performances on your database. 
Notes. 

I’ve added the pg_cancel_backend as first option to stop the query because it’s safer than pg_terminate_backend.

In order to cancel these long running queries you should execute: 

view plaincopy to clipboardprint?

1. SELECT pg_cancel_backend(__pid__);  

The pid parameter is the value returned in the pg_stat_activity select. It may take a few seconds to stop the query entirely using the pg_cancel_backend command. If the you find the process is stuck you can kill it by running: 

view plaincopy to clipboardprint?

1. SELECT pg_terminate_backend(__pid__);  

Be careful with that! As pointed by Erwin Andreasen in the comments bellow, pg_terminate_backend is the kill -9 in PostgreSQL. It will terminate the entire process which can lead to a full database restart in order to recover consistency. 

Supplement 
* PostgreSQL Doc - System Administration Functions