Migrate Mysql to postgres

[Wouaf]

Hi,

How can i do to migrate our Mysql zabbix database to postgresql?

Thanks

[xs-]

its relatively easy

prepare your postgresql database with the schema (not the data)

make a script (perl, python, php, whatever) which sequentially selects all from a mysql table and dumps that into the correct postgresql table.
(you might want to make it so its selects /inserts 10k rows a times to prevent memory issues).

There are commercial products which can also do this, but they assume they have to migrate the schema as well. Better steer clear of those.

[teferi]

its relatively easy

prepare your postgresql database with the schema (not the data)

make a script (perl, python, php, whatever) which sequentially selects all from a mysql table and dumps that into the correct postgresql table.
(you might want to make it so its selects /inserts 10k rows a times to prevent memory issues).

There are commercial products which can also do this, but they assume they have to migrate the schema as well. Better steer clear of those.

Or just use mysqldump --compact -c -e -n -t --compatitable=postgresql

[xs-]

well, i did a postgres to mysql migration once, so didnt look at mysqldump that closely. But the option is cool

[Alexei]

Or just use mysqldump --compact -c -e -n -t --compatitable=postgresql

Thanks for the hint. I wasn't aware mysqldump supports PostgreSQL friendly output!

[teferi]

Thanks for the hint. I wasn't aware mysqldump supports PostgreSQL friendly output!

It's not really that friendly, you'll still enounter warnings consearning string values, but most of them can be ignored.
I also used

sed "s/\\\'/\'\'/g" zabbix_mysql.sql > zabbix_pg.sql

but don't know really yet if it helps or makes it worser

[den_crane]

It's not really that friendly, you'll still enounter warnings

edit postgresql.conf
add option escape_string_warning = off
and postgresql suppress warnings

# mysqldump zabbix --compatible=postgresql --no-create-info --skip-quote-names --skip-add-locks > test.dmp

# cat test.dmp|psql -U zabbixuser zabbix
and I got 1 error ~~~ [ERROR: wrong byte encoding .... "UTF8": 0x89]
table images field (blob) not loading

[jsosic]

Hi guys,

I have done a recent migration of version 2.2.1 from MySQL to PostgreSQL. I've described the process on my blog, so instead of writing explanations again here, I'll just put a link:

http://jsosic.wordpress.com/2014/02/18/migrating-zabbix-from-mysql-to-postgresql/

[AnatolyUss]

Try <a href="https://github.com/AnatolyUss/FromMySqlToPostgreSql">FromMySqlToPostgreSql</a>.

This tool is feature-reach and easy to use. It maps data-types, migrates constraints, indexes, PKs and FKs exactly as they were in your MySQL db. Under the hood it uses PostgreSQL COPY, so data transfer is very fast.