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

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