Migrating Zabbix DB from Mysql to PostgreSQL

[elesouef]

Hi,

I'm planning to migrate my zabbix 1.8.6 database from Mysql 5.0 to PostgreSQL 9.1. The database is 10G large.

I don't really know how to transfer all the configuration *and* history, trends and alerts.

Do you have some advices ?

Thanks very much.

[Axilla]

mysqldump -uuser -ppassword zabbix > filename.sql

[dougbee]

I believe you want to create an empty zabbix database and then import the MySQL dump into that empty DB. If I recall correctly, I created the "zabbix" user within Postgres, giving it the ability to create databases.

On postgres server, running as user "postgres" or whatever runs the postgres process:

echo "create database zabbix" | psql template1 -U zabbix
cat create/schema/postgresql.sql | psql zabbix -U zabbix

On mysql server:
mysqldump -c -e -t --compatible=postgresql --no-create-info --skip-quote-names --skip-add-locks zabbix > zabbix.dmp

Back on postgres server:
cat zabbix.dmp | psql -U zabbix zabbix

Hope that helps. Don't be surprised if the resultant MySQL dump file is quite small, as the indexes are not dumped and will get rebuilt when importing into Postgres.

[elesouef]

Hi,

And thanks for your replies.

Axilla : I forgot to say in my question that I fear some incompatibilities between mysql and pgsql.

dougbee : thanks, I'm testing it today.

[elesouef]

The export went very well. But the import showed many many warnings such as :

2011-11-18 00:59:47 CET HINT:  Use '' to write quotes in strings, or use the escape string syntax (E'...').
2011-11-18 00:59:47 CET WARNING:  nonstandard use of \' in a string literal at character 563

and some errors such as :

2011-11-18 00:59:47 CET ERROR:  invalid byte sequence for encoding "UTF8": 0xe97461

Is there some things to do to prepare the mysqldump in order to avoid these ?