loading...
Cover image for How to migrate data from SQLite to PostgreSQL in Django

How to migrate data from SQLite to PostgreSQL in Django

coderasha profile image coderasha ・2 min read

By Reverse Python

Hi DEVs! In this post I want to talk about differences between SQLite and PostgreSQL. In addition, I will show you how to migrate your data from SQLite to PostgreSQL.

Well, SQLite and PostgreSQL are the most widely used relational database management systems. They are both open-source and free. However, they have some major differences that should be considered when choosing a database to use for your applications.

There is a lot articles on internet which covers this topic, so I will not focus on comparison of these databases.

SQLite vs PostgreSQL - Which database to use and why?

SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems

SQLite is too "light" for real world applications, so majority of developers prefer to use MySQL or PostgreSQL for web applications. Generally, SQLite is highly useful for:

  • Standalone apps
  • Small apps that don’t require expansion
  • Apps need to read or write files to disk directly
  • The internet of things devices
  • Developing and even testing

and PostgreSQL is recommended when:

  • Data integrity and reliability is highly concerned.
  • Custom Procedures which is extensible to run the complex task.
  • Complexity with ease. PostgreSQL gives you the functionality to maintain such a complex database smoothly without limitations.

Migrate data from SQLite to PostgreSQL

Dump existing data:

python3 manage.py dumpdata > datadump.json
Enter fullscreen mode Exit fullscreen mode

Change settings.py to Postgres backend. Check this awesome tutorial by Digital Ocean:

How To Use PostgreSQL with your Django Application on Ubuntu

Make sure you can connect on PostgreSQL. Then:

python3 manage.py migrate --run-syncdb
Enter fullscreen mode Exit fullscreen mode

Run this on Django shell to exclude contentype data

python3 manage.py shell
Enter fullscreen mode Exit fullscreen mode
>>> from django.contrib.contenttypes.models import ContentType
>>> ContentType.objects.all().delete()
>>> quit()
Enter fullscreen mode Exit fullscreen mode

Finally, run following command to load the json data:

python3 manage.py loaddata datadump.json
Enter fullscreen mode Exit fullscreen mode

Great! Now, your all data migrated from SQLite to PostgreSQL.

There is also interesting question on StackOverflow about MySQL vs PostgreSQL.

My Django project is going to be backed by a large database with several hundred thousand entries, and will need to support searching (I'll probably end up using djangosearch or a similar project.)

Which database backend is best suited to my project and why? Can you recommend any good resources…

That's it! Make sure you are following me on social media and if you find it useful please share with your friends. See you in next post DEVs!

Reverse Python
Instagram
Twitter

Discussion

pic
Editor guide
Collapse
matacino profile image
matacino

This only works for me with a simple example application.
If tried that on a system with CMS and different other applications I have always got duplicate key-issues because "migrate --run-syncdb" did always some migrations (at least on django 2.2).

Also a "manage.py flush" after running "migrate --run-syncdb" did not work because of foreign key-problems.

My solution was taking the SQL-output of "manage.py sqlflush" and adding CASCADE to the truncate-statement and running it in the database and than do a "manage loaddata …"

Regards

Karl

Collapse
codesnail profile image
CodeSnail 🐌

thanks, it's works :)

Collapse
adeyemitj profile image
Adeyemi Taiwo

Nice tutorial. it works for me. thank you.