Re: replication consistency checking

2015-06-04 14:04 GMT+02:00 hydra <hydrapolic(at)gmail(dot)com>:

>
>
> 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.
>
>

I was only speaking about PostgreSQL. I have no experience with MySQL.

>>
>>>
>>> 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
>>
>
>

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com