Thread: Online Oracle to Postgresql data migration
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
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
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:
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?
Another option is to talk to the folks at EnterpriseDB as Oracle-
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.
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
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
On Jan 12, 2008 9:19 AM, David Fetter <david@fetter.org> wrote:
I downloaded DBI-Link. On Fri, Jan 11, 2008 at 01:02:01PM -0500, Josh Harrison wrote:I have written some Free software, DBI-Link, for just this use case.
> 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?
The software is under the BSD license, so you can use it freely. I
also offer consulting on such migrations.
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/xslAbout a year ago we converted one of our clients multi-TB ODS systems built in
> 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?
>
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.
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:
I use version 5.8.8On Mon, Jan 14, 2008 at 11:42:50AM -0500, Josh Harrison wrote:You may have an old or broken version of perl. What's the output of
> 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.
perl -v?
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:
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
I use version 5.8.8On 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:You may have an old or broken version of perl. What's the output of
> 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.
perl -v?
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:
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
Forgot to mention......On Jan 16, 2008 10:11 AM, Josh Harrison <joshques@gmail.com> wrote:I use version 5.8.8On 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:You may have an old or broken version of perl. What's the output of
> 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.
perl -v?
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
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:
If you are migrating terabytes don't use perl. I did some experimentalOn 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.
"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 > > Thanks > Josh
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:
The same for me. I couldn't open anything there
At least from my browser the links to documentation, comparisons, FAQ and download all fail ...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
josh
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 On Jan 11, 10:02 am, joshq...@gmail.com ("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
On Jan 18, 2008 4:14 AM, Dorren <database.replication@gmail.com> wrote:
Thanks. Ill checkout those options. I also have another question in mind.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
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