Recovering from an overrunning migration in under one minute

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?

The table in Postgresql called pg_stat_activity is a record of all the processes currently running on the database at that moment. We use this to find the process ID of the thread controlling the overrunning transaction - we then use pg_cancel_backend function to cancel whatever process is running it.