Migrate MySQL 8 to PostgreSQL 12, Zabbix 5.0

Environment check:

env|sort|grep ^PG

It can print:

PGHOST=10.133.112.87
PGPASSWORD=zabbix
PGPORT=7412
PGUSER=postgres

Create a user on different server but using same credentials:

createuser --host=10.133.253.45 --pwprompt zabbix

In case of fresh start, drop existing database:

dropdb --host=10.133.253.45 future

Create a database 'future' on different server but using same credentials, database belongs to postgres user 'zabbix':

createdb --host=10.133.253.45 -O zabbix future

https://cdn.zabbix.com/zabbix/sources/oldstable/4.2/zabbix-4.2.8.tar.gz Download stock Zabbix 5.0 schema:

maj=5.0
min=8
cd
curl -kL "https://cdn.zabbix.com/zabbix/sources/stable/$maj/zabbix-$maj.$min.tar.gz" -o zabbix-$maj.$min.tar.gz
curl -kL "https://cdn.zabbix.com/zabbix/sources/oldstable/$maj/zabbix-$maj.$min.tar.gz" -o zabbix-$maj.$min.tar.gz
tar -vzxf zabbix-$maj.$min.tar.gz
mv ~/zabbix-$maj.$min/database/postgresql/schema.sql /tmp

For the migration we must configure to allow inserting data in postgres database without respecting contraints:

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

Create alter statements:

grep ALTER /tmp/schema.sql > ~/alter.sql

config:

cd
cat << 'EOF' > pgloader.conf
LOAD DATABASE
FROM mysql://root:zabbix@10.133.80.228:3307/z42
INTO postgresql://zabbix:zabbix@10.133.112.87:5432/zabbixDB
WITH include no drop,
truncate,
create no tables,
create no indexes,
no foreign keys,
reset sequences,
data only
SET maintenance_work_mem TO '256MB', work_mem to '64MB'
ALTER SCHEMA 'zabbixDB' RENAME TO 'public'
BEFORE LOAD EXECUTE create.sql
AFTER LOAD EXECUTE alter.sql;
EOF

Install pgloader on RH7

wget ftp.postgresql.org/pub/repos/yum/testing/common/redhat/rhel-7-x86_64/pgloader-3.6.2-1.rhel7.x86_64.rpm

2020-06-03T13:38:20.198000+01:00 ERROR mysql: Failed to connect to mysql at "10.133.112.87" (port 3306) as user "root": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.

Error1:

2020-12-02T10:58:39.836000Z NOTICE Executing SQL block for after load
2020-12-02T10:58:43.677000Z ERROR Database error 23503: insert or update on table "event_tag" violates foreign key constraint "c_event_tag_1"
DETAIL: Key (eventid)=(585499) is not present in table "events".
QUERY:
ALTER TABLE ONLY event_tag ADD CONSTRAINT c_event_tag_1 FOREIGN KEY (eventid) REFERENCES events (eventid) ON DELETE CASCADE

Solution:

SELECT eventid FROM event_tag WHERE eventid NOT IN (SELECT eventid FROM events);

DELETE FROM event_tag WHERE eventid=585499;

DELETE FROM event_tag WHERE eventid NOT IN (SELECT eventid FROM events);

Error2:

2020-12-02T11:40:52.498000Z ERROR Database error 23503: insert or update on table "problem_tag" violates foreign key constraint "c_problem_tag_1"
DETAIL: Key (eventid)=(585499) is not present in table "problem".
QUERY:
ALTER TABLE ONLY problem_tag ADD CONSTRAINT c_problem_tag_1 FOREIGN KEY (eventid) REFERENCES problem (eventid) ON DELETE CASCADE

Solution:

--detect incosnsistencies
SELECT eventid FROM problem_tag WHERE eventid NOT IN (SELECT eventid FROM problem);

--solve particular message
DELETE FROM problem_tag WHERE eventid=585499;

--solve furure problems
DELETE FROM problem_tag WHERE eventid NOT IN (SELECT eventid FROM problem);