Upgrade from v2.10.10 to v2.11.1

[Soeren-FT]

Hi Guys,

i currently get the following error when trying to upgrade to version v2.11.x when starting migration task:

Applying extras.0054_standardize_models...Traceback (most recent call last):
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.ObjectNotInPrerequisiteState: sequence must have same owner as table it is linked to

---

Traceback (most recent call last):
File "netbox/manage.py", line 10, in
execute_from_command_line(sys.argv)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/init.py", line 419, in execute_from_command_line
utility.execute()
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/init.py", line 413, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/base.py", line 354, in run_from_argv
self.execute(*args, **cmd_options)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute
output = self.handle(*args, **options)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/base.py", line 89, in wrapped
res = handle_func(*args, **kwargs)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 244, in handle
post_migrate_state = executor.migrate(
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 227, in apply_migration
state = migration.apply(state, schema_editor)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/migrations/migration.py", line 126, in apply
operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/migrations/operations/fields.py", line 244, in database_forwards
schema_editor.alter_field(from_model, from_field, to_field)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 594, in alter_field
self._alter_field(model, old_field, new_field, old_type, new_type,
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/postgresql/schema.py", line 196, in _alter_field
super()._alter_field(
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 780, in _alter_field
self.execute(sql, params)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 145, in execute
cursor.execute(sql, params)
File "/opt/netbox/venv/lib/python3.8/site-packages/cacheops/transaction.py", line 93, in execute
result = self._no_monkey.execute(self, sql, params)
File "/opt/netbox/venv/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 "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/opt/netbox/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
django.db.utils.OperationalError: sequence must have same owner as table it is linked to

Have someone an idea whats going wrong in my installation. My last update from v2.10.8 to v2.10.10 worked without problems.

Thanks in advanced,
Sören

[candlerb]

This looks similar to #6231, since both are due to permissions on a sequence, although it's a different migration (netbox/extras/migrations/0054_standardize_models.py vs netbox/ipam/migrations/0044_standardize_models.py). Unfortunately in your case, the error message doesn't name the offending table or sequence.

Can you get a postgres shell (typically sudo -u postgres psql netbox),then run \dt and \ds, and look at the results for the tables and sequences starting with extras. Here's what I see:

root@netbox:~# echo '\dt' | sudo -u postgres psql netbox | grep extras
could not change directory to "/root": Permission denied
 public | extras_configcontext                    | table | netbox
 public | extras_configcontext_cluster_groups     | table | netbox
 public | extras_configcontext_clusters           | table | netbox
 public | extras_configcontext_device_types       | table | netbox
 public | extras_configcontext_platforms          | table | netbox
 public | extras_configcontext_regions            | table | netbox
 public | extras_configcontext_roles              | table | netbox
 public | extras_configcontext_site_groups        | table | netbox
 public | extras_configcontext_sites              | table | netbox
 public | extras_configcontext_tags               | table | netbox
 public | extras_configcontext_tenant_groups      | table | netbox
 public | extras_configcontext_tenants            | table | netbox
 public | extras_customfield                      | table | netbox
 public | extras_customfield_content_types        | table | netbox
 public | extras_customlink                       | table | netbox
 public | extras_exporttemplate                   | table | netbox
 public | extras_imageattachment                  | table | netbox
 public | extras_jobresult                        | table | netbox
 public | extras_journalentry                     | table | netbox
 public | extras_objectchange                     | table | netbox
 public | extras_tag                              | table | netbox
 public | extras_taggeditem                       | table | netbox
 public | extras_webhook                          | table | netbox
 public | extras_webhook_content_types            | table | netbox
root@netbox:~# echo '\ds' | sudo -u postgres psql netbox | grep extras
could not change directory to "/root": Permission denied
 public | extras_configcontext_cluster_groups_id_seq     | sequence | netbox
 public | extras_configcontext_clusters_id_seq           | sequence | netbox
 public | extras_configcontext_device_types_id_seq       | sequence | netbox
 public | extras_configcontext_id_seq                    | sequence | netbox
 public | extras_configcontext_platforms_id_seq          | sequence | netbox
 public | extras_configcontext_regions_id_seq            | sequence | netbox
 public | extras_configcontext_roles_id_seq              | sequence | netbox
 public | extras_configcontext_site_groups_id_seq        | sequence | netbox
 public | extras_configcontext_sites_id_seq              | sequence | netbox
 public | extras_configcontext_tags_id_seq               | sequence | netbox
 public | extras_configcontext_tenant_groups_id_seq      | sequence | netbox
 public | extras_configcontext_tenants_id_seq            | sequence | netbox
 public | extras_customfield_id_seq                      | sequence | netbox
 public | extras_customfield_obj_type_id_seq             | sequence | netbox
 public | extras_customlink_id_seq                       | sequence | netbox
 public | extras_exporttemplate_id_seq                   | sequence | netbox
 public | extras_imageattachment_id_seq                  | sequence | netbox
 public | extras_jobresult_id_seq                        | sequence | netbox
 public | extras_journalentry_id_seq                     | sequence | netbox
 public | extras_objectchange_id_seq                     | sequence | netbox
 public | extras_tag_id_seq                              | sequence | netbox
 public | extras_taggeditem_id_seq                       | sequence | netbox
 public | extras_webhook_id_seq                          | sequence | netbox
 public | extras_webhook_obj_type_id_seq                 | sequence | netbox
root@netbox:~# 

I'm wondering if at some point in the past, on your system, a table or sequence was created with a different owner somehow?

[candlerb]

You could also try this query:

netbox=# select relnamespace::regnamespace as "schema", relname, relkind, relowner::regrole
         from pg_class where relname like 'extras_%';

If this does highlight the problem, then repeat without the extras_% constraint to see if there are any other tables/sequences with this issue. The solution may be some form of alter sequence as suggested here.

Please do report back what you see and what works, since you're not the only one to have hit this.

[candlerb]

Ah. It looks to me like in the past, Netbox was connecting as the "postgres" user when migrations were run, and then configuration.py was changed to connect as the "netboxdbuser", so tables created later were owned by that.

I think it should be possible to fix this using "ALTER TABLE ... OWNER TO ..." and/or "ALTER SEQUENCE ... OWNER TO ...", but I've not done this before.

Definitely take a pg_dump backup before playing with that. (Hopefully you took a backup prior to the upgrade attempt anyway).

[Soeren-FT]

i will try the migration of the owner and will post another update.

Of course there are backups, as well as the periodical one before each update.

[candlerb]

See also this stackoverflow question - in particular the third answer, with 236 upvotes (I can't see how to link directly to it)

Alternatively, you could take your pg_dump file, make the necessary changes by hand to the "OWNER TO" clauses, then restore from it.

root@netbox:~# zgrep "OWNER TO" /var/backups/netbox/netbox.202104220625.gz  | head
ALTER TABLE public.auth_group OWNER TO netbox;
ALTER TABLE public.auth_group_id_seq OWNER TO netbox;
ALTER TABLE public.auth_group_permissions OWNER TO netbox;
ALTER TABLE public.auth_group_permissions_id_seq OWNER TO netbox;
ALTER TABLE public.auth_permission OWNER TO netbox;
ALTER TABLE public.auth_permission_id_seq OWNER TO netbox;
ALTER TABLE public.auth_user OWNER TO netbox;
ALTER TABLE public.auth_user_groups OWNER TO netbox;
ALTER TABLE public.auth_user_groups_id_seq OWNER TO netbox;
ALTER TABLE public.auth_user_id_seq OWNER TO netbox;

[candlerb]

Even simpler, pg_dump can export just the schema, so:

su -c 'pg_dump -s netbox' postgres | grep '^ALTER.*OWNER TO' | sed 's/OWNER TO .*;/OWNER TO netboxdbuser;/'

Inspect the output and if it looks reasonable, pipe it back into psql.

That doesn't do anything with sequences, but that may not be necessary (AFAICS, sequences are owned by a table)

[Soeren-FT]

It works great. The sequences have been updated too.

The update to version 2.11.1 has now worked without problems.

Thanks for the great fast assistance!