CDC 之 PG表数据同步

在 PostgreSQL 中，数据库表的同步可通过 物理复制（流复制）、逻辑复制、第三方工具 或 自定义脚本 实现，具体选择需结合实时性、跨平台、数据一致性等需求。以下是详细方案与操作步骤：

一、物理复制（流复制）

适用场景：高可用性、灾难恢复、毫秒级实时同步。
原理：主服务器将 WAL（Write-Ahead Logging）日志实时流式传输到从服务器，确保数据物理结构一致。
配置步骤：

1. 主服务器配置：
   • 修改 postgresql.conf：

     wal_level = replica  # 或 logical（若需逻辑复制）
     max_wal_senders = 10  # 最大流复制连接数
     wal_keep_segments = 1000  # 保留的 WAL 文件数（防止被回收）
     

   • 配置 pg_hba.conf 允许从服务器连接：

     host    replication     replicator_user    192.168.1.100/32    md5
     

2. 从服务器配置：
   • 使用 pg_basebackup 初始化数据：

     pg_basebackup -h 主服务器IP -D /path/to/data -U replicator_user -P -v -R
     

   • 创建 recovery.conf（PostgreSQL 12+ 需在 postgresql.conf 中配置 primary_conninfo）：

     standby_mode = on
     primary_conninfo = 'host=主服务器IP port=5432 user=replicator_user password=密码'
     

3. 启动从服务器：从库会自动连接主库并开始同步。

优势：实时性强、数据一致性高。
局限：从库通常为只读，无法灵活选择同步表。

二、逻辑复制

适用场景：跨版本/跨平台复制、细粒度表同步、非 PostgreSQL 目标库。
原理：通过发布（Publication）和订阅（Subscription）机制，仅同步指定表的逻辑变更（INSERT/UPDATE/DELETE）。
配置步骤：

1. 主服务器（发布端）配置：
   • 修改 postgresql.conf：

     wal_level = logical
     max_replication_slots = 10  # 最大复制槽数
     

   • 创建发布：

     CREATE PUBLICATION my_pub FOR TABLE table1, table2;
     -- 或发布所有表：
     CREATE PUBLICATION my_pub FOR ALL TABLES;
     

2. 从服务器（订阅端）配置：
   • 创建订阅：

     CREATE SUBSCRIPTION my_sub
     CONNECTION 'host=主服务器IP port=5432 user=subscriber_user password=密码 dbname=源数据库'
     PUBLICATION my_pub;
     

3. 验证同步：
   • 在从库查询表数据，确认与主库一致。

优势：灵活选择表、支持跨平台。
局限：性能略低于物理复制，需 PostgreSQL 10+。

三、第三方工具

推荐工具：

1. pglogical：基于逻辑复制的扩展，支持多主复制、列级过滤。
2. Bucardo：支持多主复制、异构数据库同步。
3. Debezium + Kafka：通过 CDC（变更数据捕获）实现实时同步，适合复杂架构。

操作示例（pglogical）：

1. 在主库和从库安装 pglogical 扩展：

   CREATE EXTENSION pglogical;
   

2. 主库创建提供节点和表：

   SELECT pglogical.create_node(
     node_name := 'provider_node',
     dsn := 'host=主服务器IP port=5432 user=pglogical_user password=密码'
   );
   SELECT pglogical.create_replication_set('my_set');
   SELECT pglogical.replication_set_add_table('my_set', 'table1');
   

3. 从库创建订阅节点并同步：

   SELECT pglogical.create_node(
     node_name := 'subscriber_node',
     dsn := 'host=从服务器IP port=5432 user=pglogical_user password=密码'
   );
   SELECT pglogical.create_subscription(
     subscription_name := 'my_sub',
     provider_dsn := 'host=主服务器IP port=5432 user=pglogical_user password=密码',
     replication_sets := ARRAY['my_set']
   );
   

四、自定义脚本同步

适用场景：定时同步、简单表同步。
方法：

1. 使用 pg_dump + pg_restore：

   # 导出表数据
   pg_dump -h 主服务器IP -U 用户 -t 表名 -d 数据库 -f dump.sql
   # 导入到从库
   psql -h 从服务器IP -U 用户 -d 数据库 -f dump.sql
   

2. 编写 PL/pgSQL 函数：通过函数捕获变更并插入目标表（需触发器支持）。

优势：简单可控。
局限：非实时，需手动或定时执行。

五、延迟复制控制

场景：需要延迟同步以防止误操作传播。
配置：在从库的 recovery.conf（或 postgresql.conf）中设置：

recovery_min_apply_delay = 5min  # 延迟5分钟应用变更

影响：

• 主库事务提交后，从库会延迟指定时间再应用。
• 需配合 synchronous_commit = remote_apply 使用时，主库会等待从库确认，可能导致性能下降。

六、方案对比与推荐

方案	实时性	灵活性	复杂度	适用场景
物理复制	毫秒级	低	高	高可用、灾难恢复
逻辑复制	秒级	高	中	跨版本、细粒度同步
第三方工具	秒级	高	高	复杂架构、异构数据库
自定义脚本	分钟级	低	低	简单定时同步

推荐选择：

• 实时高可用：物理复制（流复制）。
• 跨版本/平台：逻辑复制或 pglogical。
• 企业级复杂场景：Debezium + Kafka。
• 简单定时任务：pg_dump + pg_restore。