Thread: 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@postgresql.org)
>

> 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