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

Revisions

  1. mcbrineellis revised this gist Oct 26, 2022. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion zabbix-upgrade-db-migration
    View file
    Original file line number Diff line number Diff line change
    @@ -312,7 +312,7 @@ sudo systemctl start zabbix-server
    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 postgresql-13 postgresql-client-13
    sudo apt-get install -y postgresql-13 postgresql-client-13
    sudo systemctl stop postgresql.service
    sudo systemctl stop zabbix-server
    sudo su - postgres
  2. mcbrineellis revised this gist Oct 25, 2022. No changes.
  3. mcbrineellis revised this gist Oct 25, 2022. 1 changed file with 4 additions and 2 deletions.
    6 changes: 4 additions & 2 deletions zabbix-upgrade-db-migration
    View file
    Original file line number Diff line number Diff line change
    @@ -358,6 +358,9 @@ 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
    @@ -376,8 +379,7 @@ sudo -u postgres createdb -O zabbix zabbix
    pg_dump -h [old_server_IP_or_HOSTNAME] -U zabbix > zabbix_dump.sql
    ls -alh zabbix_dump.sql

    # shut down the zabbix server and import the DB dump
    sudo systemctl stop zabbix-server
    # import the DB dump
    cat zabbix_dump.sql | sudo -u zabbix psql zabbix

    # start up the zabbix server again, we should be done now!
  4. mcbrineellis revised this gist Oct 25, 2022. 1 changed file with 14 additions and 1 deletion.
    15 changes: 14 additions & 1 deletion zabbix-upgrade-db-migration
    View file
    Original file line number Diff line number Diff line change
    @@ -17,7 +17,20 @@ 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
    # 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'
  5. mcbrineellis revised this gist Oct 13, 2022. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions zabbix-upgrade-db-migration
    View file
    Original file line number Diff line number Diff line change
    @@ -299,6 +299,7 @@ sudo systemctl start zabbix-server
    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 postgresql-13 postgresql-client-13
    sudo systemctl stop postgresql.service
    sudo systemctl stop zabbix-server
    sudo su - postgres
  6. mcbrineellis revised this gist Oct 13, 2022. 1 changed file with 36 additions and 1 deletion.
    37 changes: 36 additions & 1 deletion zabbix-upgrade-db-migration
    View file
    Original file line number Diff line number Diff line change
    @@ -332,4 +332,39 @@ 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 DB again and migrate it over to our new server with a clean Zabbix 6 install.
    # 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

    # 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

    # shut down the zabbix server and import the DB dump
    sudo systemctl stop zabbix-server
    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
  7. mcbrineellis revised this gist Oct 11, 2022. 1 changed file with 55 additions and 0 deletions.
    55 changes: 55 additions & 0 deletions zabbix-upgrade-db-migration
    View file
    Original file line number Diff line number Diff line change
    @@ -277,4 +277,59 @@ 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 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 DB again and migrate it over to our new server with a clean Zabbix 6 install.
  8. mcbrineellis revised this gist Oct 11, 2022. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions zabbix-upgrade-db-migration
    View file
    Original file line number Diff line number Diff line change
    @@ -5,6 +5,8 @@
    # 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
  9. mcbrineellis revised this gist Oct 11, 2022. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions zabbix-upgrade-db-migration
    View file
    Original file line number Diff line number Diff line change
    @@ -19,6 +19,7 @@ quit;
    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
  10. mcbrineellis revised this gist Oct 11, 2022. No changes.
  11. mcbrineellis created this gist Oct 11, 2022.
    277 changes: 277 additions & 0 deletions zabbix-upgrade-db-migration
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,277 @@
    # 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 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)