Re: RE : Oracle to PostgreSQL

Hi,

I need to port data from an Oracle 8.0 base to PostgreSQL 7.2.
I have scripts to create the base on Oracle and PostgreSQL (it is about 40
tables), but I need to port a great amount of data to PostgreSQL.
Can anybody help me?

Thanks,
Marco Roda

On Wed, 6 Aug 2003, Marco Roda wrote:

> Hi,
>
> I need to port data from an Oracle 8.0 base to PostgreSQL 7.2.
> I have scripts to create the base on Oracle and PostgreSQL (it is about 40
> tables), but I need to port a great amount of data to PostgreSQL.
> Can anybody help me?

There's a script in the contrib directory called something like ora2pg
that's been helpful to some folks.

EMS makes a migration program http://www.ems-hitech.com/pgsqlutils that
might help too. Haven't used it, but it's only $39 and I think they have
trial versions available.

"Marco Roda" <MarcoRoda(at)amdosoft(dot)com> wrote ..
> Hi,
>
> I need to port data from an Oracle 8.0 base to PostgreSQL 7.2. I have
> scripts to create the base on Oracle and PostgreSQL (it is about 40
> tables), but I need to port a great amount of data to PostgreSQL.
> Can anybody help me?

I never see any documents about migrating from Oracle8 to PostgreSQL,
but I've already done this kind of migration, here's my warrior's way :

- Make a SQL dump of the tables (structure + constraints + sequences +
data), and try to add this dump file into a PostgreSQL database (via
psql). You will probably need to do several search and replaces in the
SQL code in order to get it PostgreSQL compliant. But as PostgreSQL try
to follow the SQL norm, it shouldn't be very difficult.

- The most difficult step is to migrate the stored procedures. The
biggest work is to put PL/SQL packages into PL/PGSQL functions
(Unfortunately, PostgreSQL don't manages PL/PGSQL packages, that's still
in the TODO list). So your packages functions contacts.add() (for
example) will have to be renamed to contacts_add(); and you will also
have to patch all the clients that have to access this database.

This migration may be quite long, so leave the Oracle database running.
Keep a note of all the changes you've done to have your SQL dump valid
for PostgreSQL, and make a script (in Perl, PHP, or shell,...). When
everything is OK, stop writes to the Oracle DB (allows only select
queries), do the SQL dump (with the latests datas), execute your script
on the sql DUMP, do the migration and switch the users to the new
database. (Be sure to have the new clients ready before doing the big
jump).

The job of migrating from Oracle to PostgreSQL is a serious job (and
sometimes difficult) but the migration wins are really interesting.

I Hope this will help and that you will understand my english. :-)

Regards,

--
---------------------------------------
Bruno BAGUETTE - pgsql-ml(at)baguette(dot)net

What are the legal implications of copying Oracle's own PL/SQL
procedures code and porting them to PgSQL as you suggested?

Bruno BAGUETTE wrote:

>"Marco Roda" <MarcoRoda(at)amdosoft(dot)com> wrote ..
>
>
>>Hi,
>>
>>I need to port data from an Oracle 8.0 base to PostgreSQL 7.2. I have
>>scripts to create the base on Oracle and PostgreSQL (it is about 40
>>tables), but I need to port a great amount of data to PostgreSQL.
>>Can anybody help me?
>>
>>
>
>I never see any documents about migrating from Oracle8 to PostgreSQL,
>but I've already done this kind of migration, here's my warrior's way :
>
>- Make a SQL dump of the tables (structure + constraints + sequences +
>data), and try to add this dump file into a PostgreSQL database (via
>psql). You will probably need to do several search and replaces in the
>SQL code in order to get it PostgreSQL compliant. But as PostgreSQL try
>to follow the SQL norm, it shouldn't be very difficult.
>
>- The most difficult step is to migrate the stored procedures. The
>biggest work is to put PL/SQL packages into PL/PGSQL functions
>(Unfortunately, PostgreSQL don't manages PL/PGSQL packages, that's still
>in the TODO list). So your packages functions contacts.add() (for
>example) will have to be renamed to contacts_add(); and you will also
>have to patch all the clients that have to access this database.
>
>This migration may be quite long, so leave the Oracle database running.
>Keep a note of all the changes you've done to have your SQL dump valid
>for PostgreSQL, and make a script (in Perl, PHP, or shell,...). When
>everything is OK, stop writes to the Oracle DB (allows only select
>queries), do the SQL dump (with the latests datas), execute your script
>on the sql DUMP, do the migration and switch the users to the new
>database. (Be sure to have the new clients ready before doing the big
>jump).
>
>The job of migrating from Oracle to PostgreSQL is a serious job (and
>sometimes difficult) but the migration wins are really interesting.
>
>I Hope this will help and that you will understand my english. :-)
>
>Regards,
>
>
>

hi,

At 10:25 07.08.2003 +0200, Bruno BAGUETTE wrote:
>"Marco Roda" <MarcoRoda(at)amdosoft(dot)com> wrote ..
> > Hi,
> >
> > I need to port data from an Oracle 8.0 base to PostgreSQL 7.2. I have
> > scripts to create the base on Oracle and PostgreSQL (it is about 40
> > tables), but I need to port a great amount of data to PostgreSQL.
> > Can anybody help me?
>
>I never see any documents about migrating from Oracle8 to PostgreSQL,
>but I've already done this kind of migration, here's my warrior's way :
>
>- Make a SQL dump of the tables (structure + constraints + sequences +
>data), and try to add this dump file into a PostgreSQL database (via
>psql). You will probably need to do several search and replaces in the
>SQL code in order to get it PostgreSQL compliant. But as PostgreSQL try
>to follow the SQL norm, it shouldn't be very difficult.

I have good experiences with:

http://www.samse.fr/GPL/ora2pg/ora2pg.html

Rafal

Does anyone know the status of the Postgres-R project and when it may
fall in with a current PostgreSQL release? Thanks for the help

Kris