通过手动创建触发器模拟在线DDL（监控表A上的所有DML操作，然后同步到表B）

1. 通过手动创建触发器模拟在线DDL（监控表A上的所有DML操作，然后同步到表B）

1. 通过手动创建触发器模拟在线DDL（监控表A上的所有DML操作，然后同步到表B）

1.1. 实现方案一

核心需求实现
基于之前的 DML 监控逻辑，调整为表 A 的 DML 操作实时同步到结构完全相同的表 B，核心仍用「触发器 + 函数」实现，区别是将 “写入日志表” 改为 “同步数据到表 B”，同时处理 INSERT/UPDATE/DELETE 的精准同步逻辑。

1.1.1. 步骤 1：确认表结构（表 A / 表 B 完全一致）

假设表 A（目标监控表）和表 B（同步目标表）的结构如下（可替换为你的实际表结构）：

-- 表A（业务表）
CREATE TABLE IF NOT EXISTS table_a (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 表B（同步表，结构与表A完全一致）
CREATE TABLE IF NOT EXISTS table_b (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1.1.2. 步骤 2：创建同步存储过程（触发器函数）

该函数捕获表 A 的 DML 事件，按操作类型同步到表 B，保证数据一致性（INSERT 新增、UPDATE 覆盖、DELETE 删除）

CREATE OR REPLACE FUNCTION sync_table_a_to_b()
RETURNS TRIGGER AS $$
BEGIN
    -- 根据DML类型执行同步逻辑
    CASE TG_OP
        -- 1. INSERT：表A插入数据 → 表B同步插入
        WHEN 'INSERT' THEN
            INSERT INTO table_b 
            SELECT NEW.*; -- NEW是表A插入的新行，直接全字段插入表B
            RETURN NEW;

        -- 2. UPDATE：表A更新数据 → 表B按主键更新对应行
        WHEN 'UPDATE' THEN
            UPDATE table_b 
            SET (name, age, create_time) = (NEW.name, NEW.age, NEW.create_time) -- 可替换为表的所有字段
            WHERE id = OLD.id; -- OLD.id是表A更新前的主键，定位表B的对应行
            -- 若表B无对应行（异常场景），则插入
            IF NOT FOUND THEN
                INSERT INTO table_b SELECT NEW.*;
            END IF;
            RETURN NEW;

        -- 3. DELETE：表A删除数据 → 表B删除对应行
        WHEN 'DELETE' THEN
            DELETE FROM table_b 
            WHERE id = OLD.id; -- OLD.id定位表B的对应行
            RETURN OLD;
    END CASE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 关键说明：
-- 1. NEW/OLD：PostgreSQL触发器内置变量，NEW=表A新数据（INSERT/UPDATE），OLD=表A旧数据（UPDATE/DELETE）
-- 2. SELECT NEW.*：适配“表结构完全一致”的场景，无需逐个字段写，简化维护
-- 3. IF NOT FOUND：处理表B缺失数据的异常情况，保证同步完整性
-- 4. SECURITY DEFINER：确保函数有写入表B的权限（避免普通用户操作表A时无权限写表B）

1.1.3. 步骤 3：创建触发器（绑定函数到表 A）

为表 A 创建行级触发器，触发时机为AFTER（确保表 A 的 DML 操作成功后再同步，避免同步失败导致表 A 操作回滚）：

CREATE TRIGGER trigger_sync_a_to_b
AFTER INSERT OR UPDATE OR DELETE ON table_a
FOR EACH ROW -- 行级触发器：每一行DML都同步
EXECUTE FUNCTION sync_table_a_to_b();

-- 触发时机说明：
-- AFTER：表A的DML成功后同步到表B，优先保证业务表（表A）的可用性；
-- 若需“表A和表B强一致”（要么都成功，要么都失败），可改为BEFORE + 事务控制，但会增加表A操作的阻塞风险。

1.1.4. 步骤 4：测试验证

执行表 A 的 DML 操作，检查表 B 的数据是否同步：

-- 1. 插入测试（表A插入→表B同步插入）
INSERT INTO table_a (name, age) VALUES ('张三', 25), ('李四', 30);

-- 2. 更新测试（表A更新→表B同步更新）
UPDATE table_a SET age = 26 WHERE name = '张三';

-- 3. 删除测试（表A删除→表B同步删除）
DELETE FROM table_a WHERE name = '李四';

-- 4. 验证表B数据
SELECT * FROM table_b;

1.2. 进阶优化（解决高并发 / 异常场景）

1.2.1. 批量同步（高并发场景）

若表 A 写入量极大（如每秒上千行），行级触发器会有性能损耗，可改为语句级触发器 + 批量同步：

-- 批量同步函数
CREATE OR REPLACE FUNCTION sync_table_a_to_b_batch()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO table_b SELECT * FROM table_a WHERE id IN (SELECT id FROM NEW_TABLE);
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE table_b SET (name, age, create_time) = (t.name, t.age, t.create_time) 
        FROM table_a t WHERE table_b.id = t.id AND t.id IN (SELECT id FROM NEW_TABLE);
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM table_b WHERE id IN (SELECT id FROM OLD_TABLE);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 语句级触发器（批量同步）
CREATE TRIGGER trigger_sync_a_to_b_batch
AFTER INSERT OR UPDATE OR DELETE ON table_a
FOR EACH STATEMENT
EXECUTE FUNCTION sync_table_a_to_b_batch();

1.2.2. 同步异常监控

添加日志表记录同步失败的情况，避免数据丢失：

-- 创建同步异常日志表
CREATE TABLE IF NOT EXISTS sync_error_log (
    log_id SERIAL PRIMARY KEY,
    operation_type VARCHAR(10),
    error_msg TEXT,
    error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data JSONB
);

-- 修改同步函数，增加异常捕获
CREATE OR REPLACE FUNCTION sync_table_a_to_b()
RETURNS TRIGGER AS $$
DECLARE
    v_error_msg TEXT;
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            INSERT INTO table_b SELECT NEW.*;
        WHEN 'UPDATE' THEN
            UPDATE table_b SET (name, age, create_time) = (NEW.name, NEW.age, NEW.create_time) WHERE id = OLD.id;
            IF NOT FOUND THEN
                INSERT INTO table_b SELECT NEW.*;
            END IF;
        WHEN 'DELETE' THEN
            DELETE FROM table_b WHERE id = OLD.id;
    END CASE;
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        -- 捕获异常并写入日志
        GET STACKED DIAGNOSTICS v_error_msg = MESSAGE_TEXT;
        INSERT INTO sync_error_log (operation_type, error_msg, data)
        VALUES (TG_OP, v_error_msg, row_to_json(COALESCE(NEW, OLD)));
        RETURN NULL; -- 异常时不影响表A的DML操作
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

1.2.3. 全量初始化（若表 B 已有旧数据）

同步前先将表 A 的历史数据全量同步到表 B：

-- 清空表B旧数据（可选）
TRUNCATE TABLE table_b;

-- 全量同步表A到表B
INSERT INTO table_b SELECT * FROM table_a;

1.2.4. 也可以通过中间表记录所有的DML操作

-- 2. DML监控日志表：表B（记录所有DML操作）
-- ----------------------------
CREATE TABLE IF NOT EXISTS table_b (
    log_id SERIAL PRIMARY KEY,          -- 日志主键
    operation_type VARCHAR(10) NOT NULL,-- 操作类型：INSERT/UPDATE/DELETE
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 操作时间
    operation_user VARCHAR(50) NOT NULL,-- 操作数据库用户
    table_name VARCHAR(50) DEFAULT 'table_a', -- 被监控表名
    old_data JSONB,                     -- 旧数据（UPDATE/DELETE时记录）
    new_data JSONB,                     -- 新数据（INSERT/UPDATE时记录）
    client_ip VARCHAR(50),              -- 操作客户端IP
    session_id VARCHAR(100)             -- 数据库会话ID（便于定位会话）
);


-- ----------------------------
-- 3. 监控DML的触发器函数（存储过程）
-- ----------------------------
CREATE OR REPLACE FUNCTION monitor_table_a_dml()
RETURNS TRIGGER AS $$
DECLARE
    v_client_ip VARCHAR(50);
    v_session_id VARCHAR(100);
BEGIN
    -- 获取客户端IP和会话ID（增强监控维度）
    SELECT inet_client_addr()::VARCHAR INTO v_client_ip;
    SELECT pg_backend_pid()::VARCHAR INTO v_session_id;

    -- 根据DML类型写入表B
    CASE TG_OP
        WHEN 'INSERT' THEN
            INSERT INTO table_b (
                operation_type, operation_user, old_data, new_data, client_ip, session_id
            ) VALUES (
                'INSERT', CURRENT_USER, NULL, row_to_json(NEW), v_client_ip, v_session_id
            );
            RETURN NEW; -- INSERT触发器需返回NEW

        WHEN 'UPDATE' THEN
            INSERT INTO table_b (
                operation_type, operation_user, old_data, new_data, client_ip, session_id
            ) VALUES (
                'UPDATE', CURRENT_USER, row_to_json(OLD), row_to_json(NEW), v_client_ip, v_session_id
            );
            RETURN NEW; -- UPDATE触发器需返回NEW

        WHEN 'DELETE' THEN
            INSERT INTO table_b (
                operation_type, operation_user, old_data, new_data, client_ip, session_id
            ) VALUES (
                'DELETE', CURRENT_USER, row_to_json(OLD), NULL, v_client_ip, v_session_id
            );
            RETURN OLD; -- DELETE触发器需返回OLD
    END CASE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER; -- SECURITY DEFINER：以函数创建者权限执行，避免权限不足

-- 说明：
-- 1. TG_OP：PostgreSQL内置变量，自动识别当前操作类型（INSERT/UPDATE/DELETE）
-- 2. row_to_json()：将行数据转为JSONB，便于查看完整变更内容
-- 3. CURRENT_USER：获取执行DML的数据库用户
-- 4. inet_client_addr()：获取客户端IP（若本地连接则为NULL）

-- ----------------------------
-- 4. 为表A创建DML触发器
-- ----------------------------
CREATE TRIGGER trigger_table_a_dml
AFTER INSERT OR UPDATE OR DELETE ON table_a
FOR EACH ROW -- 行级触发器：每一行数据变更都触发
EXECUTE FUNCTION monitor_table_a_dml();

-- 说明：
-- FOR EACH ROW：区别于FOR EACH STATEMENT（语句级），行级能捕获每一行的具体变更
-- AFTER：确保DML操作成功后再记录日志（若用BEFORE，DML失败也会记录，可根据需求调整）

1.3. 关键注意事项

主键一致性：表 A / 表 B 必须有唯一主键（如 id），否则 UPDATE/DELETE 无法精准定位同步行；
性能影响：行级触发器会增加表 A 的 DML 耗时（约 1-5ms / 行），高并发场景建议用批量触发器或异步同步（如 pg_cron 定时同步）；
事务一致性：若需 “表 A 和表 B 同事务”，可将触发器改为BEFORE，并在函数中加事务控制，但会降低表 A 的写入性能；
权限配置：确保触发器函数的创建者有表 A 的 DML 权限和表 B 的读写权限；
结构变更：若表 A 结构变更（如新增字段），需同步修改表 B 结构，否则同步会报错。

1.4. 总结

核心实现：行级触发器 + 全字段同步是表 A→表 B 实时同步的最简方案，适配 “结构完全一致” 的场景；
关键逻辑：
- INSERT：直接复制 NEW 行到表 B；
- UPDATE：按主键更新表 B 对应行，无则插入；
- DELETE：按主键删除表 B 对应行；
优化方向：高并发用批量触发器，关键业务加异常日志，确保同步不丢数据。

posted @ 2026-05-18 10:44 数据库小白（专注）阅读(20) 评论(0) 收藏举报

刷新页面返回顶部

数据库小白（专注）