Migrate data from SQlite to PostgreSQL in Django

SQlite is a quick and and easy way to start with. As it has nothing to configure. We simply can use it in our Django projects. But, as the project gets bigger, we understand the necessity to move a standard databases like MySQL or PostgreSQL. But we have some data in our database right? If we simply change our settings.py file, we’ll lose all the data stored in our SQlite database. So in this tutorial, we’ll learn how to safely migrate our data from SQlite to PostgreSQL.

Setting Up our PostgreSQL Database for Django

Download and install PostgreSQL from their official website here. Open the root postgreSQL session with the following command:

sudo su - postgres

Now, you’ll enter into a session as postgres, Login into Postgres session by:

psql 

Let’s create a database to store our data which we’ll migrate from SQlite:

CREATE DATABASE project_database;

Let’s create a user for this database:

CREATE USER project_database_user WITH PASSWORD 'yourpassword';

Now, we’ll specify a few permissions and a few configurations for this user:

 ALTER ROLE project_database_user SET client_encoding TO 'utf8';
 ALTER ROLE project_database_user SET timezone TO 'UTC';

That’s it. We’re done with the user setup. Get back to the normal user’s shell by:

\q
exit

Configuring Settings of our project

To use the database of our choice other than the default SQlite, we need to install psycopg2 package:

pip install psycopg2

Now, go to out project directory. Now, we need to store our data in SQlite in a file, Not normal file. We’ll store the data in a json file with the following command:

python manage.py dumpdata > data.json
python3 manage.py dumpdata > data.json

If the above doesn’t work for you, use the second one. And after dumping the data into the json file, change the encoding from ‘utf16’ to ‘utf8’ (You can find the option to do this in your Visual Studio Code at the bottom right corner).

Configuring PostgreSQL as our Database

Now that our data is safe, we need to tweak our settings.py file to configure our database as PostgreSQL. Change the following lines in your settings.py:

. . . 
DATABASES = {     
'default': {
 'ENGINE': 'django.db.backends.postgresql_psycopg2',
 'NAME': 'project_database',
 'USER': 'project_database_user',
 'PASSWORD': 'yourpassword',
 'HOST': 'localhost',
 'PORT': ''  
} 
} 
. . .

Time for migration!

After changing the settings.py file, run the PostgreSQL server with username as project_database_user and on database project_database with the following command:

psql project_database project_database_user;

Now, keep this running in background and get back to our Django Project. Run the following command:

python manage.py migrate --run-syncdb

What this command does is, it’ll change the database backend to postgresql. Now, since we’ve got our data in json file, we don’t want the default data in our database that is shipped with Django. So, since we’ve created a new database with postgresql, we need to delete the default data on it. To do this, run the following commands:

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

Now, finally we need to dump the data in our json file back to our database (postgresql). Use the following command:

python manage.py loaddata data.json

Now, all our previous data from SQlite will get migrated! That’s it.