Hunting down blocking queries in Postgres

postgres_process.sql

-- Who is being blocked?
SELECT
  pid
     , usename
     , pg_blocking_pids(pid) AS blocked_by
     , query                 AS blocked_query
FROM pg_stat_activity
WHERE
  cardinality(pg_blocking_pids(pid)) > 0
;

-- Show me all your locks                               
SELECT *
FROM
  pg_locks pl
    LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid
;

-- List queries 
SELECT
  pid
     , age(query_start, clock_timestamp())
     , usename
     , query
FROM pg_stat_activity
WHERE
  query NOT ILIKE '%pg_stat_activity%' AND
  age(query_start, clock_timestamp()) IS NOT NULL
ORDER BY
  query_start DESC
;

-- Show everything
SELECT *
FROM pg_stat_activity
;

-- Kill the culprit
SELECT
  pg_cancel_backend(30791)
;