#django #mysql #postgresql #python #sqlite #wagtail
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
1psycopg2
2mysqlclient
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
1DATABASES = {
2 'default': {
3 'ENGINE': 'django.db.backends.sqlite3',
4 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
5 }
6}
to:
1DATABASES = {
2 'default': {
3 'ENGINE': 'django.db.backends.postgresql_psycopg2',
4 'NAME': 'db-name',
5 'USER': 'db-user',
6 'PASSWORD': 'db-pass,
7 'HOST': 'localhost',
8 'PORT': 5432,
9 }
10}
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…
If this post was enjoyable or useful for you, please share it! If you have comments, questions, or feedback, you can email my personal email. To get new posts, subscribe use the RSS feed.