PostgreSQL数据库同步：如何同步DB A新数据至DB B且不影响额外表

针对你的需求，下面提供几种可靠的方案，均不会影响DB B中的额外处理表：

方案1：逻辑复制（原生实时同步） #

这是PostgreSQL官方推荐的增量同步方案，适合准实时同步DB A的新数据，完全不会触碰DB B的额外表。

操作步骤： #

1. 配置DB A的wal级别：
   修改DB A的postgresql.conf，设置wal_level = logical，之后重启DB A服务使配置生效。
2. 在DB A创建发布（Publication）：
   仅发布需要同步的原始表（即DB A与DB B共有的表）：

   -- 替换table1、table2为你的原始表名，可添加多个表
   CREATE PUBLICATION pub_raw_data FOR TABLE table1, table2;
   

3. 在DB B创建订阅（Subscription）：
   指向DB A的发布，完成初始全量同步+后续增量同步：

   -- 替换连接参数为DB A的实际地址、账号信息
   CREATE SUBSCRIPTION sub_raw_data 
   CONNECTION 'host=db_a_host port=5432 dbname=db_a user=sync_user password=sync_pass' 
   PUBLICATION pub_raw_data;
   

优点： #

• 原生支持，无需额外工具
• 实时/准实时同步，自动处理插入、更新、删除操作
• 仅同步指定表，完全隔离DB B的额外表

方案2：选择性导出+增量导入（定期同步） #

适合不需要实时同步，按固定周期（如每日）同步的场景，通过pg_dump精准导出原始表数据，避免覆盖DB B的额外表。

操作步骤： #

1. 从DB A导出指定原始表的增量数据：

   # 导出table1、table2的增量数据，仅导数据不导表结构
   # 可通过--where过滤新数据，比如只同步昨天之后的数据
   pg_dump -h db_a_host -U your_user -d db_a \
     -t table1 -t table2 \
     --data-only \
     --on-conflict-do-nothing \
     --where "created_at > NOW() - INTERVAL '1 day'" \
     > raw_data_increment.sql
   

   参数说明：

   • -t：指定要导出的原始表
   • --data-only：仅导出数据，不修改DB B的表结构
   • --on-conflict-do-nothing：避免重复数据插入报错（PostgreSQL 12+支持）
   • --where：过滤仅同步新数据，提升效率

2. 在DB B导入增量数据：

   psql -h db_b_host -U your_user -d db_b -f raw_data_increment.sql
   

优点： #

• 操作简单，无需修改数据库配置
• 可灵活控制同步周期和数据范围

方案3：触发器+自定义同步脚本（灵活定制） #

如果需要在同步过程中做数据转换、过滤等定制化操作，可以采用触发器+脚本的方式。

操作步骤： #

1. 在DB A的原始表创建触发器：
   新建一个中间表记录数据变动，然后给原始表添加触发器，当有数据插入/更新/删除时，将变动记录写入中间表：

   -- 创建变动记录表
   CREATE TABLE data_changes (
     change_id SERIAL PRIMARY KEY,
     table_name TEXT NOT NULL,
     change_type TEXT NOT NULL, -- INSERT/UPDATE/DELETE
     data JSONB NOT NULL,
     change_time TIMESTAMP DEFAULT NOW()
   );
   
   -- 给原始表table1创建插入触发器示例
   CREATE OR REPLACE FUNCTION log_data_change()
   RETURNS TRIGGER AS $$
   BEGIN
     INSERT INTO data_changes (table_name, change_type, data)
     VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
     RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER trigger_table1_insert
   AFTER INSERT ON table1
   FOR EACH ROW EXECUTE FUNCTION log_data_change();
   

2. 编写同步脚本：
   用Python、Shell等语言编写脚本，定期从DB A的data_changes表读取未同步的变动，然后将数据同步到DB B对应的原始表，同步完成后标记已处理。

优点： #

• 高度定制化，可添加数据校验、转换逻辑
• 完全隔离DB B的额外表

关键注意事项 #

• 确保DB A与DB B的原始表结构完全一致，若结构有变更，需先同步表结构（用pg_dump --schema-only -t table1导出结构，在DB B执行）。
• 逻辑复制需注意PostgreSQL版本兼容性（9.5+支持，推荐10+版本以获得更完善的功能）。
• 定期同步时，建议用自增ID或时间戳过滤新数据，避免重复同步全量数据。

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