介绍
PostgreSQL 复制是一个可在数据库之间复制数据的强大工具；与物理复制不同，逻辑复制在选择复制哪些数据和使用方式上面，给您提供了更多的控制和灵活性。
本文探讨了一些有用的查询，可用来更轻松地管理 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：订阅进程的状态（例如：streaming、catchup、initializing）。
• sync_state：订阅的同步状态。
• sent_lsn, write_lsn, flush_lsn, replay_lsn：这些列表示各种日志序列号（LSN），用于指示复制进度。
• 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 段保留相关的问题至关重要，这可能会导致发布端上的磁盘空间耗尽。
删除未使用的复制槽
随着时间的推移，您可能会累积未使用的复制槽，尤其是在删除订阅者或更改复制配置之后。这些未使用的复制槽，可能会导致不必要地保留 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 订阅的详细概述，可帮助您优化调整复制设置并解决问题。
