如何让PostgreSQL主服务器server1作为新服务器server3某数据库的从服务器

Hey Ryan, let's break down the most practical options to get those INSERTs from server3's target table over to your primary server (server1). Since you're already running streaming replication for server2, we'll focus on approaches that play nicely with your existing PostgreSQL setup:

1. 原生逻辑复制（推荐） #

PostgreSQL 10+ 自带的逻辑复制是最适配你的场景的方案，它能精准同步指定表的特定操作（比如仅同步INSERT），无需额外第三方工具。

操作步骤： #

• 在Server1（主库）创建发布：
  先切换到目标数据库，再创建一个仅包含INSERT操作的发布：

  -- 切换到目标数据库
  \c your_target_db;
  -- 创建发布，仅同步INSERT操作
  CREATE PUBLICATION server3_insert_sync FOR TABLE your_target_table WITH (publish = 'insert');
  

• 在Server3配置订阅：
  先确保server3的wal_level设置为logical（修改postgresql.conf后重启PostgreSQL服务），再创建订阅连接到server1：

  -- 切换到server3的源数据库
  \c server3_source_db;
  -- 创建订阅，连接到server1的发布
  CREATE SUBSCRIPTION server3_to_server1
  CONNECTION 'host=server1_ip port=5432 dbname=your_target_db user=sync_user password=your_password'
  PUBLICATION server3_insert_sync;
  

• 权限配置：给server1上的sync_user分配发布表的SELECT权限，给server3上的用户分配订阅相关schema的USAGE权限。

优缺点： #

• ✅ 原生支持，无第三方依赖，稳定性高
• ✅ 仅同步必要的INSERT操作，资源占用低
• ❌ 要求PostgreSQL版本≥10，若server3版本过低无法使用
• ❌ 需提前处理主键冲突（比如server1已有相同主键记录时同步会失败，可通过ON CONFLICT逻辑或提前清理重复数据解决）

2. 触发器+Dblink自定义同步 #

如果你的PostgreSQL版本低于10，或者需要更灵活的自定义逻辑，可以用触发器结合dblink实现同步。

操作步骤： #

• 在Server3安装dblink扩展：

  CREATE EXTENSION IF NOT EXISTS dblink;
  

• 创建连接Server1的同步函数：
  编写函数，将新插入的数据通过dblink写入server1的对应表：

  CREATE OR REPLACE FUNCTION sync_insert_to_server1()
  RETURNS TRIGGER AS $$
  BEGIN
    PERFORM dblink_connect('server1_conn', 'host=server1_ip port=5432 dbname=your_target_db user=sync_user password=your_password');
    PERFORM dblink_exec('server1_conn',
      format('INSERT INTO your_target_table (col1, col2, col3) VALUES (%L, %L, %L)', NEW.col1, NEW.col2, NEW.col3)
    );
    PERFORM dblink_disconnect('server1_conn');
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  

• 在Server3的目标表创建触发器：

  CREATE TRIGGER after_insert_sync
  AFTER INSERT ON server3_source_table
  FOR EACH ROW EXECUTE FUNCTION sync_insert_to_server1();
  

优缺点： #

• ✅ 支持低版本PostgreSQL，逻辑可自定义（比如添加数据过滤、转换规则）
• ❌ 性能开销较大，高并发插入场景下可能拖慢server3的写入速度
• ❌ 无原生重试机制，若server1临时不可达会导致插入失败，需额外处理错误日志和重试逻辑

3. CDC工具（适合大数据量/复杂场景） #

如果你的插入量极大，或者未来可能需要同步更多操作（UPDATE/DELETE），可以考虑用变更数据捕获（CDC）工具（如Debezium）。它通过读取PostgreSQL的WAL日志捕获变更，再异步同步到server1。

注意点： #

• 需要部署额外服务（Kafka+Debezium连接器），学习成本较高
• 对源库性能影响极小，因为是异步读取WAL
• 适合需要解耦源库和目标库的场景

额外建议 #

• 数据一致性检查：定期用EXCEPT语句对比server3和server1的表数据，确保没有遗漏：

  -- 在server1上执行
  SELECT * FROM your_target_table
  EXCEPT
  SELECT * FROM dblink('server3_conn', 'SELECT * FROM server3_source_table') AS t(col1 type1, col2 type2);
  

• 监控同步状态：
  • 逻辑复制：用SELECT * FROM pg_stat_subscription;查看订阅状态
  • 触发器：可在同步函数中加入日志记录，方便排查问题
• 权限最小化：给同步用户仅分配必要权限（比如server1的用户只给INSERT权限，server3的用户只给触发器相关权限）

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