Skip site navigation (1) Skip section navigation (2)

Re: [DOCS] Migrating Oracle to PostgreSQL

From: Jean-Paul ARGUDO <jean-paul(dot)argudo(at)IDEALX(dot)com>
To: Makarov Gera x8521 <GMakarov(at)Citipower(dot)com(dot)au>
Cc: "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>,"'pgsql-docs(at)postgresql(dot)org'" <pgsql-docs(at)postgresql(dot)org>,"'pgsql-ports(at)postgresql(dot)org'" <pgsql-ports(at)postgresql(dot)org>,"'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>,"'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: Re: [DOCS] Migrating Oracle to PostgreSQL
Date: 2002-05-07 07:46:18
Message-ID: 20020507074618.GB8582@pastis (view raw, whole thread or download thread mbox)
Lists: pgsql-adminpgsql-docspgsql-generalpgsql-portspgsql-sql
> 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 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

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!!)
(by James Shannon, Ben Adida, and Don Baccus)

If you have some CONNECT BY statments, I really think the best way is to
use a sort_key column as explained here:
(note, this is still from OpenACS guys)

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

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

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. 


Jean-Paul ARGUDO                                IDEALX S.A.S
Consultant bases de données                     15-17, av. de Ségur                           F-75007 PARIS

In response to


pgsql-docs by date

Next:From: Marin DimitrovDate: 2002-05-07 13:49:42
Subject: Re: [ADMIN] [DOCS] Migrating Oracle to PostgreSQL
Previous:From: Wagner, ThomasDate: 2002-05-06 21:08:33
Subject: Documentation in German??

pgsql-sql by date

Next:From: Christian RishoejDate: 2002-05-07 11:03:19
Subject: Re: Optimizing the implementation of an optimized tree
Previous:From: Christopher Kings-LynneDate: 2002-05-07 02:00:24
Subject: Re: Optimizing the implementation of an optimized tree

pgsql-admin by date

Next:From: Rajesh Kumar Mallah.Date: 2002-05-07 07:46:42
Subject: Re: Add a month
Previous:From: Gaetano MendolaDate: 2002-05-07 07:29:55
Subject: Add a month

pgsql-ports by date

Next:From: Dmitry A. MordovinDate: 2002-05-07 12:53:10
Subject: Complete install
Previous:From: Christian GerdesDate: 2002-05-06 15:36:35
Subject: qnx port

pgsql-general by date

Next:From: jonDate: 2002-05-07 08:09:38
Subject: SQLData, PostgreSQL and typemap woes
Previous:From: Bertin, PhilippeDate: 2002-05-07 06:34:12
Subject: Re: IF- statements in a rule's 'DO INSTEAD SELECT ...'- statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group