Migrating Oracle to PostgreSQL

Hi there,

Has anyone migrated Oracle database to Postgres? If yes, could you please
help me - what
are the steps involved, complications you faced, any other info you think is
appropriate.
Thanks in advance.

Regards,

Gera Makarov
Oracle DBA
CitiPower Pty

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This email message has been swept for the presence of
computer viruses

CitiPower Pty. ACN 064 651 056
**********************************************************************

Hi,

Have a look at www.openacs.org. This is an open project for converting
the ACS (Oracle based) web system into an open source system based on
PostgreSQL (in the first place). I believe there is a migration document
somewhere in there.

Greetings,

Leif

On Thu, 18 Apr 2002, Makarov Gera x8521 wrote:

> Hi there,
>
> Has anyone migrated Oracle database to Postgres? If yes, could you please
> help me - what
> are the steps involved, complications you faced, any other info you think is
> appropriate.
> Thanks in advance.
>
> Regards,
>
> Gera Makarov
> Oracle DBA
> CitiPower Pty
>
>
>
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
>
> This email message has been swept for the presence of
> computer viruses
>
> CitiPower Pty. ACN 064 651 056
> **********************************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

On Thu, Apr 18, 2002 at 03:24:32PM +1000, Makarov Gera x8521 wrote:
> Hi there,
>
> Has anyone migrated Oracle database to Postgres? If yes, could you please
> help me - what
> are the steps involved, complications you faced, any other info you think is
> appropriate.
> Thanks in advance.

See:

http://techdocs.postgresql.org/

(Converting from other Databases to PostgreSQL)

Karel

PS. Why you send our question to _all_ PostgreSQL lists?!
--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

On Thu, Apr 18, 2002 at 03:21:46PM +0200, Leif Jensen wrote:
>
> Hi,
>
> Have a look at www.openacs.org. This is an open project for converting
> the ACS (Oracle based) web system into an open source system based on
> PostgreSQL (in the first place). I believe there is a migration document
> somewhere in there.

I participate in the OpenACS project, and I wrote the Oracle Porting
chapter on the PostgreSQL documentation hoping that it would help other
PostgreSQL users, drawing from the experience of working on OpenACS.

See the PostgreSQL PL/pgSQL documentation.

-Roberto

--
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
Don't worry, I'm go³ng tµ bäckup tüdäæ­!&%#~%

> Hi there,
>
> Has anyone migrated Oracle database to Postgres? If yes, could you please
> help me - what
> are the steps involved, complications you faced, any other info you think is
> appropriate.

Sorry to answer so lately!

I made some of this migrations. From Oracle 7 to 8i databases.

Basicaly, have a look at Gilles Darold's tool called ora2pg. You'll find
this tool under /contrib/oracle/ in PG sources. Or maybe, using a
distribution, you'll have to install postgresql-contrib package.
Depending the distribution you use, it may differ. For example, under
Debian, install :

postgresql-contrib - Additional facilities for PostgreSQL

It's a Perl script that is really operational now. look at
http://www.samse.fr/GPL/ for more stuff on ora2pg (it's Gilles'www)

Ora2Pg as ability to connect to your existing Oracle DB, get the schema,
translate it in PG, connect to PG, then put the schema in PG. Then, it
can also get the datas from Oracle and put in either in a flat file or
directly in the database.

I prefer working with flat file to make some tests at first, verifying
what ora2pg has done.

You'll never had any problem since your Oracle database doesn't use
esoterical Oracle datatypes.

I give you a hint: *NEVER* translate Oracle's NUMBER(4,0) into PG
NUMERIC(4,0) or you'll have performances problems. Just transalate
NUMBER(4,0) into INTEGER. PG is well known to work better with INTEGERs.
In fact INTEGER datatype in PG is the best compromise between space
needed on the hard drive and performances. With NUMBER(x,y) with y not
null, it's you to decide and making tests. Maybe you can multiply per
100 if y=2? and then be able to translate also in INTEGER? Or this is
maybe to costly for client-side applications (too much modify)...

Be aware that NUMERIC(x,y) in PG is very powerfull for atypic uses, for
example (18,9) ... and not that much for other uses. That's my point of
view.

I give you other Oracle 2 Pg links with much interest:

Open ACS links:

I understood they migrated from Oracle to PostgreSQL. They put many
sources and comments on this subject (thanks to Open ACS!!)

http://openacs.org/doc/openacs/html/oracle-to-pg-porting.html
(by James Shannon, Ben Adida, and Don Baccus)

http://openacs.org/

If you have some CONNECT BY statments, I really think the best way is to
use a sort_key column as explained here:

http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0000j6&topic_id=12&topic=OpenACS%204%2e0%20Design
(note, this is still from OpenACS guys)

The implementation they made is based on Miguel Sofer (a mathematician)
solution:

http://www.utdt.edu/~mig/trees.tar.gz

And finally, have a look at the bests technicals links for PG at
Justin'Cliff www: http:\\techdocs.postgresql.org

Don't hesitate in sending questions, we will answer as far as we know on
the subject. Please don't post at to many lists.

Thanks.

--
Jean-Paul ARGUDO IDEALX S.A.S
Consultant bases de données 15-17, av. de Ségur
http://www.idealx.com F-75007 PARIS

----- Original Message -----
From: "Jean-Paul ARGUDO"

>
> Sorry to answer so lately!
>
> I made some of this migrations. From Oracle 7 to 8i databases.
>
> Basicaly, have a look at Gilles Darold's tool called ora2pg. You'll find
> this tool under /contrib/oracle/ in PG sources. Or maybe, using a

has anyone successfully used it with 9i?

because it never worked for me - in fact I don't think the error had
anything to do with 9i, it looked more like the script fails to handle
certain cases properly

thanx,

Marin

----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "