Migrate a Django Project From SQLite to PostgreSQL

Problem statement

The project started on SQLite and now needs PostgreSQL: more concurrent writes, a managed database, full-text search, or a second app server. The data in db.sqlite3 must come along: users, orders, uploads metadata, and everything else. Copying the file is impossible, and pointing Django at an empty PostgreSQL database loses everything.

The reliable approach is to let Django create the schema in PostgreSQL with migrate, then move the data separately, either with Django's serialisation (dumpdata/loaddata) for small and medium databases or with pgloader for large ones, and finish by fixing sequences and checking counts.

1. Create the PostgreSQL database and role, and point a separate settings module at it.
2. Run python manage.py migrate against PostgreSQL to build the schema.
3. Stop writes to SQLite (maintenance mode), then export: python manage.py dumpdata --natural-foreign --natural-primary -e contenttypes -e auth.Permission -e sessions --indent 2 > data.json.
4. Load into PostgreSQL: python manage.py loaddata data.json.
5. Reset sequences with sqlsequencereset, compare row counts, and switch DATABASES to PostgreSQL.
6. Keep the SQLite file as a backup until the new database has run for a while.

Step-by-step solution

1. Prepare PostgreSQL

CREATE ROLE myapp LOGIN PASSWORD 'change-me';
CREATE DATABASE myapp OWNER myapp ENCODING 'UTF8';

Install the driver (pip install "psycopg[binary]") and add a settings override that uses PostgreSQL:

# config/settings/pg.py
from .production import *  # noqa
DATABASES = {"default": env.db("DATABASE_URL")}   # postgres://myapp:...@localhost/myapp

2. Build the schema with migrate

DJANGO_SETTINGS_MODULE=config.settings.pg python manage.py migrate

This creates every table, index, and constraint exactly as Django expects, including content types and permissions rows that migrate generates. Do not try to convert the SQLite schema directly; types differ (SQLite has no real boolean, varchar lengths are not enforced), and Django's migrations already encode the correct PostgreSQL schema.

3. Freeze writes and export

Put the site in maintenance mode or stop the app so no new rows arrive, then export from SQLite:

python manage.py dumpdata \
  --natural-foreign --natural-primary \
  -e contenttypes -e auth.Permission -e admin.LogEntry -e sessions \
  --indent 2 -o data.json

Excluding contenttypes and auth.Permission avoids conflicts with rows migrate already created; natural keys make foreign keys to those models portable. Excluding sessions and admin logs is optional and reduces size.

4. Load into PostgreSQL

DJANGO_SETTINGS_MODULE=config.settings.pg python manage.py loaddata data.json

Typical problems and fixes:

• IntegrityError on a unique field: data that SQLite accepted but PostgreSQL rejects, such as duplicate values in a field that is unique in the model, or strings longer than max_length. Clean the source data and repeat.
• Foreign key violations: rows pointing at deleted rows (SQLite does not enforce foreign keys unless enabled). Find and remove orphans before exporting.
• Memory: dumpdata loads every object; for large databases use pgloader with data only into the migrated schema, or export per app.

5. Fix sequences and verify

DJANGO_SETTINGS_MODULE=config.settings.pg python manage.py sqlsequencereset $(python manage.py shell -c "from django.apps import apps; print(' '.join(a.label for a in apps.get_app_configs()))") | DJANGO_SETTINGS_MODULE=config.settings.pg python manage.py dbshell

Then compare counts for every model:

from django.apps import apps
for m in apps.get_models():
    print(m._meta.label, m.objects.using("default").count())

Run the same script against SQLite and diff the outputs. Spot-check a few records with relations and file fields.

6. Switch and keep a way back

Point production settings at PostgreSQL, restart Gunicorn and Celery, and take the site out of maintenance mode. Keep db.sqlite3 untouched for a few days; if something is wrong, you can switch back while you investigate, at the cost of losing writes made since the cutover.

Verification check: after cutover, log in, create and edit records in each main model (new IDs should not collide), run python manage.py check --deploy, and confirm the app's connection with python manage.py dbshell -c "select version();".

Explanation

SQLite and PostgreSQL store data differently enough that a direct file conversion produces subtle errors: SQLite stores booleans as integers, dates as text, and allows any value in any column. Django abstracts these differences at the ORM level, which is why moving data through Django's serialisers (or loading into a schema Django created) gives correct types in PostgreSQL. The steps that remain manual are the ones Django does not handle automatically for bulk loads: resetting sequences and checking that constraints PostgreSQL enforces were not violated by legacy data.

Edge cases / notes

• Uploads are not in the database; MEDIA_ROOT files stay where they are, only their paths move.
• Generic relations depend on content type IDs; natural keys handle this when you exclude contenttypes from the dump.
• Raw SQL written for SQLite (date functions, LIKE case sensitivity) may need changes; run the test suite against PostgreSQL first.
• Full-text search, JSON fields, and ArrayField become available after the move; adopt them gradually.

Next steps

• Configure the connection for production with Connect Django to PostgreSQL in Production.
• Set up backups immediately with Backup and Restore PostgreSQL for Django.

FAQ

Can I convert db.sqlite3 to PostgreSQL directly?

Tools like pgloader can, but loading data into a schema created by migrate gives Django-correct types and constraints.

Why exclude contenttypes from dumpdata?

migrate already creates them in PostgreSQL. Natural keys let other rows refer to them without ID conflicts.

How long does the migration take?

For databases of a few hundred megabytes, typically minutes. Rehearse on a copy to measure.

Why do new objects fail with duplicate key after loaddata?

Sequences were not advanced. Run sqlsequencereset for every app.