如何高效生成测试数据库数据修改的SQL脚本？

直接针对你遇到的pg_dump对比痛点，给几个实用的优化方向：

1. 基于触发器的变更日志记录 #

在测试库的核心业务表上创建通用触发器，每次INSERT/UPDATE/DELETE操作时，把变更内容（操作类型、表名、变更前后的字段值，自动排除created_date这类自动生成字段）写入专门的变更日志表。示例代码如下：

-- 创建变更日志表
CREATE TABLE data_changelog (
    id SERIAL PRIMARY KEY,
    operation_type VARCHAR(10) NOT NULL,
    table_name VARCHAR(64) NOT NULL,
    changed_at TIMESTAMP DEFAULT NOW(),
    old_data JSONB,
    new_data JSONB
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION log_data_change()
RETURNS TRIGGER AS $$
BEGIN
    -- 过滤自动生成的时间字段
    IF TG_OP = 'INSERT' THEN
        INSERT INTO data_changelog (operation_type, table_name, new_data)
        VALUES (TG_OP, TG_TABLE_NAME, to_jsonb(NEW) - 'created_date' - 'updated_date');
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO data_changelog (operation_type, table_name, old_data, new_data)
        VALUES (TG_OP, TG_TABLE_NAME, to_jsonb(OLD) - 'created_date' - 'updated_date', to_jsonb(NEW) - 'created_date' - 'updated_date');
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO data_changelog (operation_type, table_name, old_data)
        VALUES (TG_OP, TG_TABLE_NAME, to_jsonb(OLD) - 'created_date' - 'updated_date');
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 给业务表绑定触发器（示例为users表）
CREATE TRIGGER trigger_user_changelog
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION log_data_change();

测试完成后，直接从data_changelog表读取记录，转换成对应的SQL脚本即可。这个方法无需复制整个库，自动过滤干扰字段，唯一需要提前配置触发器，可将其整合到Test DB的初始化脚本中。

2. 精准过滤pg_dump输出+智能diff #

如果不想修改数据库结构，可以优化现有pg_dump流程：

• 仅导出有业务修改的表，用pg_dump -t table1 -t table2 ...减少输出量，节省时间和空间
• 导出时添加--inserts参数生成单条INSERT语句，便于diff对比
• 对比前用grep过滤掉时间字段相关行，示例命令：

# 导出原始库指定表
pg_dump -t users -t orders --inserts test_db_original > original.sql
# 导出修改后的库指定表
pg_dump -t users -t orders --inserts test_db_modified > modified.sql
# 过滤干扰字段后对比
grep -v 'created_date\|updated_date' original.sql > original_filtered.sql
grep -v 'created_date\|updated_date' modified.sql > modified_filtered.sql
diff original_filtered.sql modified_filtered.sql

该方法改动最小，适合临时场景，但如果涉及表较多，手动指定表会比较繁琐。

3. 用数据库对比工具生成变更脚本 #

借助专门的数据库对比工具（如Liquibase），这类工具可以：

• 直接连接原始库和修改后的库，自动对比结构与数据
• 配置忽略指定字段（如created_date）
• 直接生成可执行的变更SQL脚本
  示例Liquibase命令：

liquibase \
  --url=jdbc:postgresql://localhost:5432/test_db_original \
  --username=postgres \
  --password=postgres \
  diff \
  --referenceUrl=jdbc:postgresql://localhost:5432/test_db_modified \
  --referenceUsername=postgres \
  --referencePassword=postgres \
  --diffTypes=data \
  --excludeObjects=column:public.users.created_date,column:public.users.updated_date

工具会自动生成仅包含有效数据变更的SQL，无需手动处理diff与过滤。

4. 逻辑复制槽捕获变更 #

利用PostgreSQL的逻辑复制功能，创建复制槽捕获测试过程中的所有数据变更：

-- 创建逻辑复制槽（需先在postgresql.conf中设置wal_level=logical）
SELECT pg_create_logical_replication_slot('test_change_slot', 'pgoutput');
-- 执行应用的数据修改操作
-- 获取变更记录
SELECT * FROM pg_logical_slot_get_changes('test_change_slot', NULL, NULL);
-- 用完删除复制槽
SELECT pg_drop_replication_slot('test_change_slot');

返回的data字段为二进制格式，可通过pg_recvlogical工具导出为可读的变更日志，再解析成SQL。该方法无需修改表结构，适合需要捕获全量变更的场景。

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