Re: Online Oracle to Postgresql data migration

From: "Dawid Kuroczko" <qnex42(at)gmail(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "Josh Harrison" <joshques(at)gmail(dot)com>, "General postgres mailing list" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Online Oracle to Postgresql data migration
Date: 2008-01-16 18:31:24
Message-ID: 758d5e7f0801161031g1052cb0h78f17dbc79f75cd5@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Jan 11, 2008 7:14 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
> On Jan 11, 2008 12:02 PM, Josh Harrison <joshques(at)gmail(dot)com> wrote:
> > Hi
> > We have an Oracle production database with some terbytes of data. We wanted
> > to migrate that to Postgresql (rigt now...a test database and not
> > production) database.
> > What are the good options to do that?
> > Please advise me on where to look for more information on this topic
>
> You're going to need to use your brain for a fair portion of this,
> because how you use oracle will be just different enough from everyone
> else that no boxed solution.
>
> You have two steps to work on. The first is the DDL, to create
> equivalent tables in pgsql as in oracle, the second is to migrate over
> your data.
>
> I've generally done the ddl conversion by hand in an editor, and
> migrated data over with some scripting language like perl or php.

If you are migrating terabytes don't use perl. I did some experimental
"for fun" migration some time ago and DBD::Oracle worked remarkably
slow... What you need is to get a program which will export data
from Oracle as CSV. As far as I know Oracle does not provide such
a tool (though it will import CSV happily through sqlldr),
but you can Google out a C-code which does just that. I don't remember
where I left if... :-(

From that, you just need to stream CSV into PostgreSQL's COPY
command. It worked FAST. Really.

And be wary of data types conversion.

Regards,
Dawid

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dave Page 2008-01-16 18:37:01 Re: Sun acquires MySQL
Previous Message Dirk Riehle 2008-01-16 18:25:45 Re: Sun acquires MySQL