Обсуждение: 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@amdosoft.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@baguette.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@amdosoft.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@amdosoft.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