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
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: