Upgrade from 4.1.7 to 4.2.1 fails with current database's encoding is not supported with this provider

[litebito]

Deployment Type

Self-hosted

Triage priority

N/A

NetBox Version

v4.1.7

Python Version

3.12

Steps to Reproduce

1. sudo PYTHON=/usr/bin/python3.12 /opt/netbox/upgrade.sh

Expected Behavior

Upgrade process completed

Observed Behavior

Upgrade process fails with the error below:

Applying database migrations (python3 netbox/manage.py migrate)...
Operations to perform:
  Apply all migrations: account, auth, circuits, contenttypes, core, dcim, django_rq, extras, ipam, netbox_device_view, netbox_documents, netbox_floorplan, netbox_inventory, netbox_topology_plugin, netbox_topology_views, sessions, social_django, taggit, tenancy, users, virtualization, vpn, wireless
Running migrations:
  Applying dcim.0197_natural_sort_collation...Traceback (most recent call last):
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/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/netbox/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/opt/netbox/venv/lib64/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/venv/lib64/python3.12/site-packages/django/core/management/base.py", line 413, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/core/management/commands/migrate.py", line 357, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/db/migrations/executor.py", line 255, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/contrib/postgres/operations.py", line 226, in database_forwards
    self.create_collation(schema_editor)
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/contrib/postgres/operations.py", line 200, in create_collation
    schema_editor.execute(
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/db/backends/postgresql/schema.py", line 48, in execute
    return super().execute(sql, None)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/db/backends/base/schema.py", line 202, in execute
    cursor.execute(sql, params)
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/python3.12/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/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/venv/lib64/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib64/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

checking some basic things:

sudo -u postgres psql netbox
psql (17.2)
Type "help" for help.

netbox=# SHOW SERVER_ENCODING;
 server_encoding
-----------------
 SQL_ASCII
(1 row)

netbox=#  SHOW CLIENT_ENCODING;
 client_encoding
-----------------
 UTF8
(1 row)

netbox=#

(I never had issues with this database before)

in configuration.py

DATABASE = {
    'ENGINE': 'django.db.backends.postgresql',  # Database engine
    'NAME': 'netbox',         # Database name
    'USER': 'netbox',               # PostgreSQL username
    'PASSWORD': 'HIDDEN',           # PostgreSQL password
    'HOST': 'localhost',      # Database server
    'PORT': '',               # Database port (leave blank for default)
    'CONN_MAX_AGE': 300,      # Max database connection age
}

sudo -u postgres psql --list
[sudo] password for a436532:
                                                 List of databases
   Name    |  Owner   | Encoding  | Locale Provider | Collate | Ctype | Locale | ICU Rules |   Access pr
ivileges
-----------+----------+-----------+-----------------+---------+-------+--------+-----------+------------
-----------
 netbox    | netbox   | SQL_ASCII | libc            | C       | C     |        |           |
 postgres  | postgres | SQL_ASCII | libc            | C       | C     |        |           |
 template0 | postgres | SQL_ASCII | libc            | C       | C     |        |           | =c/postgres
          +
           |          |           |                 |         |       |        |           | postgres=CT
c/postgres
 template1 | postgres | SQL_ASCII | libc            | C       | C     |        |           | =c/postgres
          +
           |          |           |                 |         |       |        |           | postgres=CT
c/postgres
(4 rows)

Investigating further, it seems that the lack of mentioning the requirement of UTF8 for the database in the installation docs is still missing, although it was reported in the past (and set to resolved?) #5760
But I could not find it in the documentation: https://netboxlabs.com/docs/netbox/en/stable/installation/1-postgresql/

[peteeckel]

You should never, never, NEVER use SQL_ASCII. Did I say never?

https://wiki.postgresql.org/wiki/Don%27t_Do_This

Unfortunately the NetBox installation guide does not explicitly mention it (because PostgreSQL's default encoding is fine), but don't.

[dBitech]

Some distros (ahem Debian) have SQL_ASCII as the default encoding. Providing documentation clarity is not a bad thing.

[litebito]

I installed Postgres from the Postgres repo on CentOS Stream 9
(yes, I know on the Postgres website is the statement that CentOS Stream is no longer supported, I only found that out months after installing)
So that may have been the culprit.
Maybe something can be added to the documentation for users to check before creating the netbox database in postgres. For example, check that Postgres is initialized with UTF8 (or whatever is applicable) by using "sudo -u postgres psql --list" and verifying that template1 is set to the correct encoding, collation etc...

[litebito]

For those who are in the same situation, this is how I fixed it (and so far, no issues popped-up afterwards)
(This worked in my situation, no guarantees)

1/ Before starting this: shutdown netbox services

sudo systemctl restart netbox netbox-rq

2/ Dump the netbox db

sudo pg_dump netbox > dump_netboxdb_before_locale_change.sql

3/ kill all remaining connections to the netbox database

sudo -u postgres psql

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'netbox' AND pid <> pg_backend_pid();

4/ change the template1 database to your preferred encoding/collation/type:
(the CREATE DATABASE command uses template1 as a template if not additional parameters are added in the command)

ALTER database template1 is_template=false;

DROP database template1;

CREATE DATABASE template1
WITH OWNER = postgres
   ENCODING = 'UTF8'
   TABLESPACE = pg_default
   LC_COLLATE = 'en_US.UTF-8'
   LC_CTYPE = 'en_US.UTF-8'
   CONNECTION LIMIT = -1
   TEMPLATE template0;

ALTER database template1 is_template=true;

5/ Drop the netbox db

DROP DATABASE netbox;

6/ Create the new netbox db

CREATE DATABASE netbox;
ALTER DATABASE netbox OWNER TO netbox;
\connect netbox;
GRANT CREATE ON SCHEMA public TO netbox;
exit

7/ Verify the new database is created with the correct encoding/collation

sudo -u postgres psql --list

     Name      |  Owner   | Encoding  | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges
---------------+----------+-----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 netbox        | netbox   | UTF8      | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
 netbox_old_db | netbox   | SQL_ASCII | libc            | C           | C           |        |           |

8/ import the netbox data (same steps as restoring netbox)

sudo -u postgres psql netbox < dump_netboxdb_before_locale_change.sql

9/ Restart netbox

sudo systemctl restart netbox netbox-rq
sudo systemctl status netbox netbox-rq

10/ Logon to netbox and validate the data is there (you may have to wait a minute or two after restarting)