Как проверить задержку репликации в PostgreSQL?

Я хотел бы измерить время между вставкой данных в мастер-таблицу и ведомую таблицу, используя потоковую репликацию в PostgreSQL 9.3. Для этого я создаю таблицу test_time с 2 полями id(serial), t(text). После этого добавили триггер:

cur_time:=to_char(current_timestamp, 'HH12:MI:SS:MS:US'); update test_time set t=cur_time where id=new.id;

Но время одинаково в обеих таблицах. Как я могу измерить время задержки

Источник

8 ответов

Решение

Вы можете легко получить задержку в байтах от главной стороны, используя pg_xlog_location_diff сравнивать магистра pg_current_xlog_insert_location с replay_location для этого бэкэнда pg_stat_replication запись.

Это работает только при запуске на мастере. Вы не можете сделать это из реплики, потому что реплика понятия не имеет, насколько далеко впереди мастер.

Кроме того, это не скажет вам отставание в секундах. В текущих (по крайней мере, с 9.4) версиях PostgreSQL нет отметки времени, связанной с фиксацией или записью WAL. Поэтому невозможно определить, как давно был задан LSN (позиция xlog).

Единственный способ получить задержку реплики в секундах в текущей версии PostgreSQL - это заставить внешний процесс зафиксировать update на выделенную таблицу меток времени периодически. Так что вы можете сравнить current_timestamp на реплике до отметки времени самой последней записи в этой таблице, видимой на реплике, чтобы увидеть, насколько далеко позади реплика. Это создает дополнительный трафик WAL, который затем должен быть сохранен в вашем архивированном WAL для PITR (PgBarman или чего-либо еще), поэтому вы должны сбалансировать увеличенное использование данных с гранулярностью обнаружения запаздывания, которая вам требуется.

В PostgreSQL 9.5 могут быть добавлены временные метки коммитов, которые, надеюсь, позволят вам выяснить, как давно произошел конкретный коммит и, следовательно, насколько далеко позади находится реплика в секундах.

Источник

Alf162 упомянул хорошее решение в комментариях к ответу Крейга Рингера; поэтому я добавляю это, чтобы уточнить.

PostgreSQL имеет административную функцию pg_last_xact_replay_timestamp(), которая возвращает отметку времени последней транзакции, воспроизведенной во время восстановления. Это время, когда на первичном сервере была сгенерирована запись WAL для этой транзакции.

Так что этот запрос select now()-pg_last_xact_replay_timestamp() as replication_lag на ведомом устройстве вернет длительность, представляющую разницу во времени между текущими часами и отметкой времени последней записи WAL, примененной из потока репликации.

Обратите внимание, что если мастер не получает новые мутации, не будет записей WAL для потоковой передачи, и вычисленная таким образом задержка будет расти, фактически не являясь сигналом о задержках в репликации. Если мастер находится в состоянии более или менее непрерывной мутации, он будет непрерывно передавать потоки WAL, и приведенный выше запрос является точной аппроксимацией временной задержки для изменений в ведущем устройстве, чтобы материализоваться на ведомом устройстве. Точность, очевидно, будет зависеть от того, насколько строго синхронизированы системные часы на двух хостах.

Источник

Если ваша база данных имеет частые записи, то приведенный ниже запрос является близким приближением для получения ведомой задержки

select now() - pg_last_xact_replay_timestamp() AS replication_delay;

Ниже приведен более точный запрос для расчета задержки репликации для баз данных с очень небольшим числом операций записи. Если ведущее устройство не отправило какую-либо запись на ведомое устройство, то pg_last_xact_replay_timestamp() может быть постоянным и, следовательно, может не точно определять отставание подчиненного устройства с использованием вышеуказанного запроса.

SELECT CASE WHEN pg_last_xlog_receive_location() =
pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() -
pg_last_xact_replay_timestamp()) END AS log_delay;
Источник

Для postgresql 10 или новее (функция pg_last_xlog_receive_location() и других не существует в этой версии), я использую это:

      SELECT
  pg_is_in_recovery() AS is_slave,
  pg_last_wal_receive_lsn() AS receive,
  pg_last_wal_replay_lsn() AS replay,
  pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS synced,
  (
   EXTRACT(EPOCH FROM now()) -
   EXTRACT(EPOCH FROM pg_last_xact_replay_timestamp())
  )::int AS lag;

если вы запустите этот запрос на мастере, результаты будут:

       is_slave | receive | replay | synced | lag 
----------+---------+--------+--------+-----
 f        |         |        |        |    
(1 row)

если вы запустите этот запрос на синхронизированном ведомом устройстве, результаты будут такими:

       is_slave |  receive  |  replay   | synced | lag 
----------+-----------+-----------+--------+-----
 t        | 0/3003128 | 0/3003128 | t      | 214
(1 row)

если вы запустите этот запрос на НЕ синхронизированном ведомом устройстве, результаты будут такими:

       is_slave |  receive  |  replay   | synced | lag 
----------+-----------+-----------+--------+-----
 t        | 0/30030F0 | 0/30023B0 | f      | 129
(1 row)

задержка (в секундах) используется только в том случае, если она не синхронизирована, поэтому вы можете выполнить запрос, проверяя по порядку, если:

  • is_slave является f (имеется в виду, что это не раб, может быть хозяин);
  • synced является t (имеется в виду синхронизированный раб);
  • (если это применимо) lag <= :threshold: (имеется в виду несинхронизированное ведомое устройство, но не слишком далеко от ведущего)

Если вам нужна задержка в секундах, включая десятичные дроби, выполните:

      SELECT
  pg_is_in_recovery() AS is_slave,
  pg_last_wal_receive_lsn() AS receive,
  pg_last_wal_replay_lsn() AS replay,
  pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS synced,
  EXTRACT(SECONDS FROM now() - pg_last_xact_replay_timestamp())::float AS lag;
Источник

По состоянию на 10 выпуск:

https://www.postgresql.org/docs/10/static/monitoring-stats.html

интервал write_lag Время, прошедшее между локальной очисткой последней WAL и получением уведомления о том, что этот резервный сервер записал ее (но еще не очистил или не применил ее). Это может быть использовано для определения задержки, которая произошла при удалении уровня synous_commit remote_write при фиксации, если этот сервер был настроен как синхронный резерв.

flush_lag interval Время, прошедшее между локальной очисткой последней WAL и получением уведомления о том, что этот резервный сервер записал и сбросил его (но еще не применил его). Это может быть использовано для измерения задержки, которая возникла на уровне synchronous_commit remote_flush при фиксации, если этот сервер был настроен как синхронный резерв.

replay_lag interval Время, прошедшее между локальной очисткой последней WAL и получением уведомления о том, что этот резервный сервер написал, очистил и применил ее. Это может быть использовано для определения задержки, которая возникла при удалении уровня synous_commit remote_apply при фиксации, если этот сервер был настроен как синхронный резерв.

(форматирование мое)

Увы, новые столбцы, похоже, подходят только для синхронной репликации (иначе мастер не знал бы точную задержку), таким образом, задержка асинхронной репликации, похоже, остается now()-pg_last_xact_replay_timestamp()...

Источник

Немного другая версия правильного ответа:

postgres=# SELECT
  pg_last_xlog_receive_location() receive,
  pg_last_xlog_replay_location() replay,
  (
   extract(epoch FROM now()) -
   extract(epoch FROM pg_last_xact_replay_timestamp())
  )::int lag;

  receive   |   replay   |  lag  
------------+------------+-------
 1/AB861728 | 1/AB861728 | 2027

задержка важна только тогда, когда "получить" не равно "переиграть". выполнить запрос на подчиненном

Источник

Вы можете использовать этот простой инструмент с открытым исходным кодом на основе интерфейса командной строки, который может обеспечить визуализацию задержки репликации в реальном времени с использованием различных режимов, например, интерфейса командной строки, веб-режима, а также диаграмм на основе matplotlib для удобного отслеживания.

Репликация-задержка-визуализатор

Не стесняйтесь поднимать любые вопросы или вносить свой вклад.

Источник

На мастере можно сделать select * from pg_stat_replication;
это даст вам:

|  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  

-+-------------+-------------+-------------+-------------

 | 8D/2DA48000 | 8D/2DA48000 | 8D/2DA48000 | 89/56A0D500

те могут сказать вам, где ваши смещения. как вы можете видеть из этого примера, воспроизведение на ведомом устройстве уже позади.

Источник
Другие вопросы по тегам