slony - ¿Cómo comprobar el retardo de replicación en PostgreSQL?

replicacion postgresql

Question

Me gustaría medir el tiempo entre la inserción de datos en la tabla maestra y la tabla esclava utilizando la replicación de transmisión en PostgreSQL 9.3. Para esto creo la tabla test_time con 2 campos id (serial), t (texto). Después de eso añadió un disparador:

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

Pero el tiempo es el mismo en ambas tablas. ¿Cómo puedo medir el tiempo de retardo?

Answer 1

Alf162 mencionó una buena solución en los comentarios a la respuesta de Craig Ringer; así que estoy agregando esto para aclarar.

PostgreSQL tiene una función administrativa pg_last_xact_replay_timestamp() que devuelve la marca de tiempo de la última transacción reproducida durante la recuperación. Este es el momento en el que se generó el registro WAL de confirmación o cancelación para esa transacción en el primario.

Entonces, esta consulta select now()-pg_last_xact_replay_timestamp() as replication_lag en un esclavo devolverá una duración que representa la diferencia en el tiempo entre el reloj actual y la marca de tiempo del último registro WAL aplicado desde el flujo de replicación.

Tenga en cuenta que si el maestro no está recibiendo nuevas mutaciones, no habrá registros WAL para transmitir y el retraso calculado de esta manera aumentará sin ser realmente una señal de retrasos en la replicación. Si el maestro se encuentra bajo una mutación más o menos continua, estará transmitiendo continuamente WAL y la consulta anterior es una buena aproximación del tiempo de retraso para que los cambios en el maestro se materialicen en el esclavo. La precisión obviamente se verá afectada por la forma rigurosa en que se sincronizan los relojes del sistema en los dos hosts.

Answer 2

Puede obtener la demora en bytes del lado del maestro con bastante facilidad utilizando pg_xlog_location_diff para comparar el pg_current_xlog_insert_location del maestro con la replay_location de replay_location para la entrada pg_stat_replication del backend.

Esto solo funciona cuando se ejecuta en el maestro. No puede hacerlo desde la réplica porque la réplica no tiene idea de cuán lejos está el maestro.

Además, esto no te dirá el retraso en segundos . En las versiones actuales (al menos de 9.4) de PostgreSQL, no hay una marca de tiempo asociada con un registro de confirmación o WAL. Por lo tanto, no hay forma de saber cuánto tiempo hace un LSN (posición xlog).

La única forma de obtener el retraso de la réplica en segundos en una versión actual de PostgreSQL es hacer que un proceso externo confirme una update de una tabla de marca de tiempo dedicada periódicamente. Por lo tanto, puede comparar current_timestamp en la réplica con la marca de tiempo de la entrada más reciente en esa tabla visible en la réplica para ver qué tan lejos está la réplica. Esto crea un tráfico WAL adicional que luego tendrá que mantenerse en su WAL archivado para PITR (PgBarman o lo que sea), por lo que debe equilibrar el uso de datos incrementado con la granularidad de detección de retraso que necesita.

PostgreSQL 9.5 puede agregar marcas de tiempo de confirmación que, con suerte, le permitirán saber cuánto tiempo hace una confirmación determinada y, por lo tanto, a qué distancia se encuentra una réplica en segundos de reloj de pared.

Answer 3

Si su base de datos tiene escrituras frecuentes, la siguiente consulta es una aproximación cercana para obtener el retraso esclavo

select now() - pg_last_xact_replay_timestamp() AS replication_delay;

A continuación se muestra una consulta más precisa para calcular el retardo de replicación para las bases de datos con muy pocas escrituras. Si el maestro no envía ninguna escritura al esclavo, entonces pg_last_xact_replay_timestamp () puede ser constante y, por lo tanto, no puede determinar con precisión el retraso del esclavo utilizando la consulta anterior.

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;

Answer 4

Versión ligeramente diferente de la respuesta correcta:

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

el retraso solo es importante cuando "recibir" no es igual "repetición". Ejecutar la consulta en el esclavo.

Answer 5

a partir del 10 de lanzamiento:

https://www.postgresql.org/docs/10/static/monitoring-stats.html#pg-stat-replication-view

intervalo de escritura transcurrido El tiempo transcurrido entre la descarga del WAL reciente localmente y la recepción de una notificación de que este servidor en espera lo ha escrito (pero aún no lo ha vaciado o aplicado). Esto se puede usar para medir la demora en la que se incurrió el nivel de sincronización de comunicación sincrónica en el momento de la confirmación si este servidor se configuró como un modo de espera síncrono.

Intervalo flush_lag Tiempo transcurrido entre la descarga del WAL reciente localmente y la recepción de la notificación de que este servidor en espera lo ha escrito y se ha vaciado (pero aún no se ha aplicado). Esto se puede usar para medir la demora en que se incurrió el nivel synchronous_commit remote_flush al confirmar si este servidor estaba configurado como un modo de espera síncrono.

Intervalo replay_lag Tiempo transcurrido entre la descarga del WAL reciente localmente y la recepción de la notificación de que este servidor de reserva ha escrito, vaciado y aplicado. Esto se puede usar para medir la demora en que se incurrió el nivel de sincronización de sincronización sincronía en el momento de la confirmación si este servidor se configuró como un modo de espera síncrono.

(formato mío)

Al parecer, las nuevas columnas solo se adaptan a la replicación sincrónica (de lo contrario, el maestro no conocerá el retraso exacto), por lo que la chack de retardo de replicación asíncrona parece permanecer now()-pg_last_xact_replay_timestamp() ...

Answer 6

en master, puede hacer select * from pg_stat_replication;
esto te dará:

|  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  

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

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

Aquellos pueden decirte dónde están tus compensaciones. Como puede ver en este ejemplo, la reproducción en el esclavo está detrás.