Migration from MySQL to Postgres

[PetrSkyva]

Hello,

We want to upgrade Zabbix from Zabbix 3.2.11 to Zabbix 4.0.5 and when we are into it, we wanna change database from MySQL to Postgres. Thing is that it give me: Cannot upgrade database: the database must correspond to version 2.0 or later. Exiting ...

What i done:
On old Zabbix dumped whole database:
$ mysqldump -u root -psupersecretpassword zabbix | gzip -c > zabbix.mysql

On new Zabbix I've installed MySQL and Postgres, where first i import db to mysql:
$ mysql -u root -panotherpassword zabbix < zabbix.mysql
and start a zabbix-server, then in logs i can see it upgraded the Database to new one, then i verify that Zabbix 4 is working.

After that I created new database in postgres:
$ createdb -U postgres zabbix

And then use pgloader tool to migrate database:
pgloader mysql://root:superpassword@localhost/zabbix postgresql://postgres@localhost/zabbix
This completed without any error, and i verified it with sql:
select userid from users limit 1
which worked.

Then I switch from mysql to postgres by running:
$ yum remove zabbix-server-mysql zabbix-web-mysql
$ yum -y install zabbix-server-pgsql zabbix-web-pgsql
Then when i try to start the server i got response:
Cannot upgrade database: the database must correspond to version 2.0 or later. Exiting ...

From logs of Postgres I can see zabbix can authorize, do you have any experience or ideas how to solve this problem?
Mysql is working OK but postgres got some issues.

Regards,
Peter

[Atsushi]

Please check the schema of the migrated database using pgloader. Does it match the schema public or schema set in DBSchema in zabbix_server.conf?

I compared the database that was migrated from MySQL using pgloader and the database that I created newly on PostgreSQL. There seems to be a difference such as change of data type and default value of column of text type is not set. The critical thing is that the names of the primary keys do not match.
Even if you could run the Zabbix server for the time being, it is possible that you can not upgrade the database automatically when upgrading in the future.