From: Pepe TD Vo <pepevo@yahoo.com>
To: Avinash Kumar <avinash.vallarapu@gmail.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-admin@lists.postgresql.org, Thomas Kellerer <shammat@gmx.net>
Subject: Re: Exporting TBs of data in Oracle schema/database into CSV format to migrate into PostgreSQL
Date: Fri, 24 Apr 2020 13:28:25 +0000 (UTC)
Message-ID: <231996387.101528.1587734905966@mail.yahoo.com> (raw)
In-Reply-To: <aca45930c9152836c359b7f91e5f9fceab39cf43.camel@cybertec.at>
References: <CAOLrvP6sSTH2Mq5q29-9Jdk3JrR-PGT5WE+eN=8xnqKg8vnvbw@mail.gmail.com>
<c5b9987a-ad9e-ce37-d734-2fb38d1d7330@gmx.net>
<172687860.2288957.1587391373846@mail.yahoo.com>
<ee5a27d790ac43d4358b6353f37d09eee4214bbf.camel@cybertec.at>
<CAN0TujfgG8bYSjZNF0z22muF30mKawNpLtrr_pMQVqrhuk=OLQ@mail.gmail.com>
<aca45930c9152836c359b7f91e5f9fceab39cf43.camel@cybertec.at>
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.albe@cybertec.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.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pepevo@yahoo.com, avinash.vallarapu@gmail.com, laurenz.albe@cybertec.at, shammat@gmx.net
Subject: Re: Exporting TBs of data in Oracle schema/database into CSV format to migrate into PostgreSQL
In-Reply-To: <231996387.101528.1587734905966@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox