Thread: Migrating data from Oracle 9i to PostgreSQL

Migrating data from Oracle 9i to PostgreSQL

From
Date:
Hi :-)

I sure hope I'm asking in the right place...

Is there any automated or semi-automated way of migrating data from Oracle database (9.2.0.4) to PostgreSQL one?

I only need the data, nothing else (not stored procedures, indexes, etc.), the relations are pretty simple, for
example:

CREATE TABLE EMAIL
(
  UNID             VARCHAR2(32 BYTE)            NOT NULL,
  SUBJECT          VARCHAR2(4000 BYTE),
  SEND_FROM        VARCHAR2(255 BYTE),
  PRODUCT_CASE_ID  VARCHAR2(32 BYTE),
  RECEIVE_DATE     DATE,
  SEND_TO          VARCHAR2(255 BYTE),
  SUBMITED_BY      VARCHAR2(255 BYTE),
  COPY_TO          VARCHAR2(255 BYTE),
  SEND_BY          VARCHAR2(255 BYTE),
  PUB              INTEGER                      DEFAULT 0                     NOT NULL,
  CONTENT          CLOB                         DEFAULT empty_clob(),
  RECIPIENT        VARCHAR2(255 BYTE)
)

I will be very grateful for any suggestions.

Best regards,
Maciej Zieba





Re: Migrating data from Oracle 9i to PostgreSQL

From
"Mikael Carneholm"
Date:
We have used the pro*c program found in this discussion (for the exact
same purpose):
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:45902
0243348

It is *blazingly* fast, and creates CSV formatted files suited perfectly
for postgresql's COPY command. Dumping 40Gb of data, sending it over the
network and importing it into postgres takes about an hour and a half
with fsync=true on a single SCSI disk using ext3 mounted with the
data=writeback and noatime options (using a SCSI controller with a
battery backed cache). The bottleneck here is not the dump utility but
the single SCSI disk on the postgres host, so with more disks on the
postgres host you'll probably get even shorter migration time.

- Mikael


-----Original Message-----
From: krokodylek@tenbit.pl [mailto:krokodylek@tenbit.pl]
Sent: den 4 april 2006 16:02
To: pgsql-general@postgresql.org
Subject: Migrating data from Oracle 9i to PostgreSQL


Hi :-)

I sure hope I'm asking in the right place...

Is there any automated or semi-automated way of migrating data from
Oracle database (9.2.0.4) to PostgreSQL one?

I only need the data, nothing else (not stored procedures, indexes,
etc.), the relations are pretty simple, for example:

CREATE TABLE EMAIL
(
  UNID             VARCHAR2(32 BYTE)            NOT NULL,
  SUBJECT          VARCHAR2(4000 BYTE),
  SEND_FROM        VARCHAR2(255 BYTE),
  PRODUCT_CASE_ID  VARCHAR2(32 BYTE),
  RECEIVE_DATE     DATE,
  SEND_TO          VARCHAR2(255 BYTE),
  SUBMITED_BY      VARCHAR2(255 BYTE),
  COPY_TO          VARCHAR2(255 BYTE),
  SEND_BY          VARCHAR2(255 BYTE),
  PUB              INTEGER                      DEFAULT 0

NOT NULL,
  CONTENT          CLOB                         DEFAULT empty_clob(),
  RECIPIENT        VARCHAR2(255 BYTE)
)

I will be very grateful for any suggestions.

Best regards,
Maciej Zieba