Re: replication consistency checking

Поиск
Список
Период
Сортировка
От Greg Clough
Тема Re: replication consistency checking
Дата
Msg-id CABZWJifPU=p-W65AdwVcRY5qvrA47+R+7OkhiPhexnXePMpyDg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: replication consistency checking  (hydra <hydrapolic@gmail.com>)
Список 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@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@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


Units A/B, Level 8 North, New England House, 
New England Street, Brighton, East Sussex, BN1 4GH

https://ci6.googleusercontent.com/proxy/spbuXLCE_kiYn1CBmHRh6DoZX1WwETayC0rma88wNedREVrYPrjvkpQLcBPeuKfSkfkOpDSkuDvye9g2Ps4-BW4s=s0-d-e1-ft#http://www.cwtdigital.com/hosted/twitter.png https://ci5.googleusercontent.com/proxy/q4GOyacafkMnvs7TLpy3rOcwVK_uxMoECtqVEVpwmxiLShfkGNOZGisnwu4oHGtcEK_C-TfrLguFshp1VKC-5zZw6g=s0-d-e1-ft#http://www.cwtdigital.com/hosted/facebook.png https://ci4.googleusercontent.com/proxy/BVh2CnIvbQ_Rp5LFVzt4p72zUvhG3u7ksBMtlWRjbWjXL-DXIFEXXZJCYWMlSd3MshfG4UN0NAuTj1eElX8r5iu2KQ=s0-d-e1-ft#http://www.cwtdigital.com/hosted/linkedin.png https://ci4.googleusercontent.com/proxy/QJJKpRXCvrkfCat5ycimzE7Lve3Pp9wFZWL5eBF5ELDkpl_gA8UVOc-R2p29DTS5DJmfD-FHGg2cjSf4lEuz2Ts=s0-d-e1-ft#http://www.cwtdigital.com/hosted/google.png https://ci4.googleusercontent.com/proxy/X2ddbFYXE1hmnS4LGzoCr-Pl8fpK6yoO9R-jjF3__nbZkbuCmQR2LD3-Ts3deetyHm0L8E-hjyXeRS0YTkcqtNNGkxM=s0-d-e1-ft#http://www.cwtdigital.com/hosted/instagram.png


On 4 June 2015 at 09:47, hydra <hydrapolic@gmail.com> wrote:


On Thu, Jun 4, 2015 at 8:27 AM, Venkata Balaji N <nag1010@gmail.com> wrote:


On Thu, Jun 4, 2015 at 2:49 PM, hydra <hydrapolic@gmail.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.


Вложения

В списке pgsql-admin по дате отправления:

Предыдущее
От: Ravi Krishna
Дата:
Сообщение: Re: replication consistency checking
Следующее
От: "AL-Temimi, Muthana"
Дата:
Сообщение: Problem with Out of Memory and no more connecction possible