Re: Mysql to Postgresql converter

On Jan 25, 2013, at 3:10 PM, Fred Moyer <fred(at)redhotpenguin(dot)com> wrote:

> Hello,
>
> I've been fighting a Mysql database recently, and the few tests I've
> run so far have shown a significant query performance improvement with
> a mysql database table that was converted to PostgreSQL.
>
> So I'm wondering if anyone knows of a magical program that I can hand
> my database credentials to, go get a cup of coffee, and come back to
> Postgresql database which was migrated from the Mysql database. I've
> seen a few Rails based conversion utilities on Github, but I'd prefer
> a command line application.

It's generally not too difficult to do manually - rewrite the schema a little,
as needed, then dump from mysql in a portable format.

But there are some tools.

https://github.com/ahammond/mysql2pgsql is one, and searching for my2pg
will find some others. There are also several general database-to-database
converters, like SQL::Translate.

There's also a foreign data wrapper that allows you to access mysql
tables from postgresql, which might be useful:
http://wiki.postgresql.org/wiki/Foreign_data_wrappers#mysql_fdw

and some other maybe useful links, from the #postgresql docbot:

http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL
http://www.in-nomine.org/~asmodai/mysql-to-pgsql.html
http://pgfoundry.org/projects/mysqlcompat
http://www.metatrontech.com/wpapers/mysql2postgresql.pdf

Cheers,
Steve