starred/netbox
1
0
Fork 0
mirror of https://github.com/netbox-community/netbox.git synced 2026-01-11 21:10:29 +01:00
Code Issues 273 Packages Projects Releases 10 Wiki Activity

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

New Issue
Closed
opened 2025-12-29 21:34:24 +01:00 by adam · 5 comments
adam commented 2025-12-29 21:34:24 +01:00
Owner
Copy Link

Originally created by @litebito on GitHub (Jan 16, 2025).

Originally assigned to: @bctiemann on GitHub.

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?) https://github.com/netbox-community/netbox/issues/5760
But I could not find it in the documentation: https://netboxlabs.com/docs/netbox/en/stable/installation/1-postgresql/

Image
Originally created by @litebito on GitHub (Jan 16, 2025). Originally assigned to: @bctiemann on GitHub. ### 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?) https://github.com/netbox-community/netbox/issues/5760 But I could not find it in the documentation: https://netboxlabs.com/docs/netbox/en/stable/installation/1-postgresql/ <img width="359" alt="Image" src="https://github.com/user-attachments/assets/007ddda6-c087-47d8-946e-4aa8b07b8c3d" />
adam added the status: acceptedtype: documentationseverity: low labels 2025-12-29 21:34:24 +01:00
adam closed this issue 2025-12-29 21:34:24 +01:00
adam commented 2025-12-29 21:34:25 +01:00
Author
Owner
Copy Link

@peteeckel commented on GitHub (Jan 18, 2025):

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.

@peteeckel commented on GitHub (Jan 18, 2025): 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.
adam commented 2025-12-29 21:34:26 +01:00
Author
Owner
Copy Link

@dBitech commented on GitHub (Jan 18, 2025):

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

@dBitech commented on GitHub (Jan 18, 2025): Some distros (ahem Debian) have SQL_ASCII as the default encoding. Providing documentation clarity is not a bad thing.
adam commented 2025-12-29 21:34:26 +01:00
Author
Owner
Copy Link

@litebito commented on GitHub (Jan 18, 2025):

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 commented on GitHub (Jan 18, 2025): 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...
adam commented 2025-12-29 21:34:26 +01:00
Author
Owner
Copy Link

@litebito commented on GitHub (Jan 20, 2025):

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)

@litebito commented on GitHub (Jan 20, 2025): 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)
adam commented 2025-12-29 21:34:26 +01:00
Author
Owner
Copy Link

@deanfourie1 commented on GitHub (Feb 24, 2025):

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)

Dude thanks so much this saved me! Legend! Thanks for sharing.

@deanfourie1 commented on GitHub (Feb 24, 2025): > 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) > Dude thanks so much this saved me! Legend! Thanks for sharing.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
beta
breaking change
complexity: high
complexity: low
complexity: medium
needs milestone
netbox
pending closure
plugin candidate
pull-request
Mirrored from GitHub Pull Request
 severity: high
severity: low
severity: medium
status: accepted
status: backlog
status: blocked
status: duplicate
status: needs owner
status: needs triage
status: revisions needed
status: under review
topic: GraphQL
topic: Internationalization
topic: OpenAPI
topic: UI/UX
topic: cabling
topic: event rules
topic: htmx navigation
topic: industrialization
topic: migrations
topic: plugins
topic: scripts
topic: templating
topic: testing
type: bug
type: deprecation
type: documentation
type: feature
type: housekeeping
type: translation
No Label severity: low status: accepted type: documentation
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/netbox#10662
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?