Switching Django project from sqlite3 backend to postgresql failes when loading datadump

Question

I am currently using sqlite3 as the DB for one of my Django projects. I want to change this to use postgresql, and I would like to keep all the data intact.

I used ./manage.py dumpdata > dump.json to create a dump of the data, and changed my settings to use postgresql. Trying first with an empty database to do ./manage.py loaddata dump.json resulted in errors about tables not existing, so I ran ./manage.py syncdb, and tried again. That results in this error:

Problem installing fixture 'dump.json': Traceback (most recent call last):
  File "/usr/lib/python2.6/site-packages/django/core/management/commands/loaddata.py", line 163, in handle
    obj.save()
  File "/usr/lib/python2.6/site-packages/django/core/serializers/base.py", line 163, in save
    models.Model.save_base(self.object, raw=True)
  File "/usr/lib/python2.6/site-packages/django/db/models/base.py", line 495, in save_base
    rows = manager.filter(pk=pk_val)._update(values)
  File "/usr/lib/python2.6/site-packages/django/db/models/query.py", line 448, in _update
    return query.execute_sql(None)
  File "/usr/lib/python2.6/site-packages/django/db/models/sql/subqueries.py", line 124, in execute_sql
    cursor = super(UpdateQuery, self).execute_sql(result_type)
  File "/usr/lib/python2.6/site-packages/django/db/models/sql/query.py", line 2347, in execute_sql
    cursor.execute(sql, params)
  File "/usr/lib/python2.6/site-packages/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
IntegrityError: duplicate key value violates unique constraint "django_content_type_app_label_key"

• Is this not the correct way to move data from one database to another?
• What should I be doing in order to switch DB backend safely?

Answer 1

The problem is simply that you're getting the content types defined twice - once when you do syncdb, and once from the exported data you're trying to import. Since you may well have other items in your database that depend on the original content type definitions, I would recommend keeping those.

So, after running syncdb, do manage.py dbshell and in your database do TRUNCATE django_content_type; to remove all the newly-defined content types. Then you shouldn't get any conflicts - on that part of the process, in any case.

Answer 2

There is a big discussion about it on the Django ticket 7052. The right way now is to use the --natural parameter, example: ./manage.py dumpdata --natural --format=xml --indent=2 > fixture.xml

In order for --natural to work with your models, they must implement natural_key and get_by_natural_key, as described on the Django documentation regarding natural keys.

Having said that, you might still need to edit the data before importing it with ./manage.py loaddata. For instance, if your applications changed, syncdb will populate the table django_content_type and you might want to delete the respective entries from the xml-file before loading it.