Distributed/paralelized TSDB on PostgreSQL/TimescaleDB

There are one-week-old news from Timescale about their addition to rapid increase of possible throughput and data volume using their TimescaleDB. Click here.

Timescale's article illuminates also its taking into account so called CAP theorem.

ZABBIX migration from version 2.4.8 to 4.2-PostgreSQL/TimescaleDB

---

Preconditions

• old ZABBIX server 2.4(.8) + PostgreSQL DB (alternatively MySQL/MariaDB)
• RHEL/CentOS 7 VM(s) for new ZABBIX server+DB+frontend
• review TimescaleDB licensing

---

Installations on RHEL/CentOS 7

---

• PostgreSQL/TimescaleDB 1.4
• DB creation (just for new ZBX installation and its verification, but won't be used for migration):
• sudo -u postgres psql
• CREATE ROLE zabbix_server_db_login LOGIN PASSWORD '***********';
• CREATE DATABASE zabbix_server_db OWNER zabbix_server_db_login;
• \q
• PHP 7.3
• ZABBIX server+frontend 4.2 - leave the server shut down before next steps

---

Download & unpack ZABBIX 2.4.8 DB original schema to new DB server - deployment preparation

---

wget https://sourceforge.net/projects/zabbix/files/ZABBIX%20Latest%20Stable/2.4.8/zabbix-2.4.8.tar.gz

tar xvzf zabbix-2.4.8.tar.gz

cd zabbix-2.4.8/database/postgresql/

---

Export ZABBIX 2.4.8 instance DB data (without schema)

---

pg_dump -h old-ZBX-address -d old-ZBX-DB-name -U old-ZBX-DB-read-permitted-user-name --no-owner --no-privileges --data-only --exclude-schema repeatedly-custom-not-migrating-schemas -T repeatedly-custom-not-migrating-tables -T acknowledges -T alerts -T auditlog -T events -T service_alarms -T 'history*' -T 'trends*' > zabbix_server_db_DML.sql

In case of migration from non-PostgreSQL DB (e.g. MySQL/MariaDB), PostgreSQL-compatible dump needs to be created in this step (e.g. via mysqldump -c -e -t --compatible=postgresql --no-create-info --skip-quote-names --skip-add-locks zabbix > zabbix.dmp) - not tested when writing this article.

---

Deploy ZABBIX 2.4.8 instance DB data (with original schema) to new PostgreSQL server

---

Re-create DB for import:

sudo -u postgres psql -c "DROP DATABASE zabbix_server_db_imported;" -c "CREATE DATABASE zabbix_server_db_imported OWNER zabbix_server_db_login;"

Import original schema and instance data:

psql -d zabbix_server_db_imported -U zabbix_server_db_login -f schema.sql -f zabbix_server_db_DML.sql

---

Check & disable hosts

---

psql -d zabbix_server_db_imported -U zabbix_server_db_login

zabbix_server_db_imported=> SELECT status, COUNT(status) FROM hosts GROUP BY status ORDER BY status;

status	count
0	count of enabled ZBX hosts
1	count of disabled ZBX hosts
3	count of all ZBX templates
5	count of all ZBX proxies

The same count as displayed for status=0 has to be updated in following step:

zabbix_server_db_imported=> UPDATE hosts SET status=1 WHERE status=0;

zabbix_server_db_imported=> \q

---

Start ZBX server (upgrade) & check log

---

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

sudo service zabbix-server start

Check log for possible errors, warnings, exceptions and fix what needed. After a while, old ZBX DB should be upgraded according to new ZBX DB schema.

---

Install TimescaleDB into new ZBX DB

---

sudo -u postgres psql zabbix_server_db_imported

zabbix_server_db_imported=# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

zabbix_server_db_imported=# \q

zcat /usr/share/doc/zabbix-server-pgsql*/timescaledb.sql.gz | psql zabbix_server_db_imported -U zabbix_server_db_login

For more details about TimescaleDB ZABBIX-supported deployment, read this.

ZABBIX + TimescaleDB (PostgreSQL 11) installation from Docker images

New article about installing and running ZABBIX using Docker containers published here.

If you don't want to read through whole genesis, final YAML file for docker compose can be downloaded from here.