How do I migrate easily from MySQL to PostgreSQL?

Question

I'd like to migrate an existing MySQL database (around 40tables, 400mb data) to Postgres before it gets bigger. I searched the web and tried some migration-scripts (some of them can be found here). None of them works seamlessly - if it would be just a few glitches I had to fix manually, it wouldn't be a problem, but the resulting dumps don't look like valid PostgreSQL at all.

Did anybody succeed in migrating a production table without using a full workday - is there an easy solution to that problem?

Note: I also would consider commercial products (as long as pricing is still feasible).

Answer 1

In spite of SQL being a standard, it's not full featured enough to do without each server software implementing extensions. The translation from MySQL to PostgreSQL is not simple, unless your schema is trivial. Automated translation scripts will only get you so far.

The very best approach would be to hand translate the schema, and then write your own transfer scripts for the data itself. You should also write verification scripts to make sure the schema and data come over correctly.

This isn't a cop-out answer. If your database is important enough to migrate then it's important enough to spend some time on yourself. In the end you would spend at least as much time figuring out the quirks and subtle messes than an automated migration script would cause as in the time to migrate the data yourself. But doing it yourself you have the chance to take advantage of features in PostgreSQL that aren't present in MySQL, as well as the chance to make the kinds of improvements that only come from having the chance to do something a second time.

Bite the bullet and do it.