Table of Contents
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