Find out blocked queries in PostgreSQL version 9.5 and earlier

Below is the query that can be used to find out blocked queries in PostgreSQL version 9.6 onwards -

SELECT blockingl.relation::regclass,

  blockeda.pid AS blocked_pid, blockeda.query as blocked_query,

  blockedl.mode as blocked_mode,

  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,

  blockingl.mode as blocking_mode

FROM pg_catalog.pg_locks blockedl

JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid

JOIN pg_catalog.pg_locks blockingl ON(blockingl.relation=blockedl.relation

  AND blockingl.locktype=blockedl.locktype AND blockedl.pid != blockingl.pid)

JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid

WHERE NOT blockedl.granted;

Once we figure out the blocked query, we can kill the blocked query using the PID and below command -

postgres=# select pg_terminate_backend(3014);

We can also figure out if queries are waiting from OS using the below command -

[rahul@linux] ps -ef | grep wait