Skip site navigation (1) Skip section navigation (2)

Re: Migration from MySQL to PostgreSQL : Datatypes?

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: "Bill P(dot)" <maxarbos(at)yahoo(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Migration from MySQL to PostgreSQL : Datatypes?
Date: 2010-12-23 22:27:21
Message-ID: AANLkTikDBLSi=eKrCMPpHKoGHibRRZwnLFavRi9J9sRp@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, Dec 23, 2010 at 3:48 PM, Bill P. <maxarbos(at)yahoo(dot)com> wrote:
> Thanks for all the responses so far.
>
> I can get the data in it seems with no problem, but I refuse to believe that
> I need create all the tables manually. Most of the tables will import and
> convert just fine, so oI dont think there is a need to do that.
>
> As for the ETL suggestion. I have been using talend to migrate and transform
> data imports, but the actual migration from mysql to postgres table
> structures are not something I am aware it can do.

Some object-relational-manager libraries (Hibernate, SQLAlchemy, etc.)
could probably be used to automate much of the process.

Sean

> I guess my main question would be is: is there a best practice to migrate
> db/schema tables of mysql to schema tables in postgres without having to
> manually do them one at a time?
> A migration tool like the one provided by enterprisedb would be great, IF it
> allowed me to change or fix errors as they occur instead of the entire
> process exiting.
>
> Thanks.
>
>
> ________________________________
> From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
> To: Bill P. <maxarbos(at)yahoo(dot)com>
> Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
> Sent: Thu, December 23, 2010 12:50:10 PM
> Subject: Re: [NOVICE] Migration from MySQL to PostgreSQL : Datatypes?
>
> Bill P. wrote:
>> 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.
>>
>>
> I don't know how complex your schema is, but I would create the objects
> manually and then use Perl scripts to copy the data. Copying shouldn't be a
> problem because MySQL supports things like this:
>
> SELECT *
> INTO OUTFILE '/tmp/emp.csv'
> FIELDS TERMINATED BY ','
> ENCLOSED BY '"'
> LINES TERMINATED BY '\n'
> FROM emp
>
> That should be picked up quite nicely by the COPY command from PostgreSQL.
>
>
> --
> Mladen Gogala Sr. Oracle DBA
> 1500 Broadway
> New York, NY 10036
> (212) 329-5251
> http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
>
>
>
>
>

In response to

pgsql-novice by date

Next:From: John DeSoiDate: 2010-12-24 02:41:51
Subject: Re: super beginner setting up postgresql for the first time
Previous:From: Bill P.Date: 2010-12-23 20:48:46
Subject: Re: Migration from MySQL to PostgreSQL : Datatypes?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group