relation "extras_customfield_object_types_id_seq" already exists

[SwaroopGiri]

Deployment Type

Self-hosted

NetBox Version

v3.7.8

Python Version

3.12

Steps to Reproduce

NEWVER=4.0.5
wget https://github.com/netbox-community/netbox/archive/v$NEWVER.tar.gz
sudo tar -xzf v$NEWVER.tar.gz -C /opt
sudo ln -sfn /opt/netbox-$NEWVER/ /opt/netbox

OLDVER=3.7.8
sudo cp /opt/netbox-$OLDVER/local_requirements.txt /opt/netbox/
sudo cp /opt/netbox-$OLDVER/netbox/netbox/configuration.py /opt/netbox/netbox/netbox/
sudo cp /opt/netbox-$OLDVER/netbox/netbox/ldap_config.py /opt/netbox/netbox/netbox/

sudo cp -pr /opt/netbox-$OLDVER/netbox/media/ /opt/netbox/netbox/

sudo cp -r /opt/netbox-$OLDVER/netbox/scripts /opt/netbox/netbox/
sudo cp -r /opt/netbox-$OLDVER/netbox/reports /opt/netbox/netbox/

sudo cp /opt/netbox-$OLDVER/gunicorn.py /opt/netbox/

sudo /opt/netbox/upgrade.sh

Expected Behavior

Upgrade complete! Don't forget to restart the NetBox services:

sudo systemctl restart netbox netbox-rq

Observed Behavior

Operations to perform:
  Apply all migrations: account, auth, circuits, contenttypes, core, dcim, django_rq, extras, ipam, sessions, social_django, taggit, tenancy, users, virtualization, vpn, wireless
Running migrations:
  Applying dcim.0186_location_facility... OK
  Applying dcim.0187_alter_device_vc_position... OK
  Applying extras.0108_convert_reports_to_scripts... OK
  Applying extras.0109_script_model... OK
  Applying extras.0110_remove_eventrule_action_parameters... OK
  Applying extras.0111_rename_content_types...Traceback (most recent call last):
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.DuplicateTable: relation "extras_customfield_object_types_id_seq" already exists

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

Traceback (most recent call last):
  File "/opt/netbox-4.0.5/netbox/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/core/management/base.py", line 413, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/migrations/operations/special.py", line 106, in database_forwards
    self._run_sql(schema_editor, self.sql)
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/migrations/operations/special.py", line 133, in _run_sql
    schema_editor.execute(statement, params=None)
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/backends/base/schema.py", line 202, in execute
    cursor.execute(sql, params)
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-4.0.5/venv/lib/python3.12/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: relation "extras_customfield_object_types_id_seq" already exists

[SwaroopGiri]

After spending a few days on this, I checked 0111_rename_content_types.py file and saw that the migration tries to rename relations twice to the same name which leads to duplicate error. After commenting below code in "0111_rename_content_types.py" The upgrade went through but I'm unsure if this is the correct solution.

 # Pre-v2.10 sequence name (see #15605)
       # migrations.RunSQL(
       #     "ALTER TABLE IF EXISTS extras_customfield_obj_type_id_seq RENAME TO extras_customfield_object_types_id_seq"
       # ),

The upgrade path I followed was v3.2.1 --> v3.7.8 --> v4.0.5

[arthanson]

Looks like a potential edge case from older installs, I'm guessing there should probably be a check if extras_customfield_object_types_id_seq already exists then skip the second ALTER TABLE in the below code from 0111_rename_content_types.py

        migrations.RunSQL(
            "ALTER TABLE IF EXISTS extras_customfield_content_types_id_seq RENAME TO extras_customfield_object_types_id_seq"
        ),
        # Pre-v2.10 sequence name (see #15605)
        migrations.RunSQL(
            "ALTER TABLE IF EXISTS extras_customfield_obj_type_id_seq RENAME TO extras_customfield_object_types_id_seq"
        ),

[bbruun]

Is there a fix for this yet - for the docker version?
The netbox process exit's when hitting this in v4.0-2.9.0 or v4.0-2.9.1 when upgrading from v3.7-2.8.0 from an old upgrade starting from v2.10.8.

The first ALTER TABLE command works on the database but the netbox DB migrations runs the second one.

[arthanson]

@SwaroopGiri @bbruun can either/both of you please test Draft PR #16893 and see if that resolves the issue.

[arthanson]

Closing due to lack of any response and an edge case.

[bbruun]

I didn't get any notification for some reason on Jul 12, don't know why, so I'm very sorry for the late response.

I'm running the docker version and I've played around with your update in
#16893 and found that it is the "Pre-v2.10" that is the issue", which in my case might the the root cause as I don't know if any <2.10 version was running.
I had to comment out the "Pre-v2.10" part e.g.:

        migrations.RunSQL(
            "ALTER TABLE IF EXISTS extras_customfield_content_types_id_seq RENAME TO extras_customfield_object_types_id_seq"
        ),
        # Pre-v2.10 sequence name (see #15605)
        #migrations.RunSQL(
        #    "ALTER TABLE IF EXISTS extras_customfield_obj_type_id_seq RENAME TO extras_customfield_object_types_id_seq"
        #),

My solution to fix it was to make a local image using the 0111_rename_content_types.py migrations file v4.0.1-2.9.1 and then update as above and build it manually to get the migrations done before moving on to v4.0.9-2.9.1 in the upgrade process to get to v4.1.2-3.0.2.
Dockerfile used is this:

FROM docker.io/netboxcommunity/netbox:v4.0.1-2.9.1

COPY 0111_rename_content_types.py /opt/netbox/netbox/extras/migrations/

So I'm able to go from old v2.10.8 to v2.11 -> v3.0.0 -> v3.2-2.0.0 -> v3.2.9-2.1.0 -> v3.7.8-2.8.0 -> v4.0.1-2.9.1-custom -> v4.0.9-2.9.1 -> v4.1-3.0.1 -> v4.1.2-3.0.2.
However from 2.10.8 to 2.11 I had to do the following before v2.11 upgrade, which I'm unsure is because of some failed update attempts back in the past or a bug. But while 2.10.8 is running then I had to do this (all tables were empty by the way, so I suspect failed update attempt:

docker exec -it postgres psql -U netbox -d netboxdb -c 'drop table dcim_location;'
docker exec -it postgres psql -U netbox -d netboxdb -c 'drop table ipam_iprange;'
docker exec -it postgres psql -U netbox -d netboxdb -c 'drop table dcim_sitegroup CASCADE;'
docker exec -it postgres psql -U netbox -d netboxdb -c 'drop table extras_configcontext_site_groups;'
docker exec -it postgres psql -U netbox -d netboxdb -c 'drop table extras_configcontext_device_types;'
docker exec -it postgres psql -U netbox -d netboxdb -c 'drop table extras_journalentry;'
docker exec -it postgres psql -U netbox -d netboxdb -c 'drop table circuits_providernetwork;'

I now have a fully 4.1.2 version running and no errors on any of the steps in the upgrade process.

Thanks for #16893 @arthanson it is much appreciated.