March 13, 2018 Josh Warwick 1 min read

Have you ever accidentally run a blocking migration on your Postgres database? Here's how to fix it in under 1 minute:

First, connect to your database instance.

Now, run the query: SELECT pid, query_start, query FROM pg_stat_activity

This will return a table listing all the processes running on the database, with the date they started and the query.

It should be easy to identify the pid (process id) of the blocking query

Now run SELECT pg_cancel_backend([pid]) to cancel that transaction

Congrats, you are done!

Finally, you fool! If you want to avoid table locks you should avoid the following types of migration:

Adding a new column with a default value

Change the type of a column

Adding a new non nullable column

Adding a column with a uniqueness constraint

What exactly are we doing here?