Re: replication consistency checking

On Thu, Jun 4, 2015 at 1:41 PM, Guillaume Lelarge <guillaume(at)lelarge(dot)info>
wrote:

> 2015-06-04 13:03 GMT+02:00 hydra <hydrapolic(at)gmail(dot)com>:
>
>> Thanks Greg, this looks nice.
>>
>> However my original question still remains. You know, every software has
>> bugs, every bits and pieces can break, hardware can be misbehaving. Really,
>> checking the data and counting the checksum is the only way to be sure.
>>
>>
> There is no such tool available as far as I know. Writing one that does
> that should not be quite difficult. The main issue, AFAICT, would be to
> stop writing on both while you do the check. I know many users wouldn't be
> happy with this. And if you do not stop them from writing, you'll get quite
> a lot of false positives on a busy system.
>

I have little experience with PostgreSQL, but that is not true for MySQL.
You can have your system running and doing consistency checking online.

>
>
>>
>> On Thu, Jun 4, 2015 at 11:12 AM, Greg Clough <
>> greg(dot)clough(at)cwtdigital(dot)co(dot)uk> wrote:
>>
>>> If you're using Streaming Replication, then the internal PostgreSQL code
>>> ensures consistency... but if you wanted to make sure that your standby is
>>> applying logs, then you can use something like:
>>>
>>> postgres(at)HOST2:~$ psql -c 'select pg_last_xlog_receive_location()
>>> "receive_location", pg_last_xlog_replay_location() "replay_location",
>>> pg_is_in_recovery() "recovery_status";'
>>>
>>> receive_location | replay_location | recovery_status
>>>
>>> ------------------+-----------------+-----------------
>>>
>>> 7/68002388 | 7/68002388 | t
>>>
>>> (1 row)
>>>
>>>
>>> ... or as suggested previously, use repmgr, which has a "repmgrd" daemon
>>> that keeps things monitored for you:
>>>
>>> postgres(at)HOST1:~$ psql repmgr repmgr -c 'select * from
>>> repmgr_test.repl_monitor;'
>>>
>>> primary_node | standby_node | last_monitor_time |
>>> last_apply_time | last_wal_primary_location |
>>> last_wal_standby_location | replication_lag | apply_lag
>>>
>>>
>>> --------------+--------------+-------------------------------+-------------------------------+---------------------------+---------------------------+-----------------+-----------
>>>
>>> 1 | 2 | 2015-06-04 09:09:37.920809+00 | 2015-06-04
>>> 09:09:37.712235+00 | 30/F85D7280 | 30/F85D7280
>>> | 0 | 0
>>>
>>> 1 | 2 | 2015-06-04 09:09:40.45123+00 | 2015-06-04
>>> 09:09:38.251625+00 | 30/F85D73C0 | 30/F85D73C0
>>> | 0 | 0
>>>
>>> 1 | 2 | 2015-06-04 09:09:42.966728+00 | 2015-06-04
>>> 09:09:40.75668+00 | 30/F85D74C0 | 30/F85D74C0
>>> | 0 | 0
>>>
>>> 1 | 2 | 2015-06-04 09:09:45.480471+00 | 2015-06-04
>>> 09:09:43.273428+00 | 30/F85D75C0 | 30/F85D75C0
>>> | 0 | 0
>>>
>>> 1 | 2 | 2015-06-04 09:09:47.997739+00 | 2015-06-04
>>> 09:09:45.785806+00 | 30/F85D76C0 | 30/F85D76C0
>>> | 0 | 0
>>>
>>> 1 | 2 | 2015-06-04 09:09:50.515236+00 | 2015-06-04
>>> 09:09:48.303822+00 | 30/F85D77C0 | 30/F85D77C0
>>> | 0 | 0
>>>
>>>
>>> *Greg Clough*
>>>
>>> *Database Administrator | CWT Digital*
>>>
>>> *t.* 0845 456 0070 *w.* cwtdigital.com <http://www.cwtdigital.com/>
>>>
>>>
>>> Units A/B, Level 8 North, New England House,
>>> New England Street, Brighton, East Sussex, BN1 4GH
>>>
>>> [image:
>>> https://ci6.googleusercontent.com/proxy/spbuXLCE_kiYn1CBmHRh6DoZX1WwETayC0rma88wNedREVrYPrjvkpQLcBPeuKfSkfkOpDSkuDvye9g2Ps4-BW4s=s0-d-e1-ft#http://www.cwtdigital.com/hosted/twitter.png]
>>> <https://twitter.com/cwtdigital> [image:
>>> https://ci5.googleusercontent.com/proxy/q4GOyacafkMnvs7TLpy3rOcwVK_uxMoECtqVEVpwmxiLShfkGNOZGisnwu4oHGtcEK_C-TfrLguFshp1VKC-5zZw6g=s0-d-e1-ft#http://www.cwtdigital.com/hosted/facebook.png]
>>> <https://www.facebook.com/CWTdigital> [image:
>>> https://ci4.googleusercontent.com/proxy/BVh2CnIvbQ_Rp5LFVzt4p72zUvhG3u7ksBMtlWRjbWjXL-DXIFEXXZJCYWMlSd3MshfG4UN0NAuTj1eElX8r5iu2KQ=s0-d-e1-ft#http://www.cwtdigital.com/hosted/linkedin.png]
>>> <https://www.linkedin.com/company/cwt-digital> [image:
>>> https://ci4.googleusercontent.com/proxy/QJJKpRXCvrkfCat5ycimzE7Lve3Pp9wFZWL5eBF5ELDkpl_gA8UVOc-R2p29DTS5DJmfD-FHGg2cjSf4lEuz2Ts=s0-d-e1-ft#http://www.cwtdigital.com/hosted/google.png]
>>> <https://plus.google.com/s/CWT%20Digital> [image:
>>> https://ci4.googleusercontent.com/proxy/X2ddbFYXE1hmnS4LGzoCr-Pl8fpK6yoO9R-jjF3__nbZkbuCmQR2LD3-Ts3deetyHm0L8E-hjyXeRS0YTkcqtNNGkxM=s0-d-e1-ft#http://www.cwtdigital.com/hosted/instagram.png]
>>> <http://instagram.com/cwtdigital>
>>>
>>> On 4 June 2015 at 09:47, hydra <hydrapolic(at)gmail(dot)com> wrote:
>>>
>>>>
>>>>
>>>> On Thu, Jun 4, 2015 at 8:27 AM, Venkata Balaji N <nag1010(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>>
>>>>>
>>>>> On Thu, Jun 4, 2015 at 2:49 PM, hydra <hydrapolic(at)gmail(dot)com> wrote:
>>>>>
>>>>>> After setting up streaming replication, is it possible to check
>>>>>> whether the slave has the same data as the master?
>>>>>>
>>>>>>
>>>>> Yes. There are quite a few ways to check the sync status between
>>>>> master and standby. Which version of PostgreSQL are you using by the way ?
>>>>>
>>>>> Generally, you can easily build some scripts and schedule them in cron
>>>>> to check the status. You can use functions or catalog views to monitor the
>>>>> status, this depends on the version of PG you are using.
>>>>>
>>>>> You can also use tools like "repmgr" to monitor and manage
>>>>> replication. I am not 100% sure how good or efficient it is.
>>>>>
>>>>> Regards,
>>>>> Venkata Balaji N
>>>>>
>>>>> Fujitsu Australia
>>>>>
>>>>>
>>>> Thanks for the answer, however I'm looking for a data consistency check.
>>>>
>>>> That means, you deploy your master standby replication, you're happily
>>>> running it and after 3 months you decide to do a failover. How can one be
>>>> sure the data are the same?
>>>>
>>>> I'm running PostgreSQL 9.4.
>>>>
>>>>
>>>
>>
>
>
> --
> Guillaume.
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>