python - Why I obtain this error migrationg data from SQL Lite to Posgres DB ? duplicate key value violates unique constraint

Question

I am pretty new in Django and Python and I am trying to follow this videotutorial about how to migrate a Django application from SqlLite to Postgres database:

https://www.youtube.com/watch?v=ZgRkGfoy2nE&t=554s

But I am finding some problem. Following the details on what I have done:

I am working on Ubuntu 20.04 and I have a Django application made by someone else that uses SQLLite and that have to be migrated on Postgres. Following the previous tutorial I have done the following steps:

1. First of all I installed Postegres and PgAdmin4 on my Ubuntu system. Then here I created a DB named dgsrgdb that have to be my new database for my application in replacement of SQL Lite DB.

2. I installed this package to let PythonDjango operate with Postgres:

   pip3 install psycopg2-binary
   

3. I performed the backup of my original SqlLite DB by this command:

   python3 manage.py dumpdata > datadump.json
   

   and I obtained the datadump.json file that should contains the data inside the original DB that have to moved on the new Postgres DB.

4. Into the Django settings.py file I replaced this configuration:

   DATABASES = {
       'default': {
           'ENGINE': 'django.db.backends.sqlite3',
           'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
       }
   }
   

   whith this configuration related to Postgres:

   DATABASES = {
       'default': {
           'ENGINE': 'django.db.backends.postgresql_psycopg2',
           'NAME': 'dgsrg',
           'USER': 'dgsrguser',
           'PASSWORD': 'password',
           'HOST': '127.0.0.1',
           'PORT': '5432',
       }
   }
   

   so now my application points to my new dgsrgdb previously created on Postgres. I am using the "root" postgres user at the moment.

5. And now my problem. As shown in the tutorial I execute this command in my command line in order to create my tables on my dgsrg Postgres DB:

   python3 manage.py migrate --run-syncdb
   

   The tables related to my Django applications seems to be created on my new DB, this is what I have using PgAdmin:

6. Then following the tutorial I am trying to import the data previously exported into my datadump.json* file into these tables using this command:

   python manage.py loaddata datadump.json
   

but perfroming this command I am obtaining this error message:

    (DGSRG) andrea@ubuntu:~/Documenti/Python-WS/django_projects/dgsrg$ python manage.py loaddata datadump.json
    Traceback (most recent call last):
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
        return self.cursor.execute(sql, params)
    psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq"
    DETAIL:  Key (app_label, model)=(admin, logentry) already exists.


    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "manage.py", line 21, in <module>
        main()
      File "manage.py", line 17, in main
        execute_from_command_line(sys.argv)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
        utility.execute()
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/__init__.py", line 395, in execute
        self.fetch_command(subcommand).run_from_argv(self.argv)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/base.py", line 330, in run_from_argv
        self.execute(*args, **cmd_options)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/base.py", line 371, in execute
        output = self.handle(*args, **options)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 72, in handle
        self.loaddata(fixture_labels)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 114, in loaddata
        self.load_label(fixture_label)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 181, in load_label
        obj.save(using=self.using)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/serializers/base.py", line 223, in save
        models.Model.save_base(self.object, using=using, raw=True, **kwargs)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/base.py", line 790, in save_base
        updated = self._save_table(
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/base.py", line 872, in _save_table
        updated = self._do_update(base_qs, using, pk_val, values, update_fields,
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/base.py", line 926, in _do_update
        return filtered._update(values) > 0
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/query.py", line 803, in _update
        return query.get_compiler(self.db).execute_sql(CURSOR)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1522, in execute_sql
        cursor = super().execute_sql(result_type)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1156, in execute_sql
        cursor.execute(sql, params)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 98, in execute
        return super().execute(sql, params)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
        return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
        return executor(sql, params, many, context)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
        return self.cursor.execute(sql, params)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
        raise dj_exc_value.with_traceback(traceback) from exc_value
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
        return self.cursor.execute(sql, params)
    django.db.utils.IntegrityError: Problem installing fixture '/home/andrea/Documenti/Python-WS/django_projects/dgsrg/datadump.json': Could not load contenttypes.ContentType(pk=1): duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq"
    DETAIL:  Key (app_label, model)=(admin, logentry) already exists.

Why? What could be the problem? What am I missing? How can I try to fix it?

Answer 1

Before loading data using loaddata command,

We have to flush all the tables because django itself create some objects during migration.

Try running below command, command will flush data of all tables:

 python manage.py sqlflush | python manage.py dbshell

Then, loaddata into the PostgreSQl.

Comments

Thank you very much for this solution, it work for me