跨服务器PostgreSQL集群数据同步咨询：差异化数据留存需求

方案一：原生逻辑复制（推荐，实时性高） #

这是PostgreSQL官方支持的跨集群同步方案，适配分区表结构，性能和稳定性有保障。

1. 配置同步链路

   • 集群A（发布端）：
     • 修改postgresql.conf，设置wal_level = logical，调整max_replication_slots、max_wal_senders参数满足同步需求，重启集群生效。
     • 创建发布对象，开启分区根节点同步，确保子分区的所有变更能被同步：

       CREATE PUBLICATION pub_cluster_a FOR TABLE your_partitioned_table WITH (publish_via_partition_root = true);
       

   • 集群B（订阅端）：
     • 创建订阅连接到集群A，首次同步可添加copy_data = true初始化历史数据：

       CREATE SUBSCRIPTION sub_cluster_b CONNECTION 'host=集群A地址 port=5432 dbname=目标库 user=用户名 password=密码' PUBLICATION pub_cluster_a;
       

2. 数据留存管理

   • 集群A：按月创建分区，用pg_cron定时清理超过1个月的旧分区，避免锁表可先解绑再删除：

     CREATE EXTENSION IF NOT EXISTS pg_cron;
     -- 每月1号清理前1个月的分区（替换占位符为实际分区名规则）
     SELECT cron.schedule('cleanup_a_old_partitions', '0 0 1 * *', $$
       ALTER TABLE your_partitioned_table DETACH PARTITION your_table_202402;
       DROP TABLE IF EXISTS your_table_202402;
     $$);
     

   • 集群B：采用相同分区规则，独立设置1年的留存周期，定时清理超期分区，不受集群A的清理操作影响。

方案二：触发器+远程连接（自定义灵活场景） #

适合需要自定义同步逻辑的场景，比如仅同步特定字段，但性能略低于逻辑复制。

1. 创建同步触发器

   • 在集群A的分区表上绑定触发器，通过dblink远程执行变更操作到集群B：

     CREATE EXTENSION IF NOT EXISTS dblink;
     -- 定义同步函数
     CREATE OR REPLACE FUNCTION sync_to_b() RETURNS TRIGGER AS $$
     BEGIN
       PERFORM dblink_connect('dbname=集群B库名 host=集群B地址 port=5432 user=用户名 password=密码');
       CASE TG_OP
         WHEN 'INSERT' THEN
           PERFORM dblink_exec('INSERT INTO your_partitioned_table VALUES (' || quote_literal(NEW.id) || ', ' || quote_literal(NEW.content) || ')');
         WHEN 'UPDATE' THEN
           PERFORM dblink_exec('UPDATE your_partitioned_table SET content=' || quote_literal(NEW.content) || ' WHERE id=' || quote_literal(NEW.id));
         WHEN 'DELETE' THEN
           PERFORM dblink_exec('DELETE FROM your_partitioned_table WHERE id=' || quote_literal(OLD.id));
       END CASE;
       PERFORM dblink_disconnect();
       RETURN NULL;
     END;
     $$ LANGUAGE plpgsql;
     -- 给分区表绑定触发器（可直接绑定根表，自动应用到所有子分区）
     CREATE TRIGGER sync_trigger AFTER INSERT OR UPDATE OR DELETE ON your_partitioned_table FOR EACH ROW EXECUTE FUNCTION sync_to_b();
     

2. 数据留存管理
   与方案一一致，两个集群各自通过分区表+定时任务独立清理旧数据，触发器仅同步数据变更，A的清理操作不会同步到B。

方案三：定时批量同步（非实时场景） #

如果对同步实时性要求不高，可采用定时导出导入的轻量化方案。

1. 同步逻辑

   • 用pg_dump定时导出集群A近1个月的分区数据：

     pg_dump -h 集群A地址 -U 用户名 -d 目标库 -t your_table_202403 -f monthly_data.sql
     

   • 将导出的数据导入集群B对应分区：

     psql -h 集群B地址 -U 用户名 -d 目标库 -f monthly_data.sql
     

   • 通过系统定时任务（如Linux的cron）每日/每周执行上述脚本。

2. 数据留存管理
   集群A定期删除1个月以上的分区，集群B删除1年以上的分区，各自独立执行清理逻辑。

关键注意事项 #

• 分区结构一致性：确保两个集群的分区规则完全一致（如按月分区），避免数据同步时落错分区。
• 性能监控：逻辑复制优先于触发器方案，高并发场景需监控复制延迟；触发器需优化批量操作效率，可考虑批量提交减少远程连接开销。
• 数据校验：定期执行查询对比两个集群的分区数据量，或用pg_checksums验证数据完整性。
• 网络容错：跨服务器同步需保证网络稳定，逻辑复制支持自动重连；触发器方案需添加错误重试逻辑，避免数据丢失。

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