Django: Moving data from SQLite to PostgreSQL

Question

I've recently inherited a production codebase for a webapp written using Django. Up until now, the database the project has been using has been the default SQLite3 database, but now that more people are using the app, moving to Postgres is necessary.

I've been able to set up an empty postgres database with the project that works fine. The problem I'm encountering is in moving the data from the old project to the new one. I can run python manage.py dumpdata --natural-foreign > dump.json to dump the data, which works fine, but when I switch to postgres in settings.py and run python manage.py loaddata dump.json I get the following error:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 338, in execute_from_command_line
    utility.execute()
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 330, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/core/management/base.py", line 390, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/core/management/base.py", line 441, in execute
    output = self.handle(*args, **options)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/core/management/commands/loaddata.py", line 60, in handle
    self.loaddata(fixture_labels)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/core/management/commands/loaddata.py", line 90, in loaddata
    self.load_label(fixture_label)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/core/management/commands/loaddata.py", line 147, in load_label
    obj.save(using=self.using)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/core/serializers/base.py", line 173, in save
    models.Model.save_base(self.object, using=using, raw=True)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 738, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 803, in _save_table
    forced_update)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/db/models/base.py", line 853, in _do_update
    return filtered._update(values) > 0
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/db/models/query.py", line 580, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 1062, in execute_sql
    cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 840, in execute_sql
    cursor.execute(sql, params)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/db/utils.py", line 97, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/mnt/d/Code/MCJobTrack/_VENV/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: Problem installing fixture '/mnt/d/Code/MCJobTrack/jobtrack_project/dump.json': Could not load contenttypes.ContentType(pk=15): duplicate key value violates unique constraint "django_content_type_app_label_45f3b1d93ec8c61c_uniq"
DETAIL:  Key (app_label, model)=(jobtrack, purchaseorder) already exists.

So far I've tried:

Running TRUNCATE django_content_type RESTART IDENTITY CASCADE; in the dbshell
Excluding contenttypes when dumping the data. (I get the error django.core.serializers.base.DeserializationError: Problem installing fixture '/mnt/d/Code/MCJobTrack/jobtrack_project/dump_no_contenttypes.json': ContentType matching query does not exist.)
Resetting the primary key sequence
Using the --natural-primary tag when dumping the data

Any help would be greatly appreciated.

score 1 · Accepted Answer · answered Dec 10 '17 at 22:59

1

Turns out there were two things I needed to do:

Dump the data with --natural-primary, --natural-foreign, and -e contenttypes
Have loaddata ignore post_save signals

It still takes a very long time to load the data, but it works.

answered Dec 10 '17 at 22:59

Miles Levitsky

73
6

score 0 · Answer 2 · answered Dec 09 '17 at 02:20

0

You probably need to use --natural-primary as well, so that the primary keys of content types are not exported to the fixture:

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

answered Dec 09 '17 at 02:20

solarissmoke

30,039
14
71
73

Tried that: running loaddata now produces the error `jobtrack.models.DoesNotExist: Problem installing fixture '/mnt/d/Code/MCJobTrack/jobtrack_project/dump.json': Employee matching query does not exist.` – Miles Levitsky Dec 10 '17 at 11:23

Django: Moving data from SQLite to PostgreSQL

2 Answers2