Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jRyNh-0000T2-0d for pgsql-admin@arkaria.postgresql.org; Fri, 24 Apr 2020 13:28:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jRyNf-0008VP-SD for pgsql-admin@arkaria.postgresql.org; Fri, 24 Apr 2020 13:28:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jRyNf-0008VI-26 for pgsql-admin@lists.postgresql.org; Fri, 24 Apr 2020 13:28:35 +0000 Received: from sonic302-2.consmr.mail.bf2.yahoo.com ([74.6.135.41]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jRyNb-0006v6-U8 for pgsql-admin@lists.postgresql.org; Fri, 24 Apr 2020 13:28:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1587734910; bh=rVmh5SVmoDUKtJ9dZiD+z6qcgbQlAKobOsmvy/vYt04=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=ANo7SfS7gfDZPIBJ9jGooTOz/Ybf/I9wpceg8tgURcOIhlJbxdJxWd1IcKqJlLWrEeI4NcCfjkhF6BFBSJcKrrYnher+Yp1Zvjn44cnfhFpwjIExqFCsJ2cOEAhigWqoTv2XNfHzMKLvCdSfkT1uP9CNLXhro7NE7/Ot2LKKcNvkYq52SvBGvt8lORFqHvoeIXYDg6+VtFRQtc95wXbdOjGllcwaDYvzmrMBMhR54rm0UNXvuv7B51NoOxtHClTADlz7DJSynzsppTIZCguJ/061v+hFOBRmIuNJFfyIvaoFI8dZvlS6Pmdy0Z9hv3g+P/FQxvJm6LgNimvfR1w77g== X-YMail-OSG: wf3ZPgcVM1nboKI3S3XRosplQhsrE4Zug_kKY.5ySpy0wslJxLIQZn5EezzHqsx UIk0eYEaGLFyMi9oVyYC7dZzefb5ps7Z_WYGlkYuHA1QJU9qOkHKogp9iAr8XLwuIpp.lixPUY4l MqRjzm6onwsQWjw8IYqStDy9G.QIrNL5VJqqta9KQb6hiIv2RHPVrHcIs7uFo2ln73BCMLx8.ETp g3e8y.Oqs6aph4cFIuOnS0k2gemUKcmmoiKU6Hb8EDBsGjlvMZkqAcIswDzjHt1qs6KtGTqBw3Bo UjkY6aCW85xA_hGW26P.mIAq6vrsEMeMeqdMRvetHBIDMVnhY6jWQXVULaOY8btHgUyO7YGWvhD_ Kb6yicZfR.NFo2ZklcyyJ6HhkMqZ8bIii4tAYvvaNzUDPrT4Pw8_Ss1N6wSn.rRhOr3BguYAKGRN aJxrUrAY3SfnQ5s1w8k1WzZS_DSfsNibX_A5alJZXT4Zaca703oCusN9Cfv29ZMzId5lmCtMYqNf sibUQIb9H93OnMZn.TG1U2c5CWPN.sVBpgBxKBLAjYS6sZ2ygB.j.OZ2XIwULZhEDGZ5vrcI9Mv3 wuBIPizrj4euDPYiq2VtAzfMNFRszO9WzF5pleQSraumK595R39CHlGzmpiZZSkjLxdLDOrSDn8L jlnC1KEcIAfKJqYytrlCOLj9xHaXC8jpjV6x9bVtaGhDjY7Lj2uwE4j3hq7yJ4YXBCnVgDViVCoG DtH1D2gGCAx8SLRj9H.L93qKfYT6N_BUh5mh3THG1K9QBGAYGuWV2pXxyYNF.QvU82lJB4FSWbHN AcV5wTwwmgPCZp6.rA7VWR_uLTPDWmxMseFmk7tFwaDjRYV.97zJqaEUznKZ7VvcwY5.HCx7i6hb fRjxgRPPDsRfTGJWu00DCsyOY6BAnaZlcxuAuWFfc9BPCQxRhB_d4Jj2SvUXQDnhhQOCUZGgG8A0 Iw7ZYJPZXIi0eq5jQC1oPWTp54Y_HRYco3yoXsmLr2ZD659PPXkwQ.VDWJI1wCcMQ7_ERws1nG0H bOaLrvsao1JeS5iknyou8J0xHnIFho3MHHKHJvr5_VAA6Ve_uBBkZBlP6jypckHzH9HEX0zvFzb2 hMbTvzn9sHjLfezXjedMdhrO4B18gJreyExvTAtQ3e7Y0b6HrRe1N0PaR8nVy1DbJWJMS6TZSp4T 6OoKejhSM9mdrXQ6TOR_1A4NXaC8bEz9ybYcqF432FMqxA8P83BBQMCWdpsW_dNUYp3hNtXWwTpf ooWIEXh1pU1_yafwBkMr6M30Fw0PvByk.TcphWXibD68OhJyWHeYTP7BXjQj0DRgubL3jHeFgC7n 0HTaUbNP1e3qmP_8.XmUiyvAyMX3FTQjpyAwVqLLBRwd20w-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic302.consmr.mail.bf2.yahoo.com with HTTP; Fri, 24 Apr 2020 13:28:30 +0000 Date: Fri, 24 Apr 2020 13:28:25 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Avinash Kumar , Laurenz Albe Cc: pgsql-admin@lists.postgresql.org, Thomas Kellerer Message-ID: <231996387.101528.1587734905966@mail.yahoo.com> In-Reply-To: References: <172687860.2288957.1587391373846@mail.yahoo.com> Subject: Re: Exporting TBs of data in Oracle schema/database into CSV format to migrate into PostgreSQL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_101527_1972603929.1587734905964" X-Mailer: WebService/1.1.15776 YMailNorrin Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.113 Safari/537.36 Content-Length: 8959 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_101527_1972603929.1587734905964 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Laurenz, is a foreign-data wrapper open source?=C2=A0=C2=A0 v/r, Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir 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 t= o 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=20 On Monday, April 20, 2020, 11:12:03 AM EDT, Laurenz Albe wrote: =20 =20 On Mon, 2020-04-20 at 11:59 -0300, Avinash Kumar wrote: > On Mon, Apr 20, 2020 at 11:39 AM Laurenz Albe = wrote: > > On Mon, 2020-04-20 at 14:02 +0000, Pepe TD Vo wrote: > > > we can access Oracle table within Postgres?=C2=A0 How?=C2=A0 Need to = create a database link > > > and/or how can both talk to each other? > >=20 > > 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 (al= most 3 times > faster than Ora2PG in some tests) a lot faster than what is existing toda= y 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.=20 >=20 > 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 loc= al postgres > table (using md5()) ?=20 > What else do you think is the best to validate the data between Oracle an= d 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 va= lidation? Unless you don't trust oracle_fdw of course. Calculating MD5-sums or similar of the data before and after would be diffi= cult, because for example timestamps are stored quite differently. You'd have to convert the data into an identical text format on both system= s, 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 --=20 Cybertec | https://www.cybertec-postgresql.com =20 ------=_Part_101527_1972603929.1587734905964 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
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 c= all him a dog hardly seems to do him justice though in as much as he had fo= ur legs, a tail, and barked, I admit he was, to all outward appearances. Bu= t to those who knew him well, he was a perfect gentleman (Hermione Gingold)=

**Live si= mply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success=


=20
=20
On Monday, April 20, 2020, 11:12:03 AM EDT, Laurenz Alb= e <laurenz.albe@cybertec.at> wrote:


On Mon, 2020-04-20 at 11:59 -0300, Av= inash Kumar wrote:
> On Mon, Apr 20, 2020 at 11:39 AM = Laurenz Albe <laurenz.albe@cybertec.at&g= t; wrote:
> > On Mon, 2020-04-20 at 14:02 +0000, Pe= pe 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 f= oreign data wrapper:
> > https://laurenz.gi= thub.io/oracle_fdw/
>
> I ech= o. 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 co= py. 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 thin= k we could do something like -> creating
> a md5 ha= sh out of oracle (using dbms_crypto.hash()) foreign table and local postgre= s
> table (using md5()) ?
> What= else do you think is the best to validate the data between Oracle and Post= gres,
> other than Application and QA tests related to= the App ?

If there is no error transf= erring 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 woul= d be difficult,
because for example timestamps are stored= quite differently.
You'd have to convert the data into a= n identical text format on both systems,
and I cannot thi= nk of a good way to do that.

I think t= hat everything beyond comparing the row count would be paranoid.


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

------=_Part_101527_1972603929.1587734905964--