如何将PostgreSQL主库视图同步至从库表并保持数据一致？

一、原生逻辑复制方案 #

视图本身无法直接作为复制源，需从它依赖的底层表入手，用PostgreSQL原生逻辑复制实现持续同步：

1. 先将主库的wal_level设置为logical（修改postgresql.conf后重启服务生效），这是逻辑复制的前提条件。
2. 为视图依赖的所有底层表创建发布，可指定只同步视图用到的字段，或者过滤符合视图条件的行：

   -- 主库创建发布，匹配视图的字段和过滤规则
   CREATE PUBLICATION view_sync_pub
   FOR TABLE underlying_table1, underlying_table2
   WITH (publish = 'insert, update, delete')
   WHERE (underlying_table1.status = 'active'); -- 对应视图的WHERE过滤条件
   

3. 在从库创建订阅，关联主库的发布，同时自动同步初始数据及后续变更：

   -- 从库创建订阅
   CREATE SUBSCRIPTION view_sync_sub
   CONNECTION 'host=主库IP port=5432 dbname=主库名 user=xxx password=xxx'
   PUBLICATION view_sync_pub
   WITH (copy_data = true);
   

4. 若视图为多表关联结构，可在从库创建与视图结构一致的目标表，再通过订阅接收的变更，配合从库本地触发器函数做数据拼接，保持与视图数据一致。

二、触发器+自定义函数方案 #

给主库视图的底层表添加触发器，当数据发生增删改时，直接同步到从库目标表：

1. 主库和从库都安装dblink扩展，用于跨库连接：

   CREATE EXTENSION IF NOT EXISTS dblink;
   

2. 编写触发器函数，根据不同操作类型同步数据到从库：

   CREATE OR REPLACE FUNCTION sync_view_to_slave()
   RETURNS TRIGGER AS $$
   BEGIN
     IF TG_OP = 'INSERT' THEN
       PERFORM dblink_connect('slave_conn', 'host=从库IP port=5432 dbname=从库名 user=xxx password=xxx');
       PERFORM dblink_exec('slave_conn', format('INSERT INTO target_table (col1, col2) VALUES (%L, %L)', NEW.col1, NEW.col2));
       PERFORM dblink_disconnect('slave_conn');
     ELSIF TG_OP = 'UPDATE' THEN
       PERFORM dblink_connect('slave_conn', 'host=从库IP port=5432 dbname=从库名 user=xxx password=xxx');
       PERFORM dblink_exec('slave_conn', format('UPDATE target_table SET col1=%L WHERE id=%L', NEW.col1, OLD.id));
       PERFORM dblink_disconnect('slave_conn');
     ELSIF TG_OP = 'DELETE' THEN
       PERFORM dblink_connect('slave_conn', 'host=从库IP port=5432 dbname=从库名 user=xxx password=xxx');
       PERFORM dblink_exec('slave_conn', format('DELETE FROM target_table WHERE id=%L', OLD.id));
       PERFORM dblink_disconnect('slave_conn');
     END IF;
     RETURN COALESCE(NEW, OLD);
   END;
   $$ LANGUAGE plpgsql;
   

3. 给底层表绑定触发器（以INSERT为例，UPDATE、DELETE触发器同理）：

   CREATE TRIGGER sync_after_insert
   AFTER INSERT ON underlying_table1
   FOR EACH ROW EXECUTE FUNCTION sync_view_to_slave();
   

注意：该方案适合视图依赖表较少的场景，需添加异常处理逻辑，避免网络故障导致的数据丢失。

三、第三方工具方案 #

1. Debezium #

基于CDC（变更数据捕获）的工具，可解析PostgreSQL的WAL日志，将变更事件实时同步到从库表：

• 配置Debezium连接器时，指定要捕获的底层表，通过单消息转换（SMT）筛选出视图包含的数据，再写入从库的目标表，支持低延迟的实时同步。

2. 定时脚本同步（准实时） #

若对同步实时性要求不高，可使用cron定时任务定期导出视图数据，再导入从库目标表：

# 示例脚本，每小时同步一次
pg_dump -h 主库IP -U 用户名 -d 库名 -t "view_name" -f /tmp/view_data.sql
psql -h 从库IP -U 用户名 -d 库名 -c "TRUNCATE TABLE target_table; \copy target_table FROM '/tmp/view_data.sql' WITH (FORMAT sql);"

该方案实现简单，但存在同步延迟，适合非核心数据的同步场景。

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