Dealing with weird migrations/table updates that have locked postgres

postgres-is-locked.md

See what is waiting on what:

SELECT 
    waiting.locktype           AS waiting_locktype,
    waiting.relation::regclass AS waiting_table,
    waiting_stm.query          AS waiting_query,
    waiting.mode               AS waiting_mode,
    waiting.pid                AS waiting_pid,
    other.locktype             AS other_locktype,
    other.relation::regclass   AS other_table,
    other_stm.query            AS other_query,
    other.mode                 AS other_mode,
    other.pid                  AS other_pid,
    other.GRANTED              AS other_granted
FROM
    pg_catalog.pg_locks AS waiting
JOIN
    pg_catalog.pg_stat_activity AS waiting_stm
    ON (
        waiting_stm.pid = waiting.pid
    )
JOIN
    pg_catalog.pg_locks AS other
    ON (
        (
            waiting."database" = other."database"
        AND waiting.relation  = other.relation
        )
        OR waiting.transactionid = other.transactionid
    )
JOIN
    pg_catalog.pg_stat_activity AS other_stm
    ON (
        other_stm.pid = other.pid
    )
WHERE
    NOT waiting.GRANTED
AND
    waiting.pid <> other.pid

Select all blocked (or false for not blocked) queries:

SELECT * FROM pg_stat_activity WHERE waiting = true;

Select queries that might match the one you suspect, e.g. if it's an alter table:

SELECT * FROM pg_stat_activity WHERE query LIKE 'ALTER%'

You may as well save this state to a file to evaluate more later. At the end of the query, add \g blocked_query_info.txt.

In some cases, you may have mistakenly shipped a dangerous migration that e.g. adds a column with a default value to a large table in postgres. If the table is big but not massive (say, < 5 million rows, but depends on many factors like db hardware size and current db load), you're often better off waiting for it to finish and start working again on its own.

In other cases, you may have a deadlock with another process, or a table that's so big that you can't afford to wait for it to finish the migration, or a totally uknown situation, in which cases you'll just have to kill the migration query and hope for the best.

Kill the query by pid, e.g. for pid 1234:

SELECT pg_cancel_backend(1234);