How To Migrate From SQLite To PostgreSQL In Django In 3 steps

Overview

So your Django project has already grown and you need to migrate your data from the default SQLite¹ basic database to PostgreSQL².

This tutorial assumes you have already configured a PostgreSQL database in another server, we will just focus in migrating all the data and database structure.

Steps

1. Dump Sqlite Data

We will dump all the data from the sqlite database with the command³ manage.py dumpdata, and redirect the output to a file > dump.json, in this way, we will create a dump file with all installed applications data in the database.

$ ./manage.py dumpdata --indent 4 > dump.json

This command has two special parameters worth noting, that may be useful depending on how you designed your application.

• --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. If you’re dumping contrib.auth Permission objects or contrib.contenttypes ContentType objects, you should probably use this flag. ⁴

This basically means that if you get error mes

• --natural-primary
  • Omits the primary key in the serialized data of this object since it can be calculated during deserialization. ⁵

In Django terminology, we are creating a fixture, “a file that contain the serialized contents of the database”⁶

Data serialization is the process of converting structured data to a format that allows sharing or storage of the data in a form that allows recovery of its original structure

2. Create Tables in PostgreSQL

On the new Django instance which has PostgreSQL configured, Create the required structure in the new database with the migration command: ./manage migrate ⁷

$ ./manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, games, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying games.0001_initial... OK
  Applying games.0002_auto_20200825_1236... OK
  Applying games.0003_auto_20200825_1326... OK
  Applying games.0004_auto_20200825_1413... OK
  Applying games.0005_auto_20200825_1416... OK
  Applying games.0006_auto_20200825_1635... OK
  Applying games.0007_auto_20200825_1635... OK
  Applying games.0008_auto_20200825_1652... OK
  Applying games.0009_auto_20200825_1705... OK
  Applying games.0010_auto_20200825_1900... OK
  Applying games.0011_auto_20200828_1851... OK
  Applying games.0012_auto_20200828_1852... OK
  Applying games.0013_auto_20200828_1907... OK
  Applying sessions.0001_initial... OK
$

3. Load data

We are ready to load the dumped data into the new database with: ./manage loaddata.⁸

$ ./manage.py loaddata dump.json
Installed 153 object(s) from 1 fixture(s)

3.1 Problems

Possible problems may arise when loading data because of how differently each database defines the schema and its requirements.

For example, common problems include how differently sqlite and postgresql handle models.DateTime fields, and the enforcement of foreign keys uniqueness, etc.

If you get an IntegrityError message like:

django.db.utils.IntegrityError: Problem installing fixture 'local_datadump.json': Could not load contenttypes.ContentType(pk=3): duplicate key value violates unique constraint "django_content_type_app_label_model_XXXXXX_uniq"
DETAIL:  Key (app_label, model)=(admin, logentry) already exists.

You should be sure the database is empty ./manage.py flush or can also dump the sqlite database using natural-foreign keys, and excluding contenttypes and auth.permission:

$ ./manage.py dumpdata --natural-foreign --exclude auth.permission --exclude contenttypes --indent 4 > data.json

3.1.1 Dumpdata Wagtaill

To dump a database containing a Wagtail app, further exclusions should be considered or it would give errors like wagtail "psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint:

./manage.py dumpdata --natural-foreign \
		--exclude auth.permission --exclude contenttypes \
		-e wagtailcore.groupcollectionpermission \
		-e wagtailcore.grouppagepermission -e wagtailimages.rendition \
		-e sessions \
		--indent 4 > fixtures/local_datadump.json

Character varying Error

If you see something like: Could not load YOUR-MODEL(pk=1): value too long for type character varying(50) it may be that SQLite didn’t check the VARCHAR max_length you have specified in your models definitions, so you need to adjust or your data or the schema.

Many other kind of errors may (will) arise, that will need some tweaking, but after some adjustments you can enjoy your new PostgreSQL database.

References

• https://www.postgresql.org/docs/
• Management commands https://docs.djangoproject.com/en/3.1/ref/django-admin/