Re: 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.

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