Give explicit encoding for netbox database?

[candlerb]

Change Type

[ ] Addition
[X] Correction
[ ] Deprecation
[ ] Cleanup (formatting, typos, etc.)

Area

[X] Installation instructions
[ ] Configuration parameters
[ ] Functionality/features
[ ] REST API
[ ] Administration/development
[ ] Other

Proposed Changes

Netbox relies on the postgres database having UTF8 encoding, but the installation instructions don't explicitly set this. For most users this is fine, if the template1 database has UTF8 encoding, but there may be issues if template1 uses SQL_ASCII or something else - see this thread.

Would it be better to change the instructions to give explicit create database settings?

postgres=# CREATE DATABASE netbox ENCODING 'UTF8' LC_COLLATE='C.UTF-8' LC_CTYPE='C.UTF-8';

[lewisbergman]

My insatllation was a default Ubuntu 20.10 with Postgres installed as per the netbox instructions vis a vi
sudo apt install -y postgresql libpq-dev
In fact, I chose the latest Ubuntu as this container is dedicated to netbox and the docs suggested the release just prior (20.04). 20.10 has some security patches so I went with it. Maybe a big mistake as I could have just run apt-get update && apt-get dist-upgrade on a 20.04 install and probably avoided my whole issue assuming that my locale was properly set in 20.04. It was not, for some reason, in 20.10. The changelog for Ubuntu 20.10 makes no mention of any Postgres changes.

[candlerb]

20.04 is a much better choice anyway as it's a Long Term Support (LTS) release, supported until April 2025. 20.10 is only supported for 9 months from release; it will be end-of-life this July.

[lewisbergman]

I think all in all the best route is to start with the LTS, but I'll do a do-release-upgrade every so often until the next LTS arrives.

[candlerb]

I just compared Ubuntu 20.04 vs 20.10, using lxd images, and they both work the same (giving me UTF8).

But I have another theory: that Debian/Ubuntu set the template0 and template1 encoding from the default system locale. Testing this out:

$ lxc launch ubuntu:20.04 ubuntu-20-04
$ lxc exec ubuntu-20-04 bash
# dpkg-reconfigure locales   # I enabled only `zu_ZA ISO-8859-1`, and also selected `zu_ZA` as system default
Generating locales (this might take a while)...
  en_US.UTF-8... done
  zu_ZA.ISO-8859-1... done
Generation complete.
# apt-get update; apt-get -y install postgresql
...
# sudo -u postgres psql --list
could not change directory to "/root": Permission denied
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | LATIN1   | zu_ZA   | zu_ZA |
 template0 | postgres | LATIN1   | zu_ZA   | zu_ZA | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | LATIN1   | zu_ZA   | zu_ZA | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

Aha, there you have it. And I find them here too:

root@ubuntu-20-04:~# grep -1R zu.ZA /etc/postgresql/12/main/
/etc/postgresql/12/main/postgresql.conf-# These settings are initialized by initdb, but they can be changed.
/etc/postgresql/12/main/postgresql.conf:lc_messages = 'zu_ZA'			# locale for system error message
/etc/postgresql/12/main/postgresql.conf-					# strings
/etc/postgresql/12/main/postgresql.conf:lc_monetary = 'zu_ZA'			# locale for monetary formatting
/etc/postgresql/12/main/postgresql.conf:lc_numeric = 'zu_ZA'			# locale for number formatting
/etc/postgresql/12/main/postgresql.conf:lc_time = 'zu_ZA'				# locale for time formatting
/etc/postgresql/12/main/postgresql.conf-

According to the comments, it's initdb which initializes postgres and picks up these settings.