PostgreSQL 逻辑复制与高可用：原生发布订阅与 pglogical 可验证实践

本文以两个数据库为例，演示如何使用原生逻辑复制或 `pglogical` 搭建读写分离/异构同步通道，给出可执行命令与验证方法，并讨论与物理复制的取舍。

## 前提与版本

• PostgreSQL 13–16；建议 15/16。
• 开启 wal_level=logical，确保具备发布订阅功能。

SHOW wal_level; -- 期望输出 logical

## 原生发布/订阅配置

在主库（publisher）：

CREATE PUBLICATION app_pub FOR TABLE orders, users;

在从库（subscriber）：

CREATE SUBSCRIPTION app_sub
CONNECTION 'host=10.0.0.10 port=5432 dbname=app user=replicator password=StrongPass#2024'
PUBLICATION app_pub;

验证：在主库写入数据，观察从库对应表是否实时更新；通过 `pg_stat_subscription` 观测延迟与错误。

SELECT * FROM pg_stat_subscription;

## 使用 pglogical（增强特性）

安装扩展并创建节点：

CREATE EXTENSION IF NOT EXISTS pglogical;
SELECT pglogical.create_node('node_pub', 'host=10.0.0.10 port=5432 dbname=app user=replicator password=StrongPass#2024');
SELECT pglogical.create_node('node_sub', 'host=10.0.0.11 port=5432 dbname=app user=replicator password=StrongPass#2024');

创建复制集与订阅：

SELECT pglogical.create_replication_set('default', include_insert := true, include_update := true, include_delete := true);
SELECT pglogical.replication_set_add_table('default', 'orders');
SELECT pglogical.create_subscription('sub1', provider_dsn := 'host=10.0.0.10 port=5432 dbname=app user=replicator password=StrongPass#2024');

## 一致性与冲突处理

• 逻辑复制不复制 DDL 与序列状态（原生），需在迁移前统一结构并评估冲突。
• 多源写入会产生冲突，需设计幂等与版本字段或只读从库策略。

## 延迟监控

SELECT * FROM pg_stat_subscription; -- 原生
SELECT * FROM pglogical.show_subscription_status(); -- pglogical

## 与物理复制的取舍

• 物理复制：高一致性与性能，适合主从/HA；不适合跨版本或部分表同步。
• 逻辑复制：灵活粒度，支持异构与在线迁移；需关注 DDL 与冲突处理。

## 相关文章（同分类热门）

• [PostgreSQL 查询计划与索引优化实战：EXPLAIN ANALYZE 与 pg_stat_statements](./PostgreSQL 查询计划与索引优化实战：EXPLAIN ANALYZE 与 pg_stat_statements.md)
• [MySQL 8.0 复制与高可用：Group Replication 与 InnoDB Cluster 实战指南](./MySQL 8.0 复制与高可用：Group Replication 与 InnoDB Cluster 实战指南.md)

## 结语

逻辑复制为跨版本与异构迁移提供了工程化路径。通过延迟观测与冲突策略设计，可以在满足业务连续性的前提下稳步完成同步与上线。