Converting Database to UTF8

[troycarpenter]

Hopefully someone will find this useful.

In the upgrade from 4.1.11 to 4.2.0, I had a problem during the build where the error indicated my database had the wrong encoding. To fix it, I had to jump through some hoops. I will note that there are instructions online that will change the encoding of a database with a simple psql command, but I wanted to outline the proper way.

First, I followed instructions here: #14104 (reply in thread)
It discusses exporting/deleting/importing the database for netbox.

However, the creation part of those steps will just create a new database with the default encoding from a template from which new database are created and therefore will still be wrong.

I had to follow instructions I found here: https://stackoverflow.com/questions/16736891/pgerror-error-new-encoding-utf8-is-incompatible to get the database template to have the correct UNICODE encoding. Once the template was corrected, then the create database command created the database in UTF8 format and the installation continued.

To summarize the commands:
Stop Netbox

sudo systemctl stop netbox netbox-rq

Dump Database then get the postgres prompt:

pg_dump --username netbox --password --host localhost netbox > netbox.sql
sudo -u postgres psql

Issue these command in postgres. It makes the database template UNICODE and eventually re-creates the netbox database:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
DROP DATABASE template1;
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
\c template1
VACUUM FREEZE;
DROP DATABASE netbox;
CREATE DATABASE netbox ENCODING='UTF8';
GRANT ALL PRIVILEGES ON DATABASE netbox TO netbox;
\q

Finally, import the database:

sudo -u postgres psql netbox < netbox.sql

Doing this allowed the ./upgrade.sh script to complete. My installation is compiled from github, but if you have access to your database it should work for any installation method.

[troycarpenter]

Follow-up:
I just performed the same commands on a 4.1.11 installation. I simply stopped Netbox, followed the steps, then restarted Netbox after the database import without issues.

Before executing the procedure:

root@netbox:/opt/netbox# sudo -u postgres psql -d netbox -c 'SHOW SERVER_ENCODING'
 server_encoding 
-----------------
 SQL_ASCII
(1 row)

After:

root@netbox:/opt/netbox# sudo -u postgres psql -d netbox -c 'SHOW SERVER_ENCODING'
 server_encoding 
-----------------
 UTF8
(1 row)