Moving your Django/Wagtail Database from SQLite to PostgreSQL (for dummies)

I suffered through this so you don't have to.

This is a quick tutorial for anyone that happens to be using Django (optionally with Wagtail) and wants to move your database backend from the default of sqlite3 over to postgres. It probably works for some other backends too.

If you're not using Wagtail then naturally you can just ignore it whenever it gets mentioned. Also, I'm assuming you've already got your Postgres/whatever database already running.

The Plan

  1. Dump the sqlite database into a JSON file,
  2. switch the Django settings over to the new database,
  3. run migrations,
  4. load the data dump into the new database.

Straight forward enough, but there's some weird caveats no-one seems to have written down in one place (at least that shows up in a web search)

The Doing

First, make sure you've activated your virtual environment, or these commands won't work!

Secondly, please, do not delete the original database until you are absolutely sure the new one works!! Maybe never delete it!

This process will utilise the manage.py dumpdata and loaddata commands. You could use these to quickly make and load a backup and of your database (or a part of it) and have it saved to a JSON file (other formats available) which, if you're a mad person, you can hand-edit.

Dump the Data

For our purposes we are going to need a few extra arguments applied to dumpdata:

python manage.py dumpdata --natural-foreign --natural-primary --exclude=wagtailsearch.sqliteftsindexentry --exclude=wagtailsearch.IndexEntry --indent=4>data.json

Quick explanation of the arguments:

  • --natural-foreign and --natural-primary: after migrateing the new database, a bunch of the default entries created by Django will likely get created in a different order, so the primary keys won't match up with what's in the dump, and Django sure hates that. You can read more about natural keys on the Django docs.
  • --exclude=wagtailsearch.sqliteftsindexentry and --exclude=wagtailsearch.IndexEntry: these are Wagtail tables holding search data that is specific to sqlite3. These will be different on Postgres, so we exclude them.
  • --indent=4: This makes the JSON dump pretty-print with 4-space indents. You can omit this for a more compressed output.
  • >data.json: not an argument, this directs the output into a file named data.json, otherwise it vomits all the JSON onto the console window.

Swap the Database

I'll be honest if you didn't look up the documentation for this by yourself I'm not sure why we're even here.

Anyway, crack open the main settings.py file and find where the DATABASE dictionary is declared. If you've not changed this before it should still be referencing sqlite3.

My version of this, for Postgres, looks like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'USER': POSTGRES_USERNAME,
        'NAME': POSTGRES_NAME,
        'PASSWORD': POSTGRES_PASSWORD,
        'HOST': POSTGRES_HOST,
        'PORT': POSTGRES_PORT,
    }
}

Obviously you're going to need to fill in the user details by yourself. If you need help, ask a trusted adult.

Migratory Databases and Where to Find Them

These titles might be getting stupider.

You've now got a connection to a blank database, so it doesn't have all the tables for your models. We can treat this as if you were starting up a project from scratch. Just do the usual manage.py migrate command to set them all up.

(I am assuming that you've already got valid migrations ready to go. If not, go back to the original database, makemigrations, migrate, dumpdata, then follow the steps back here.)

Lock and (re)Load

Right then, your new database is prepped and your data is sat in a file. We can join the two in holy matrimony using loaddata:

python loaddata data.json

This will attempt to load the data dump file you made earlier into the database. Obviously, if you used a different file name for your dump file, use that.

It's important to note that the file extension matters here as it tells Django what format the data is in.

It's possible to dump the file as JSON, XML, or as a sequence of SQL commands, and Django will use the file extension to determine the format to expect the data to be. You can't just named it "my data.txt" because Django will think you've invented a new data format named txt and get very upset when it finds out you're a liar.

Finish

Read this next sentence only if you had success: You did it, woo!

Addendum: The Index

"We must collect the Index before we can activate the installation." - 343 Guilty Spark

You may have noticed we excluded the search index data from transfer - it's (probably) not key to the server running, but we can re-create appropriate indexes using:

manage.py update_index

wait a few moments for it to run, and all being well, you're done! Again!

If you still encountered an error after this, refer to the trusted adult link above.

Related links