Postgres Pro
Downloads
Home   >   mailing lists

Thread: transfer data from oracle to postgres

transfer data from oracle to postgres

From
s
Date:
Is there an easy way to transfer DATA from an oracle database to a
postgres database (tables are already created)?  As far as I can tell
the oracle export creates a binary file.  I could write a script, but
it would be a bit tedious.  I imagine it has already been done?

I searched the archives and docs and the web, but the archives are
coming up quite right on my browser and I didn't find any good
suggestions.

thanks,

Sarah
smarie@ekno.com

____________________________________________________________________________
Lonely Planet's ekno - more than a phonecard
Get ekno before you go!
http://www.ekno.lonelyplanet.com

Re: transfer data from oracle to postgres

From
Justin Clift
Date:
Hi Sarah,

These is some information about converting from Oracle to PostgreSQL
here:

http://techdocs.postgresql.org/

On the front page there is a section called "Converting from other
Databases to PostgreSQL" and there is information there about converting
from Oracle to PostgreSQL.

Under the category of "Non-specific", there is a link to a conversion
guide by the pgAdmin development team which might also prove to be
useful (not sure).

Hope that helps.

:-)

Regards and best wishes,

Justin Clift


s wrote:
>
> Is there an easy way to transfer DATA from an oracle database to a
> postgres database (tables are already created)?  As far as I can tell
> the oracle export creates a binary file.  I could write a script, but
> it would be a bit tedious.  I imagine it has already been done?
>
> I searched the archives and docs and the web, but the archives are
> coming up quite right on my browser and I didn't find any good
> suggestions.
>
> thanks,
>
> Sarah
> smarie@ekno.com
>
> ____________________________________________________________________________
> Lonely Planet's ekno - more than a phonecard
> Get ekno before you go!
> http://www.ekno.lonelyplanet.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Re: transfer data from oracle to postgres

From
"Graeme Merrall"
Date:
>
> Is there an easy way to transfer DATA from an oracle database to a
> postgres database (tables are already created)?  As far as I can tell
> the oracle export creates a binary file.  I could write a script, but
> it would be a bit tedious.  I imagine it has already been done?
>
> I searched the archives and docs and the web, but the archives are
> coming up quite right on my browser and I didn't find any good
> suggestions.

There's a ora2pg script in the postgres contrib directory which you can use.
A more updated versionis available from http://www.samse.fr/GPL/ora2pg/.
It's pretty good but it does not do CLOB/BLOB data and it doesn't see
synonyms either.

Cheers,
 Graeme

Re: transfer data from oracle to postgres

From
Gunther Schadow
Date:
s wrote:

> Is there an easy way to transfer DATA from an oracle database to a
> postgres database (tables are already created)?  As far as I can tell
> the oracle export creates a binary file.  I could write a script, but
> it would be a bit tedious.  I imagine it has already been done?
>
> I searched the archives and docs and the web, but the archives are
> coming up quite right on my browser and I didn't find any good
> suggestions.


There is the ora2pg perl script. But I chose to do this manually.
It's easy to do some Oracle metadata queries and create statements
that suck the entire table. You could do that even in Oracle SQL*Plus
if it wasn't so stupid about trying to format an ASCII table, etc.
In the end the delimiter-escape problem forced me to just make a
quick Java/JDBC program. You can stream the reocrds right into a
pgsql COPY FROM command. Streaming is useful if your tables are
BIG (mine are.) I also put some counter in the middle that shows
me the progress. I noticed that PostgreSQL could pump data in as
fast as I could read it from the Oracle server. 300 to 1500 records
per second was the normal range.

regards
-Gunther




--
Gunther Schadow, M.D., Ph.D.                    gschadow@regenstrief.org
Medical Information Scientist      Regenstrief Institute for Health Care
Adjunct Assistant Professor        Indiana University School of Medicine
tel:1(317)630-7960                         http://aurora.regenstrief.org