Migration MySQL Zabbix to PostgreSQL

[jwatson]

Hi Everyone,

We are looking at deploying a brand new zabbix server for our environment. We currently have Zabbix 4.0.11 on Ubuntu 18.04 LTS. One of the main changes is to use PostgreSQL rather then MySQL. Also this is going from one physical server to another.

Could someone give me some guidance of how to migrate the database (MySQL) on one server to the new server which will have the PostgreSQL packages?

Kind Regards,

James

[Atsushi]

Past posts.

https://www.zabbix.com/forum/zabbix-help/375203-migration-from-mysql-to-postgres

[kloczek]

To be honest migration from MySQL to PostgreSQL is a bit pointless.
MySQL is way better suited as zabbix DB backednd because:
- it has much less functionalities beyond SQL92 spec which is using (only) zabbix
- MySQL has way better partitioned tables support
- MySQL provides away more usable metrics about SQL engine state

[kloczek]

You should add "in my opinion".
If someone knows better PostgreSQL/Oracle/DB2 or others the reason is clear.
Pushing someone to MySQL, because you using it is bad. Especially if you don't know reasons to move.
Also first 2 min of "Zabbix Top 5 Mistakes" is about that: https://www.youtube.com/watch?v=syNGgogf8W0

Sorry this is not my opinion but objective facts.
If you prefer PostgreSQL and you want to change to it only because that reason that decision will base on your skills and knowledge and not on objective technical facts.
In that case it is like "nose is for tabaco and not tabaco for nose".
If you are not able to adapt to use better/faster/more powerful technologies that is always your problem.

Your approach will be working up to some threshold. Above it you will be forced to pay more for for example hardware.
Only thing is that wit time hardware is more and more powerful and THIS allows use longer approach which you prefer.

Other thing is that knowing details of exact SQL engine is not enough!!
Always you must be able correctly recognise type of the workload.
If exact DB specialist didn't work before with zabbix as DB client with PostgreSQL you have VERY limited abilities to recognise what that type of client demands from engine.
All because (again) relatively short list of metrics provided by PostgreSQL.
In other words it is kind of catch 22 .. you know SQL engine X (and nothing more) and you have to tune it for workload Y but you don't have enough input data about state of the SQL engine to tune it correctly.

And some additional comment about first point of the "Zabbix Top 5 Mistakes".
Zabbix DB backend needs to be tuned for low latency READ operations. Zabbix does a lot of inserts and update queries and not everyone knows that all those operations depends on low latency READ I/Os because before something will be written or updated some data needs to be first red to locate WHERE to write new data or update existing rows.
More data is flowing in and out than more important is compression of the data. Compression on storage layer (use ZFS or btrfs with transparent compression), on DB engine layer (using columnar compression with Oracle DB which sometimes allow compress data with +20 times compression ratio) or kind of new compression of the data series which now offers zabbix +4.2.

Just to show you something how that aspect is important. Only yesterday I've opened new zabbix issue related to that new possibilities https://support.zabbix.com/browse/ZB...6?filter=16951

[kloczek]

Simple questions. Why Zabbix dev team don't suggest MySQL as best backend? Are you suggesting that they are hiding something or, worse, lying in the "Top 5 Zabbix mistakes"?

BTW.
To you issue. How to distinguish missing data in DB due to lack of data (no monitoring) and no data changes? There is no way. Use "throttling" thoughtfully. And as you know, classical graph draw only data that exist in db, graph from dashboard can do more - treat missing data as: none, connected (no change), treat as 0.

I'm not suggesting anything.
Generally this video is OK. Only few details are not enough well described/worded. Only this and nothing more.
Generally as well everyone of us quite often prefer what we like or well knew.

Discarding not changed data in the series is very important technique. It works like the compression and if item data are sampled regularly (are not trapper/async items) still those data between physically written data points are well known.
This allows "on-the-fly" fill those gaps of the data on using those data on for example forecast triggers or when they are used as input data of the the other calculated items or functions like last(10m) or last (#5)

[alexandra.zayceva]

Agree with the above opinion, it's a bit strange to migrate from MySQL to Postgre, but one may have a reason.
the good thing about this is the fact that there are tool that can do it automatically like Ispirer MnMTK Migrate MySQL to PostgreSQL - Ispirer

[tinsmith1]

TimescaleDB would like to have a talk with you postgres haters, postgres is now better suited for timeseries data than any other SQL database.