From spotluri@ismartpanache.com Tue Jul 8 12:43:07 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C5949650111 for ; Tue, 8 Jul 2008 09:43:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 18124-05 for ; Tue, 8 Jul 2008 09:42:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from app01.sm.onr.chicago.hostway (fw.mfg.onr.siteprotect.com [64.26.60.152]) by postgresql.org (Postfix) with ESMTP id 7C117650127 for ; Tue, 8 Jul 2008 09:42:30 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by app01.sm.onr.chicago.hostway (Postfix) with ESMTP id D34078DC113 for ; Tue, 8 Jul 2008 07:42:28 -0500 (CDT) MIME-Version: 1.0 X-Mailer: AtMail PHP 5.2 Message-ID: <49982.1215520948@ismartpanache.com> To: Reply-To: spotluri@ismartpanache.com Content-Type: multipart/alternative; boundary="=_1b4d45d6d3b96e024e9b38a6ff76a65d" X-Origin: 220.225.201.89 Date: Tue, 8 Jul 2008 07:42:28 -0500 Subject: How do i compare 2 postgresql instanses ? From: Potluri Srikanth X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.394 tagged_above=0 required=5 tests=AWL=0.393, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200807/83 X-Sequence-Number: 29693 --=_1b4d45d6d3b96e024e9b38a6ff76a65d Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" =20 How do i compare 2 postgresql instanses ? 1) compare all objects including data in tables a) production box b) DR site ? can you help me with the query, through which i can get all the object of each schema/database ? Regards,=20 Srikanth k Potluri=20 --=_1b4d45d6d3b96e024e9b38a6ff76a65d Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"
How do i compare 2 postgresql instanses ?
1) compare all objects including data in tables a) production box b) DR sit= e ?

can you help me with the query, through which i can get all the object of e= ach schema/database ?



Regards,
Srikanth k Potluri

--=_1b4d45d6d3b96e024e9b38a6ff76a65d-- From achill@matrix.gatewaynet.com Tue Jul 8 13:02:49 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5F0216500B8 for ; Tue, 8 Jul 2008 10:02:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36039-05 for ; Tue, 8 Jul 2008 10:02:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smadev.internal.net (host3.dynacom.ondsl.gr [62.103.35.211]) by postgresql.org (Postfix) with ESMTP id 418FB6500A4 for ; Tue, 8 Jul 2008 10:02:45 -0300 (ADT) Received: from smadev.internal.net (localhost [127.0.0.1]) by smadev.internal.net (8.14.2/8.14.2) with ESMTP id m68D2e6h058054; Tue, 8 Jul 2008 16:02:40 +0300 (EEST) (envelope-from achill@matrix.gatewaynet.com) Received: from localhost (localhost [[UNIX: localhost]]) by smadev.internal.net (8.14.2/8.14.2/Submit) id m68D2dF8058053; Tue, 8 Jul 2008 16:02:39 +0300 (EEST) (envelope-from achill@matrix.gatewaynet.com) From: Achilleas Mantzios Organization: Dynacom Tankers Mgmt To: pgsql-admin@postgresql.org, spotluri@ismartpanache.com Subject: Re: How do i compare 2 postgresql instanses ? Date: Tue, 8 Jul 2008 16:02:39 +0300 User-Agent: KMail/1.9.7 References: <49982.1215520948@ismartpanache.com> In-Reply-To: <49982.1215520948@ismartpanache.com> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200807081602.39336.achill@matrix.gatewaynet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/84 X-Sequence-Number: 29694 =CE=A3=CF=84=CE=B9=CF=82 Tuesday 08 July 2008 15:42:28 =CE=BF/=CE=B7 Potlur= i Srikanth =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5: >=20 > How do i compare 2 postgresql instanses ? > 1) compare all objects including data in tables a) production box b) > DR site ? > can you help me with the query, through which i can get all the > object of each schema/database ? > Regards,=20 > Srikanth k Potluri=20 >=20 you could write a little program (awk) to produce an sql file of the form: select count(*) from airlines; select count(*) from airports; select count(*) from airticket; select count(*) from airticketflights; =2E... select count(*) from zzzobjects; and then run it against the two databases, and diff the outputs. If the outputs are not identical then you have 2 different databases, otherwise you have to search more to know the answer. So this technique can prove that yours DBs are *not* identical, but does no= t say anything about the opposite. =2D-=20 Achilleas Mantzios From m.nacos@gmail.com Tue Jul 8 13:21:09 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6033B650109 for ; Tue, 8 Jul 2008 10:21:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51971-08 for ; Tue, 8 Jul 2008 10:20:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.224]) by postgresql.org (Postfix) with ESMTP id AF637650125 for ; Tue, 8 Jul 2008 10:20:59 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id c49so1729691wra.19 for ; Tue, 08 Jul 2008 06:20:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:in-reply-to:mime-version:content-type:references; bh=ZNqaYam65XJ1JAQFs564gNaUVPuW50odzGiQcr+fvuI=; b=Pr/gD9bJBLa2y4rQBvm3TYkRlCqDv1oKMtG47aVBPJDQaDjAGpFBlUug/M4LFNWqpN hn8Kjs+qC++1yPKHuY3rqHW3iIoI8p3GfouwUGUZjxC2LUjKnwDuRVZKfrTOX4d1Rhgj z1Ng+AtGKJyeP06SRkR862XsdNxMMz8uPtzqM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version :content-type:references; b=HXM6Nve+N836zjMuxqEi6qj0Z0zIADivV9AXUvpZ3ax/PzXQpl0nmP+BEhUsd/u1MM DZDmP7K0uHezLjppw/Gvr/6uLQGxaByBBd+jY/Sz06iryNsDImn8/tc6uiO48iydYDlP NRqyAeAYcENpFjd7In4V1qBZA0c5s8vMKEYdM= Received: by 10.90.74.7 with SMTP id w7mr6942759aga.45.1215523258398; Tue, 08 Jul 2008 06:20:58 -0700 (PDT) Received: by 10.90.55.11 with HTTP; Tue, 8 Jul 2008 06:20:58 -0700 (PDT) Message-ID: <407fa4640807080620r33a3463fgff195d8ae5ef708e@mail.gmail.com> Date: Tue, 8 Jul 2008 14:20:58 +0100 From: "Michael Nacos" To: pgsql-admin@postgresql.org Subject: Re: How do i compare 2 postgresql instanses ? In-Reply-To: <200807081602.39336.achill@matrix.gatewaynet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_26620_30643805.1215523258388" References: <49982.1215520948@ismartpanache.com> <200807081602.39336.achill@matrix.gatewaynet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL=-0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200807/85 X-Sequence-Number: 29695 ------=_Part_26620_30643805.1215523258388 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello there. Simple counts can certainly prove two databases are not the same version. But what if you actually want to look at the data? I have been thinking about row checksums, would you say this would be a reasonable way of verifying two databases are equivalent? I have many other things on my mind, so I haven't tried implementing this yet. Michael > you could write a little program (awk) to produce an sql file of the form: > select count(*) from airlines; > select count(*) from airports; > select count(*) from airticket; > select count(*) from airticketflights; > .... > select count(*) from zzzobjects; > > and then run it against the two databases, and diff the outputs. > If the outputs are not identical then you have 2 different databases, > otherwise you have to search more to know the answer. > So this technique can prove that yours DBs are *not* identical, but does > not say anything > about the opposite. > > -- > Achilleas Mantzios > > ------=_Part_26620_30643805.1215523258388 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello there. Simple counts can certainly prove two databases are not the same version. But what if you actually want to look at the data? I have been thinking about row checksums, would you say this would be a reasonable way of verifying two databases are equivalent? I have many other things on my mind, so I haven't tried implementing this yet.

Michael


you could write a little program (awk) to produce an sql file of the form:
select count(*) from airlines;
select count(*) from airports;
select count(*) from airticket;
select count(*) from airticketflights;
....
select count(*) from zzzobjects;

and then run it against the two databases, and diff the outputs.
If the outputs are not identical then you have 2 different databases,
otherwise you have to search more to know the answer.
So this technique can prove that yours DBs are *not* identical, but does not say anything
about the opposite.

--
Achilleas Mantzios


------=_Part_26620_30643805.1215523258388-- From Kevin.Grittner@wicourts.gov Tue Jul 8 14:28:35 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D608A6500F9 for ; Tue, 8 Jul 2008 11:28:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 17476-01 for ; Tue, 8 Jul 2008 11:28:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id EDCB96500B8 for ; Tue, 8 Jul 2008 11:28:28 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Tue, 08 Jul 2008 09:28:26 -0500 Message-Id: <4873335D.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.3 Date: Tue, 08 Jul 2008 09:28:14 -0500 From: "Kevin Grittner" To: "Michael Nacos" , Subject: Re: How do i compare 2 postgresql instanses ? References: <49982.1215520948@ismartpanache.com> <200807081602.39336.achill@matrix.gatewaynet.com> <407fa4640807080620r33a3463fgff195d8ae5ef708e@mail.gmail.com> In-Reply-To: <407fa4640807080620r33a3463fgff195d8ae5ef708e@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/86 X-Sequence-Number: 29696 >>> "Michael Nacos" wrote: > what if you actually want to look at the data? I have been > thinking about row checksums, would you say this would be a reasonable way > of verifying two databases are equivalent? Our home-grown replication technique does synchronization checks during idle time. It works pretty well for us, and we do use md5sum techniques to minimize bandwidth. In summary, a process requests a middle tier on a "source" machine to send "sync" information; that middle tier reads some set of rows and sends the md5 sum of all data in the rows, along with information on what rows from which table were used to develop the number. The requesting process then asks similar middle tiers on all replication targets to return their md5sum for the same set of rows. If there are any differences, the source and all differing replication targets are queried for the actual values, which are compared using match-merge type logic on the primary key sequence. Something similar might work for you. -Kevin From m.nacos@gmail.com Tue Jul 8 15:22:00 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C9E0664FFDC for ; Tue, 8 Jul 2008 12:22:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 66801-04 for ; Tue, 8 Jul 2008 12:21:47 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.224]) by postgresql.org (Postfix) with ESMTP id CE374650125 for ; Tue, 8 Jul 2008 12:21:46 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id c49so1768748wra.19 for ; Tue, 08 Jul 2008 08:21:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:in-reply-to:mime-version:content-type:references; bh=sSTNMUP61kd/2H3G0CgeUsUONOVGRfbSCOt5A0jpSgA=; b=T1uyDkTpoXzBUp39dzR1aEMy6QJFLMnIeQNKngtlQ5bygdCLhl4HgmG75jyXT+paZo lkbnNUrfYe/jy0W3ofjOrHr6ZHgtkA8LYY1LNDLrSzB21YNRxYRGFPy1Eog2NtyY1Ppl QIAK4NLnnZk5j7BuwMSTs1mw7Qkrr8EBWN8Lk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version :content-type:references; b=J52DgTGam3255tZGwbNuLV/wugWddmBfxXqlofiEXwH6rsPtEra2eYGX0bIfBHznXe zlHhepZ4oIOGgl06vB3W0V5WIll2XBqfoWTgqCzblxk79H7K6PBGsiW144QPjcQZhVjD PDtL/yxrlYR1PFb1ObDc92yvb5cQgvXISfUg8= Received: by 10.90.30.2 with SMTP id d2mr7070378agd.118.1215530506060; Tue, 08 Jul 2008 08:21:46 -0700 (PDT) Received: by 10.90.55.11 with HTTP; Tue, 8 Jul 2008 08:21:46 -0700 (PDT) Message-ID: <407fa4640807080821w3e33c567u82fbdc4fe6d08712@mail.gmail.com> Date: Tue, 8 Jul 2008 16:21:46 +0100 From: "Michael Nacos" To: pgsql-admin@postgresql.org Subject: Re: How do i compare 2 postgresql instanses ? In-Reply-To: <4873335D.EE98.0025.0@wicourts.gov> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_27419_17040798.1215530506057" References: <49982.1215520948@ismartpanache.com> <200807081602.39336.achill@matrix.gatewaynet.com> <407fa4640807080620r33a3463fgff195d8ae5ef708e@mail.gmail.com> <4873335D.EE98.0025.0@wicourts.gov> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL=-0.000, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200807/87 X-Sequence-Number: 29697 ------=_Part_27419_17040798.1215530506057 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks Kevin... I am working on something different right now but I'll soon have to give it a go, one way or the other... Michael On Tue, Jul 8, 2008 at 3:28 PM, Kevin Grittner wrote: > > Our home-grown replication technique does synchronization checks > during idle time. It works pretty well for us, and we do use md5sum > techniques to minimize bandwidth. > > In summary, a process requests a middle tier on a "source" machine to > send "sync" information; that middle tier reads some set of rows and > sends the md5 sum of all data in the rows, along with information on > what rows from which table were used to develop the number. The > requesting process then asks similar middle tiers on all replication > targets to return their md5sum for the same set of rows. If there are > any differences, the source and all differing replication targets are > queried for the actual values, which are compared using match-merge > type logic on the primary key sequence. > > Something similar might work for you. > > -Kevin > ------=_Part_27419_17040798.1215530506057 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Thanks Kevin... I am working on something different right now but I'll soon have to give it a go, one way or the other...

Michael

On Tue, Jul 8, 2008 at 3:28 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

Our home-grown replication technique does synchronization checks
during idle time.  It works pretty well for us, and we do use md5sum
techniques to minimize bandwidth.

In summary, a process requests a middle tier on a "source" machine to
send "sync" information; that middle tier reads some set of rows and
sends the md5 sum of all data in the rows, along with information on
what rows from which table were used to develop the number.  The
requesting process then asks similar middle tiers on all replication
targets to return their md5sum for the same set of rows.  If there are
any differences, the source and all differing replication targets are
queried for the actual values, which are compared using match-merge
type logic on the primary key sequence.

Something similar might work for you.

-Kevin

------=_Part_27419_17040798.1215530506057--