Re: MySQL -> Postgres migration tools?

On 23/01/2010 3:31 PM, Grzegorz Jaśkiewicz wrote:
> On Fri, Jan 22, 2010 at 7:15 PM, Erik Jones<ejones(at)engineyard(dot)com> wrote:
>> Hello,
>>
>> Given that the EU has approved Oracle's acquisition of Sun w/ MySQL it's fairly likely that there may be a number of people and companies looking to move from MySQL to Postgres in the coming months. Does anyone know of any good, current migration tools out there? A search for mysql on pgfoundry only turns up a few projects that haven't seen any recent updates...
>>
>
> The problem there is not that there are no such tools, but that the
> database might need a bit of refinement when you move away from such
> simple database engine as MySQL. Most often, either due to lack of
> knowledge, or sometimes because you are forced to - the mysql database
> won't utilise sometimes even basic features of postgresql (sequences
> would be one grand example).

An InnoDB `AUTO_INCREMENT' column under MySQL 5.x is pretty similar to a
sequence.

I increasingly think it's pretty safe to just
's/AUTO_INCREMENT/SERIAL/g'
in DDL. Apps that use MyISAM aren't going to be fussy about how it
works, and apps that use InnoDB get Pg-like behaviour anyway.

Modern (5.x-something-late) MySQL with the InnoDB storage engine is a
very different beast from MySQL 3.x/4.x or MySQL 5.x with MyISAM. It
still has some scary data-conversion habits and you need to turn on
several different kinds of strict modes and behave-standard-ways modes
to get it to play nice, but if you have and you're moving over to Pg you
shouldn't actually have too rough a time.

( Unless you use some of the MySQL features, like its upsert command
REPLACE or the INSERT ... ON DUPLICATE KEY UPDATE ..., that just have no
equivalent in Pg. Or you're relying on scary MySQL-isms like 0000-00-00
dates. ).

> So to summarise, it is two stage process really. And the first stage -
> migration, should be fallowed by the database and code refinement
> before even the first 'after migration' release. And I believe that
> there is numerous sources out there to help people with that. Plus, we
> are always trying to be helpful on the list.

That I tend to agree with. It's not just about converting your data and
schema. You *will* need to adjust apps with any non-trivial queries.
Even if you can hack it to work, you can often learn things as part of
the porting process that can improve your results on *both* databases.

--
Craig Ringer