Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Z2P5E-0007Bj-O4 for pgsql-admin@arkaria.postgresql.org; Tue, 09 Jun 2015 19:21:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Z2P5E-0006Bv-87 for pgsql-admin@arkaria.postgresql.org; Tue, 09 Jun 2015 19:21:12 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Z2P5C-000697-Ak for pgsql-admin@postgresql.org; Tue, 09 Jun 2015 19:21:10 +0000 Received: from mout.web.de ([212.227.17.12]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.84) (envelope-from ) id 1Z2P55-0001pq-Qi for pgsql-admin@postgresql.org; Tue, 09 Jun 2015 19:21:09 +0000 Received: from vm-mailsrv.lan.net ([87.155.107.154]) by smtp.web.de (mrweb102) with ESMTPSA (Nemesis) id 0M5x3d-1ZHi0C19JZ-00xtC2; Tue, 09 Jun 2015 21:21:01 +0200 Received: from localhost (localhost [127.0.0.1]) by vm-mailsrv.lan.net (Postfix) with ESMTP id 6509318800; Tue, 9 Jun 2015 21:21:00 +0200 (CEST) X-Spam-Flag: NO X-Spam-Score: -2.896 X-Spam-Level: X-Spam-Status: No, score=-2.896 tagged_above=-99.9 required=6.31 tests=[ALL_TRUSTED=-1, BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, MIME_QP_LONG_LINE=0.001, URIBL_BLOCKED=0.001] autolearn=ham autolearn_force=no Received: from vm-mailsrv.lan.net ([127.0.0.1]) by localhost (vm-mailsrv.lan.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id sEF9nWluaOOp; Tue, 9 Jun 2015 21:20:53 +0200 (CEST) Received: from [10.94.76.50] (ipad.lan.net [10.94.76.50]) by vm-mailsrv.lan.net (Postfix) with ESMTP id 738DE187F6; Tue, 9 Jun 2015 21:20:53 +0200 (CEST) Content-Type: multipart/alternative; boundary=Apple-Mail-B259E646-F63B-4F1E-BC80-90B9FE7005EB Mime-Version: 1.0 (1.0) Subject: Re: replication consistency checking From: Jan Lentfer X-Mailer: iPad Mail (12D508) In-Reply-To: Date: Tue, 9 Jun 2015 21:20:53 +0200 Cc: pgsql-admin Content-Transfer-Encoding: 7bit Message-Id: References: To: hydra X-Provags-ID: V03:K0:sjr5TtH0AyRdXhrQrzUcNsYGqc4CdiKkp5n0YBJSpAi9toIaXn1 26Yue79BikyZuFPZSjwGv9nt/K4lsGEoHfJAAD5gWzUa0NvSPxA5T3PDJkC/sZ6Nc2PnDXD 9ds/g5WsTEZ5it0TrHm17aL8tD+oB12tzHYMPNEyhh4tPg7Y4q3sysuOh1wFKPBPUWVwi06 XJtS63q8l7AnHeekyXa0g== X-UI-Out-Filterresults: notjunk:1;V01:K0:RlTldBfRXXg=:PxJ+B1mzb1yEh6V9gvBT6b MbV7MSPQGJePiBBUMJOjLJ70bh6KEZsuotAyd2PAW1hG+4KRp2wVrpyoy+w+1yy0wwSM/PCCV ILzwKEU/nsT7NPWVU9fs4+cLwkliLIFm4NNvYMtnyyxBtWx4u/+vr9yiF7yd15do445/inqAq jkrJIrB5UHp6F/Chdlh+8BUyNmaO9e0vYutGI4M8Vxeyy0XfzpPyDxMyfLoixsaP3/pT/KrV9 xuuf2xFc9THbtnRCwqKdCgckHwTrGIm9uytV9WF9uZIiL4s1xHwOGl5R6xW+OjMo8E2friOP6 v8sL7L5EaJfReEVsQqElF2eV3uT2Dvz715S6dzRfGTp/kSoN6LMmgFLqDD1ywKyb1K4nlNOIb O/+Lk6kT/JsOTgsR5NatF/GMIK+feBXYNXr9ida14v//mKpdjIpV8sg2pbTz98+i1e9yz9IAC fPm9ZNFDYMlgEc0w7aPHWIyi6X37gFd2egCXlVkv/MHhDF4+p5kBkvcBmjhjWUmibDp7R4yUG 3GWHpaWoGooQ1qNtnoKPX7UNkNdMtkzMfwqmMVg0V2woOEZZhYb5hOv+2NuE0RheFScZvTObi ZRvYlxjj5T4HD3AgBVTlvVBCfmM3ZRfDe7azX8t1JFr3ra8QYhl/d8g3YzD4ygtl+Bpw7P1sc 1hqxns6k01OdytpqlGpP5OgB3EA4cfEtDw3v7Zm5su5d1DVKDz4kkUhaZGAL5nQKivo8= X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --Apple-Mail-B259E646-F63B-4F1E-BC80-90B9FE7005EB Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable > Am 06.06.2015 um 06:43 schrieb hydra : >=20 >=20 >=20 >> On Fri, Jun 5, 2015 at 4:56 PM, Scott Ribe w= rote: >> On Jun 5, 2015, at 8:42 AM, Igor Neyman wrote: >> > >> > The problem I see with =E2=80=9Cchecksum utility=E2=80=9D is that for i= t to work both compared servers should be =E2=80=9Cstatic=E2=80=9D: not tra= nsactions while it does its job. >>=20 >> Indeed, and that was brought up before and OP seems to be ignoring it. Wh= at magic does MySQL (supposedly) use to compare databases without interferin= g with updates? >>=20 >> One could imagine a built-in feature in PG which depends on using MVCC an= d having both sides look at the same snapshot. (Which would require repeatab= le reads.) >>=20 >> But for an external utility, that's quite a bit harder. One suggestion wh= ich would involve minimal interruption to processing: if you have a snapshot= table file system, shut down master, take snapshot, shut down replica, bring= master back up, snapshot replica, bring it back up. You *still* have the is= sue of making sure that at the moment you take master down all changes have b= een streamed to replica, and the (easier) issue of making sure replica has a= pplied them before taking its snapshot... But if you can manage to pull that= off, then you can checksum & compare as much as you want, *IF* you can actu= ally pull that off correctly ;-) >=20 >=20 > Hello, > I wasn't talking about a static check, indeed I was referring to an online= tool. >=20 > I haven't read the sources for the MySQL tool yet, however it computes the= checksum on the master, writes the operation to the binary log and while us= ing statement replication, the slave computes the checksum - then those can b= e compared. Not all data in the table are checksummed at once, but smaller c= hunks are used instead. >=20 > As Igor mentioned before, that tool can also fail and is not 100%. But I s= uppose if it would be misbehaving, chances are the checksums will differ and= you will notice it. It will probably not by accident compute the same crc/m= d5 whatever. >=20 > I have done some tests with it, comparing data after setting up replicatio= n, all went fine, then I changed some integer on the slave, it really comput= ed different checkum as expected. I also did a logical dump compare, it real= ly seems to be doing its job fine. >=20 >=20 I am entering this discussion a bit late, so maybe I am missing the point. B= ut SR is using xlog and there is a crc32 checksum on each xlog record. So wh= y would you need to compare the whole thing again when each record has been a= pproved during replication ? Reference: http://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3D= src/include/access/xlogrecord.h;h=3D4ef6c206d28437ffdecdd7097eb012055ac69c7d= ;hb=3Db94085920b016e64ee40a0f6c50199889565cc56 Jan= --Apple-Mail-B259E646-F63B-4F1E-BC80-90B9FE7005EB Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable




Am 06.06.20= 15 um 06:43 schrieb hydra <hydrap= olic@gmail.com>:



On Fri, Ju= n 5, 2015 at 4:56 PM, Scott Ribe <scott_ribe@elevated-dev.com&= gt; wrote:
On Jun 5, 2015, at 8:42 AM, Igor Neyman <ineyman@perceptron.com> wrote:
>
> The problem I see with =E2=80=9Cchecksum utility=E2=80=9D is that for i= t to work both compared servers should be =E2=80=9Cstatic=E2=80=9D:  no= t transactions while it does its job.

Indeed, and that was brought up before and OP seems to be ignoring it= . What magic does MySQL (supposedly) use to compare databases without interf= ering with updates?

One could imagine a built-in feature in PG which depends on using MVCC and h= aving both sides look at the same snapshot. (Which would require repeatable r= eads.)

But for an external utility, that's quite a bit harder. One suggestion which= would involve minimal interruption to processing: if you have a snapshottab= le file system, shut down master, take snapshot, shut down replica, bring ma= ster back up, snapshot replica, bring it back up. You *still* have the issue= of making sure that at the moment you take master down all changes have bee= n streamed to replica, and the (easier) issue of making sure replica has app= lied them before taking its snapshot... But if you can manage to pull that o= ff, then you can checksum & compare as much as you want, *IF* you can ac= tually pull that off correctly ;-)



Hello,
I wasn't talking about a static check, indeed I was refer= ring to an online tool.

I haven't read the sources for the MySQL tool= yet, however it computes the checksum on the master, writes the operation t= o the binary log and while using statement replication, the slave computes t= he checksum - then those can be compared. Not all data in the table are chec= ksummed at once, but smaller chunks are used instead.

As Igor mention= ed before, that tool can also fail and is not 100%. But I suppose if it woul= d be misbehaving, chances are the checksums will differ and you will notice i= t. It will probably not by accident compute the same crc/md5 whatever.
I have done some tests with it, comparing data after setting up replicatio= n, all went fine, then I changed some integer on the slave, it really comput= ed different checkum as expected. I also did a logical dump compare, it real= ly seems to be doing its job fine.



I am entering this discussion a bit late, so maybe I a= m missing the point. But SR is using xlog and there is a crc32 checksum on e= ach xlog record. So why would you need to compare the whole thing again when= each record has been approved during replication ?








Jan
= --Apple-Mail-B259E646-F63B-4F1E-BC80-90B9FE7005EB--