Re: Oracle data -> PostgreSQL

We're currently in the process of migrating an e-commerce customer off a
24x7 Oracle system that includes standby databases,
production-to-development refresh processes, data warehousing, etc. Many of
the tables run in the millions of rows. One runs almost 20 million rows. Not
big in enterprise-class database terms but big enough that existing Perl
approaches fell over almost immediately. The ora2pg scripts in particular
had so many errors: bad datatype conversions, bad data conversions,
exceptionally poor performance on large tables, etc. that we eventually gave
up on them. Most errors were potentially repairable but fixing the
performance just wasn't happening and we got tired of chasing bugs and
wondering what was going to bite us next.

We found the best solution to be using Oracle procedures written in PL/SQL
to do the schema and data extractions. The process was much cleaner and
significantly (sometimes order-of-magnitude) faster. With the schema and
data properly extracted the load process can be managed more cleanly and the
old Oracle data is right there ready to archive to CD/etc. in a
non-Oracle-specific data format before you shut down Oracle for the last
time. After all, you don't want to have to find an old copy of Oracle to use
when you discover you might have had a data conversion problem. For example,
forgetting to set the NLS_DATE_FORMAT when you extract your date fields
thereby losing the timestamps. Been there, done that. Don't want to go there
again.

There are numerous other issues not mentioned in the existing online
documentation such as it is. Things like properly extracting lob and long
data, numerous variations in DDL, dealing with Pg's inability to skip
validation of foreign key constraints (serious time sink on conversion and
even one broken link causes failure), etc. We're working to create a package
of Oracle procedures and documenation for this process and hope to release
it in the future. In the meantime take the ora2pg and online documentation
as a simplistic starting point and expect to put some serious energy into
migrating an Oracle installation of any complexity. It *can* be done. But
it's not going to be as simple as firing up ora2pg and coming back in a
couple hours.

ss

Scott Shattuck
President/CEO
Technical Pursuit Inc.

----- Original Message -----
From: "Michael G. Martin" <michael(at)vpmonline(dot)com>
To: "Leonardo Camargo" <leonardo(dot)camargo(at)eds(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Sent: Wednesday, July 03, 2002 2:25 PM
Subject: Re: [ADMIN] Oracle data -> PostgreSQL

> If I were tasked to do it, I'd probably use PERL and DBI/DBD. Once you
> get all the Oracle tables converted and built in Postgres, you could
> write a PERL program to open a database descriptor to your oracle
> database and postgres database at the same time. Then, copy all the
> records in each table from one descriptor to the other.
>
> Here is a code snip I use to syncronize some tables across two postgres
> dbs, but one descriptor could easily be an oracle descriptor. $dbhM is
> the master descriptor, and $common::dbh is the local descriptor.
>
> As long as your data types are consistant across the tables, you
> shouldn't have too many problems.
>
> If you want to do two steps, you can always write a custom dump program
> for each table in some delimitted format from oracle, then write a
> loader to put the data back in. This may also be a better option if you
> are unable to access both databases at the same time.
>
> sub syncTable {
> #sync table from primary
>
> my $table=shift(@_);
> my $fromDate=shift(@_); #inclusive date to start
> my $fromDateStr="";
>
> if (defined $fromDate && $fromDate eq "") {
> undef $fromDate;
> }
>
> my $sth;
> if (defined $fromDate && $fromDate ne "") {
> $sth=$dbhM->prepare("select * from $table where date >= '$fromDate'");
> $fromDateStr="From Date $fromDate.";
> }
> else {
> $sth=$dbhM->prepare("select * from $table");
> }
> $sth->execute();
>
> if ($DBI::err) {
> warn ("Error processing request. $DBI::errstr");
> return;
> }
>
> my $totalRows=$sth->rows;
>
> my $numFields=$sth->{NUM_OF_FIELDS};
>
> print "Syncronizing table $table from $dbConfig::dbPrimaryHost
($totalRows rows. $numFields columns. $fromDateStr )\n";
>
> $common::dbh->{AutoCommit} = 0;
>
> if (! defined $fromDate) {
> # common::doSql("truncate table $table");
> common::doSql("delete from $table");
> }
> else {
> common::doSql("delete from $table where date >= '$fromDate'");
> }
>
> my $insertSql="insert into $table values (";
> for (my $i=0; $i < $numFields; $i++) {
> if ($i > 0) {
> $insertSql.=",";
> }
> $insertSql.="?";
> }
> $insertSql.=")";
>
> my $sthLocal=$common::dbh->prepare($insertSql);
>
> my $count=0;
> while (my @row=$sth->fetchrow_array()) {
> $sthLocal->execute(@row);
> $count++;
> if ($count % 1000 == 0) {
> print "$table $count / $totalRows records\n";
> }
> }
> $common::dbh->{AutoCommit} = 1;
>
> }
>
> --Michael
>
>
> Leonardo Camargo wrote:
>
> >Hi
> >
> >How do i migrate data from an Oracle db to a PostgreSQL db ?
> >
> >Is there an article, paper, site, howto, aboutm it?
> >
> >Any point would be helpful
> >
> >Thnx in advance.
> >
> >Kal
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: Have you searched our list archives?
> >
> >http://archives.postgresql.org
> >
> >
> >
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>