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.
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.
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?
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.
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?
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 ?
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?
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 500000 but the dbversion is 405000. Any idea regarding this? Any help would be appreciated. thank you.
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.
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?
Probably so that you can have basic base structure out of the way. history and trends is bigger tables and are more prone to breaking (timeouts, run out of ram, disk space etc.)
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.
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: www.kostolansky.sk/posts/upgrading-to-postgresql-12/
Make sure you are using the latest version of pgloader, the adsrc column was deprecated and dropped in postgres 12. pgloader 3.6.2 and onwards has the compatibility fix to support this change.
► Grab your Monitoring Love Merchandise - teespring.com/stores/monitoring-it
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.
Thank you!
INCLUDING ONLY TABLE NAMES MATCHING ~/history.*/, ~/trend.*/;
Books About Zabbix
Amazon COM
► Zabbix 4 Network Monitoring 3rd Edition amzn.to/2Ra7XDE
► Zabbix Network Monitoring 2nd Edition amzn.to/2DQLWqI
Amazon UK
► Zabbix 4 Network Monitoring amzn.to/2G7BCey
► Mastering Zabbix - Second Edition amzn.to/306Ru86
Amazon DE
► Zabbix 4 Network Monitoring amzn.to/2FYg3NU
► Zabbix Cookbook amzn.to/3iXuqQK
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.
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?
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.
Thank you
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?
Super !! All working; thanks man for this grat guidance !!!
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 ?
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?
So for new installation, should I right off the bat use PostgreSQL?
does it support spatial data?
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 500000 but the dbversion is 405000. Any idea regarding this?
Any help would be appreciated. thank you.
Can you post one video for migrating the zabbix server from CentOS to ubuntu
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.
No, sorry
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?
Probably so that you can have basic base structure out of the way. history and trends is bigger tables and are more prone to breaking (timeouts, run out of ram, disk space etc.)
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.
This is terrific, thanks a ton!
doesn't work for me
Why not?
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:
www.kostolansky.sk/posts/upgrading-to-postgresql-12/
I don't recall that there would be a column "adsrc" in zabbix
Make sure you are using the latest version of pgloader, the adsrc column was deprecated and dropped in postgres 12. pgloader 3.6.2 and onwards has the compatibility fix to support this change.
you are back, cool :)
thank you so much
You're welcome!