RE : Oracle to PostgreSQL

"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