Error trying to run manage.py loaddata > duplicate key value violates unique constraint

[SquirrelSloth]

Hi, we are trying to migrate our database to a new version of Netbox and having some issues. Here's the scenario:

• Current old version is 2.9.11
• Installed 3.2.9 on another server
• Performed python netbox/manage.py dumpdata --format json -o dump.json --exclude extras.Script --exclude extras.Report --natural-foreign on old server and copied that dump over to new server
• Performed python manage.py loaddata /tmp/dump.json on new server
• Got an error about duplicate key value violates unique constraint, but that was easy enough to resolve because the details told us it was with device interfaces, so we resolved that
• Now we killed the new version and installed same version on the new server (2.9.11) just to eliminate any discrepancies.
• Then we took another dumpdata and trying loaddata again and this time same type of error but I don't understand which table or object type to look at for this...

🧬 loaded config '/etc/netbox/config/configuration.py'
🧬 loaded config '/etc/netbox/config/configuration.py'
🧬 loaded config '/etc/netbox/config/extra.py'
/opt/netbox/netbox/utilities/api.py:116: SyntaxWarning: "is" with a literal. Did you mean "=="?
if obj is '':
/opt/netbox/netbox/utilities/api.py:124: SyntaxWarning: "is" with a literal. Did you mean "=="?
if data is '':
Traceback (most recent call last):
File "/usr/local/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 10, in
execute_from_command_line(sys.argv)
File "/usr/local/lib/python3.8/site-packages/django/core/management/init.py", line 401, in execute_from_command_line
utility.execute()
File "/usr/local/lib/python3.8/site-packages/django/core/management/init.py", line 395, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/usr/local/lib/python3.8/site-packages/django/core/management/base.py", line 330, in run_from_argv
self.execute(*args, **cmd_options)
File "/usr/local/lib/python3.8/site-packages/django/core/management/base.py", line 371, in execute
output = self.handle(*args, **options)
File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 72, in handle
self.loaddata(fixture_labels)
File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 114, in loaddata
self.load_label(fixture_label)
File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 181, in load_label
obj.save(using=self.using)
File "/usr/local/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 "/usr/local/lib/python3.8/site-packages/django/db/models/base.py", line 787, in save_base
updated = self._save_table(
File "/usr/local/lib/python3.8/site-packages/django/db/models/base.py", line 869, in _save_table
updated = self._do_update(base_qs, using, pk_val, values, update_fields,
File "/usr/local/lib/python3.8/site-packages/django/db/models/base.py", line 923, in _do_update
return filtered._update(values) > 0
File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 803, in _update
return query.get_compiler(self.db).execute_sql(CURSOR)
File "/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1515, in execute_sql
cursor = super().execute_sql(result_type)
File "/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1154, in execute_sql
cursor.execute(sql, params)
File "/usr/local/lib/python3.8/site-packages/cacheops/transaction.py", line 93, in execute
result = self._no_monkey.execute(self, sql, params)
File "/usr/local/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 "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 90, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/usr/local/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 '/tmp/dump.json': Could not load contenttypes.ContentType(pk=8): duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq"
DETAIL: Key (app_label, model)=(admin, logentry) already exists.

Would anyone have a clue where I should start looking for this?

[mtinberg]

This is mentioned in the upgrade and release notes but may have been missed, you need to do a stepwise upgrade to 2.11.x before upgrading to the 3.x series as all the database schema migrations for older versions are no longer generated. You may need to pin a few dependencies as well to build a 2.11.x Netbox, the latest version installed by pip of some libraries are no longer compatible with Netbox 2.11. If you can search the list you may find some info from the last person to run into this issue. I don't know of any upgrade procedure that uses dumpdata/loaddata, the schema between versions changes and only the Django migrations have the right logic for updating the data in the database to the correct schema. Best would be to backup the postgresql data, restore on your test host with 2.11.12 installed, run the upgrade.sh to upgrade the db schema from 2.9.11 to 2.11.12, then upgrade that instance to 3.2.9 or 3.3.8 then run upgrade.sh again on that version to migrate the db schema from 2.11.12 to to current. — Mark Tinberg ***@***.***> Division of Information Technology-Network Services University of Wisconsin-Madison

…

________________________________ From: SquirrelSloth ***@***.***> Sent: Wednesday, November 16, 2022 12:14 PM To: netbox-community/netbox ***@***.***> Cc: Subscribed ***@***.***> Subject: [netbox-community/netbox] Error trying to run manage.py loaddata > duplicate key value violates unique constraint (Discussion #10940) Hi, we are trying to migrate our database to a new version of Netbox and having some issues. Here's the scenario: * Current old version is 2.9.11 * Installed 3.2.9 on another server * Performed python netbox/manage.py dumpdata --format json -o dump.json --exclude extras.Script --exclude extras.Report --natural-foreign on old server and copied that dump over to new server * Performed python manage.py loaddata /tmp/dump.json on new server * Got an error about duplicate key value violates unique constraint, but that was easy enough to resolve because the details told us it was with device interfaces, so we resolved that * Now we killed the new version and installed same version on the new server (2.9.11) just to eliminate any discrepancies. * Then we took another dumpdata and trying loaddata again and this time same type of error but I don't understand which table or object type to look at for this... 🧬 loaded config '/etc/netbox/config/configuration.py' 🧬 loaded config '/etc/netbox/config/configuration.py' 🧬 loaded config '/etc/netbox/config/extra.py' /opt/netbox/netbox/utilities/api.py:116: SyntaxWarning: "is" with a literal. Did you mean "=="? if obj is '': /opt/netbox/netbox/utilities/api.py:124: SyntaxWarning: "is" with a literal. Did you mean "=="? if data is '': Traceback (most recent call last): File "/usr/local/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 10, in execute_from_command_line(sys.argv) File "/usr/local/lib/python3.8/site-packages/django/core/management/init.py", line 401, in execute_from_command_line utility.execute() File "/usr/local/lib/python3.8/site-packages/django/core/management/init.py", line 395, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/usr/local/lib/python3.8/site-packages/django/core/management/base.py", line 330, in run_from_argv self.execute(*args, **cmd_options) File "/usr/local/lib/python3.8/site-packages/django/core/management/base.py", line 371, in execute output = self.handle(*args, **options) File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 72, in handle self.loaddata(fixture_labels) File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 114, in loaddata self.load_label(fixture_label) File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 181, in load_label obj.save(using=self.using) File "/usr/local/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 "/usr/local/lib/python3.8/site-packages/django/db/models/base.py", line 787, in save_base updated = self._save_table( File "/usr/local/lib/python3.8/site-packages/django/db/models/base.py", line 869, in _save_table updated = self._do_update(base_qs, using, pk_val, values, update_fields, File "/usr/local/lib/python3.8/site-packages/django/db/models/base.py", line 923, in _do_update return filtered._update(values) > 0 File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 803, in _update return query.get_compiler(self.db).execute_sql(CURSOR) File "/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1515, in execute_sql cursor = super().execute_sql(result_type) File "/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1154, in execute_sql cursor.execute(sql, params) File "/usr/local/lib/python3.8/site-packages/cacheops/transaction.py", line 93, in execute result = self._no_monkey.execute(self, sql, params) File "/usr/local/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 "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers return executor(sql, params, many, context) File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute return self.cursor.execute(sql, params) File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 90, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "/usr/local/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 '/tmp/dump.json': Could not load contenttypes.ContentType(pk=8): duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq" DETAIL: Key (app_label, model)=(admin, logentry) already exists. Would anyone have a clue where I should start looking for this? — Reply to this email directly, view it on GitHub<#10940>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAS7UM7PUWDEDWT5BPBULJLWIUP7FANCNFSM6AAAAAASCQVUII>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[SquirrelSloth]

Thank you Mark. Unfortunately we deployed the same version (2.9.11) on this second attempt (2nd to last bullet-point) and the above pasted error was from that attempt, so this eliminates any stepwise issues :(

Any other suggestions?

[mtinberg]

I think you should use upgrade.sh which calls manage.py migrate (among other essential manage.py tasks) to upgrade the _database_ on your new server (using pg_dump/restore tools to load the data and not manage.py dump/loaddata). — Mark Tinberg ***@***.***> Division of Information Technology-Network Services University of Wisconsin-Madison

…

________________________________ From: SquirrelSloth ***@***.***> Sent: Wednesday, November 16, 2022 3:00 PM To: netbox-community/netbox ***@***.***> Cc: Mark Tinberg ***@***.***>; Comment ***@***.***> Subject: Re: [netbox-community/netbox] Error trying to run manage.py loaddata > duplicate key value violates unique constraint (Discussion #10940) Thank you Mark. Unfortunately we deployed the same version (2.9.11) on this second attempt (2nd to last bullet-point) and the above pasted error was from that attempt, so this eliminates any stepwise issues :( Any other suggestions? — Reply to this email directly, view it on GitHub<#10940 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAS7UM6GE2RLDNCZVYL2ZITWIVDQXANCNFSM6AAAAAASCQVUII>. You are receiving this because you commented.Message ID: ***@***.***>

[SquirrelSloth]

Thank you Mark. We've tried the upgrade.sh multiple times previously and it never completed successfully. I'm hoping the error we're getting from above someone might know what table this may reference :(

django.db.utils.IntegrityError: Problem installing fixture '/tmp/dump.json': Could not load contenttypes.ContentType(pk=8): duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq"
DETAIL: Key (app_label, model)=(admin, logentry) already exists.

[markkuleinio]

What's the reason again why you are trying the jsondumps? Normal way to install a new NetBox server using the old data is to use pg_dump and psql to move the data. I, for one, have done is successfully countless times.

https://docs.netbox.dev/en/stable/administration/replicating-netbox/

[mtinberg]

https://beta-demo.netbox.dev/static/docs/installation/ https://beta-demo.netbox.dev/static/docs/installation/upgrading/ The error you are listing seems to be related to /tmp/dump.json which I am taking to be part of manage.py dumpdata/loaddata which is not the workflow to copy data between your prod netbox (2.9.11) and the new prod host. Drop the database in postgresql on the new host and reload it using sudo -u postgres /usr/bin/pg_dump on the 2.9.11 host and sudo -u postgres /usr/bin/pg_restore on the 2.11.12 host then run upgrade.sh to update the schema on the 2.11.12 host then install 3.3.8 on the new host and run upgrade.sh again to update the schema, you should then have a consistent database without foreign key errors in the current version of the db schema that can be used by the application. Look through the release notes for each major version between (3.0, 3.1, 3.2, ...) to see if there are any flags or changes you need to do by hand but you should be good-to-go. https://beta-demo.netbox.dev/static/docs/release-notes/ I don't think the manage.py dumpdata/loaddata process is guaranteed to rebuild the database foreign key relationships correctly and I can't imagine it would work right across versions of the database schema. — Mark Tinberg ***@***.***> Division of Information Technology-Network Services University of Wisconsin-Madison

…

________________________________ From: SquirrelSloth ***@***.***> Sent: Thursday, November 17, 2022 9:42 AM To: netbox-community/netbox ***@***.***> Cc: Mark Tinberg ***@***.***>; Comment ***@***.***> Subject: Re: [netbox-community/netbox] Error trying to run manage.py loaddata > duplicate key value violates unique constraint (Discussion #10940) Thank you Mark. We've tried the upgrade.sh multiple times previously and it never completed successfully. I'm hoping the error we're getting from above someone might know what table this may reference :( django.db.utils.IntegrityError: Problem installing fixture '/tmp/dump.json': Could not load contenttypes.ContentType(pk=8): duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq" DETAIL: Key (app_label, model)=(admin, logentry) already exists. — Reply to this email directly, view it on GitHub<#10940 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAS7UMY6WWPMGTOS45JTKSTWIZG6NANCNFSM6AAAAAASCQVUII>. You are receiving this because you commented.Message ID: ***@***.***>

[SquirrelSloth]

Thank you Markku and Mark,

We will try as you suggested using pg_dump and pg_restore and doing the step upgrades. The reason we had been trying the jsondumps was because even on our existing Netbox server we were never able to do upgrade.sh past 2.9.11 without it failing, I'm assuming that will still cause issues afterwards trying to upgrade the same database past 2.9.11 but I will keep you posted. Thank you again, I appreciate your help :)

[mtinberg]

Well, whatever (data integrity) issue was causing db upgrade to fail post 2.9.11 is something that should be fixed to allow upgrades to proceed, let us know how it goes. — Mark Tinberg ***@***.***> Division of Information Technology-Network Services University of Wisconsin-Madison

…

________________________________ From: SquirrelSloth ***@***.***> Sent: Thursday, November 17, 2022 10:41 AM To: netbox-community/netbox ***@***.***> Cc: Mark Tinberg ***@***.***>; Comment ***@***.***> Subject: Re: [netbox-community/netbox] Error trying to run manage.py loaddata > duplicate key value violates unique constraint (Discussion #10940) Thank you Markku and Mark, We will try as you suggested using pg_dump and pg_restore and doing the step upgrades. The reason we had been trying the jsondumps was because even on our existing Netbox server we were never able to do upgrade.sh past 2.9.11 without it failing, I'm assuming that will still cause issues afterwards trying to upgrade the same database past 2.9.11 but I will keep you posted. Thank you again, I appreciate your help :) — Reply to this email directly, view it on GitHub<#10940 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAS7UM35PO5U3GN4LWDE3QLWIZN2XANCNFSM6AAAAAASCQVUII>. You are receiving this because you commented.Message ID: ***@***.***>