Re: replication consistency checking

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.

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