Django SQLite to PostgreSQL database migration

Django SQLite to PostgreSQL database migration

Photo by Julia Craice on Unsplash

Hi All, in this article I'm going to show you how to move data from SQLite to Postgres.

Steps are

1. Take SQLite whole DB dumpdata fixture backup
2. Create Postgres DB with user and password
3. Change settings.py
4. import fixture using loaddata

1. Take SQLite whole DB dumpdata fixture backup.

first, you need to take the backup of the whole DB using the below command

python manage.py dumpdata > whole.json

in this command some user used to prefer to use the natural foreign key and primary key but I won't suggest using the bellow command until you got an error while restoring(loaddata) data to Postgres

python manage.py dumpdata — natural-foreign — natural-primary > whole.json

this commands will generate the whole.json in the root of your projects, this means you generated the dumpdata from SQLite in JSON fixture format.

2. Create Postgres DB with user and password.

in this step, you need to install Postgres in your OS like Ubuntu or mac(google), after installation is done, log in to Postgres

sudo su — postgres
psql

use the below command to create a user with password and DB and grant permission on DB from the user

create user hero;create database my_db;alter role hero with password ‘my_db@123’;grant all privileges on database my_db to hero;alter database my_db owner to hero;

3. Change settings.py

# install this package 
pip install psycopg2

settings.py

DATABASES = {
‘default’: {
‘ENGINE’: ‘django.db.backends.postgresql_psycopg2’,
‘NAME’: ‘my_db’,
‘USER’ : ‘hero’,
‘PASSWORD’ : ‘my_db@123’,
‘HOST’ : ‘localhost’,
‘PORT’ : ‘5432’,
}
}

delete all the migrations files because we do not need all old migrations, instead, we will create one migration file per app. use the below command to delete all migration files.

find . -path “*/migrations/*.py” -not -name “__init__.py” -delete
find . -path “*/migrations/*.pyc” -delete

do

python manage.py makemigrations
python manage.py migrate

now delete content types(mandatory steps) otherwise you will beautiful billions of errors

python manage.py shell
from django.contrib.contenttypes.models import ContentType
ContentType.objects.all().delete()

this means empty Postgres DB created with migrate done. next, we need to load into Postgres db from fixtures.

4. import fixture using loaddata.

One main step is to disable all signals in projects otherwise you will get a unique constraint or object already created.

use the below command to load data to DB from fixtures

python manage.py loaddata fixture/whole.json

that is it, you successfully moved data from SQLite to Postgres using fixtures

My advice is to use SQLite only for development and do the above data migration in the initial stage of the projects because this fixture method requires huge pc RAM to loaddata, if your SQLite more than 100MB it won't work.

And if you are using groups and permissions then take dumpdata without excluding the content types and permissions otherwise you will error while loaddata because groups depend on permission.

And sometimes you need to changes the model charfield to max_length size because dumpdata create some space in charfield value so while importing loaddata you will error like “in the model, field varying length 50 exceeds 50”.

Conclusion

In this article, I showed how to convert SQLite to Postgres using the fixture method, I know you will get errors please let me know in the comment section below, I will try to fix it