Switching to PostgreSQL fails loading datadump

Question

Using sqlite3 and Django I want to change to PostgreSQL and keep all data intact. I used ./manage.py dumpdata > dump.json to dump the data, and changed settings to use PostgreSQL.

With an empty database ./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? How should I switch database backend safely?

i ignored some django tables. ./manage.py dumpdata -e sessions -e admin -e contenttypes -e auth.Permission -e authtoken --natural > db.json and then did ./manage.py loaddata db.json. Maybe it helps someone. For details see this question stackoverflow.com/questions/853796/… — Andrei-Niculae Petre
– Andrei-Niculae Petre, Commented Apr 21, 2015 at 17:42

Daniel Roseman · Accepted Answer · 2010-06-19 20:17:35Z

40

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.

answered Jun 19, 2010 at 20:17

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shacker Over a year ago

In my case, postgres complained that it could not perform the truncation due to foreign key constraints, and suggested adding the CASCADE argument, which worked. So: "TRUNCATE django_content_type CASCADE;"

Hardest Over a year ago

In my case I had to TRUNCATE django_content_type CASCADE; but it worked great!

ogai · Accepted Answer · 2011-04-04 09:14:27Z

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.

answered Apr 4, 2011 at 9:14

ogai

212 bronze badges

Comments

dieb · Accepted Answer · 2016-04-04 04:52:47Z

0

This worked for me. You probably want to ensure the server is stopped so no new data is lost. Dump it:

$ python manage.py dumpdata --exclude auth.permission --exclude contenttypes --natural > db.json

Make sure your models don't have signals (e.g. post_save) or anything that creates models. If you do, comment it out momentarily.

Edit settings.py to point to the new database and set it up:

$ python manage.py syncdb

$ python manage.py migrate

Load the data:

./manage.py loaddata db.json

answered Apr 4, 2016 at 4:52

dieb

3492 silver badges3 bronze badges

Comments

hyances · Accepted Answer · 2022-11-12 02:02:59Z

0

I used pgloader, just take a few seconds to migrate successfully:

$ pgloader project.load

project.load file with:

load database
  from sqlite:////path/to/dev.db
  into postgresql://user:pwd@localhost/db_name

with include drop, create tables, create indexes, reset sequences

 set work_mem to '16MB', maintenance_work_mem to '512 MB';

answered Nov 12, 2022 at 2:02

hyances

133 bronze badges

Collectives™ on Stack Overflow

Switching to PostgreSQL fails loading datadump

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related