22

I have developed a simple django application using sqlite3. At first, I wanted to keep it simple using sqlite3 but, things are beginning to scale up (Yes, I actually started using that application with sqlite3! Shame on me...) so I want to migrate all my data to postgresql database.

Does django or another third-party provide such feature, or should I suffer for my own stupidity...

yasar
  • 13,158
  • 28
  • 95
  • 160

4 Answers4

39

First, execute 

manage.py dumpdata > out.json

then change your DB config, migrate (or syncdb) and finally

echo "delete from auth_permission; delete from django_content_type;" | python manage.py dbshell

(If you have Django older than 1.11, you need to use

echo "delete from django_content_type;" | manage.py dbshell

)

Then load the JSON file:

manage.py loaddata out.json

(as of 2013 django_contenttype is replaced with django_content_type)

Arpad Horvath -- Слава Україні
  • 1,744
  • 1
  • 17
  • 39
rewritten
  • 16,280
  • 2
  • 47
  • 50
  • I get an error like this: Integrity Error: duplicate key value violates unique constraint "django_content_type_app_label_model_key" DETAIL: KEY (app_label, model)=(admin,logentry) already exists – yasar Aug 10 '11 at 05:58
  • 1
    have you changed db? When you dump your data, you can simply delete the tables you have, update the schema, run syncdb (tables will be recreated) and the load data again (remember if you deleted some columns from schema, you need to delete it from json file too). You can see the tables and queries needed to reset the app, when you ran "manage.py sqlreset appname". And also, --indent=4 will help with the dumpdata command ;) – Ignas Butėnas Aug 10 '11 at 06:38
  • btw - http://stackoverflow.com/questions/5739880/django-update-database-schema-without-loosing-data could be helpful.. – Ignas Butėnas Aug 10 '11 at 06:41
  • The ContentType table is populated by the syncdb, still the same data esists is the out.json. Edited to cleanup the table before `loaddata`. – rewritten Aug 10 '11 at 07:04
  • 1
    I had to do `echo "delete from django_content_type;" | python managepy dbshell` (note the extra _). It also gave a few error messages of other tables I had to delete first. – thumbtackthief Nov 21 '13 at 01:45
  • 1
    In two years, maybe the django tables changed. @thumbtackthief could you confirm that it's a change an in that case update my answer or give a new one? – rewritten Nov 21 '13 at 15:45
  • In Django 1.11, it should be this. `echo "delete from auth_permission; delete from django_content_type;" | python manage.py dbshell` – Yu Kobayashi Feb 03 '18 at 06:13
  • I get ERROR: syntax error at or near ""delete from django_content_type;"" when I copy paste exactly the line – RMS Sep 16 '19 at 13:34
  • If you use `manage.py dumpdata -o out.json` instead of `manage.py dumpdata > out.json` you'll get a properly formatted file and a progress bar in your terninal – JayTurnr Sep 19 '20 at 11:00
3

I am trying to do the same exact thing right now, but I am running into a problem with resolving dependencies basically the same as ticket 16317. But enough about me...

Troubleshooting this led me to find a link for django-smuggler which allows you to create dumps and load data from the admin interface.

It looks promising for any data transfer needed or to use as a backup utility.

j_syk
  • 6,511
  • 2
  • 39
  • 56
2

If you get errors when loading the data, first dump it like this:

python manage.py dumpdata --exclude auth.permission --exclude contenttypes > datadump.json

as described here:

http://www.denzow.me/entry/2017/09/06/223517

aris
  • 22,725
  • 1
  • 29
  • 33
0

I was in this same situation and was having trouble loading the data into the new postgresql database due to key constraint errors. What worked for me was to rename my 'default' sqlite database to 'sqlite', add my postgresql database connection as 'default', do the data export like this:

python manage.py dumpdata --database sqlite --natural-foreign --natural-primary > sqlite-dump.json

Then load the data into the new database like this:

python manage.py loaddata sqlite-dump.json

(This is with Django 2.2.)

dhobbs
  • 3,357
  • 1
  • 18
  • 19