Skip to content
Logo Theodo

Recovering from an overrunning migration in under one minute

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

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.

Liked this article?