Zabbix 4 to 6 Upgrade: MySQL to PostgreSQL migration, CentOS 7 to Ubuntu 20.04 migration

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!
#
# Before starting, make sure that the source and target systems are on the exact same version of Zabbix to avoid issues.

# 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)

# Also, if you have issues logging in as root, and you are running CentOS and MariaDB, you can reset the password with these steps:
systemctl stop mariadb
mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
FLUSH PRIVILEGES;
update mysql.user set password=password('newrootpassword') where user='root';
FLUSH PRIVILEGES;
quit;
ps aufx | grep mysql | grep -v grep
kill # include the PID number from the above command
systemctl start mariadb
# now you should be able to login using the newrootpassword normally

# now that we created the migration DB user, 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)
# if the server starts up fine, then we are good to proceed with the upgrade
#
# steps taken from https://bestmonitoringtools.com/upgrade-zabbix-to-the-latest-version

wget https://repo.zabbix.com/zabbix/6.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_6.0-4+ubuntu$(lsb_release -rs)_all.deb
sudo dpkg -i zabbix-release_6.0-4+ubuntu$(lsb_release -rs)_all.deb
sudo apt update
sudo apt install -y --only-upgrade zabbix-server-pgsql zabbix-frontend-php
sudo apt-get install -y zabbix-apache-conf
sudo systemctl start zabbix-server

# the DB upgrade begins in the background
# let's watch
# uh oh, it failed, because Zabbix is complaining I need at least pgsql13, so let's fix that now.

# Upgrading postgresql from 12 to 13 (my Zabbix 4 server was installed with pgsql12)
#
# steps taken from https://www.kostolansky.sk/posts/upgrading-to-postgresql-12/

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt-get install -y postgresql-13 postgresql-client-13
sudo systemctl stop postgresql.service
sudo systemctl stop zabbix-server
sudo su - postgres
# check clusters
/usr/lib/postgresql/13/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/12/main \
  --new-datadir=/var/lib/postgresql/13/main \
  --old-bindir=/usr/lib/postgresql/12/bin \
  --new-bindir=/usr/lib/postgresql/13/bin \
  --old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf' \
  --check
# migrate the data
/usr/lib/postgresql/13/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/12/main \
  --new-datadir=/var/lib/postgresql/13/main \
  --old-bindir=/usr/lib/postgresql/12/bin \
  --new-bindir=/usr/lib/postgresql/13/bin \
  --old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf'
exit
sudo vi /etc/postgresql/13/main/postgresql.conf
# ...and change "port = 5433" to "port = 5432"
sudo vi /etc/postgresql/12/main/postgresql.conf
# ...and change "port = 5432" to "port = 5433"
sudo systemctl start postgresql.service
sudo su - postgres
psql -c "SELECT version();"
./analyze_new_cluster.sh
exit
sudo systemctl start zabbix-server

# You should be able to log into the web interface at this point and Zabbix 6 should load.
# Now we can dump the upgraded DB and migrate it over to our new server with a clean Zabbix 6 install.

# SSH into the temporary upgrade server and temporarily allow connections over the network, for the purposes of the migration
# Also, shut down the Zabbix Server so that the DB dump will be consistent

echo "host all all 0.0.0.0/0 trust" | sudo tee -a /etc/postgresql/13/main/pg_hba.conf > /dev/null
echo "listen_addresses = '*'" | sudo tee -a /etc/postgresql/13/main/postgresql.conf > /dev/null
sudo systemctl restart postgresql
sudo systemctl stop zabbix-server

# Log back into our fresh Zabbix 6 server

# shut down the zabbix server
sudo systemctl stop zabbix-server

# delete the existing DB which was automatically created by the zabbix ansible role
# since we will be migrating over the data ourselves we want to start from scratch
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

# use pg_dump to copy over the database from the temporary upgrade server

pg_dump -h [old_server_IP_or_HOSTNAME] -U zabbix > zabbix_dump.sql
ls -alh zabbix_dump.sql

# import the DB dump
cat zabbix_dump.sql | sudo -u zabbix psql zabbix

# start up the zabbix server again, we should be done now!
sudo systemctl start zabbix-server