数据库内容版本控制

Question

我希望记录数据库中某些表的每个更改历史，以便能够重建历史状态以进行分析。 我正在使用Postgres，并且这个MVCC东西似乎可以用于此目的，但我找不到任何支持此事的文档。我能行吗？ 有更好的方法吗？

更新

我已经将Denis的回答标记为答案，因为他确实回答了我想要什么，即MVCC是否适合该任务。 然而，我采取的策略如下：

可进行在线备份/定时恢复(Postgres功能)，详见：http://www.postgresql.org/docs/8.1/static/backup-online.html。 将Write Ahead Log设置为归档模式，拍摄数据库快照(例如，在数据库启动之前)，然后不断归档WAL。 您随时可以使用日志重放来调用数据库的状态，并具有热备份的附加好处(通过在您的备份服务器上不断重播新的WAL)。

也许这种方法不像其他保留历史方式那样优雅，因为您需要为您想要查询的每个时间点实际上构建数据库，但它似乎非常容易设置且不会失去任何信息。 这意味着，当我有时间改进我的历史数据处理方式时，我将拥有所有内容，因此能够将我的笨拙系统转换为更优雅的系统。

使这个方法非常完美的一个关键事实是，对于特定应用程序，我的“有效时间”与我的“事务时间”相同——如果不是这种情况，我只会捕获“事务时间”。 在发现了WAL之前，我正在考虑仅拍摄每天的快照之类的操作，但是其大量的存储需求和数据损失并不适合我的需求。

对于快速启动并在一开始就不会影响到数据保留的快速方法，这似乎是完美的解决方案。

- user800576

---

你一定要看看NeXtep Designer IDE - 你可以将你的数据库放在版本控制下，并重新创建任何版本或迁移任何增量，同步数据库状态等。 - Christophe Fondacci

@ChristopheFondacci 你还在继续开发你的NexStep Designer IDE吗？ - CMCDragonkai

@CMCDragonkai 我们目前正在提供维护和修复漏洞的服务。我们暂时无法开发新功能，但我们仍然几乎每天都在我们的任务中使用该产品。 - Christophe Fondacci

Answer 1

时间旅行

PostgreSQL曾经拥有这个功能，并称其为“时间旅行”。请参见旧文档。

spi贡献模块中有类似的功能，您可能需要查看一下。

复合类型审计触发器

我通常使用触发器将更改记录到带有时间戳的归档表中，并针对这些表进行查询。如果表结构不会更改，您可以使用以下内容：

CREATE TABLE sometable_history(
    command_tag text not null check (command_tag IN ('INSERT','DELETE','UPDATE','TRUNCATE')),
    new_content sometable,
    change_time timestamp with time zone
);

您的版本控制触发器可以只是 insert into sometable_history(TG_OP,NEW,current_timestamp)（对于 DELETE，使用不同的 CASE，其中 NEW 未定义）。

hstore 审计触发器

如果数据库模式更改以添加新的 NOT NULL 列，则会变得麻烦。如果您打算执行任何类似操作，请考虑使用 hstore 存档列，而不是复合类型。我已经在 PostgreSQL 维基上添加了 这方面的实现。

PITR

如果您想避免对主数据库（增长表等）的影响，您可以交替使用连续归档和时间点恢复来记录可在任何时间点上重放的WAL文件。请注意，WAL文件很大，它们不仅包括您更改的元组，还包括VACUUM活动和其他详细信息。您需要通过clearxlogtail运行它们，因为如果它们是来自存档超时的部分段，它们可能会在末尾有垃圾数据，然后您需要将它们进行大量压缩以进行长期存储。

- Craig Ringer

Answer 2

我正在使用Postgres，而这个MVCC的东西似乎可以用于这个目的，但我找不到任何支持它的文档。我能做到吗？ 并不是真的。有一些工具可以查看死行，因为自动清理所以最终会被回收。

有更好的方法吗？ 如果我理解你的问题正确，你正在研究记录慢变化的维度。

您可能会发现这个最近相关的主题很有趣： 时间数据库设计，带有一个扭曲（现场与草案行）。

- Denis de Bernardy

---

感谢您的回答，我本来希望答案是肯定的，但现在知道了哪些方向是不可行的也是好的。我过去使用过有效时间的时间数据库设计，并将研究SCD作为替代方案。 - user800576

Answer 3

我不知道是否有任何专门用于此目的的工具/产品。

虽然这可能不是您要求的准确内容，但您可以配置Postgresql以记录ddl更改。设置log_line_prefix参数（尝试包括％d，％m和％u），并将log_statement参数设置为ddl，应该可以给您一个合理的历史记录，了解谁在何时进行了哪些ddl更改。

话虽如此，我认为记录ddl并不是万无一失的。例如，考虑以下情况：

1. 多个模式具有相同名称的表，
2. 其中一个表被更改，
3. ddl没有完全限定表名（依赖搜索路径来正确获取它），
4. 那么可能无法从日志中知道实际更改了哪个表。

另一个选择可能是像上面那样记录ddl，但然后让监视程序在记录ddl条目时执行数据库模式的pg_dump。您甚至可以比较新的转储与先前的转储，并提取仅更改的对象。

- gsiems