Issues during migration Zabbix 7.4.2 BD (MariaDB -> PSQL 17.x) using pgloader

[Jaceqp]

Hi there.

I've created new environment to fight my way through migration process. My current zabbix is 7.4.2 and recreated exact same version on my temporary vm's. Only difference is slightly different mariadb (10.11.15 vs 10.11.16).

I found migration tutorial here:

https://www.initmax.com/webinar/migrate-a-zabbix-database-from-mysql-to-postgresql/

Obviously adjusted repos to my exact zabbix version.

Before migrating mysql DB to postgres I shrinked it's size by manually trunking oldest db data in:

alerts
acknowledges
events
history
history_uint
history_str
history_text
history_log

trends
trends_uint

I think built in housekeeper didn't really do the job. Anyway I managed to reduce DB size from ~25G to just 2.1G (keeping data not older than 7 days). Thats fine since I'm not really concerned atm on keeping history anyways. Without shrinking mysql DB I got some crazy errors like buffers overfilled (perhapse quite normal) but also 'KABOOM' errors.

pgloader KABOOM! BROKEN-PIPE: Couldn t write to #<SB-SYS:FD-STREAM for socket 127.0.0.1:34172, peer: 127.0.0.1:3306 {100582A0D3}>: Broken pipe An unhandled error condition has been signalled:

My trunking method was to rename tables to xxx_old, creating new ones "LIKE" old ones and pumping rows not older than xxx timestamp then remove old ones.
After that zabbix still worked fine but something went wrong with events table - there were some dependencies in mysql logs showing missing events_old table.
So I restored all events*** tables from former backup and left it unshrinked that way. Warnings were gone.

OK. I promise u, back to migration itself
All procedure went pretty smooth - 0 errors but after data migration on ALTER TABLE step (running alter_table.sql) there were few warnings:

ALTER TABLE ONLY acknowledges ADD CONSTRAINT c_acknowledges_2 FOREIGN KEY (eventid) REFERENCES events (eventid) ON DELETE CASCADE;

psql:/tmp/zabbix-db-migration/alter_table.sql:132: ERROR:  insert or update on table "acknowledges" violates foreign key constraint "c_acknowledges_2"
DETAIL:  Key (eventid)=(14738788) is not present in table "events".
-----------------------------------------------------------------------

ALTER TABLE ONLY event_tag ADD CONSTRAINT c_event_tag_1 FOREIGN KEY (eventid) REFERENCES events (eventid) ON DELETE CASCADE;

psql:/tmp/zabbix-db-migration/alter_table.sql:158: ERROR:  insert or update on table "event_tag" violates foreign key constraint "c_event_tag_1"
DETAIL:  Key (eventid)=(537) is not present in table "events".
-----------------------------------------------------------------------

ALTER TABLE ONLY problem ADD CONSTRAINT c_problem_1 FOREIGN KEY (eventid) REFERENCES events (eventid) ON DELETE CASCADE;

psql:/tmp/zabbix-db-migration/alter_table.sql:159: ERROR:  insert or update on table "problem" violates foreign key constraint "c_problem_1"
DETAIL:  Key (eventid)=(537) is not present in table "events".
-----------------------------------------------------------------------

ALTER TABLE ONLY event_recovery ADD CONSTRAINT c_event_recovery_1 FOREIGN KEY (eventid) REFERENCES events (eventid) ON DELETE CASCADE;

psql:/tmp/zabbix-db-migration/alter_table.sql:165: ERROR:  insert or update on table "event_recovery" violates foreign key constraint "c_event_recovery_1"
DETAIL:  Key (eventid)=(12763150) is not present in table "events".
-----------------------------------------------------------------------

ALTER TABLE ONLY event_recovery ADD CONSTRAINT c_event_recovery_2 FOREIGN KEY (r_eventid) REFERENCES events (eventid) ON DELETE CASCADE;

psql:/tmp/zabbix-db-migration/alter_table.sql:166: ERROR:  insert or update on table "event_recovery" violates foreign key constraint "c_event_recovery_2"
DETAIL:  Key (r_eventid)=(12763217) is not present in table "events".
-----------------------------------------------------------------------

ALTER TABLE ONLY event_suppress ADD CONSTRAINT c_event_suppress_1 FOREIGN KEY (eventid) REFERENCES events (eventid) ON DELETE CASCADE;

psql:/tmp/zabbix-db-migration/alter_table.sql:208: ERROR:  insert or update on table "event_suppress" violates foreign key constraint "c_event_suppress_1"
DETAIL:  Key (eventid)=(10376152) is not present in table "events".
-----------------------------------------------------------------------​

So still some issues related to 'events' I suppose.
Despite that my zabbix seem to work yet I'm not sure it's a good idea to leave it just the way it is.
I'm also considering wiping some tables clean (if needed or just to ensure data consistency - please feel free to guide me through). Obviously my main "no-touch"ies are hosts, users, templates,images etc. I'd like to keep my objects intact.
I'm moving to postgres instead of mysql since mu future production environment preffers it. Also postgres 18 is yet not supported there - thats why I'm on 17.x atm.

Anyway - any suggestions much apreciated.

[cyber]

ERROR: insert or update on table "acknowledges" violates foreign key constraint "c_acknowledges_2"
DETAIL: Key (eventid)=(14738788) is not present in table "events".

ERROR: insert or update on table "event_tag" violates foreign key constraint "c_event_tag_1"
DETAIL: Key (eventid)=(537) is not present in table "events"

ERROR: insert or update on table "problem" violates foreign key constraint "c_problem_1"
DETAIL: Key (eventid)=(537) is not present in table "events"

All it says is, that you have no such event in event table, what this acknowledge, event_tag or problem wants... You removed data manually, that means those acknowledges, problems etc, what you try to import, reference now noneisten event...
Maybe you should reduce all the data retention periods from UI and let housekeeper do its job (not instant, may take some days to keep up, if reduced a lot)... So all related records get removed with the event removal...

[tim.mooney]

I agree 100% with Cyber.

How big is your events table? I can see using the "copy from old to empty table" procedure for any of the 5 history* tables, but I don't see why that would be useful or necessary for alerts, acknowledges, events, or the trends tables. Those shouldn't be all that big.

Assuming your housekeeper finishes in a reasonable amount of time with your MariaDB install, you can force it to run more frequently than hourly or whatever it's running at. If you know e.g. that every housekeeper run takes between 7 minutes and 7 minutes 30 seconds (or whatever for your environment), you can run it in a loop from the command line, with a long-enough delay between runs. See the documentation for the '-R' flag for zabbix_server.

Tim

[cyber]

Trends tables can be quite big.. I just copied over from one instance to another... trends tables .. ~60 million rows per month.. history tables 500M rows per day..:P Also auditlog table is damn huge, if you keep all of it for a year..:P
But with the 25G DB I would not even bother to shrink it..:P 1T... maybe, maybe not...