Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Dealing with weird migrations/table updates that have locked postgres

See what is waiting on what:

    waiting.locktype           AS waiting_locktype,
    waiting.relation::regclass AS waiting_table,
    waiting_stm.query          AS waiting_query,
    waiting.mode               AS waiting_mode,                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,                  AS other_pid,
    other.GRANTED              AS other_granted
    pg_catalog.pg_locks AS waiting
    pg_catalog.pg_stat_activity AS waiting_stm
    ON ( =
    pg_catalog.pg_locks AS other
    ON (
            waiting."database" = other."database"
        AND waiting.relation  = other.relation
        OR waiting.transactionid = other.transactionid
    pg_catalog.pg_stat_activity AS other_stm
    ON ( =
    NOT waiting.GRANTED
AND <>

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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.