Find and Kill Long Running Process in PostgreSQL

Find and Kill Long Running Query/Connection in PostgreSQL 

There are some conditions when queries in the transaction executes for a long time and do not releases the resources. The worst impact of this situation is that another transactions do not get resources. Hence increases the query response time abruptly. In some cases, this situation can also cause 100% CPU utilisation. This situation is very well explained here.

To overcome this situation, one possible solution is to find long running queries and kill them. Here is the process to achieve it:

1. First find out the processes running from a long time:

SELECT pid, xact_start FROM pg_stat_activity ORDER BY xact_start ASC;

Output will be like:

  pid  |          xact_start           

-------+-------------------------------

15681 | 2016-10-08 06:49:54.382894+00

15509 | 2016-10-08 06:49:37.716762+00
16580 | 2016-10-08 06:52:03.381788+00

18040 | 2016-10-08 06:56:01.144984+00
17059 | 2016-10-08 06:53:33.534518+00
 18032 | 2016-10-08 06:56:00.037787+00
 18489 | 2016-10-08 06:57:25.48098+00

30643 | 2016-10-08 14:43:15.596917+00
19446 | 2016-10-08 07:00:11.268985+00
 24363 | 2016-10-08 11:57:31.244977+00
  1646 | 2016-10-08 12:19:44.068969+00

3266 | 
27730 | 2016-10-08 14:43:15.640929+00
 13560 | 
 26602 | 

29654 | 

As you can see, there are 12 processes that are running from a long time. So lets kill them.

2. Processes can be killed using their respective pid. Below command kills the process having pid 15681.

SELECT pg_cancel_backend(15681);

Output:

 pg_cancel_backend 

-------------------

(1 row)

Likewise, kill all the processes, that are showing in above commands. Now your PostgreSql will behave normally.