Change default encoding to UTF-8

References:

1. https://www.endpoint.com/blog/2017/07/21/postgres-migrating-sqlascii-to-utf-8
2. https://www.shubhamdipt.com/blog/how-to-change-postgresql-database-encoding-to-utf8/

NOTE: Never perform this work on a database that is being written to. For my example a DB used by a very busy instance of Zabbix.

root@zabdb:~ # psql -U zabbix zabbix
psql (11.9)
Type "help" for help.

zabbix=> SHOW SERVER_ENCODING;
 server_encoding 
-----------------
 SQL_ASCII
(1 row)

zabbix=> \c zabbix
You are now connected to database "zabbix" as user "zabbix".
zabbix=> SHOW SERVER_ENCODING;
 server_encoding 
-----------------
 SQL_ASCII
(1 row)

pg_dump -U zabbix zabbix > dump.sql​

dropdb -U zabbix zabbix

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
DROP DATABASE template1;
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8';
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

\c template1;
You are now connected to database "template1" as user "postgres".

VACUUM FREEZE;

createdb -U zabbix -E utf8 zabbix

NOTE: We are going to use a Perl module named Encoding::FixLatin Make sure this is installed on the machine you about to attempt this next step on.

fix_latin NOT DONE YET!!!

psql -U zabbix zabbix < dump_cleaned.sql