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.gogala@vmsinfo.com>
To: Bill P. <maxarbos@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.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