PostgreSQL Discussion - Migrating data from Oracle 9i to PostgreSQL

Author

Migrating data from Oracle 9i to PostgreSQL

krokodylek@tenbit.pl

2006-04-04, 9:28 am

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Mikael Carneholm

2006-04-05, 8:25 pm

We have used the pro*c program found in this discussion (for the exact
same purpose):
http://asktom.oracle.com/pls/ask/f?...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 & #91;mailto:krokodyle
k@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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links