Moving Zabbix from MySQL to PostgreSQL

Learn the real working way to move Zabbix from MySQL to PostgreSQL without data loss. Includes all commands, scripts, and practical steps that actually work. Our PostgreSQL Support Team is always here to help you.

Moving Zabbix from MySQL to PostgreSQL

When it comes to zabbix migrate from mysql to postgresql, most people overcomplicate it. Truth is, the process is straightforward once you know the exact commands and order of execution. Some say the move is unnecessary since MySQL handles Zabbix well, offering better partitioned tables and more SQL engine metrics. Still, many admins prefer PostgreSQL for its performance stability and data consistency. Let’s go through the actual working process step by step, without skipping a single line of code.

Setting Up PostgreSQL

Start by adding the PostgreSQL repository and installing the required packages:

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# yum install postgresql12-server

# /usr/pgsql-12/bin/postgresql-12-setup initdb

# systemctl enable postgresql-12

# systemctl start postgresql-12

Installing pgloader

pgloader helps move the database easily from MySQL to PostgreSQL. Install and verify it:

# yum install pgloader

# pgloader -V

# pgloader version "3.6.2"

Preparing Your Environment

Create a working directory:

mkdir myzabbix-pgzabbix

cd myzabbix-pgzabbix

Then download and unpack the Zabbix source:

# yum install wget

# wget https://cdn.zabbix.com/zabbix/sources/stable/5.0/zabbix-5.0.1.tar.gz

# tar -zxvf zabbix-5.0.1.tar.gz

Now split the default schema:

cd myzabbix-pgzabbix/zabbix-5.0.1/database/postgresql/

sed -n '/CREATE.*/,/INSERT.*$/p' schema.sql | head -n-1 > create.sql

grep ALTER schema.sql > alter.sql

Creating PostgreSQL User and Database

# sudo -u postgres createuser --pwprompt zabbix

# sudo -u postgres createdb -O zabbix zabbix

Setting Up the Migration Script

Inside the directory containing create.sql and alter.sql, create zabbix_migrate.load:

LOAD DATABASE

FROM mysql://zabbix:zabbix-password@localhost/zabbix

INTO postgresql://zabbix:zabbix-password@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 '1024MB', work_mem to '256MB'

ALTER SCHEMA 'zabbix' RENAME TO 'public'

BEFORE LOAD EXECUTE create.sql

AFTER LOAD EXECUTE alter.sql

Running the Migration

1. Stop the Zabbix server:

# systemctl stop zabbix-server

2. Run pgloader:

# pgloader zabbix-migrate.load

3. Ignore warnings, they’re fine.

4. Verify the results.

5. Remove MySQL-based Zabbix:

# yum remove Zabbix-server-mysql

6. Remove old front-end:

# yum remove zabbix-web-*

7. Install the PostgreSQL version:

# yum install zabbix-server-pgsql

8. Install the new web front-end:

# yum install zabbix-web-pgsql-scl zabbix-apache-conf-scl

9. Edit Zabbix server config:

# vi /etc/zabbix/zabbix_server.conf

10. Add database password:

DBPassword=zabbix

11. Remove old web config:

# rm /etc/zabbix/web/zabbix.conf.php

12. Set time zone again:

# vi /etc/httpd/conf.d/zabbix.conf

13. Restart services:

# systemctl restart zabbix-server httpd

14. Reconfigure the front-end for PostgreSQL.

15. Finally, stop MySQL:

# systemctl stop mariadb

Alternative Way Using CSV Dumps

Another method for zabbix migrate from mysql to postgresql involves dumping tables into CSV format:

mkdir /var/tmp/zabbix/CSV; chown -R mysql: /var/tmp/zabbix

for i in `find /var/lib/mysql/zabbix/ -type f | \

sed 's/\....$//' | sort | uniq | sed 's#/var/lib/mysql/zabbix/##'`; do

mysqldump --compact -c -e -n -t --compatible=postgresql zabbix $i > $i.sql ;

mysqldump -t -T/var/tmp/zabbix/CSV zabbix $i --fields-terminated-by=';' ;

done

Use PostgreSQL’s COPY command for imports:

$ psql zabbix

zabbix# COPY alerts FROM '/var/tmp/zabbix/CSV/alerts.txt' DELIMITER ';' CSV;

For tables that fail to import, modify the SQL dumps:

SET standard_conforming_strings = 'off';

SET backslash_quote = 'on';

SET escape_string_warning = 'off';

BEGIN;

-- your SQL dump here

COMMIT;

Finally, fix table ownership:

for i in `echo '\dt' | psql zabbix | grep 'public |' | \

awk -F\| '{ print $2 }' `; do

echo "ALTER TABLE public.${i} OWNER TO zabbix;" | psql zabbix;

done

And that’s it, zabbix migrate from mysql to postgresql is done cleanly, without missing a thing.

[If needed, Our team is available 24/7 for additional assistance.]

Conclusion

Before closing the chapter on MySQL, make sure Zabbix housekeeper has completed its cleanup. Check your logs for “housekeep.” Once done, you’re ready to enjoy the reliability of PostgreSQL, your zabbix migrate from mysql to postgresql journey is complete.