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

Hi,

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/
>
> > 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.
>
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 ?

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

--
Regards,
Avinash Vallarapu