如何在PostgreSQL中维护历史记录以实现数据回滚

以下是三种基于历史数据存储的实现方案，可根据你的业务场景选型：

方案1：应用层临时存储变更快照（最适配UI交互场景） #

这个方案是短会话临时回退需求的最优解，实现成本低、灵活性高：

• 执行UPDATE table SET date = '2021-03-02' WHERE id=1之前，先查询id=1的全量行数据，序列化后存储在前端sessionStorage或者后端对应用户的会话上下文中，存储键可按pending_change_<表名>_<主键值>_<操作时间戳>规则命名，同步记录变更涉及的字段、原始值、主键信息。
• 点击Quit按钮时，直接从存储中取出原始数据，构造反向更新SQL执行。如果涉及多表/多行变更，可以把所有变更的快照存在同一个集合里，回滚时批量执行反向SQL，全程加事务包裹，避免部分回滚成功部分失败导致数据混乱。
• 回滚完成后清空对应存储的快照即可。

方案2：数据库层面新增历史版本表（适合需持久化变更留痕的场景） #

如果你的业务需要留存所有变更的可追溯记录，不止支持当前会话回滚，推荐做数据库层面的历史版本留存：

• 给业务表新增配套的历史镜像表，比如业务表名为business_table，对应历史表名为business_table_history，字段至少包含：自增主键、业务表对应行的主键、变更前所有字段的镜像值、操作时间、操作人ID、当前操作的会话唯一标识。
• 每次执行业务更新操作前，先将当前待修改的行数据全量插入历史表，绑定当前前端会话的唯一ID。
• 点击Quit按钮时，用当前会话ID查询历史表中该会话产生的所有变更记录，按操作时间倒序排列，逐行构造反向更新语句回滚到变更前的值，回滚完成后可以选择删除对应历史记录或者留存做审计。

方案3：利用数据库事务/临时表特性（适合长连接场景） #

如果你的应用和数据库是长连接会话模式（比如桌面端应用），可以用更轻量的数据库原生特性实现：

• 所有更新操作都在显式开启的数据库事务中执行，更新前也可以可选的将要修改的行备份到当前会话的临时表中。
• 用户点击确认提交按钮时才执行COMMIT，点击Quit按钮时直接执行ROLLBACK即可撤销所有未提交的变更，不需要额外做历史数据存储。

注意：该方案不适合常规短连接的Web应用场景，Web场景下数据库会话通常请求结束就释放，无法跨请求保留事务状态。

通用避坑点 #

不管选哪种方案，都要处理并发修改冲突：如果其他会话在你修改之后、回滚之前也修改了同一行数据，直接回滚会覆盖其他人的更新。建议给业务表加乐观锁字段（比如version int类型，每次更新自增1），回滚时校验当前行的版本号和你存储快照时的版本号一致，才执行回滚操作，避免数据错乱。

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