MySQL Database Migration To PostgreSQL

Zabbix 5.0 Introduced TimescaleDB native compression which immediately raised many questions like how to migrate my database to PostgreSQL. Thing is that again, there is no one golden "Apply everywhere" solution. In strictly depends on your database size, performance of disks and server specs. For small databases, native GUI migration tools will be fully enough. For complex databases with a lot of tables and relations that may grow to multiple terabytes, in most cases you will have to work extra.

☕ In case if you want to support this content with coffee:

👋 SOCIAL MEDIA
📹 TWITTER - @RealDeimons

📫 Sign Up For Newsletter And Don't Miss Out on Anything

[DmitryLambert]

Books About Zabbix
Amazon COM

Amazon UK

Amazon DE

[robustictsolutions]

Thanks for the helpful video. successfully migrated from MariaDB10.4 to PostgreSQL12. At 16:19-16:40, while importing history and trends tables I just included the ones I excluded while migrating configuration tables, used the pgloader 'Include only' instead of excluding the many individual tables.

[guillermolos]

Hi Dmitry, quick question, I'm running zabbix 4.0 with mysql with table partitions(about 700GB) . which should be the best path to upgrade to posgress TimescaleDB and Zabbix 5.0LTS ?

[MiguelAngel-rerh]

Super !! All working; thanks man for this grat guidance !!!

[ogi]

Good video. One question if you migrate data to be able to use timescaledb plugin in postgresql do you turn on the plugin before migration or after the data has been migrated?

[janisritmanis]

Just successfully migrated one setup Centos7/MariaDB/PHP7.2/Zabbix 5.0.16 to PG12 with Timescale based on your instructions. Quite small server with about 20GB of DB running on slow HDDs. One thing I had to do is to add to the pgloader config files for historical/trend data migration "SET MySQL PARAMETERS net_read_timeout = '3600', net_write_timeout = '3600'". (not sure about the exact optimal values), but without them pgloader crashed with connection terminated errors from MariaDB side at random times (10min, 30min, 40min). Migration to Timescale was done after all data was already migrated to PG.

[sweetentertainment]

hi, thanks for the video. I have a question. After migration from mariadb which was connected with zabbix 4.0 the new postgresql database is not able to connect with zabbix 5.0 server. It is showing version mismatch. Which by default requires but the dbversion is 405000. Any idea regarding this?
Any help would be appreciated. thank you.

[manupmukherjee]

Thank you Dmitry for your effort. I have 1 Zabbix Server(5.0.2) with apache frontend and mariadb database, I have prepared another zabbix server(5.4) with NGINX front end and Postgres SQL, I have also installed and configured timescaledb on this server. I have about 150gb of database sitting in zabbix server(5.0.2) with maridb database, and I need to move that database into the new zabbix(5.4) with Postgres SQL, can I run pgloader without additional include and exclude or will I need to run pgloader with config as in your video? Your help will really be appreciated.

[kcsj]

So for new installation, should I right off the bat use PostgreSQL?

[rafissaifulin]

when I insert alter tables "cat altertable.sql | psql -Uzabbix zabbix" i get this:
ERROR: insert or update on table "problem" violates foreign key constraint "c_problem_1"
DETAIL: Key (eventid)=(71317) is not present in table "events".
why?

[guillaumebuttet]

Thanks Dimitry, I've successfully migrated. But had to do 2 things : Increase the CacheSize in the zabbix-server conf otherwise server doesn't start and manually import the zabbix.conf.php after the wizard.

[ChrisHale]

Thanks for the fantastic info. Any chance that the basic scripts for PGloader you setup are located anywhere? I realize they're not everything I'll need, but it'd be great to have them as a starting point for testing.

[onurbabur]

Dmitry Hello, I have migrated my build from mysql to postgre. In addition, I installed timescaledb. There is unsupport information of Zabbix 6.4 and timescaledb 2.10. How can I fix this problem? Or when will zabbix 6.4 be able to support timescaledb 2.10?

[polpavel]

It works. But I do not understand why you separate the history tables during migration and copy them later. There is no change in downtime during migration. Or do you start the server, and only then copy the historical data when the server is already running?

[prakashc]

Can you post one video for migrating the zabbix server from CentOS to ubuntu

[cenubit]

This method did not work if you want to migrate from MySQL to PostgreSQL 12. I execute pgloader config. I get ERROR: column "adsrc" does not exist at character 242. I see in PostgreSQL version page Remove obsolete. column (Peter Eisentraut)    How is it possible to fix it?
I currently install the second PostrgeSQL 11 server. Migration is done! Tomorrow I trying to update to PostgreSQL 13
Update:
Next, I updated POstgreSQL11 to 13. I used this manual: