Re: Online Oracle to Postgresql data migration

On Friday 11 January 2008 13:44, Josh Harrison wrote:
> On Jan 11, 2008 1:22 PM, Erik Jones <erik(at)myemma(dot)com> wrote:
> > On Jan 11, 2008, at 12:14 PM, Scott Marlowe 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 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 had done this with the test database. For ddl generation I used xml/xsl
> and for data migration I used jdbc. I can get the ddl generated fine. With
> JDBC the data migration is a bit slow.
> My question is abt the data migration. Im not sure how to try this with an
> online oracle database. We are required to run both postgres and oracle
> database simultaneously for a couple of months (atleast till we decide
> whether we are going to shut down oracle for good !!!). Since the oracle
> database is a production database, It will have updates/inserts during this
> time. How do you manage that?
>

About a year ago we converted one of our clients multi-TB ODS systems built in
Oracle over to PostgreSQL. There's a case study about it you can get from the
Sun folks at
http://www.sun.com/third-party/srsc/resources/postgresql/postgre_success_dwp.pdf

Now, due to the size of the project, we had to run both the Oracle and
Postgres systems in parallel for several months. We kept the data up to date
using a slew of custom code, designed to replicate data from either the ODS
system or the OLTP system, depending on various technical and business
factors. My guess is that in your case, you'd want a mix of replicating data
from the current Oracle database and your application, as best possible.

Figuring out how you go about replicating the data is certainly easier if
you've have been through it before, but I don't think it is anything too
magical; we went through a number of different ideas and ended up using
multiple methods depending on the data involved. HTH.

--
Robert Treat
Database Architect
http://www.omniti.com