Finding which query holds a lock and blocks others in PostgreSQL

-- ======================================================================
-- Q: How do you find which query is holding a lock and blocking the others?
--
-- A common cause: a transaction left open (idle in transaction) or a migration
-- waiting for an ACCESS EXCLUSIVE lock.
-- ======================================================================

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state,
       now() - xact_start AS xact_age, left(query, 80)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- if needed, stop the blocking query
SELECT pg_cancel_backend(<pid>);      -- cancel first, then pg_terminate_backend