How to migrate data from SQLite to PostgreSQL in Django
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
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
Run this on Django shell to exclude contentype data
python3 manage.py shell
>>> from django.contrib.contenttypes.models import ContentType
>>> ContentType.objects.all().delete()
>>> quit()
Finally, run following command to load the json data:
python3 manage.py loaddata datadump.json
Great! Now, your all data migrated from SQLite to PostgreSQL.
There is also interesting question on StackOverflow about MySQL vs PostgreSQL.
more
dumpdata command
- It is a django management command, which can be use to backup(export) you model instances or whole database
dumpdata for basic database dump
- Following command will dump whole database in to a
db.json
file
./manage.py dumpdata > db.json
dumpdata for backup specific app
- Following command will dump the content in django
admin
app intoadmin.json
file
./manage.py dumpdata admin > admin.json
dumpdata for backup specific table
- Following command will dump only the content in django
admin.logentry
table
./manage.py dumpdata admin.logentry > logentry.json
- Following command will dump the content in django
auth.user
table
./manage.py dumpdata auth.user > user.json
dumpdata (--exclude)
You can use
--exclude
option to specify apps/tables which don't need being dumpedFollowing command will dump the whole database with out including
auth.permission
table content
./manage.py dumpdata --exclude auth.permission > db.json
dumpdata (--indent)
By default,
dumpdata
will output all data on a single line. It isn’t easy for humans to readYou can use the
--indent
option to pretty-print the output with a number of indentation spaces
./manage.py dumpdata auth.user --indent 2 > user.json
- Example output of above command is below
dumpdata (--format)
By default, dumpdata will format its output in JSON
You can specify the format using --format option
Command supports for following formats(serialization formats)
- json
- xml
- yaml
./manage.py dumpdata auth.user --indent 2 --format xml > user.xml
- Above command output an xml file(user.xml)
loaddata command
- This command can be use to load the fixtures(database dumps) into database
./manage.py loaddata user.json
- This command will add the
user.json
file content into the database
Restore fresh database
When you backup whole database by using
dumpdata
command, it will backup all the database tablesIf you use this database dump to load the fresh database(in another django project), it can be causes
IntegrityError
(If youloaddata
in same database it works fine)To fix this problem, make sure to backup the database by excluding
contenttypes
andauth.permissions
tables
./manage.py dumpdata --exclude auth.permission --exclude contenttypes > db.json
- Now you can use
loaddata
command with a fresh database
./manage.py loaddata db.json
Comments
Post a Comment