Re: replication consistency checking

On Tue, Jun 9, 2015 at 9:20 PM, Jan Lentfer <Jan(dot)Lentfer(at)web(dot)de> wrote:

>
>
>
>
> Am 06.06.2015 um 06:43 schrieb hydra <hydrapolic(at)gmail(dot)com>:
>
>
>
> On Fri, Jun 5, 2015 at 4:56 PM, Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
> wrote:
>
>> On Jun 5, 2015, at 8:42 AM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
>> >
>> > The problem I see with “checksum utility” is that for it to work both
>> compared servers should be “static”: not 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
>> interfering with updates?
>>
>> One could imagine a built-in feature in PG which depends on using MVCC
>> and having both sides look at the same snapshot. (Which would require
>> repeatable reads.)
>>
>> But for an external utility, that's quite a bit harder. One suggestion
>> which would involve minimal interruption to processing: if you have a
>> snapshottable file system, shut down master, take snapshot, shut down
>> replica, bring master 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 been streamed to replica, and the (easier) issue of
>> making sure replica has applied 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 actually pull that off correctly ;-)
>>
>>
>
> Hello,
> I wasn't talking about a static check, indeed I was referring 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 to the binary log and while
> using statement replication, the slave computes the checksum - then those
> can be compared. Not all data in the table are checksummed at once, but
> smaller chunks are used instead.
>
> As Igor mentioned before, that tool can also fail and is not 100%. But I
> suppose 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/md5 whatever.
>
> I have done some tests with it, comparing data after setting up
> replication, all went fine, then I changed some integer on the slave, it
> really computed different checkum as expected. I also did a logical dump
> compare, it really seems to be doing its job fine.
>
>
>
> I am entering this discussion a bit late, so maybe I am missing the point.
> But SR is using xlog and there is a crc32 checksum on each xlog record. So
> why would you need to compare the whole thing again when each record has
> been approved during replication ?
>

Hello Jan,
you don't have it if you don't want to. However I'd like to have the
possibility to do so and thus I was asking - is it possible? Are you guys
doing it?

The reasons are mentioned above, but still:
- bugs can appear anywhere,
- the bug report mentioned before also states to check the data, but does
not give and hints how to do it, that's why I asked here

>
>>
>>
>>
>>
>>
>>
> Reference:
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/access/xlogrecord.h;h=4ef6c206d28437ffdecdd7097eb012055ac69c7d;hb=b94085920b016e64ee40a0f6c50199889565cc56
>
> Jan
>