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

Re: Migration from MySQL to PostgreSQL : Datatypes?

From: "Bill P(dot)" <maxarbos(at)yahoo(dot)com>
To:
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 20:48:46
Message-ID: 194084.62246.qm@web65801.mail.ac4.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

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

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2010-12-23 22:27:21
Subject: Re: Migration from MySQL to PostgreSQL : Datatypes?
Previous:From: Mladen GogalaDate: 2010-12-23 17:50:10
Subject: Re: Migration from MySQL to PostgreSQL : Datatypes?

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