You've successfully subscribed to Nuvotex Blog
Great! Next, complete checkout for full access to Nuvotex Blog
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

Migrating zabbix from mysql to timescaledb - database setup

This part continues our mysql to timescaleDB migration. We prepare our target database and add some helpers on the source to create a resumable process.

Daniel Nachtrub
Daniel Nachtrub

In the previous post (https://blog.nuvotex.de/migrating-zabbix-from-mysql-to-timescaledb/) I've spoken about why we're migrating data from mysql to timescaleDB.

Short summary: Zabbix is collecting timeseries data, timescaleDB is optimized for that and much faster than mysql. Period.

Procedure overview

We're using pgloader to copy over data from our existing (zabbix) database. We start copying the largest amount of data (timeseries) first (up to a recent point like yesterday midnight), then we copy all other tables and verify the application using a new instance of zabbix frontend.

If everything is fine, we are going to final phase:

  • Remove modifications of database (enable triggers again, create indices etc.)
  • Stop zabbix-server
  • Migrate remaining data (prepare chunks so that the operation completes in a short amount of time)
  • Migrate all non-timeseries data
  • Switch server to psql database

This process goes quite smooth and has no point of no return until the very last step.

Use of zabbix proxies Using zabbix proxies is in general encouraged if you monitor a certain amount of hosts. In this case proxies buffer incoming data so that we can even switch without any loss of incoming data.

Prepare destination database - timescaleDB

I assume that you've a timescaleDB server (psql with timescaleDB extension) already installed and accounts are also create. If not, there's plenty of information regarding this topic on the web :-)

Create schema

Before starting, make sure that the timescaleDB has the expected schema available. Follow zabbix documentation on how to create the schema: https://www.zabbix.com/documentation/current/en/manual/appendix/install/timescaledb

Create hypertables

Having completed this, you also need to create hypertables for the relevant relations/tables. This is also documented in zabbix manual - as a short reference, it will be similar to this:

SELECT create_hypertable('history_log', 'clock', chunk_time_interval => 86400, migrate_data => true); SELECT create_hypertable('history_text', 'clock', chunk_time_interval => 86400, migrate_data => true); SELECT create_hypertable('history_str', 'clock', chunk_time_interval => 86400, migrate_data => true); SELECT create_hypertable('trends', 'clock', chunk_time_interval => 2592000, migrate_data => true); SELECT create_hypertable('trends_uint', 'clock', chunk_time_interval => 2592000, migrate_data => true); SELECT create_hypertable('history_uint', 'clock', chunk_time_interval => 86400, migrate_data => true); SELECT create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true);
create hypertables

Drop indices on timeseries tables

To make sure we are using ingestion at max speed, we drop indices on the destination database and create them afterwards. This should speed up the overall process and make the operation mostly an I/O bound process.

DROP INDEX history_1;
DROP INDEX history_log_1;
DROP INDEX history_str_1;
DROP INDEX history_text_1;
DROP INDEX history_uint_1;
drop indices on timescaleDB

Now it's time to prepare the source database.

Disable triggers in destination database

We are migrating also tables that have constraints. A common issue in such a migration is that we do not follow the dependency graph associated with it, so constraints are violated during migration. This also applies here, so we disable triggers during this process.

ALTER TABLE acknowledges DISABLE TRIGGER ALL;
ALTER TABLE actions DISABLE TRIGGER ALL;
ALTER TABLE alerts DISABLE TRIGGER ALL;
ALTER TABLE auditlog DISABLE TRIGGER ALL;
ALTER TABLE auditlog_details DISABLE TRIGGER ALL;
ALTER TABLE autoreg_host DISABLE TRIGGER ALL;
ALTER TABLE conditions DISABLE TRIGGER ALL;
ALTER TABLE config DISABLE TRIGGER ALL;
ALTER TABLE config_autoreg_tls DISABLE TRIGGER ALL;
ALTER TABLE corr_condition DISABLE TRIGGER ALL;
ALTER TABLE corr_condition_group DISABLE TRIGGER ALL;
ALTER TABLE corr_condition_tag DISABLE TRIGGER ALL;
ALTER TABLE corr_condition_tagpair DISABLE TRIGGER ALL;
ALTER TABLE corr_condition_tagvalue DISABLE TRIGGER ALL;
ALTER TABLE corr_operation DISABLE TRIGGER ALL;
ALTER TABLE correlation DISABLE TRIGGER ALL;
ALTER TABLE dashboard DISABLE TRIGGER ALL;
ALTER TABLE dashboard_page DISABLE TRIGGER ALL;
ALTER TABLE dashboard_user DISABLE TRIGGER ALL;
ALTER TABLE dashboard_usrgrp DISABLE TRIGGER ALL;
ALTER TABLE dbversion DISABLE TRIGGER ALL;
ALTER TABLE dchecks DISABLE TRIGGER ALL;
ALTER TABLE dhosts DISABLE TRIGGER ALL;
ALTER TABLE drules DISABLE TRIGGER ALL;
ALTER TABLE dservices DISABLE TRIGGER ALL;
ALTER TABLE escalations DISABLE TRIGGER ALL;
ALTER TABLE event_recovery DISABLE TRIGGER ALL;
ALTER TABLE event_suppress DISABLE TRIGGER ALL;
ALTER TABLE event_tag DISABLE TRIGGER ALL;
ALTER TABLE events DISABLE TRIGGER ALL;
ALTER TABLE expressions DISABLE TRIGGER ALL;
ALTER TABLE functions DISABLE TRIGGER ALL;
ALTER TABLE globalmacro DISABLE TRIGGER ALL;
ALTER TABLE globalvars DISABLE TRIGGER ALL;
ALTER TABLE graph_discovery DISABLE TRIGGER ALL;
ALTER TABLE graph_theme DISABLE TRIGGER ALL;
ALTER TABLE graphs DISABLE TRIGGER ALL;
ALTER TABLE graphs_items DISABLE TRIGGER ALL;
ALTER TABLE group_discovery DISABLE TRIGGER ALL;
ALTER TABLE group_prototype DISABLE TRIGGER ALL;
ALTER TABLE host_discovery DISABLE TRIGGER ALL;
ALTER TABLE host_inventory DISABLE TRIGGER ALL;
ALTER TABLE host_tag DISABLE TRIGGER ALL;
ALTER TABLE hostmacro DISABLE TRIGGER ALL;
ALTER TABLE hosts DISABLE TRIGGER ALL;
ALTER TABLE hosts_groups DISABLE TRIGGER ALL;
ALTER TABLE hosts_templates DISABLE TRIGGER ALL;
ALTER TABLE housekeeper DISABLE TRIGGER ALL;
ALTER TABLE hstgrp DISABLE TRIGGER ALL;
ALTER TABLE httpstep DISABLE TRIGGER ALL;
ALTER TABLE httpstep_field DISABLE TRIGGER ALL;
ALTER TABLE httpstepitem DISABLE TRIGGER ALL;
ALTER TABLE httptest DISABLE TRIGGER ALL;
ALTER TABLE httptest_field DISABLE TRIGGER ALL;
ALTER TABLE httptest_tag DISABLE TRIGGER ALL;
ALTER TABLE httptestitem DISABLE TRIGGER ALL;
ALTER TABLE icon_map DISABLE TRIGGER ALL;
ALTER TABLE icon_mapping DISABLE TRIGGER ALL;
ALTER TABLE ids DISABLE TRIGGER ALL;
ALTER TABLE images DISABLE TRIGGER ALL;
ALTER TABLE interface DISABLE TRIGGER ALL;
ALTER TABLE interface_discovery DISABLE TRIGGER ALL;
ALTER TABLE interface_snmp DISABLE TRIGGER ALL;
ALTER TABLE item_condition DISABLE TRIGGER ALL;
ALTER TABLE item_discovery DISABLE TRIGGER ALL;
ALTER TABLE item_parameter DISABLE TRIGGER ALL;
ALTER TABLE item_preproc DISABLE TRIGGER ALL;
ALTER TABLE item_rtdata DISABLE TRIGGER ALL;
ALTER TABLE item_tag DISABLE TRIGGER ALL;
ALTER TABLE items DISABLE TRIGGER ALL;
ALTER TABLE lld_macro_path DISABLE TRIGGER ALL;
ALTER TABLE lld_override DISABLE TRIGGER ALL;
ALTER TABLE lld_override_condition DISABLE TRIGGER ALL;
ALTER TABLE lld_override_opdiscover DISABLE TRIGGER ALL;
ALTER TABLE lld_override_operation DISABLE TRIGGER ALL;
ALTER TABLE lld_override_ophistory DISABLE TRIGGER ALL;
ALTER TABLE lld_override_opinventory DISABLE TRIGGER ALL;
ALTER TABLE lld_override_opperiod DISABLE TRIGGER ALL;
ALTER TABLE lld_override_opseverity DISABLE TRIGGER ALL;
ALTER TABLE lld_override_opstatus DISABLE TRIGGER ALL;
ALTER TABLE lld_override_optag DISABLE TRIGGER ALL;
ALTER TABLE lld_override_optemplate DISABLE TRIGGER ALL;
ALTER TABLE lld_override_optrends DISABLE TRIGGER ALL;
ALTER TABLE maintenance_tag DISABLE TRIGGER ALL;
ALTER TABLE maintenances DISABLE TRIGGER ALL;
ALTER TABLE maintenances_groups DISABLE TRIGGER ALL;
ALTER TABLE maintenances_hosts DISABLE TRIGGER ALL;
ALTER TABLE maintenances_windows DISABLE TRIGGER ALL;
ALTER TABLE media DISABLE TRIGGER ALL;
ALTER TABLE media_type DISABLE TRIGGER ALL;
ALTER TABLE media_type_message DISABLE TRIGGER ALL;
ALTER TABLE media_type_param DISABLE TRIGGER ALL;
ALTER TABLE module DISABLE TRIGGER ALL;
ALTER TABLE opcommand DISABLE TRIGGER ALL;
ALTER TABLE opcommand_grp DISABLE TRIGGER ALL;
ALTER TABLE opcommand_hst DISABLE TRIGGER ALL;
ALTER TABLE opconditions DISABLE TRIGGER ALL;
ALTER TABLE operations DISABLE TRIGGER ALL;
ALTER TABLE opgroup DISABLE TRIGGER ALL;
ALTER TABLE opinventory DISABLE TRIGGER ALL;
ALTER TABLE opmessage DISABLE TRIGGER ALL;
ALTER TABLE opmessage_grp DISABLE TRIGGER ALL;
ALTER TABLE opmessage_usr DISABLE TRIGGER ALL;
ALTER TABLE optemplate DISABLE TRIGGER ALL;
ALTER TABLE problem DISABLE TRIGGER ALL;
ALTER TABLE problem_tag DISABLE TRIGGER ALL;
ALTER TABLE profiles DISABLE TRIGGER ALL;
ALTER TABLE proxy_autoreg_host DISABLE TRIGGER ALL;
ALTER TABLE proxy_dhistory DISABLE TRIGGER ALL;
ALTER TABLE proxy_history DISABLE TRIGGER ALL;
ALTER TABLE regexps DISABLE TRIGGER ALL;
ALTER TABLE report DISABLE TRIGGER ALL;
ALTER TABLE report_param DISABLE TRIGGER ALL;
ALTER TABLE report_user DISABLE TRIGGER ALL;
ALTER TABLE report_usrgrp DISABLE TRIGGER ALL;
ALTER TABLE rights DISABLE TRIGGER ALL;
ALTER TABLE role DISABLE TRIGGER ALL;
ALTER TABLE role_rule DISABLE TRIGGER ALL;
ALTER TABLE script_param DISABLE TRIGGER ALL;
ALTER TABLE scripts DISABLE TRIGGER ALL;
ALTER TABLE service_alarms DISABLE TRIGGER ALL;
ALTER TABLE services DISABLE TRIGGER ALL;
ALTER TABLE services_links DISABLE TRIGGER ALL;
ALTER TABLE services_times DISABLE TRIGGER ALL;
ALTER TABLE sessions DISABLE TRIGGER ALL;
ALTER TABLE sysmap_element_trigger DISABLE TRIGGER ALL;
ALTER TABLE sysmap_element_url DISABLE TRIGGER ALL;
ALTER TABLE sysmap_shape DISABLE TRIGGER ALL;
ALTER TABLE sysmap_url DISABLE TRIGGER ALL;
ALTER TABLE sysmap_user DISABLE TRIGGER ALL;
ALTER TABLE sysmap_usrgrp DISABLE TRIGGER ALL;
ALTER TABLE sysmaps DISABLE TRIGGER ALL;
ALTER TABLE sysmaps_element_tag DISABLE TRIGGER ALL;
ALTER TABLE sysmaps_elements DISABLE TRIGGER ALL;
ALTER TABLE sysmaps_link_triggers DISABLE TRIGGER ALL;
ALTER TABLE sysmaps_links DISABLE TRIGGER ALL;
ALTER TABLE tag_filter DISABLE TRIGGER ALL;
ALTER TABLE task DISABLE TRIGGER ALL;
ALTER TABLE task_acknowledge DISABLE TRIGGER ALL;
ALTER TABLE task_check_now DISABLE TRIGGER ALL;
ALTER TABLE task_close_problem DISABLE TRIGGER ALL;
ALTER TABLE task_data DISABLE TRIGGER ALL;
ALTER TABLE task_remote_command DISABLE TRIGGER ALL;
ALTER TABLE task_remote_command_result DISABLE TRIGGER ALL;
ALTER TABLE task_result DISABLE TRIGGER ALL;
ALTER TABLE timeperiods DISABLE TRIGGER ALL;
ALTER TABLE token DISABLE TRIGGER ALL;
ALTER TABLE trigger_depends DISABLE TRIGGER ALL;
ALTER TABLE trigger_discovery DISABLE TRIGGER ALL;
ALTER TABLE trigger_queue DISABLE TRIGGER ALL;
ALTER TABLE trigger_tag DISABLE TRIGGER ALL;
ALTER TABLE triggers DISABLE TRIGGER ALL;
ALTER TABLE users DISABLE TRIGGER ALL;
ALTER TABLE users_groups DISABLE TRIGGER ALL;
ALTER TABLE usrgrp DISABLE TRIGGER ALL;
ALTER TABLE valuemap DISABLE TRIGGER ALL;
ALTER TABLE valuemap_mapping DISABLE TRIGGER ALL;
ALTER TABLE widget DISABLE TRIGGER ALL;
ALTER TABLE widget_field DISABLE TRIGGER ALL;
disable triggers in sql

Yep, quite some of them.

Having completed these steps, the timescaleDB is ready for data ingestion.

Preparation of source database - mysql

As your existing database is probably constantly changing (history & trends tables), you cannot easily copy all data to a new database without either loosing data because of cutoff or loosing data because you stopped zabbix server.

The solution: Split the time series data (which is append only [except the edge case of late incoming data from proxies]) into views and migrate the views using pgloader.

The procedure is then as follows:

  • Create a view for your first chunk (oldest data first)
  • Migrate data
  • Alter the view to select the next chunk
  • Migrate data
  • etc.

To create a view, you can use the following SQL commands. (note: we used partitioning before in mysql, so we had very cheap selection of ranges, you might need to filter date column).

CREATE OR REPLACE VIEW history_view AS SELECT itemid, clock, value, ns FROM history PARTITION (p2021_06_06,p2021_06_07,p2021_06_08,p2021_06_09,p2021_06_10,p2021_06_11,p2021_06_12,p2021_06_13,p2021_06_14,p2021_06_15,p2021_06_16,p2021_06_17,p2021_06_18,p2021_06_19); CREATE OR REPLACE VIEW history_str_view AS SELECT itemid, clock, value, ns FROM history_str PARTITION (p2021_06_06,p2021_06_07,p2021_06_08,p2021_06_09,p2021_06_10,p2021_06_11,p2021_06_12,p2021_06_13,p2021_06_14,p2021_06_15,p2021_06_16,p2021_06_17,p2021_06_18,p2021_06_19); CREATE OR REPLACE VIEW history_text_view AS SELECT itemid, clock, value, ns FROM history_text PARTITION (p2021_06_06,p2021_06_07,p2021_06_08,p2021_06_09,p2021_06_10,p2021_06_11,p2021_06_12,p2021_06_13,p2021_06_14,p2021_06_15,p2021_06_16,p2021_06_17,p2021_06_18,p2021_06_19); CREATE OR REPLACE VIEW history_uint_view AS SELECT itemid, clock, value, ns FROM history_uint PARTITION (p2021_06_06,p2021_06_07,p2021_06_08,p2021_06_09,p2021_06_10,p2021_06_11,p2021_06_12,p2021_06_13,p2021_06_14,p2021_06_15,p2021_06_16,p2021_06_17,p2021_06_18,p2021_06_19); 

CREATE OR REPLACE VIEW trends_view AS SELECT itemid,clock,num,value_min,value_avg,value_max FROM trends PARTITION (p2019_07);
CREATE OR REPLACE VIEW trends_uint_view AS SELECT itemid,clock,num,value_min,value_avg,value_max FROM trends_uint PARTITION (p2019_07);
sql views as range selection

As you can see, views are created for history & trends tables.

That is already everything we need to start with the migration. The next part will describe the actual migration of data.

Database
Daniel Nachtrub

Daniel Nachtrub

Kind of likes computers. Linux foundation certified: LFCS / CKA / CKAD / CKS. Microsoft certified: Cybersecurity Architect Expert & Azure Solutions Architect Expert.

You might also like

Choosing a postgres operator

Choosing a postgres operator

This post describes my journey on the selection of the postgres operator that matches our demand.

Daniel Nachtrub
Daniel Nachtrub
Kubernetes
Running postgres in kubernetes with hugepages

Running postgres in kubernetes with hugepages

To run postgres in a container on nodes with huge pages enabled requires you to configure the container accordingly. This post shows how to do this on kubernetes/openshift.

Daniel Nachtrub
Daniel Nachtrub
Container

Authors →

Daniel Nachtrub

Kind of likes computers. Linux foundation certified: LFCS / CKA / CKAD / CKS. Microsoft certified: Cybersecurity Architect Expert & Azure Solutions Architect Expert.

Sebastian Augustin

Lorenz Maier