Re: Oracle data -> PostgreSQL
От | Michael G. Martin |
---|---|
Тема | Re: Oracle data -> PostgreSQL |
Дата | |
Msg-id | 3D235DC5.2090806@vpmonline.com обсуждение исходный текст |
Ответ на | Oracle data -> PostgreSQL (leonardo.camargo@eds.com (Leonardo Camargo)) |
Список | 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 > > >
В списке pgsql-admin по дате отправления: