Migrating from MySQL to PostgreSQL

When you come, like me, from a LAMP background, using MySQL as backend for DBMail seems natural.

As your database grows, little things at the edge of your mind start nagging you, or you've grown to like and appreciate PostgreSQL's more mature feature set. Or maybe you just dis-like Oracle.

Migrating from MySQL to PostgreSQL is no simple feat. Typically, MySQL is very lax when it comes to accepting encoded strings. And this will bite you when you try to load a SQL dump from MySQL into PostgreSQL. And bite you hard it will. Especially if your DBMail database, like mine, dates back many years.

I started working on some migration script that lives in contrib/sql2sql/mysql2pgsql.sh. It's old and unfinished, because last time I tried to migrate my main dog-food installation, I failed.

But those little things kept nagging at me, so I started anew. And this time I was successful.

The mysql2pgsql.sh script will now simply work, though you will have to install a pre-requisite (py-mysql2pgsql), and edit the yaml file included in the .../sql2sql/ directory.

You will also want to shutdown your email services, because the export/import is not atomic.

happy migration!

This entry was posted by Paul J Stevens on Thursday, September 13. 2012 at 15:48. and is filed under database. You can leave a response, or trackback from your own blog.

Trackbacks

Trackback specific URI for this entry

    No Trackbacks

Comments

Display comments as (Linear | Threaded)

    No comments


Add Comment


Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA