手动复制监控

Track replication health and performance.

使用源端的槽指标来监控任何逻辑复制消费者。如果目标是另一个 Postgres 数据库，也要检查它的订阅状态。

🌐 Use source-side slot metrics to monitor any logical replication consumer. If the destination is another Postgres database, also check its subscription state.

仪表板：在报表中查看复制延迟。
数据库：在源 Postgres 数据库的 Primary 上运行查询，在目标 Postgres 数据库的 Subscriber 上运行查询。
指标：使用你项目的 Prometheus 端点来跟踪复制槽的延迟变化。

主要 #

🌐 Primary

在源数据库上运行这些查询。

🌐 Run these queries on the source database.

复制槽状态 #

🌐 Replication slot status

复制槽有独立的连接和 WAL 可用性字段：

🌐 A replication slot has separate connection and WAL availability fields:

当消费者连接到插槽时，active就是true`。
wal_status 是 reserved 或 extended，当所需 WAL 可用时；插槽有可能失去所需 WAL 时是 unreserved；在所需 WAL 被移除后是 lost。
safe_wal_size 估计在插槽有风险之前还能写入多少字节。当 max_slot_wal_keep_size 是无限的或插槽已丢失时，它是 NULL。

检查这些字段以及从槽 restart_lsn 保留的 WAL 数量：

🌐 Check these fields and the amount of WAL retained from the slot's restart_lsn:

1
select
2
  slot_name,
3
  active,
4
  wal_status,
5
  pg_size_pretty(safe_wal_size) as wal_retention_remaining,
6
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal
7
from pg_replication_slots;

一个不活跃的插槽仍然可以保留 WAL。调查那些保留的 WAL 不断增加、剩余保留时间不断减少，或者 wal_status 变成 unreserved 的插槽。lost 插槽无法从之前的位置继续，通常需要重新进行初始同步。

🌐 An inactive slot can still retain WAL. Investigate a slot whose retained WAL keeps growing, whose remaining retention keeps shrinking, or whose wal_status becomes unreserved. A lost slot can't continue from its previous position and usually requires a new initial sync.

复制状态和延迟 #

🌐 Replication status and lag

pg_stat_replication 表显示了连接到主数据库的所有副本的状态。

🌐 The pg_stat_replication table shows the status of any replicas connected to the primary database.

1
select
2
  pid,
3
  application_name,
4
  state,
5
  sent_lsn,
6
  write_lsn,
7
  flush_lsn,
8
  replay_lsn,
9
  sync_state
10
from pg_stat_replication;

WAL 大小 #

🌐 WAL size

可以使用 pg_ls_waldir() 函数来检查 WAL 大小：

🌐 The WAL size can be checked using the pg_ls_waldir() function:

1
select pg_size_pretty(sum(size)) as wal_directory_size
2
from pg_ls_waldir();

检查 LSN #

🌐 Check the LSN

1
select pg_current_wal_lsn();

订阅者 #

🌐 Subscriber

只有当目标是 Postgres 逻辑订阅者时，才在目标上运行这些查询。对于其他目标系统，使用它们的监控工具。

🌐 Run these queries on the destination only when it is a Postgres logical subscriber. For other destination systems, use their monitoring tools.

订阅状态 #

🌐 Subscription status

pg_subscription 表显示了副本上任何订阅的状态，而 pg_subscription_rel 表显示了订阅中每个表的状态。

🌐 The pg_subscription table shows the status of any subscriptions on a replica and the pg_subscription_rel table shows the status of each table within a subscription.

[pg_subscription_rel](https://www.postgresql.org/docs/15/catalog-pg-subscription-rel.html) 中的 srsubstate 列记录了每个表的同步状态：

🌐 The srsubstate column in pg_subscription_rel records each table's synchronization state:

i：正在初始化
d：复制现有数据
f：复制完成；同步尚未完成
s：同步的
r：准备好进行正常复制

1
select
2
  sub.subname as subscription_name,
3
  srel.srrelid::regclass as table_name,
4
  srel.srsubstate as replication_state,
5
  case srel.srsubstate
6
    when 'i' then 'Initializing'
7
    when 'd' then 'Copying data'
8
    when 'f' then 'Finished copying'
9
    when 's' then 'Synchronized'
10
    when 'r' then 'Ready'
11
    else 'Unknown'
12
  end as state_description,
13
  srel.srsublsn as synchronization_lsn
14
from pg_subscription sub
15
join pg_subscription_rel srel on sub.oid = srel.srsubid
16
order by table_name;

synchronization_lsn 协调表的初始同步。它不是持续更新的复制检查点。

检查 LSN #

🌐 Check the LSN

使用 pg_stat_subscription 来检查订阅工作者报告的 WAL 位置：

🌐 Use pg_stat_subscription to inspect the WAL positions reported by subscription workers:

1
select
2
  subname,
3
  pid,
4
  received_lsn,
5
  latest_end_lsn,
6
  latest_end_time
7
from pg_stat_subscription;

缺少 pid 表示该工作线程没有运行。received_lsn 显示接收到的最后 WAL 位置，而 latest_end_lsn 和 latest_end_time 显示报告给发布者的最新位置和时间。仅凭这些字段不能确认所有表都已完成初始同步。

🌐 A missing pid means that worker is not running. received_lsn shows the last WAL position received, while latest_end_lsn and latest_end_time show the latest position and time reported to the publisher. These fields do not by themselves confirm that all tables have finished initial synchronization.