Consultas para Optimizar y Depurar la Replicación de PostgreSQL

Tutoriales

PostgreSQL

La replicación lógica de PostgreSQL proporciona la potencia y organización detrás de un clúster de replicación pgEdge, lo que te permite replicar tablas de forma selectiva y, a un nivel más granular, los cambios en esas tablas. Ya sea que estés utilizando la replicación de PostgreSQL distribuida de pgEdge para análisis en tiempo real, baja latencia o alta disponibilidad, optimizar la configuración de replicación y el uso de consultas te permite optimizar el rendimiento, la consistencia y la fiabilidad.

La replicación de Postgres es una herramienta poderosa para replicar datos entre bases de datos; a diferencia de la replicación física, la replicación lógica te brinda más control y flexibilidad sobre qué datos se replican y cómo se utilizan.

Este blog explora consultas que facilitan la gestión de la replicación lógica para tu base de datos PostgreSQL.

Monitoreo del estado de la replicación lógica de Postgres

Monitorear el estado de tu configuración de replicación lógica es fundamental para asegurar que tu replicación funcione sin problemas. Consultar la vista pg_stat_subscription puede ayudarte a monitorear el estado de todas las suscripciones en tu base de datos:

SQL

SELECT 

    subname AS subscription_name,

    pid AS process_id,

    usename AS user_name,

    application_name,

    client_addr AS client_address,

    state,

    sync_state,

    sent_lsn,

    write_lsn,

    flush_lsn,

    replay_lsn,

    clock_timestamp() - write_lsn_timestamp AS replication_delay

FROM 

    pg_stat_subscription

ORDER BY 

    subscription_name;

​

​

subscription_name | process_id | user_name | application_name | client_address |    state    | sync_state |   sent_lsn   |  write_lsn   |  flush_lsn   |  replay_lsn  | replication_delay

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

 sub1              |      23456 | postgres  | logical_rep_sub   | 192.168.1.10   | streaming   | synced     | 0/3000128    | 0/3000128    | 0/3000128    | 0/3000128    | 00:00:00.12345

 sub2              |      23478 | postgres  | logical_rep_sub   | 192.168.1.11   | catchup     | async      | 0/4000238    | 0/4000200    | 0/40001F8    | 0/40001E0    | 00:00:02.67890

subname – El nombre de la suscripción.
state – El estado del proceso de suscripción (por ejemplo, transmisión, recuperación, inicialización).
sync_state – El estado de sincronización de la suscripción.
sent_lsn, write_lsn, flush_lsn, replay_lsn – Estas columnas representan varios Números de Secuencia de Registro (LSN) que indican el progreso de la replicación.
replication_delay – El retraso entre el LSN que se escribe y su aplicación en el suscriptor es crucial para identificar el retraso en la replicación.

Esta consulta proporciona una visión general completa del estado de la replicación lógica, permitiéndote identificar rápidamente problemas como el retraso en la replicación o suscriptores desconectados.

Analizando el Retraso de Replicación de Postgres

Entender el retraso de replicación es esencial para mantener la consistencia y frescura de los datos en tus bases de datos replicadas. La vista del sistema pg_replication_slots puede ayudarte a calcular el retraso de replicación entre el publicador y el suscriptor:

SQL

SELECT 

    s.slot_name,

    s.active,

    s.restart_lsn,

    pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn) AS replication_lag_bytes,

    clock_timestamp() - pg_last_xact_replay_timestamp() AS replication_lag_time

FROM 

    pg_replication_slots s

WHERE 

    s.active = true

AND 

    s.plugin = 'pgoutput';

​

​

slot_name | active | restart_lsn | replication_lag_bytes | replication_lag_time 

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

 slot1     | t      | 0/3000128   |                 65536 | 00:00:00.12345

 slot2     | t      | 0/4000238   |                131072 | 00:00:02.67890

slot_name – El nombre del slot de replicación que se está utilizando.
replication_lag_bytes – La diferencia en bytes entre la posición actual del WAL en el publicador y la última posición del WAL reconocida por el suscriptor.
replication_lag_time – La diferencia de tiempo entre la última transacción reproducida en el suscriptor y el tiempo actual.

Esta consulta te ayuda a evaluar el tamaño y el retraso basado en el tiempo en tu replicación lógica, lo que te permite tomar medidas proactivas si el retraso excede los umbrales aceptables.

Monitoreo del Uso de Slots de Replicación

Los slots de replicación son críticos en la replicación lógica, asegurando que los segmentos WAL se retengan hasta que todos los suscriptores los procesen. Puedes consultar la vista pg_replication_slots para monitorear el uso de los slots de replicación:

SQL

SELECT

    slot_name,

    plugin,

    slot_type,

    active,

    confirmed_flush_lsn,

    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS slot_lag_bytes

FROM 

    pg_replication_slots

WHERE 

    slot_type = 'logical';

​

​

slot_name | plugin  | slot_type | active | confirmed_flush_lsn | slot_lag_bytes 

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

 slot1     | pgoutput| logical   | t      | 0/3000128           |           65536

 slot2     | pgoutput| logical   | t      | 0/4000238           |          131072

slot_name – El nombre del slot de replicación.
slot_lag_bytes – El retraso en bytes entre la posición WAL actual y la última posición confirmada como vaciada por el slot.

Monitorear el uso de los slots de replicación es crucial para prevenir problemas relacionados con la retención de segmentos WAL, que podrían llevar a un agotamiento del espacio en disco en el publicador.

Eliminación de Slots de Replicación No Utilizados

Con el tiempo, puedes acumular slots de replicación no utilizados, especialmente después de eliminar suscriptores o cambiar configuraciones de replicación. Estos slots no utilizados pueden causar una retención innecesaria de archivos WAL, lo que lleva a un desperdicio de espacio en disco. La siguiente consulta identifica y elimina los slots de replicación no utilizados:

SQL

DO $$

DECLARE

    slot_record RECORD;

BEGIN

    FOR slot_record IN

        SELECT slot_name FROM pg_replication_slots WHERE active = false

    LOOP

        EXECUTE format('SELECT pg_drop_replication_slot(%L)', slot_record.slot_name);

    END LOOP;

END $$;

Esta consulta itera sobre tus slots de replicación inactivos y utiliza la función de gestión pg_drop_replication_slot para eliminarlos. Limpiar regularmente los slots de replicación no utilizados asegurará que tu base de datos siga siendo eficiente y evitará problemas potenciales con la retención de archivos WAL.

Creando Slots de Replicación

Si necesitas crear un nuevo slot de replicación lógica, la siguiente consulta es útil:

SQL

SELECT * FROM pg_create_logical_replication_slot('my_slot', 'pgoutput');

​

slot_name | xlog_position 

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

 my_slot   | 0/3000128

Esta consulta utiliza la función pg_create_logical_replication_slot para crear un nuevo slot de replicación lógica con el nombre especificado y el plugin de salida (pgoutput en nuestro ejemplo). La consulta es útil al configurar nuevas configuraciones de replicación lógica; úsala para confirmar que el suscriptor puede comenzar a recibir cambios desde el punto correcto en los registros WAL.

Optimizando la Replicación Lógica Con pglogical

Si estás utilizando la extensión pglogical para capacidades de replicación lógica más avanzadas, la siguiente consulta puede ayudarte a verificar el estado de todas las suscripciones pglogical:

SQL

SELECT 

    subscription_name,

    status,

    received_lsn,

    replay_lag,

    last_received_change,

    pending_changes

FROM 

    pglogical.show_subscription_status();

​

​

subscription_name | status   | received_lsn | replay_lag | last_received_change | pending_changes 

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

 sub_pglogical1    | replicating | 0/3000128 | 00:00:01.234 | 2024-08-22 10:30:00 | 5

 sub_pglogical2    | idle      | 0/4000238 | 00:00:00.000 | 2024-08-22 10:29:30 | 0

subscription_name – El nombre de la suscripción pglogical.
replay_lag – La diferencia entre el último cambio recibido y el tiempo actual.
pending_changes – El número de cambios pendientes de aplicarse al suscriptor.

Esta consulta proporciona una visión detallada de tus suscripciones pglogical, ayudándote a ajustar la configuración de replicación y solucionar problemas.

Conclusión

pgEdge Distributed PostgreSQL utiliza replicación lógica en todo tu clúster, proporcionando un mayor control y flexibilidad sobre qué datos se replican y cómo se almacenan esos datos. pgEdge continúa desarrollando herramientas versátiles que ofrecen un control detallado sobre los procesos de replicación de datos. Las consultas descritas en este blog pueden ayudarte a monitorear, gestionar y optimizar eficazmente tus clústeres de replicación lógica. Estas consultas ayudan a asegurar la consistencia de los datos, minimizar la latencia de replicación y prevenir conflictos, todos esenciales para mantener un entorno de base de datos robusto y fiable.

A medida que continúas trabajando con la replicación lógica, considera incorporar estas consultas en tus rutinas regulares de monitoreo y mantenimiento para asegurar que tus bases de datos PostgreSQL y clústeres pgEdge funcionen de manera óptima.

Source:
https://dzone.com/articles/optimizing-debugging-postgresql-replication-queries