如何结合Flyway与PostgreSQL历史表实现DDL/DML版本回滚？

嘿，这个需求确实得把DDL和DML的版本管理拧到一块儿才行，咱们一步步拆解来解决。核心问题是要把Flyway的DDL版本追踪和你现有的PostgreSQL DML历史表结合，同时避开DDL触发器的限制，下面是具体的思路和可落地的方案：

Flyway本身是按版本脚本管理DDL的，咱们可以把每个DDL版本对应的DML状态做个“快照标记”。不需要DDL触发器，用Flyway的回调或者脚本末尾的逻辑就能实现：

首先建一个专门的关联表，用来记录Flyway版本和对应DML状态的映射：

CREATE TABLE schema_version_dml (
    flyway_version VARCHAR(50) PRIMARY KEY,
    snapshot_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description TEXT
);

然后有两种方式自动填充这个表：

• 方式一：在每个Flyway脚本末尾加标记
  比如你的V1__create_customers_table.sql脚本执行完DDL后，加一行：

  INSERT INTO schema_version_dml (flyway_version, description)
  VALUES ('1', 'Created customers table, initial DML state post-DDL v1');
  

• 方式二：用Flyway的全局回调
  创建一个afterMigrate.sql放在Flyway的SQL目录下，每次迁移完成后自动写入标记：

  INSERT INTO schema_version_dml (flyway_version, description)
  VALUES (
      (SELECT version FROM flyway_schema_history WHERE success = TRUE ORDER BY installed_rank DESC LIMIT 1),
      'DML state synced after latest Flyway migration'
  )
  ON CONFLICT (flyway_version) DO UPDATE SET snapshot_timestamp = CURRENT_TIMESTAMP;
  

这样每一次DDL变更后，都能对应到一个明确的DML状态节点。

你的DML历史表已经记录了每一条增删改操作，现在给它加个字段，把这些DML操作绑定到对应的Flyway版本上：

ALTER TABLE your_existing_dml_history
ADD COLUMN flyway_version VARCHAR(50) REFERENCES schema_version_dml(flyway_version);

然后在Access前端的DML操作逻辑里，每次执行增删改前，先查询当前Flyway的最新版本（从PostgreSQL的flyway_schema_history表拿），把版本号存入这条DML的历史记录。比如Access里的VBA代码片段：

Dim currentFlywayVersion As String
currentFlywayVersion = DLookup("version", "flyway_schema_history", "success = TRUE ORDER BY installed_rank DESC LIMIT 1")
' 执行DML操作后，把currentFlywayVersion写入your_existing_dml_history的flyway_version字段

现在有了DDL和DML的关联，回滚就可以分两步走，保证数据和结构一致：

步骤1：DDL回滚 #

• 如果用Flyway专业版：直接用官方的undo脚本（命名格式U1__rollback_xxx.sql），执行flyway undo就能回滚到指定版本。
• 如果用社区版：手动编写每个版本的回滚脚本，按版本号倒序执行。比如要回滚到版本n，就先执行Un__rollback_xxx.sql，再执行U(n-1)__rollback_xxx.sql，直到目标版本。

步骤2：DML回滚 #

根据目标Flyway版本，从schema_version_dml拿到对应的快照时间，然后筛选出这个时间之后的所有DML操作，按逆序执行反向操作：

1. 先查询目标版本的快照时间：

   SELECT snapshot_timestamp FROM schema_version_dml WHERE flyway_version = '目标版本号';
   

2. 筛选出需要回滚的DML记录：

   SELECT * FROM your_existing_dml_history
   WHERE operation_timestamp > '快照时间' AND flyway_version > '目标版本号'
   ORDER BY operation_timestamp DESC;
   

3. 对每条记录执行反向操作：
   • 原操作是INSERT：执行DELETE FROM 目标表 WHERE 主键 = :记录主键
   • 原操作是UPDATE：执行UPDATE 目标表 SET 字段1 = :旧值1, 字段2 = :旧值2 WHERE 主键 = :记录主键
   • 原操作是DELETE：执行INSERT INTO 目标表 (字段1, 字段2...) VALUES (:旧值1, :旧值2...)

可以把这个DML回滚逻辑封装成PostgreSQL存储过程，Access前端只需要调用存储过程并传入目标版本号就行，更安全也更高效。

在Access里做两个小优化，让版本管理更易用：

• 加一个版本选择界面，列出所有schema_version_dml里的版本和描述，用户选择后触发回滚流程。
• 在执行DML操作时，自动校验当前Flyway版本是否有效（比如有没有未完成的迁移），避免DML操作和DDL迁移冲突。

• 确保你的DML历史表记录足够完整：尤其是UPDATE的旧值、DELETE的全字段值，否则回滚时无法恢复数据。
• 回滚操作一定要在测试环境反复验证，生产环境建议先做全量备份再执行。
• 如果Flyway版本有跳过或者失败的情况，要在schema_version_dml里手动标记，避免DML关联错误。

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