🐥 Migrating your Wagtail site to a different database engine

In the Wagtail Slack channel, there was a question recently about how to move a Wagtail site from PostgreSQL to MySQL. There are many tutorials available on how to do this with Django, but for Wagtail, there are a small number of extra steps you need to take into account.

In this tutorial, I'm going to describe how I do this. The good thing about my approach is that it doesn't really matter what the source database engine is and what the database engine is you want move to. You can use it go from SQLite to PostgreSQL or MySQL, but you can also use to move from PostgreSQL to MySQL or the other way around. Moving back to SQLite also works.

We are going to use the built-in functionality of Django to do the hard work. We are mainly going to use the dumpdata, migrate, shell and loaddata admin commands.

Prerequisites

Before we start, we are assuming your project is configured with the source database in the settings.py file. Also make sure you already have installed the proper packages so that you're able to connect to the source and destination database engines (SQLite is provided in the default Python install):

requirements.txt

psycopg2
mysqlclient

Dump the data

Step 1 is to use the dumpdata command to dump the data in a JSON file. We are choosing JSON because it's a neutral, database inspecific way of describing the data. A SQL dump is not an option as each database engine has it's own SQL dialect and they can't be easily interchanged.

$ ./manage.py dumpdata --natural-foreign --indent 2 \
    -e contenttypes -e auth.permission -e postgres_search.indexentry \
    -e wagtailcore.groupcollectionpermission \
    -e wagtailcore.grouppagepermission -e wagtailimages.rendition \
    -e sessions > data.json
CommandError: Unknown model: postgres_search.indexentry

The error above is because I'm migrating from a SQLite database to Postgres. Since SQLite doesn't support the Postgres search, we need to omit it from the export.

$ ./manage.py dumpdata --natural-foreign --indent 2 \
    -e contenttypes -e auth.permission \
    -e wagtailcore.groupcollectionpermission \
    -e wagtailcore.grouppagepermission -e wagtailimages.rendition \
    -e sessions > data.json

This commmand dumps all the data into a file called data.json. We are excluding a number of tables which a transient and which we don't need to migrate. The --natural-foreign uses the natural_key() model method to serialize any foreign key and many-to-many relationship to objects of the type that defines the method. Since Wagtail uses contrib.contenttypes ContentType objects, we need to use this.

Update the database config

Now that we have a copy of the database content, it's time to update the configuration to the destination database. This can be done in the settings. Depending on your configuration, you'll need to update either mysite/base.py, mysite/dev.py or mysite/prod.py. In my examples, I'm migrating from SQLite to PostgreSQL. Therefor, I'm changing this:

mysite/settings/base.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

to:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'db-name',
        'USER': 'db-user',
        'PASSWORD': 'db-pass,
        'HOST': 'localhost',
        'PORT': 5432,
    }
}

At this point, you should create the database if you didn't already yet. For SQLite, this will be done automatically. For MySQL and PostgreSQL, you'll need to create the database.

Initial migrate

Since we now have an empty database, we will first apply all migrations to get the basic structure of the database setup. This can be done as usual with the migrate admin command:

./manage.py migrate

Empty the pages table

Since the initial migrations from Wagtail don't just create database tables, we need an extra step before loading the data. The migrations also create the initial pages, but these are also included in the dump. This will lead to duplicate entries and will fail the import. We need to delete these before doing the import. We can do this using the shell admin command:

$ ./manage.py shell
>>> from wagtail.core.models import Page
>>> Page.objects.all().delete()
>>> exit()

Load the data

The last step is to load the data into the database using the loaddata admin command:

$ ./manage.py loaddata data.json
Installed 53 object(s) from 1 fixture(s)

At this point, you should have your site ready and available using the different database engine. Do yourself a favour and make a proper backup before doing the migration. This ensures there is always a way back…

Thanks for this and this post for the inspiration.