Обсуждение: Exporting TBs of data in Oracle schema/database into CSV format tomigrate into PostgreSQL

On 4/20/20 6:53 AM, Prashant Kulkarni wrote:
> Hi Team,
>
> Please can you let me know the menthds/tools/approches to export large 
> amount of data (TBs) present in the schemas into the CSV/delimated format 
> as we have to import that into PostgreSQL later.
>
> I tried for options like SQL Developer/Toad but those are not feasible for 
> vast data size, these tool are OK for GBs of data to export.
>
> Kindly let me know if you have any solution to complete this task.

What kind of data (transaction history, binary blobs, GIS, etc)?

-- 
Angular momentum makes the world go 'round.

Prashant Kulkarni schrieb am 20.04.2020 um 13:53:
> Hi Team,
>
> Please can you let me know the menthds/tools/approches to export
> large amount of data (TBs) present in the schemas into the
> CSV/delimated format as we have to import that into PostgreSQL
> later.
>
> I tried for options like SQL Developer/Toad but those are not
> feasible for vast data size, these tool are OK for GBs of data to
> export.
>
> Kindly let me know if you have any solution to complete this task.

Using a foreign table would remove the need to dump and transfer
the data to begin with.

Then you could access the Oracle table directly from within Postgres:

insert into some_table (...)
select ...
from oracle_fdw_table;

On Mon, 2020-04-20 at 14:02 +0000, Pepe TD Vo wrote:
> we can access Oracle table within Postgres?  How?  Need to create a database link
> and/or how can both talk to each other?

That would be the Oracle foreign data wrapper:
https://laurenz.github.io/oracle_fdw/

> My data is from production, can turn off the foreign key.  Is there a way to copy
> data from Oracle to Postgres faster?  Should I export the Oracle data table via
> dump file as text/csv and insert it into Postgres?  Can we use an oracle dump file?

You cannot use an Oracle dump, because that is in a proprietary format.

Using the foreign data wrapper, you define a foreign table in PostgreSQL.
When you select from that table, the data are directly fetched from Oracle.

Then you can do

   INSERT INTO localtable SELECT * FROM foreign_table;

to transfer the data, without any intermediary file storage.
In a way, the data are streamed from Oracle to PostgreSQL.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Mon, 2020-04-20 at 11:59 -0300, Avinash Kumar wrote:
> On Mon, Apr 20, 2020 at 11:39 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Mon, 2020-04-20 at 14:02 +0000, Pepe TD Vo wrote:
> > > we can access Oracle table within Postgres?  How?  Need to create a database link
> > > and/or how can both talk to each other?
> > 
> > That would be the Oracle foreign data wrapper:
> > https://laurenz.github.io/oracle_fdw/
>
> I echo. In my experience i have seen copy using oracle_fdw performing (almost 3 times
> faster than Ora2PG in some tests) a lot faster than what is existing today for the
> offline data copy. So, one may just avoid the longer route of generating a CSV
> and then looking for options to load it faster. 
> 
> However, for data validation - do you think we could do something like -> creating
> a md5 hash out of oracle (using dbms_crypto.hash()) foreign table and local postgres
> table (using md5()) ? 
> What else do you think is the best to validate the data between Oracle and Postgres,
> other than Application and QA tests related to the App ?

If there is no error transferring the data, why should there be need for validation?
Unless you don't trust oracle_fdw of course.

Calculating MD5-sums or similar of the data before and after would be difficult,
because for example timestamps are stored quite differently.
You'd have to convert the data into an identical text format on both systems,
and I cannot think of a good way to do that.

I think that everything beyond comparing the row count would be paranoid.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

So, the big question is: how much downtime can you afford on the current Oracle DB?

Yes, you should investigate the most efficient way to transfer data, but with TBs it will still be significant time. If
youcan afford the down time, great--kill all other connections to Oracle and go for it. If not, that's more complicated
sinceyou'll have to figure out a way to track and apply changes that happen during the copy... 

Actually we have something that is called the "ModelMapper" and is used
for large amounts of data transports between databases and even
different database models and this is part of our "Caelus" software
infrastructure.

Contact us for more information.

-- 
Open Systems Development B.V.
Peter M. Groen ( Managing Director )
Rooseveltstraat 18P
2321 BM  Leiden
Email : peter@osdev.nl


On Mon, 20 Apr 2020 11:58:42 +0000, soumik.bhattacharjee@kpn.com wrote:

>Ora2pg,Oracle Golden Gate and EDB Migration toolkit.
>
>From: Prashant Kulkarni <ppk10.prashant@gmail.com>
>Sent: maandag 20 april 2020 13:53
>To: pgsql-admin <pgsql-admin@postgresql.org>
>Subject: Exporting TBs of data in Oracle schema/database into CSV
>format to migrate into PostgreSQL
>
>Hi Team,
>
>Please can you let me know the menthds/tools/approches to export large
>amount of data (TBs) present in the schemas into the CSV/delimated
>format as we have to import that into PostgreSQL later.
>
>I tried for options like SQL Developer/Toad but those are not feasible
>for vast data size, these tool are OK for GBs of data to export.
>
>Kindly let me know if you have any solution to complete this task.
>
>Thanks.

ora2pg would be the most straightforward way. 



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

On Fri, 2020-04-24 at 13:28 +0000, Pepe TD Vo wrote:
> is a foreign-data wrapper open source? 

Yes, see the license.
The page is https://laurenz.github.io/oracle_fdw/

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Fri, 2020-04-24 at 10:35 -0300, Avinash Kumar wrote:
> On Fri, Apr 24, 2020 at 10:31 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Fri, 2020-04-24 at 13:28 +0000, Pepe TD Vo wrote:
> > > is a foreign-data wrapper open source? 
> > 
> > Yes, see the license.
> > The page is https://laurenz.github.io/oracle_fdw/
> 
> Was it due to the tricky part of Oracle libraries linking and dependency on Oracle client that you did not plan to
thinkof creating an RPM of it ? 
 
> Not sure if it was discussed before, but what was the reason for Oracle_FDW not being available in PGDG repo ?

No, but rather that there is a plethora of Linux distributions in various versions
on various architectures, and I'd have to create an RPM for any of them
(not to speak of the distributions that use other package managers).

And on Linux it is really easy to build the binaries.

About the PGDB packages, that's not for me to answer.
Perhaps it is not deemed important enough, perhaps there are technical or
license problems.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com