PostgreSQL逻辑复制：将订阅端UPDATE转换为INSERT（仅追加设计）

问题1：仅通过原生逻辑复制配置能否实现？ #

不能。PostgreSQL原生逻辑复制的核心目标是维持发布端与订阅端的数据一致性，默认会严格同步操作类型：发布端的INSERT对应订阅端INSERT，UPDATE对应订阅端UPDATE。没有原生配置项可以直接将UPDATE转换为INSERT，也无法强制订阅端表仅保持追加行为。

问题2：无需外部CDC工具，PostgreSQL内部是否有支持的方法？ #

有，最直接且易维护的方式是在订阅端使用触发器拦截并转换操作；另外也可通过自定义逻辑解码插件实现，但触发器方案更轻量、上手成本低。

问题3：订阅端使用触发器是否安全、受支持？会影响复制一致性吗？ #

触发器是安全且受PostgreSQL官方支持的方案，只要逻辑编写正确，不会破坏复制一致性。需要注意以下细节：

• 使用BEFORE UPDATE触发器拦截原更新操作，转换为INSERT后返回NULL，阻止原UPDATE执行
• 确保触发器不会引发未处理的异常，否则会导致订阅端复制进程中断
• 额外列（代理主键、last_modified）的赋值逻辑要在触发器或表定义中处理完成

推荐的设计模式与实现步骤 #

1. 创建订阅端表：
   定义包含发布端所有列，加上代理主键（如BIGSERIAL）和last_modified列的表，可额外设置约束禁止直接UPDATE（防止误操作）：

   CREATE TABLE target_table (
     id BIGSERIAL PRIMARY KEY,
     -- 发布端同步的业务列
     publish_col1 INT,
     publish_col2 TEXT,
     -- 自定义额外列
     last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   -- 可选：禁止直接对表执行UPDATE操作
   REVOKE UPDATE ON target_table FROM PUBLIC;
   

2. 创建转换触发器：
   编写BEFORE UPDATE触发器，将更新操作转换为插入新行：

   CREATE OR REPLACE FUNCTION update_to_insert()
   RETURNS TRIGGER AS $$
   BEGIN
     -- 插入新行，自动生成代理主键和last_modified
     INSERT INTO target_table (publish_col1, publish_col2)
     VALUES (NEW.publish_col1, NEW.publish_col2);
     -- 返回NULL阻止原UPDATE操作执行
     RETURN NULL;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER trigger_update_to_insert
   BEFORE UPDATE ON target_table
   FOR EACH ROW EXECUTE FUNCTION update_to_insert();
   

3. 配置逻辑复制订阅：
   创建订阅时，确保订阅端表结构与发布端兼容（额外列需设默认值或允许为空），按需设置copy_data参数控制初始数据同步：

   CREATE SUBSCRIPTION my_subscription
   CONNECTION 'host=pub_host dbname=pub_db user=pub_user'
   PUBLICATION my_publication
   WITH (copy_data = true);
   

4. 验证行为：
   在发布端执行INSERT和UPDATE操作，检查订阅端表是否仅生成新行，无更新行为。

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