This tip is a recipe for how to recover from a Rails migration that failed to apply in production, by applying it manually. This process could work for any SQL migration but the example used below is for an index added to a table with a PostgreSQL database.
This is also more of a symptom of an underlying problem to fix, but nevertheless it has been a useful technique to “right the ship” and keep things moving.
Manually add the schema migration version
Figure out the schema version. The version is the numeric part of the filename, for example in
20211121190924_create_index.rb it would be
20211121190924. This version will exist in the schema migrations table where it was applied earlier in pre-production as well.
Since we’re going to manually create the migration, we will insert the version manually so that on future deploys it does not attempt to run again and appears as if it had been applied normally.
INSERT INTO schema_migrations (version) VALUES (20211121190924);
If the migration was written in Ruby, translate it to SQL
Let’s use an example of adding an index. We occasionally have issues where the index create even with
CONCURRENTLY (in background) will be canceled due to a statement timeout. As an aside, make sure the statement timeout is raised a bit for migrations. Strong Migrations raises it to 1 hour.
To avoid typos, I want to get the exact SQL statement used to create the index from an earlier environment. I can achieve that using this query and the name of the index:
SELECT indexdef FROM pg_indexes WHERE indexname = 'index_name';
Let’s say we want to index the blog post ID for comments where the post_id is not null. So we will use a partial index. Let’s say comments can be stored without being associated to a post.
SELECT indexdef FROM pg_indexes WHERE indexname = 'index_comments_on_post_id';
Running the query then might produce a statement like below. Please note I’ve manually added the
CONCURRENTLY keyword below. The index build will be slower but will not block other operations on the table.
CREATE INDEX CONCURRENTLY index_comments_on_post_id ON public.comments USING btree (post_id) WHERE (post_id IS NOT NULL);
Wrap-up and Summary
- The migration version is the numeric part of the filename, and is inserted into the
schema_migrationstable to track which migrations have run
- The original Rails migration file will still be useful for other developers and other environments.
- It may be worth raising the statement timeout if migrations fail to finish in time
- Run migrations concurrently to avoid blocking other table operations
- Consider using Strong Migrations or a similar utility to increase the safety and reliability of migrations
- Once you have the SQL modification to apply, apply it manually and track it via schema migrations.
- Since we’re usually in a failed deploy state, after performing this manual fix we typically deploy again to confirm everything is a known good state.
- Whenever applying production changes, it’s a good idea to do a dry run on a non-production DB and share the plan in advance with a team member to help spot mistakes