Online Oracle to Postgresql data migration

Josh Harrison 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
thanks
josh

You can start here:
http://en.wikipedia.org/wiki/Extract%2C_transform%2C_load

--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)

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.

On Jan 11, 2008, at 12:14 PM, Scott Marlowe 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.

Another option is to talk to the folks at EnterpriseDB as Oracle-
Postgres compatibility is their specialty.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Jan 11, 2008 1:22 PM, Erik Jones <erik@myemma.com> wrote:

On Jan 11, 2008, at 12:14 PM, Scott Marlowe 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.

Another option is to talk to the folks at EnterpriseDB as Oracle-
Postgres compatibility is their specialty.

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?

Thanks
josh

Thanks

On Jan 12, 2008 9:19 AM, David Fetter <david@fetter.org> wrote:

On Fri, Jan 11, 2008 at 01:02:01PM -0500, Josh Harrison 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?

I have written some Free software, DBI-Link, for just this use case.
The software is under the BSD license, so you can use it freely. I
also offer consulting on such migrations.

I downloaded DBI-Link.
When I tried to compile postgres8.3 with-perl option it gives me this error.

ld: fatal: relocations remain against allocatable but non-writable sections
collect2: ld returned 1 exit status
gmake[3]: *** [libplperl.so.0.0] Error 1

My OS is SunOS 5.10
(The same compiles fine in FreeBSD but gives an error in Solaris). Is this
kind 0f solaris-specific error?

Do you know what caues this error?
Thanks
josh

Josh Harrison wrote:

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?

Thanks
josh

Check out ora2pg. You can use this perl tool for data migration as well.

http://pgfoundry.org/projects/ora2pg/

Also, While you are converting your DDL and other queries, you could run
'explain analyze' on them to see how good they perform and make
appropriate changes for better performance.

Btw, glad to see that you are planning to move to PG. :)

Mayuresh

On Mon, Jan 14, 2008 at 11:42:50AM -0500, Josh Harrison wrote:

Thanks

On Jan 12, 2008 9:19 AM, David Fetter <david@fetter.org> wrote:

On Fri, Jan 11, 2008 at 01:02:01PM -0500, Josh Harrison 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?

I have written some Free software, DBI-Link, for just this use case.
The software is under the BSD license, so you can use it freely. I
also offer consulting on such migrations.

I downloaded DBI-Link.
When I tried to compile postgres8.3 with-perl option it gives me this error.

You may have an old or broken version of perl. What's the output of
perl -v?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Friday 11 January 2008 13:44, Josh Harrison wrote:

On Jan 11, 2008 1:22 PM, Erik Jones <erik@myemma.com> wrote:

On Jan 11, 2008, at 12:14 PM, Scott Marlowe 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 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

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.

Thanks .
We have around 3TB of data now running in Oracle. I have done replication in
postgresql but not much in Oracle. Is there a way you can replicate between
Oracle and Postgresql. For writing the custom codes do you suggest any
preferred language ...like java, perl etc?

Thanks
Josh

On Jan 15, 2008 3:58 PM, David Fetter <david@fetter.org> wrote:

On Mon, Jan 14, 2008 at 11:42:50AM -0500, Josh Harrison wrote:

Thanks

On Jan 12, 2008 9:19 AM, David Fetter <david@fetter.org> wrote:

On Fri, Jan 11, 2008 at 01:02:01PM -0500, Josh Harrison 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?

I have written some Free software, DBI-Link, for just this use case.
The software is under the BSD license, so you can use it freely. I
also offer consulting on such migrations.

I downloaded DBI-Link.
When I tried to compile postgres8.3 with-perl option it gives me this

error.

You may have an old or broken version of perl. What's the output of
perl -v?

I use version 5.8.8
Thanks. I sorted out that. That was a linker problem. I installed binutils
and made gcc use that ld. Now I can compile postgres with perl option.

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.

Regards,
Dawid

On Jan 16, 2008 10:11 AM, Josh Harrison <joshques@gmail.com> wrote:

On Jan 15, 2008 3:58 PM, David Fetter <david@fetter.org> wrote:

On Mon, Jan 14, 2008 at 11:42:50AM -0500, Josh Harrison wrote:

Thanks

On Jan 12, 2008 9:19 AM, David Fetter <david@fetter.org> wrote:

On Fri, Jan 11, 2008 at 01:02:01PM -0500, Josh Harrison 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?

I have written some Free software, DBI-Link, for just this use case.
The software is under the BSD license, so you can use it freely. I
also offer consulting on such migrations.

I downloaded DBI-Link.
When I tried to compile postgres8.3 with-perl option it gives me this

error.

You may have an old or broken version of perl. What's the output of
perl -v?

I use version 5.8.8
Thanks. I sorted out that. That was a linker problem. I installed binutils
and made gcc use that ld. Now I can compile postgres with perl option.

Now a new problem had come up. When I try createlang command
createlang plperlu test
I get this error...
createlang: language installation failed: ERROR: could not load library
"/export/home/josh/postgres8.3-perl/lib/plperl.so": ld.so.1: postgres:
fatal: relocation error: file
/export/home/josh/postgres8.3-perl/lib/plperl.so: symbol PL_curpad:
referenced symbol not found

perl information:
perl -v
This is perl, v5.8.8 built for i86pc-solaris-64int

Can you advise pls
josh

On Jan 16, 2008 1:43 PM, Josh Harrison <joshques@gmail.com> wrote:

On Jan 16, 2008 10:11 AM, Josh Harrison <joshques@gmail.com> wrote:

On Jan 15, 2008 3:58 PM, David Fetter <david@fetter.org> wrote:

On Mon, Jan 14, 2008 at 11:42:50AM -0500, Josh Harrison wrote:

Thanks

On Jan 12, 2008 9:19 AM, David Fetter <david@fetter.org> wrote:

On Fri, Jan 11, 2008 at 01:02:01PM -0500, Josh Harrison 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?

I have written some Free software, DBI-Link, for just this use

case.

The software is under the BSD license, so you can use it freely.

I

also offer consulting on such migrations.

I downloaded DBI-Link.
When I tried to compile postgres8.3 with-perl option it gives me

this error.

You may have an old or broken version of perl. What's the output of
perl -v?

I use version 5.8.8
Thanks. I sorted out that. That was a linker problem. I installed
binutils and made gcc use that ld. Now I can compile postgres with perl
option.

Now a new problem had come up. When I try createlang command
createlang plperlu test
I get this error...
createlang: language installation failed: ERROR: could not load library
"/export/home/josh/postgres8.3-perl/lib/plperl.so": ld.so.1: postgres:
fatal: relocation error: file
/export/home/josh/postgres8.3-perl/lib/plperl.so: symbol PL_curpad:
referenced symbol not found

perl information:
perl -v
This is perl, v5.8.8 built for i86pc-solaris-64int

Can you advise pls
josh

Forgot to mention......

My perl information
perl -V
Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
Platform:
osname=solaris, osvers=2.10, archname=i86pc-solaris-64int
uname='sunos aishwarya 5.10 generic_118844-26 i86pc i386 i86pc '
config_args='-Dcc=gcc -Dprefix=/export/home/josh/perl5 -Duse64bitint
-Duseshrplib'
hint=recommended, useposix=true, d_sigaction=define
usethreads=undef use5005threads=undef useithreads=undef
usemultiplicity=undef
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=define use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='gcc', ccflags ='-fno-strict-aliasing -pipe
-Wdeclaration-after-statement -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64
-DPERL_USE_SAFE_PUTENV -DPERL_USE_SAFE_PUTENV -DPERL_USE_SAFE_PUTENV
-DPERL_USE_SAFE_PUTENV -DPERL_USE_SAFE_PUTENV -DPERL_USE_SAFE_PUTENV
-DPERL_USE_SAFE_PUTENV',
optimize='-O',
cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement'
ccversion='', gccversion='3.4.5', gccosandvers='solaris2.8'
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=12345678
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=12
ivtype='long long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t',
lseeksize=8
alignbytes=4, prototype=define
Linker and Libraries:
ld='gcc', ldflags =' -L/usr/local/lib '
libpth=/usr/local/lib /usr/lib /usr/ccs/lib
libs=-lsocket -lnsl -ldl -lm -lc
perllibs=-lsocket -lnsl -ldl -lm -lc
libc=/lib/libc.so, so=so, useshrplib=true, libperl=libperl.so
gnulibc_version=''
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags=' -Wl,-E -R
/export/home/josh/perl5/lib/5.8.8/i86pc-solaris-64int/CORE'
cccdlflags='-fPIC', lddlflags=' -Wl,-E -G -L/usr/local/lib'

Characteristics of this binary (from libperl):
Compile-time options: PERL_MALLOC_WRAP PERL_USE_SAFE_PUTENV
USE_64_BIT_INT USE_LARGE_FILES USE_PERLIO
Built under solaris
Compiled at Jan 16 2008 12:13:26
@INC:
/export/home/josh/perl5/lib/5.8.8/i86pc-solaris-64int
/export/home/josh/perl5/lib/5.8.8
/export/home/josh/perl5/lib/site_perl/5.8.8/i86pc-solaris-64int
/export/home/josh/perl5/lib/site_perl/5.8.8
/export/home/josh/perl5/lib/site_perl

On Jan 16, 2008 1:31 PM, Dawid Kuroczko <qnex42@gmail.com> wrote:

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.

In this case how do you migrate the simultaneous

updates/deletes/inserts, Since this is a production system, there are
everyday changes in the databse ?

Josh Harrison wrote:

Thanks .
We have around 3TB of data now running in Oracle. I have done
replication in postgresql but not much in Oracle. Is there a way you
can replicate between Oracle and Postgresql. For writing the custom
codes do you suggest any preferred language ...like java, perl etc?

See, if this can help,
https://daffodilreplicator.dev.java.net/

... and do let us know if you find it useful.
Rgds
Mayuresh

Terabytes of data: this is a lot of Oracle data to migrate. You would
need a high performance tools capable to handle heterogeneous
environment
People suggested links here, so I will add some that could be very
appropriate to your case:

PostgreSQL loader is limited by the way. For instance, if you have a
end of the line character within your data then load into PostgreSQL
will fail.
Check this pdf: http://www.wisdomforce.com/dweb/resources/docs/OracleToNetezzaWithFastReader.pdf

Few tools to consider:

FastReader: http://www.wisdomforce.com/dweb/index.php?id=23 -
extracts data from Oracle into ASCII flat files or pipe and create a
input for PostgreSQL loader. Many people use it for fast initial
synchronization. Fastreader performs bulk data extract when terabytes
of data can be migrated in hours

Database Sync - http://www.wisdomforce.com/dweb/index.php?id=1001 -
also fast data transfer tool that operates as a change data capture.
It captures all the latest transactions and could be used for data
warehouse incremental feeds with OLTP Oracle data. You may need it if
don't want each time to move terabytes of data but only the changed
data

Mayuresh Nirhali wrote:

Josh Harrison wrote:

Thanks .
We have around 3TB of data now running in Oracle. I have done
replication in postgresql but not much in Oracle. Is there a way you
can replicate between Oracle and Postgresql. For writing the custom
codes do you suggest any preferred language ...like java, perl etc?

See, if this can help,
https://daffodilreplicator.dev.java.net/

... and do let us know if you find it useful.
Rgds
Mayuresh

At least from my browser the links to documentation, comparisons, FAQ and download all fail ...

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

On Jan 18, 2008 7:45 AM, Gregory Williamson <
Gregory.Williamson@digitalglobe.com> wrote:

Mayuresh Nirhali wrote:

Josh Harrison wrote:

Thanks .
We have around 3TB of data now running in Oracle. I have done
replication in postgresql but not much in Oracle. Is there a way you
can replicate between Oracle and Postgresql. For writing the custom
codes do you suggest any preferred language ...like java, perl etc?

See, if this can help,
https://daffodilreplicator.dev.java.net/

... and do let us know if you find it useful.
Rgds
Mayuresh

At least from my browser the links to documentation, comparisons, FAQ and
download all fail ...

The same for me. I couldn't open anything there
josh

On Jan 18, 2008 4:14 AM, Dorren <database.replication@gmail.com> wrote:

Terabytes of data: this is a lot of Oracle data to migrate. You would
need a high performance tools capable to handle heterogeneous
environment
People suggested links here, so I will add some that could be very
appropriate to your case:

PostgreSQL loader is limited by the way. For instance, if you have a
end of the line character within your data then load into PostgreSQL
will fail.
Check this pdf:
http://www.wisdomforce.com/dweb/resources/docs/OracleToNetezzaWithFastReader.pdf

Few tools to consider:

FastReader: http://www.wisdomforce.com/dweb/index.php?id=23 -
extracts data from Oracle into ASCII flat files or pipe and create a
input for PostgreSQL loader. Many people use it for fast initial
synchronization. Fastreader performs bulk data extract when terabytes
of data can be migrated in hours

Database Sync - http://www.wisdomforce.com/dweb/index.php?id=1001 -
also fast data transfer tool that operates as a change data capture.
It captures all the latest transactions and could be used for data
warehouse incremental feeds with OLTP Oracle data. You may need it if
don't want each time to move terabytes of data but only the changed
data

Thanks. Ill checkout those options. I also have another question in mind.
How good(or fast) will it be to use java with jdbc to transfer these
terabytes of data from oracle to postgresql? This worked okay for small
datasets but Im not sure how it will behave for large data.
And also keep track of the changes in the Oracle production system using
triggers?

Thanks
josh