PostgreSQL逻辑复制为pgEdge复制集群提供了强大的组织能力，使您可以有选择地复制表，更细粒度地复制这些表中的更改。无论您是为实时分析、低延迟还是高可用性而使用pgEdge分布式PostgreSQL复制，优化复制配置和查询使用可以帮助您优化性能、一致性和可靠性。
Postgres复制是在数据库之间复制数据的强大工具；与物理复制不同，逻辑复制使您可以更加灵活地控制要复制的数据以及其使用方式。
本博客探讨了简化管理PostgreSQL数据库逻辑复制的查询。
监控Postgres逻辑复制状态
监控逻辑复制设置的状态对于确保复制顺利运行至关重要。查询pg_stat_subscription视图可以帮助您监视数据库中所有订阅的状态：
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– 这些列代表不同的日志序列号（LSNs），用于指示复制进度。replication_delay– 写入LSN和在订阅者上应用之间的延迟对于识别复制中的滞后非常重要。
该查询提供了逻辑复制状态的全面概述，让您快速识别复制滞后或断开的订阅者等问题。
分析Postgres复制滞后
了解复制滞后对于在复制的数据库之间保持数据的一致性和新鲜度至关重要。 pg_replication_slots 系统视图可帮助您计算发布者和订阅者之间的复制滞后：
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视图来监视复制槽的使用情况：
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 文件保留，从而导致磁盘空间浪费。以下查询标识并删除未使用的复制槽：
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文件保留可能引起的问题。
创建复制插槽
如果您需要创建一个新的逻辑复制插槽，以下查询很有用：
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订阅的状态：
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订阅的详细概述，帮助您微调复制设置并排查问题。
结论
pgEdge分布式PostgreSQL在整个集群中使用逻辑复制，提供对精确复制哪些数据以及如何存储这些数据的更大控制和灵活性。pgEdge继续开发多功能工具，提供对数据复制过程的细粒度控制。本博客中概述的查询可以帮助您有效监视、管理和优化逻辑复制集群。这些查询有助于确保数据一致性，最小化复制延迟，并预防冲突，这些对于维护强大可靠的数据库环境至关重要。
在继续使用逻辑复制时，考虑将这些查询纳入您的定期监控和维护例程中，以确保您的PostgreSQL数据库和pgEdge集群性能最佳。
Source:
https://dzone.com/articles/optimizing-debugging-postgresql-replication-queries