Skip to content

Instantly share code, notes, and snippets.

@mcbrineellis

mcbrineellis/zabbix-upgrade-db-migration

Last active October 26, 2022 06:28
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save mcbrineellis/18b04d2f77a2ee74ff6ca4ad6b460616 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save mcbrineellis/18b04d2f77a2ee74ff6ca4ad6b460616 to your computer and use it in GitHub Desktop.
Download ZIP
Zabbix 4 to 6 Upgrade: MySQL to PostgreSQL migration, CentOS 7 to Ubuntu 20.04 migration
Raw
zabbix-upgrade-db-migration
# This guide was customized for my own specific use case:
# Migrating from CentOS 7 to Ubuntu 20.04, Zabbix 4 to 6, and MySQL to PostgreSQL.
#
# I created this guide after watching this excellent tutorial by Dmitry Lambert on Youtube:
# https://www.youtube.com/watch?v=S-C5NCZJnt0
#
# Thank you Dmitry!
# on original system, take a backup and create a new DB user
systemctl stop zabbix-server
mysqldump --opt --flush-logs --single-transaction --databases zabbix -uroot -p | gzip -9 > /root/zabbix-backup.sql.gz
mysql -uroot -p
create user zabbix@'%' identified by 'zabbix';
grant all privileges on zabbix.* to zabbix@'%';
quit;
# (you can start the server back up again now, until the DB migration part)
# on the zabbix4 temporary upgrade server, configure pgloader
sudo apt install curl ca-certificates gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install -y pgloader
sudo systemctl stop zabbix-server
wget https://cdn.zabbix.com/zabbix/sources/stable/4.0/zabbix-4.0.44.tar.gz
tar -zxf zabbix-4.0.44.tar.gz
vi zabbix-4.0.44/database/postgresql/schema.sql
# remove the unwanted commands in the schema.sql file
#
# - move the cursor to the first ALTER TABLE line in the file
# - use the command :.,$d and all the ALTER TABLE lines will be removed
# - and remove the following line as well
#
# INSERT INTO dbversion VALUES ('4000000','4000006');
#
# - now the file should end with
#
# CREATE TABLE dbversion (
# mandatory integer DEFAULT '0' NOT NULL,
# optional integer DEFAULT '0' NOT NULL
# );
#
# delete existing DB, automatically created by the zabbix ansible role
sudo su - postgres
psql
DROP DATABASE IF EXISTS zabbix;
DROP USER zabbix;
\q
exit
# recreate the DB, use the same password as before
sudo -u postgres createuser --pwprompt zabbix
sudo -u postgres createdb -O zabbix zabbix
# import the schema to psql and confirm it was imported properly
cat zabbix-4.0.44/database/postgresql/schema.sql | sudo -u zabbix psql zabbix
sudo su - postgres
psql
\c zabbix;
\d items;
select * from items;
select * from hosts;
# these should be empty other than the table headers of course
\q
exit
# Create a new file called config with the following configuration info
LOAD DATABASE
FROM mysql://zabbix:zabbix@OLDSERVER/zabbix
INTO postgresql://zabbix:NEWPASSWORD@localhost/zabbix
WITH include no drop,
truncate,
create no tables,
create no indexes,
no foreign keys,
reset sequences,
data only
SET maintenance_work_mem TO '2048MB', work_mem to '1024MB'
EXCLUDING TABLE NAMES MATCHING ~/history.*/, ~/trend.*/
ALTER SCHEMA 'zabbix' RENAME TO 'public';
# Now, try out the migration, hopefully it works
pgloader config
# Check the migrated data
sudo su - postgres
psql
\c zabbix;
select * from items;
# now there should be data, whereas before there was nothing
\q
exit
# Create a new file called data with the following configuration info
LOAD DATABASE
FROM mysql://zabbix:zabbix@OLDSERVER/zabbix
INTO postgresql://zabbix:NEWPASSWORD@localhost/zabbix
WITH include no drop,
no truncate,
create no tables,
create no indexes,
no foreign keys,
reset sequences,
data only
SET maintenance_work_mem TO '2048MB', work_mem to '1024MB'
EXCLUDING TABLE NAMES MATCHING 'acknowledges' ,
'actions' ,
'alerts' ,
'application_discovery' ,
'application_prototype' ,
'application_template' ,
'applications' ,
'auditlog' ,
'auditlog_details' ,
'autoreg_host' ,
'conditions' ,
'config' ,
'corr_condition' ,
'corr_condition_group' ,
'corr_condition_tag' ,
'corr_condition_tagpair' ,
'corr_condition_tagvalue' ,
'corr_operation' ,
'correlation' ,
'dashboard' ,
'dashboard_user' ,
'dashboard_usrgrp' ,
'dbversion' ,
'dchecks' ,
'dhosts' ,
'drules' ,
'dservices' ,
'escalations' ,
'event_recovery' ,
'event_suppress' ,
'event_tag' ,
'events' ,
'expressions' ,
'functions' ,
'globalmacro' ,
'globalvars' ,
'graph_discovery' ,
'graph_theme' ,
'graphs' ,
'graphs_items' ,
'group_discovery' ,
'group_prototype' ,
'host_discovery' ,
'host_inventory' ,
'hostmacro' ,
'hosts' ,
'hosts_groups' ,
'hosts_templates' ,
'housekeeper' ,
'hstgrp' ,
'httpstep' ,
'httpstep_field' ,
'httpstepitem' ,
'httptest' ,
'httptest_field' ,
'httptestitem' ,
'icon_map' ,
'icon_mapping' ,
'ids' ,
'images' ,
'interface' ,
'interface_discovery' ,
'item_application_prototype' ,
'item_condition' ,
'item_discovery' ,
'item_preproc' ,
'items' ,
'items_applications' ,
'maintenance_tag' ,
'maintenances' ,
'maintenances_groups' ,
'maintenances_hosts' ,
'maintenances_windows' ,
'mappings' ,
'media' ,
'media_type' ,
'opcommand' ,
'opcommand_grp' ,
'opcommand_hst' ,
'opconditions' ,
'operations' ,
'opgroup' ,
'opinventory' ,
'opmessage' ,
'opmessage_grp' ,
'opmessage_usr' ,
'optemplate' ,
'problem' ,
'problem_tag' ,
'profiles' ,
'proxy_autoreg_host' ,
'proxy_dhistory' ,
'proxy_history' ,
'regexps' ,
'rights' ,
'screen_user' ,
'screen_usrgrp' ,
'screens' ,
'screens_items' ,
'scripts' ,
'service_alarms' ,
'services' ,
'services_links' ,
'services_times' ,
'sessions' ,
'slides' ,
'slideshow_user' ,
'slideshow_usrgrp' ,
'slideshows' ,
'sysmap_element_trigger' ,
'sysmap_element_url' ,
'sysmap_shape' ,
'sysmap_url' ,
'sysmap_user' ,
'sysmap_usrgrp' ,
'sysmaps' ,
'sysmaps_elements' ,
'sysmaps_link_triggers' ,
'sysmaps_links' ,
'tag_filter' ,
'task' ,
'task_acknowledge' ,
'task_check_now' ,
'task_close_problem' ,
'task_remote_command' ,
'task_remote_command_result' ,
'timeperiods' ,
'trigger_depends' ,
'trigger_discovery' ,
'trigger_tag' ,
'triggers' ,
'users' ,
'users_groups' ,
'usrgrp' ,
'valuemaps' ,
'widget' ,
'widget_field'
ALTER SCHEMA 'zabbix' RENAME TO 'public';
# Now, try out the migration, hopefully it works
pgloader data
# we have to run the ALTER TABLE statements that we previously removed from the config file
# let's delete the extracted directory, and reextract once again to get an unmodified file
rm -rf zabbix-4.0.44/
tar -zxf zabbix-4.0.44.tar.gz
# now let's grep the ALTER TABLE statements into a new sql file
cat zabbix-4.0.44/database/postgresql/schema.sql | grep ALTER > altertable.sql
cat altertable.sql | sudo -u zabbix psql zabbix
# validate the contraints were added
sudo su - postgres
psql
\c zabbix;
\d+ items;
\q
exit
# near the bottom, we should see "Foreign-key constraints:" and some items listed below that
# now we can start up Zabbix 4.0 with the new DB (don't forget to point the config at pgsql)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment