Re: Oracle data -> PostgreSQL - Mailing list pgsql-admin

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
>
>
>