pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Avinash Kumar <avinash.vallarapu@gmail.com>
Cc: Pepe TD Vo <pepevo@yahoo.com>
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: Mon, 20 Apr 2020 17:11:58 +0200
Message-ID: <aca45930c9152836c359b7f91e5f9fceab39cf43.camel@cybertec.at> (raw)
In-Reply-To: <CAN0TujfgG8bYSjZNF0z22muF30mKawNpLtrr_pMQVqrhuk=OLQ@mail.gmail.com>
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>

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






view thread (21+ messages)  latest in thread

Message-ID: <aca45930c9152836c359b7f91e5f9fceab39cf43.camel@cybertec.at>
Permalink:  ../aca45930c9152836c359b7f91e5f9fceab39cf43.camel@cybertec.at/
Also on:    postgresql.org/message-id/aca45930c9152836c359b7f91e5f9fceab39cf43.camel@cybertec.at

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: laurenz.albe@cybertec.at, avinash.vallarapu@gmail.com, pepevo@yahoo.com, shammat@gmx.net
  Subject: Re: Exporting TBs of data in Oracle schema/database into CSV format to migrate into PostgreSQL
  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>

* 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