MySql to PostgreSql migration

My experience with MySQL -> Postgresql migration wasn't really pleasant, so I'd have to second Daniel's suggestion about CSV files.

In my case, I recreated the schema by hands and then imported all tables, one-by-one, using mysqldump and pg_restore.

So, while this dump/restore may work for the data, you are most likely out of luck with schema. I haven't tried any commercial solutions, so see what other people say and... good luck!

UPDATE: I looked at the code the process left behind and here is how I actually did it.

I had a little different schema in my PostgreSQL db, so some tables were joined, some were split. This is why straightforward import was not an option and my case is probably more complex than what you describe and this solution may be an overkill.

For each table in PG database I wrote a query that selects the relevant data from MySQL database. In case the table is basically the same in both databases, and there are no joins it can be as simple as this

select * from mysql_table_name

Then I exported results of this query to XML, to do this you need to run it like this:

echo "select * from mysql_table_name" | mysql [CONNECTION PARAMETERS] -X --default-character-set=utf8 > mysql_table_name.xml

This will create a simple XML file with the following structure:

<resultset statement="select * from mysql_table_name">  <row>    <field name="some_field">field_value</field>    ...  </row>  ...</resultset>

Then, I wrote a script, that produces INSERT statement for each row element in this XML file. The name of the table, where to insert the data was given as a command line parameter to this script. Python script, in case you need it.

These sql statements were written to a file, and then fed to psql like this:

psql [CONNECTION PARAMETERS] -f FILENAME -1

The only trick there was in XML -> SQL transformation is to recognize numbers, and unquote them.

To sum it up: mysql can produce query results as XML and you can use it.

It's a bit more complicated than that. There is plenty of documentation here:

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL

There, you'll also find conversion scripts.

In my rather simple case (30 tables, 10000 records), I used a perl script:

http://pgfoundry.org/frs/?group_id=1000198

It chugged through the mysql dump file and produced a pg dump file, with the following issues.

I was importing to Heroku so I used their pgbackups plugin which worked almost flawlessly.

Issues to watch for

1. Boolean data types. MySQL stores these as 0 and 1. PostGreSQL stores them as t and f. Watch that the booleans dont get migrated as integers.
2. Auto incrementing IDs. You may find your ids start counting again from 1. You'll get errors like this: "duplicate key value violates unique constraint ...". It's easy to fix, but watch out for it.