Zabbix 4.0 to 5.0 migration, PostgreSQL, enable TimescaleDB

Because of preprocessing being moved at the side of zabbix proxy we can not keep cache at the proxy this time. Must upgrade proxy servers in paralel, this is classical sequence on proxies:

rpm -Uvh https://repo.zabbix.com/zabbix/5.0/rhel/7/x86_64/zabbix-release-5.0-1.el7.noarch.rpm
yum clean all
yum -y upgrade 'zabbix-*'
# it will automatically stop and start the proxy component.

Process starts by renaming all historical tables to "old". this is required to not interfer much with the monitoring tool, not lock tables. This can be done on the fly with a running instance. It is suposed to create database table partitions for new tables automatically.

ALTER TABLE history_uint RENAME TO history_uint_old; CREATE TABLE history_uint (LIKE history_uint_old INCLUDING ALL);
ALTER TABLE history_log RENAME TO history_log_old; CREATE TABLE history_log (LIKE history_log_old INCLUDING ALL);
ALTER TABLE history_str RENAME TO history_str_old; CREATE TABLE history_str (LIKE history_str_old INCLUDING ALL);
ALTER TABLE history_text RENAME TO history_text_old; CREATE TABLE history_text (LIKE history_text_old INCLUDING ALL);
ALTER TABLE trends_uint RENAME TO trends_uint_old; CREATE TABLE trends_uint (LIKE trends_uint_old INCLUDING ALL);
ALTER TABLE trends RENAME TO trends_old; CREATE TABLE trends (LIKE trends_old INCLUDING ALL);
ALTER TABLE history RENAME TO history_old; CREATE TABLE history (LIKE history_old INCLUDING ALL);

At this point we must backup "old" tables. Since the tables are huge, consider to perform task in batches. Consider using this technique:

time psql z40 -c "COPY (
SELECT * FROM history_uint_old
WHERE clock >= EXTRACT(EPOCH FROM (TIMESTAMP '2021-02-01 00:00:00'))
AND clock < EXTRACT(EPOCH FROM (TIMESTAMP '2021-03-01 00:00:00'))
) TO STDOUT;" | pigz --best > history_uint.202102.raw.gz

Bash loop:

echo "
history_str_old
history_log_old
history_text_old
trends_uint_old
trends_old
history_old
history_uint_old
" |
grep -v "^$" | \
while IFS= read -r TABLE
do {

# if a file is there this is a clear indication 
# this month has been backed up successfully already
# otherwise backup this table
[ -z $TABLE.raw.gz ] && time psql z40 -c "COPY (
SELECT * FROM $TABLE
) TO STDOUT;" | pigz --best > in.progress.raw.gz && \
mv in.progress.raw.gz $TABLE.raw.gz
# the 'mv' comand will be execute only if the 'psql' command report status code 0
# this is nice way to track which table is fully completed

} done

Always perform the backup only through 'screen' utility! Once the data has backed up we must drop the "old" tables so database is tiny:

DROP TABLE history_uint_old;
DROP TABLE history_old;
DROP TABLE history_log_old;
DROP TABLE history_str_old;
DROP TABLE history_text_old;
DROP TABLE trends_uint_old;
DROP TABLE trends_old;

Delete internal,discovery,autoregistration events:

DELETE FROM events WHERE source > 0 AND clock IN (SELECT clock FROM events WHERE source > 0 LIMIT 1 OFFSET 0);
DELETE FROM events WHERE source > 0 AND clock IN (SELECT clock FROM events WHERE source > 0 LIMIT 10 OFFSET 0);
DELETE FROM events WHERE source > 0 AND clock IN (SELECT clock FROM events WHERE source > 0 LIMIT 100 OFFSET 0);

--check some data inconistency here. stock count of tables in 4.0. orphaned data. Shut down backend process. Create a full database backup containing all data. 'custom' format is the most flexible it works together with pg_restore utility:

pg_dump \
--dbname=z40 \
--file=zabbix40.dump \
--format=custom \
--blobs \
--verbose

Upgrade database structure to 5.0. In server where service 'zabbix-server' runs, upgrade zabbix repository:

Start upgrade process:

systemctl start zabbix-server

Follow log:

tail -99f /var/log/zabbix/zabbix_server.log

Create back 'old' tables which does not have partitions:

cat pg_create_only_history_trends.sql | psql --user=zabbix z40b

Make sure the owner is 'zabbix':

ALTER TABLE history_old OWNER TO zabbix;
ALTER TABLE history_uint_old OWNER TO zabbix;
ALTER TABLE history_log_old OWNER TO zabbix;
ALTER TABLE history_text_old OWNER TO zabbix;
ALTER TABLE history_str_old OWNER TO zabbix;
ALTER TABLE trends_old OWNER TO zabbix;
ALTER TABLE trends_uint_old OWNER TO zabbix;

Enable timescale DB for 'old' tables:

cat timescaledb4old.sql | psql --user=zabbix z40b

Reset owner:

ALTER TABLE history OWNER TO zabbix;
ALTER TABLE history_uint OWNER TO zabbix;
ALTER TABLE history_log OWNER TO zabbix;
ALTER TABLE history_text OWNER TO zabbix;
ALTER TABLE history_str OWNER TO zabbix;
ALTER TABLE trends OWNER TO zabbix;
ALTER TABLE trends_uint OWNER TO zabbix;

Later enter 'screen' mode and restore with:

ls -1 *_old.raw.gz | sed "s|.raw.gz||" | \
while IFS= read -r TABLE
do {
time zcat $TABLE.raw.gz | psql -c "COPY $TABLE FROM STDIN;" z40b
} done

It will do inserts in background, will insert data right into hypertables When 'old' data has been delivered, make the old table as original.

ALTER TABLE history_uint RENAME TO history_uint_tmp;
ALTER TABLE history_uint_old RENAME TO history_uint;
--be sure that we have enough free storage space to dublicate few days
INSERT INTO history_uint SELECT * FROM history_uint_tmp;
--check recent graphs which contains integers, those should be completed now

ALTER TABLE history RENAME TO history_tmp;
ALTER TABLE history_old RENAME TO history;
INSERT INTO history SELECT * FROM history_tmp;
--check recent graphs with floats. those should be comleted now.

ALTER TABLE trends_uint RENAME TO trends_uint_tmp;
ALTER TABLE trends_uint_old RENAME TO trends_uint;
INSERT INTO trends_uint SELECT * FROM trends_uint_tmp;

ALTER TABLE trends RENAME TO trends_tmp;
ALTER TABLE trends_old RENAME TO trends;
INSERT INTO trends SELECT * FROM trends_tmp;

ALTER TABLE history_str RENAME TO history_str_tmp;
ALTER TABLE history_str_old RENAME TO history_str;
INSERT INTO history_str SELECT * FROM history_str_tmp;

ALTER TABLE history_log RENAME TO history_log_tmp;
ALTER TABLE history_log_old RENAME TO history_log;
INSERT INTO history_log SELECT * FROM history_log_tmp;

ALTER TABLE history_text RENAME TO history_text_tmp;
ALTER TABLE history_text_old RENAME TO history_text;
INSERT INTO history_text SELECT * FROM history_text_tmp;

If graphs are completed, then drop 'tmp' tables:

DROP TABLE history_uint_tmp;
DROP TABLE history_tmp;
DROP TABLE trends_uint_tmp;
DROP TABLE trends_tmp;
DROP TABLE history_str_tmp;
DROP TABLE history_log_tmp;
DROP TABLE history_text_tmp;

Appendinx. Restore backup:

pg_restore \
--dbname=z40b \
zabbix40.dump