Re: Migration from MySQL to PostgreSQL : Datatypes?

Jasen Betts wrote:
> On 2010-12-23, Bill P. <maxarbos(at)yahoo(dot)com> wrote:
>> --0-1401805872-1293124338=:85107
>> Content-Type: text/plain; charset=us-ascii
>>
>> Hello,
>>
>> What would be the best way to deal with migrating a database structure and data
>> from a mysql db to postgres when there are dataypes in the mysql tables that are
>> not present, and thus throw errors using the current migration wizard, in
>> postgres?
>>
>> If i leave these few tables out on first import, many of the other tables that
>> these few key back to dont get created either.
>>
>> I found a program called easyfrom, but it's over $200 and only runs on windows.
>> Navicat, which i have, doesnt seem to have any provision to change the datatypes
>> on structure sync from one to the other types of db.
>>
>> Any suggestions would be great.
>> thanks.
>
> when I had to migrate an (mdbtools) Access dump. I was able to do all
> that was needed using sed.
>
> (But if you don't speak regex that's probably not going to work for
> you, it should be equally possible with any general purpose
> programming language)
>
> Is there any sort of guarantee, for that $200 tool, paying for it
> might be better than reinventing your own. That translator will
> probably work fine under wine. Ask the vendor, if they don't know,
> offer to test it for them: you might get a discount!

There are a number of tools available to translate schema between
different engines, both FLOSS and commercial. The simple problem with
all of them is that they make some basic assumptions about how to
convert those data types that don't have a direct correspondence between
the two engines. Those assumptions will *not* produce an efficient
schema for the new engine in all cases. Furthermore, even if there is a
comparable type, there is no guarantee that the actual use of the
individual fields will work well with the results. Therefore, it is
always necessary to examine the translation offered to see if it will
actually work in the new environment. For large volumes of data, trial
conversions from a recent backup, with thorough functional and
performance testing are an absolute requirement before committing
production systems.

We do all of that testing just for a major version upgrade even without
a change in engines. We don't want our clients discovering problems
after we update their servers.

Bob McConnell
N2SPP