Howto: Find and kill a hanging query in Postgresql

 In normal case of a query to Postgresql database, it would take a very small amount of time to be processed. However, when query takes a long time to process then it will be a problem as resources such as connection, CPU and RAM will be locked to process that query.

Problem queries may lead to more queries in waiting until the database maximum connection is reached. Default Postgresql is set to maximum 100 connections. This will cause users to fail when trying to login to the application or trying to access the database. These 2 are common examples of when you will need to kill queries.

Lets see break this to 2 parts. 

Part 1, list max connection, and the current settings. Part 2, find the problem query, then to end it.

Part 1

Identify is the maximum connection to database has been reached.

Step 1: list the current maximum connection settings and where it is configured.

Login to postgresql terminal as admin (postgres) and use the commands.

SELECT name, setting, sourcefile FROM pg_settings WHERE name LIKE 'max_connections';

Step 2: List connections used

SELECT count(*) used FROM pg_stat_activity;

Compare values, to determine if this is an issue.

Part 2

Step 1: List query that is currently active

SELECT datid, pid, usename, client_addr, backend_start FROM pg_stat_activity WHERE state = 'active';

Step 2: Kill the query

SELECT pg_cancel_backend(PID);

Where PID is the value identified in Step 1 above.

Part 3

Identify long running queries can be done with a popular query mentioned in (https://wiki.postgresql.org/wiki/Lock_Monitoring)

SELECT a.datname,

         l.relation::regclass,

         l.transactionid,

         l.mode,

         l.GRANTED,

         a.usename,

         a.query,

         a.query_start,

         age(now(), a.query_start) AS "age",

         a.pid

FROM pg_stat_activity a

JOIN pg_locks l ON l.pid = a.pid

ORDER BY a.query_start desc;

This concludes how to troubleshoot basic connection and hanging queries.