Database migrations

When changes are made to the model classes in ckan.model that alter CKAN’s database schema, a migration script has to be added to migrate old CKAN databases to the new database schema when they upgrade their copies of CKAN. These migration scripts are kept in ckan.migration.versions.

When you upgrade a CKAN instance, as part of the upgrade process you run any necessary migration scripts with the ckan db upgrade command.

A migration script should be checked into CKAN at the same time as the model changes it is related to.

To auto generate a new migration script from your model changes, use CKAN CLI:

ckan -c /etc/ckan/default/ckan.ini generate --autogenerate migration -m "Add account table"

Edit the generated file, because auto generation might not have generated all changes correctly, and remove the ### commands auto generated by Alembic - please adjust! comments. For more details see: https://alembic.sqlalchemy.org/en/latest/tutorial.html#create-a-migration-script

Rename the file to include a prefix numbered one higher than the previous one, like the others in ckan/migration/versions/.

Manual checking

As a diagnostic tool, you can manually compare the database as created by the model code and the migrations code:

# Database created by model
ckan -c |ckan.ini| db clean
ckan -c |ckan.ini| db create-from-model
sudo -u postgres pg_dump -s -f /tmp/model.sql ckan_default

# Database created by migrations
ckan -c |ckan.ini| db clean
ckan -c |ckan.ini| db init
sudo -u postgres pg_dump -s -f /tmp/migrations.sql ckan_default

sudo -u postgres diff /tmp/migrations.sql /tmp/model.sql

Troubleshooting

If you are working on a branch that adds new database migrations and merge the most recent commits from master, you might find the following error when running the tests (or manually upgrading the database):

        if len(current_heads) > 1:
            raise MultipleHeads(
                current_heads,
>               "%s@head" % branch_label if branch_label else "head")
E           CommandError: Multiple head revisions are present for given argument 'head'; please specify a specific target revision, '<branchname>@head' to narrow to a specific head, or 'heads' for all heads

../../local/lib/python2.7/site-packages/alembic/script/revision.py:271: CommandError

This means that your current alembic history has two heads, because a new database migration was also added in master in the meantime. To check which migrations need adjusting, go to the ckan/migrations folder and run:

alembic history

You should see a branchpoint revision and two head revisions, like in this example:

d4d9be9189fe -> 588d7cfb9a41 (head), Add metadata_modified filed to Resource
d4d9be9189fe -> f789f233226e (head), Add package_member_table
01afcadbd8c0 -> d4d9be9189fe (branchpoint), Remove activity.revision_id
0ffc0b277141 -> 01afcadbd8c0, resource package_id index
980dcd44de4b -> 0ffc0b277141, group_extra group_id index
23c92480926e -> 980dcd44de4b, delete migrate version table

In this case d4d9be9189fe was the latest common migration, and changes in master introduced 588d7cfb9a41, while we had already added f789f233226e.

The easiest fix is to manually set the down revision in our branch migration to the most recent one in master:

diff --git a/ckan/migration/versions/f789f233226e_add_package_member_table.py b/ckan/migration/versions/f789f233226e_add_package_member_table.py
index 5628d1350..ade2dd07f 100644
--- a/ckan/migration/versions/f789f233226e_add_package_member_table.py
+++ b/ckan/migration/versions/f789f233226e_add_package_member_table.py
@@ -10,7 +10,7 @@ import sqlalchemy as sa

 # revision identifiers, used by Alembic.
 revision = 'f789f233226e'
-down_revision = u'd4d9be9189fe'
+down_revision = u'588d7cfb9a41'
 branch_labels = None
 depends_on = None

This will give us a linear history once again:

588d7cfb9a41 -> f789f233226e (head), Add package_member_table
d4d9be9189fe -> 588d7cfb9a41, Add metadata_modified filed to Resource
01afcadbd8c0 -> d4d9be9189fe, Remove activity.revision_id
0ffc0b277141 -> 01afcadbd8c0, resource package_id index
980dcd44de4b -> 0ffc0b277141, group_extra group_id index
23c92480926e -> 980dcd44de4b, delete migrate version table

In more complex scenarios like two migrations updating the same tables, you can use the alembic merge command.