Запросы для оптимизации и отладки репликации PostgreSQL

PostgreSQL

Логическая репликация PostgreSQL обеспечивает мощность и организацию кластера репликации pgEdge, позволяя вам избирательно реплицировать таблицы и, на более детальном уровне, изменения в этих таблицах. Независимо от того, используете ли вы распределенную репликацию PostgreSQL pgEdge для аналитики в реальном времени, низкой задержки или высокой доступности, оптимизация конфигурации репликации и использования запросов позволяет вам оптимизировать производительность, согласованность и надежность.

Репликация Postgres является мощным инструментом для репликации данных между базами данных; в отличие от физической репликации, логическая репликация дает вам больше контроля и гибкости над тем, какие данные реплицируются и как они используются.

Этот блог исследует запросы, которые упрощают управление логической репликацией для вашей базы данных PostgreSQL.

Мониторинг статуса логической репликации Postgres

Мониторинг статуса вашей настройки логической репликации критически важен для обеспечения ее бесперебойной работы. Запрос к представлению pg_stat_subscription может помочь вам отслеживать статус всех подписок в вашей базе данных:

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 – Название подписки.
state – Состояние процесса подписки (например, потоковая передача, синхронизация, инициализация).
sync_state – Состояние синхронизации подписки.
sent_lsn, write_lsn, flush_lsn, replay_lsn – Эти столбцы представляют различные номера последовательности журнала (LSN), которые указывают на прогресс репликации.
replication_delay – Задержка между записью LSN и ее применением на подписчике является важным для определения задержки в репликации.

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

Анализ задержки репликации в Postgres

Понимание задержки репликации необходимо для поддержания согласованности и актуальности данных в ваших реплицируемых базах данных. Системный вид pg_replication_slots может помочь вам рассчитать задержку репликации между издателем и подписчиком:

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 – Имя используемого слота репликации.
replication_lag_bytes – Разница в байтах между текущей позицией WAL на издателе и последней позицией WAL, подтвержденной подписчиком.
replication_lag_time – Временная разница между последней воспроизведенной транзакцией на подписчике и текущим временем.

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

Мониторинг использования слотов репликации

Слоты репликации критически важны в логической репликации, обеспечивая сохранение сегментов WAL до их обработки всеми абонентами. Вы можете выполнить запрос к представлению pg_replication_slots для отслеживания использования слотов репликации:

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 – Имя слота репликации.
slot_lag_bytes – Задержка в байтах между текущей позицией WAL и последней позицией, подтвержденной как записанная слотом.

Мониторинг использования слотов репликации критичен для предотвращения проблем, связанных с сохранением сегментов WAL, что может потенциально привести к исчерпанию места на диске у издателя.

Удаление неиспользуемых слотов репликации

Со временем вы можете накапливать неиспользуемые слоты репликации, особенно после удаления абонентов или изменения конфигураций репликации. Эти неиспользуемые слоты могут вызвать ненужное сохранение файлов WAL, что приведет к нецелесообразному расходованию дискового пространства. Следующий запрос выявляет и удаляет неиспользуемые слоты репликации:

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 $$;

Этот запрос перебирает ваши неактивные слоты репликации и использует функцию управления pg_drop_replication_slot для их удаления. Регулярная очистка неиспользуемых слотов репликации обеспечит эффективную работу вашей базы данных и предотвратит потенциальные проблемы с хранением файлов WAL.

Создание слотов репликации

Если вам нужно создать новый логический слот репликации, следующий запрос будет полезен:

SQL

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

​

slot_name | xlog_position 

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

 my_slot   | 0/3000128

Этот запрос использует функцию pg_create_logical_replication_slot для создания нового логического слота репликации с указанным именем и выходным плагином (pgoutput в нашем примере). Запрос полезен при настройке новых конфигураций логической репликации; используйте его, чтобы убедиться, что подписчик может начать получать изменения с правильной точки в записях WAL.

Оптимизация логической репликации с pglogical

Если вы используете расширение pglogical для более продвинутых возможностей логической репликации, следующий запрос поможет вам проверить статус всех подписок 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 – Имя подписки pglogical.
replay_lag – Задержка между последним полученным изменением и текущим временем.
pending_changes – Количество ожидающих применения изменений для подписчика.

Этот запрос предоставляет подробный обзор ваших подписок pglogical, помогая настраивать параметры репликации и устранять проблемы.

Заключение

Распределенный PostgreSQL pgEdge использует логическую репликацию по всему кластеру, предоставляя больший контроль и гибкость над тем, какие данные реплицируются и как эти данные хранятся. pgEdge продолжает разрабатывать универсальные инструменты, предлагающие точный контроль над процессами репликации данных. Описанные в этом блоге запросы могут помочь вам эффективно мониторить, управлять и оптимизировать ваши логические репликационные кластеры. Эти запросы помогают обеспечить согласованность данных, минимизировать задержку репликации и предотвращать конфликты, что является важным для поддержания надежной и стабильной базы данных.

Продолжая работать с логической репликацией, рассмотрите возможность включения этих запросов в ваши регулярные процедуры мониторинга и обслуживания, чтобы обеспечить оптимальную производительность ваших баз данных PostgreSQL и кластеров pgEdge.

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