Re: Online Oracle to Postgresql data migration

On Jan 11, 2008 7:14 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> On Jan 11, 2008 12:02 PM, Josh Harrison < joshques@gmail.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.