Re: Exporting TBs of data in Oracle schema/database into CSV format to migrate into PostgreSQL

Laurenz,
is a foreign-data wrapper open source?  
v/r,
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success

On Monday, April 20, 2020, 11:12:03 AM EDT, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

On Mon, 2020-04-20 at 11:59 -0300, Avinash Kumar wrote:
> On Mon, Apr 20, 2020 at 11:39 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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