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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

On Mon, Apr 20, 2020 at 11:39 AM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux