Problem applying dcim.0197_natural_sort_collation when upgrading from 3.7.8 to 4.2

[jtradel]

The below sounds like it's related to the breaking collation change that came with 4.2, so I will shortly revert using my pre-upgrade snapshot and have a go at going only as far as 4.1 if I can't otherwise fix this.

Applying dcim.0197_natural_sort_collation...Traceback (most recent call last):
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
psycopg.errors.FeatureNotSupported: current database's encoding is not supported with this provider

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

Traceback (most recent call last):
  File "/opt/netbox-3.1/netbox/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/opt/netbox-3.1/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-3.1/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-3.1/venv/lib/python3.12/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/core/management/commands/migrate.py", line 357, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 255, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/contrib/postgres/operations.py", line 226, in database_forwards
    self.create_collation(schema_editor)
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/contrib/postgres/operations.py", line 200, in create_collation
    schema_editor.execute(
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/db/backends/postgresql/schema.py", line 48, in execute
    return super().execute(sql, None)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/db/backends/base/schema.py", line 202, in execute
    cursor.execute(sql, params)
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-3.1/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-3.1/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/opt/netbox-3.1/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-3.1/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox-3.1/venv/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
django.db.utils.NotSupportedError: current database's encoding is not supported with this provider

This is using a git release upgrade with Ubuntu 24.04.1, Postgresql 14, python 3.12.3.

My best reading of the django (which I'm not really familiar with) documentation is that, by default at least, it is much happier with all text in the database using UTF8. I can't see any indication that netbox overrides this, but did want to confirm that I'm not missing the point here somehow.

And, yes, the current server_encoding value for my netbox database is "SQL_ASCII".

[jtradel]

I can confirm that the upgrade from 3.7.8 to 4.1.11 works fine without any changes to the database.

It appears that a truly safe re-encoding from SQL_ASCII to UTF8 is a bit tricky, so I'll probably spin up a test container at some point to play with that.

[ravench]

FYI, I have the same issue upgrading from 4.1.9 to 4.2.0.