Flink-CDC-PostgreSQL 之 数据同步信息查询

PostgreSQL数据同步信息

在 PostgreSQL 中，您可以通过以下系统视图和函数来查询发布(Publication)、复制槽(Replication Slot)以及逻辑复制的相关信息：

1. 查询发布(Publication)信息

-- 查看所有发布
SELECT * FROM pg_publication;

-- 查看发布中的表
SELECT pubname, schemaname, tablename 
FROM pg_publication_tables pt
JOIN pg_tables t ON pt.tablename = t.tablename AND pt.schemaname = t.schemaname;

-- 查看发布的详细信息
SELECT p.pubname, p.pubowner::regrole, p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate
FROM pg_publication p;

2. 查询订阅(Subscription)信息

-- 查看所有订阅
SELECT * FROM pg_subscription;

-- 查看订阅的详细信息
SELECT subname, subconninfo, subslotname, subenabled, subpublications
FROM pg_subscription;

-- 查看订阅状态
SELECT * FROM pg_stat_subscription;

3. 查询复制槽(Replication Slot)信息

-- 查看所有复制槽
SELECT * FROM pg_replication_slots;

-- 查看逻辑复制槽的详细信息
SELECT slot_name, plugin, slot_type, database, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- 查看复制槽的统计信息
SELECT * FROM pg_stat_replication_slots;

4. 查询逻辑复制状态

-- 查看逻辑复制的发送方状态
SELECT * FROM pg_stat_replication;

-- 查看逻辑复制的接收方状态(在订阅端)
SELECT * FROM pg_stat_subscription;

-- 查看WAL发送者进程
SELECT * FROM pg_stat_wal_sender;

5. 查询表同步状态

-- 查看表的同步状态(需要超级用户权限)
SELECT * FROM pg_subscription_rel;

-- 查看特定订阅的表同步状态
SELECT sr.subid, sr.relid, n.nspname, c.relname, 
       sr.srsubstate, sr.srsublsn, sr.srrelstate
FROM pg_subscription_rel sr
JOIN pg_class c ON sr.relid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE sr.subid = (SELECT oid FROM pg_subscription WHERE subname = 'your_subscription_name');

6. 监控复制延迟

-- 计算复制延迟(字节数)
SELECT 
    client_addr, 
    client_hostname, 
    sent_lsn, 
    write_lsn, 
    flush_lsn, 
    replay_lsn,
    pg_size_pretty(sent_lsn - write_lsn) AS write_delay,
    pg_size_pretty(sent_lsn - flush_lsn) AS flush_delay,
    pg_size_pretty(sent_lsn - replay_lsn) AS replay_delay
FROM pg_stat_replication;

-- 计算订阅端的延迟
SELECT 
    subname,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), latest_end_lsn)) AS replication_delay
FROM pg_stat_subscription;

7. 常用函数

-- 创建复制槽(在发布端)
SELECT * FROM pg_create_logical_replication_slot('slot_name', 'plugin_name');

-- 删除复制槽
SELECT pg_drop_replication_slot('slot_name');

-- 获取当前WAL位置
SELECT pg_current_wal_lsn();

-- 计算两个LSN之间的差异(字节数)
SELECT pg_wal_lsn_diff('0/12345678', '0/12345600');

这些查询可以帮助您监控和管理 PostgreSQL 的逻辑复制设置，包括发布、订阅和复制槽的状态。