Update PostgreSQL Datenbank Zammad von SQL_ASCII zu UTF8

Grobes vorgehen in 5 Schritten

• zabbix Datenbank sichern und löschen
• Standard Template1 anpassen
• Datenbank anlegen
• Datenbank zabbix rücksichern
• zabbix Konfiguration anpassen (wenn nötig)

Aktueller Stand Datenbank und Template1

root@zabbix:~# systemctl stop zabbix-server.service
root@zabbix:~# cd /tmp
root@zabbix:/tmp# su postgres
postgres@zabbix:/tmp$ psql
psql (15.3 (Debian 15.3-0+deb12u1))
Type "help" for help.
 
postgres=# SHOW SERVER_ENCODING;
 server_encoding 
-----------------
  SQL_ASCII
(1 row)
 
postgres=# \c zabbix
You are now connected to database "zabbix" as user "postgres".
zabbix=# SHOW SERVER_ENCODING;
 server_encoding 
-----------------
  SQL_ASCII
(1 row)
 
zabbix=#\q

Sichern und löschen der vorhandenen zabbix Datenbank

postgres@zabbix:/tmp$ pg_dump zabbix > zabbix.dump
​postgres@zabbix:/tmp$ dropdb zabbix

Update template1

​postgres@zabbix:/tmp$ psql
psql (15.3 (Debian 15.3-0+deb12u1))
Type "help" for help.
 
postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
postgres=# DROP DATABASE template1;
postgres=# CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8';
postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
postgres=# \c template1;
You are now connected to database "template1" as user "postgres".
template1=# VACUUM FREEZE;
template1=# \q
postgres@zabbix:/tmp$

Datenbank Zabbix wieder anlegen

postgres@zabbix:/tmp$ createdb -E utf8 zabbix
postgres@zabbix:/tmp$ psql zabbix < zabbix.dump
postgres@zabbix:/tmp$ exit
root@zabbix:~# systemctl restart zabbix-server.service

Hatte man im Backend noch die Systeminformation „Database history tables upgraded: No. Support for the old numeric type is deprecated. Please upgrade to numeric values of extended range“, kann man dies auch gleich mit erledigen. Das Update Script gibt es im Zabbix Git repository.

Script anlegen oder herunterladen

root@zabbix:/tmp# vim zabbixpatch

ALTER TABLE trends
	ALTER COLUMN value_min TYPE DOUBLE PRECISION,
	ALTER COLUMN value_min SET DEFAULT '0.0000',
	ALTER COLUMN value_avg TYPE DOUBLE PRECISION,
	ALTER COLUMN value_avg SET DEFAULT '0.0000',
	ALTER COLUMN value_max TYPE DOUBLE PRECISION,
	ALTER COLUMN value_max SET DEFAULT '0.0000';
ALTER TABLE history
	ALTER COLUMN value TYPE DOUBLE PRECISION,
	ALTER COLUMN value SET DEFAULT '0.0000';

Patch auf die Datenbank anwenden

root@zabbix:/tmp# systemctl stop zabbix-server.service
root@zabbix:/tmp# su postgres                          
postgres@zabbix:/tmp$ psql zabbix < zabbixpatch
ALTER TABLE
ALTER TABLE
postgres@zabbix:/tmp$ exit
root@zabbix:/tmp# nano /etc/zabbix/web/zabbix.conf.php

Darin die Variable $DB['DOUBLE_IEEE754'] ändern in true oder wenn nicht vorhanden hinzufügen

// Use IEEE754 compatible value range for 64-bit Numeric (float) history values.
// This option is enabled by default for new Zabbix installations.
// For upgraded installations, please read database upgrade notes before enabling this option.
// $DB['DOUBLE_IEEE754']           = false;
$DB['DOUBLE_IEEE754']           = true;

Nochmal alles prüfen

root@zabbix:/tmp# su postgres
postgres@zabbix:/tmp$ psql
psql (15.3 (Debian 15.3-0+deb12u1))
Type "help" for help.
 
postgres=# \c zabbix
You are now connected to database "zabbix" as user "postgres".
zabbix=# SHOW SERVER_ENCODING;
 server_encoding 
-----------------
 UTF8
(1 row)
 
zabbix=#\q

Dienste wieder Starten und fertig.

postgres@zabbix:/tmp$ exit
exit
root@zabbix:/tmp# systemctl restart zabbix-server.service
root@zabbix:/tmp#