Recovering from an overrunning migration in under one minute
March 13, 2018Josh 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
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.