如何追踪PostgreSQL逻辑复制初始加载的进度

有成熟的内置方案可以追踪逻辑复制初始加载（基表同步阶段）的全量进度，无需额外安装第三方插件，可直接通过系统视图获取你需要的所有统计数据：

1. 基础同步状态查询 #

在订阅节点执行以下查询，可直接查看每张表的同步阶段和完成进度（PostgreSQL 14及以上版本支持原生进度百分比）：

SELECT 
  srrelid::regclass AS table_name,
  CASE srsubstate
    WHEN 'i' THEN '初始化中'
    WHEN 'd' THEN '数据拷贝中'
    WHEN 's' THEN '同步完成等待catchup'
    WHEN 'r' THEN '已进入持续复制阶段'
    WHEN 'e' THEN '同步报错'
  END AS sync_state,
  srsubprogress AS completed_percent
FROM pg_subscription_rel;

• 若srsubstate返回e说明该表同步过程中出现错误，错误详情可查PostgreSQL服务日志。

2. 已复制/剩余数据量计算 #

第一步：先在发布节点查询待同步的全量数据总大小 #

SELECT 
  pg_size_pretty(SUM(pg_total_relation_size(relid))) AS total_sync_size
FROM pg_publication_tables
JOIN pg_class ON relname = tablename AND relnamespace = schemaname::regnamespace
WHERE pubname = '替换为你的发布名称';

第二步：在订阅节点计算已同步和剩余数据量 #

WITH completed_tables AS (
  SELECT srrelid FROM pg_subscription_rel WHERE srsubstate IN ('s','r')
),
in_progress_tables AS (
  SELECT srrelid, srsubprogress FROM pg_subscription_rel WHERE srsubstate = 'd'
)
SELECT 
  pg_size_pretty(
    SUM(pg_total_relation_size(c.srrelid)) 
    + COALESCE(SUM(pg_total_relation_size(p.srrelid) * p.srsubprogress / 100), 0)
  ) AS copied_size,
  pg_size_pretty(
    (SELECT SUM(pg_total_relation_size(relid)) FROM pg_publication_tables JOIN pg_class ON relname = tablename AND relnamespace = schemaname::regnamespace WHERE pubname = '替换为你的发布名称')
    - (SUM(pg_total_relation_size(c.srrelid)) + COALESCE(SUM(pg_total_relation_size(p.srrelid) * p.srsubprogress / 100), 0))
  ) AS remaining_size
FROM completed_tables c
LEFT JOIN in_progress_tables p ON true;

如果你使用的是PostgreSQL 14以下版本，没有srsubprogress字段，可以通过对比发布端和订阅端同表的行数、表大小来估算当前同步中的表的完成比例。

3. 预估完成时间 #

PostgreSQL 13及以上版本可以通过pg_stat_progress_copy视图查询当前正在拷贝的表的实时速度和预估剩余时间：

SELECT 
  relid::regclass AS syncing_table,
  pg_size_pretty(bytes_processed) AS processed_bytes,
  pg_size_pretty(bytes_total) AS total_table_bytes,
  ROUND((extract(epoch from now() - start_time) / bytes_processed * (bytes_total - bytes_processed)) / 60, 2) AS remaining_minutes
FROM pg_stat_progress_copy
WHERE command = 'LOGICAL REPLICATION COPY';

如果有多个待同步表，可以将当前同步表的预估剩余时间，加上其余未开始同步的表的大小除以平均复制速度来计算总预估时间。你可以间隔5-10分钟两次查询已同步数据量，计算出平均复制速度后即可算出整体预估耗时。

4. 复制报错信息查询 #

除了查看PostgreSQL服务日志外，还可以直接通过系统视图查询最近的复制错误：

SELECT 
  subname AS subscription_name,
  last_error_time,
  last_error_msg
FROM pg_stat_subscription
WHERE subname = '替换为你的订阅名称';

如果某张表同步失败，pg_subscription_rel中对应的srsubstate会返回e，错误详情会记录在last_error_msg字段或者服务日志中。

内容的提问来源于stack exchange，提问作者gaurav garg