Change default encoding to UTF-8

References:

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.

Verify current server encoding

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)

Backup your Database

pg_dump -U zabbix zabbix > dump.sql​

Drop Database

dropdb -U zabbix zabbix

Update template1 encoding to UTF8

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;

Create your DB with the new encoding type

createdb -U zabbix -E utf8 zabbix

Clean dump file data

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!!!

Restore data

psql -U zabbix zabbix < dump_cleaned.sql