From: | Greg Clough <greg(dot)clough(at)cwtdigital(dot)co(dot)uk> |
---|---|
To: | hydra <hydrapolic(at)gmail(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: replication consistency checking |
Date: | 2015-06-04 09:12:45 |
Message-ID: | CABZWJifPU=p-W65AdwVcRY5qvrA47+R+7OkhiPhexnXePMpyDg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | hydra | 2015-06-04 11:03:00 | Re: replication consistency checking |
Previous Message | hydra | 2015-06-04 08:47:03 | Re: replication consistency checking |