Why no rolling-back completed migrations?
Opened this issue · 1 comments
I've been using alembic
for database migrations for ~1y now, but I'm considering switching to pgroll
because I need to migrate O(100M) rows with minimal downtime.
Is there a reason pgroll
doesn't support rolling-back completed migrations? I can upgrade
and downgrade
using alembic
. AFAICT my options are:
- Use
alembic
andpgroll
- Use
alembic
to write the O(100M) row migration using the same concepts thatpgroll
uses (i.e. expand/contract, triggers)
I would love to fully switch over to pgroll
, but being able to downgrade would make me more comfortable.
Hi @wpcarro 👋
pgroll
supports rolling back migrations in the 'active migration period' ie, the time between pgroll start
and pgroll complete
. There is no limit to how long a migration can remain in this state, only that the migration must be completed before another can be started.
Once the migration is completed, the database schema is contracted to leave the final desired state of the schema. This is a lossy operation; for example if you have altered a column to add a check constraint with a migration that involves a data migration, like this:
{
"name": "22_add_check_constraint",
"operations": [
{
"alter_column": {
"table": "posts",
"column": "title",
"check": {
"name": "title_length",
"constraint": "length(title) > 3"
},
"up": "(SELECT CASE WHEN length(title) <= 3 THEN LPAD(title, 4, '-') ELSE title END)",
"down": "title"
}
}
]
}
Then during the active migration period the database schema has an additional _pgroll_new_title
field which contains the migrated values from the title
field. Rolling back from this state is straightforward - remove the _pgroll_new_title
field. The existing title
field remains unchanged. Once the migration is completed however, the old title
field is dropped and the new one is renamed to title
. There is no way back from this operation - the old title
field is gone forever.
In your case, is it possible that you can keep the migration active (ie started but not completed) for as long as you need to verify the correctness of the migration?