Is it possible to convert from MySQL to Postgresql?

[mike_k]

I have a zabbix 1.4.5 installation running with mysql 5.0.51 database. Is it possible to switch to PostgreSQL 8.3.1?
I'd like to use sql dumps, as xml export/import is not powerful enough yet.

[nelsonab]

It's been done by others, but it's not always simple. I understand there are some performance penalties in doing so. You'll likely need to write a conversion script for some of the subtle differences between the two, either that or hand edit the MySQLDump output.

Now I think more about it, you can have MySQL dump the data and the schema separately. This route might be easier. You would then import the raw schema files from Zabbix then import the data files from the MySQLDump. The Data files are Tab delimited by default I believe and are dumped in the order the data is found in the table, no schema information is in the data file.

Hopefully this helps.

[xs-]

Converting between databases is, for a zabbix db, a relative easy task.

Regardless of the database brand, the db structures are identical except 2 tempt tables which are supposed to be empty when zabbix is not running.

1. setup a new db with the schema only, do NOT import the data.sql (specially when going TO postgresql). Both database schema's must be the same
2. Stop zabbix_server!
3. migrate data
4. modify configs of zabbix_server and web frontend
5. Do basic checks with the webfrontend before starting zabbix_server

Special note on migrating the data.
- You will need to write a script which selects records from db 1 and inserts them in db 2. Code something in perl/python/php/whatever. These are 1on1 select->inserts as all tables are exactly the same and no auto_increments / sequences are used.

- dont delete your old database (duh) if, after tests / trials, the old database was faster, you can easily go back.

Special note on migrating from mysql to postgresql. postgresql gives crappy performance for a zabbix instance due to the insane amount of inserts / deletes (index polution). Mysql handles this differently.
I started with postgresql and switched to mysql5 after reaching 700+ hosts since postgres8 needed reindexing on a daily basis. (just my 2 cents).

Good luck migrating!