How to Find and Kill Long Running Process in PostgreSQL

To Find and Kill Long Running Query/Connection in PostgreSQL

In this post we will learn to find and kill long Running Query/Connection in PostgreSQL.

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

To overcome this situation,first we have to to find long running queries and after that kill them. Following are the steps to achieve it:

1. Firstly, get the processes running from a long time.

SELECT pid, xact_start FROM pg_stat_activity ORDER BY xact_start ASC;

Output :

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 for a long time.

Now we will kill them by following the given steps:

2. These processes can be killed using their respective pid.

The below command kills the process having pid 15681.

SELECT pg_cancel_backend(15681);

Output

pg_cancel_backend
-------------------
(1 row)

Likewise, we can kill all the processes which are shown in above commands. Now your PostgreSQL will behave normally.