postgresql 为数据库应用程序留下审计跟踪/更改历史记录的有效策略？

Effective strategy for leaving an audit trail/change history for DB applications?

提问by Dana the Sane

What are some strategies that people have had success with for maintaining a change history for data in a fairly complex database. One of the applications that I frequently use and develop for could really benefit from a more comprehensive way of tracking how records have changed over time. For instance, right now records can have a number of timestamp and modified user fields, but we currently don't have a scheme for logging multiple change, for instance if an operation is rolled back. In a perfect world, it would be possible to reconstruct the record as it was after each save, etc.

人们在维护相当复杂的数据库中数据的更改历史方面取得了哪些成功？我经常使用和开发的应用程序之一确实可以从更全面的跟踪记录随时间变化的方式中受益。例如，现在记录可以有多个时间戳和修改过的用户字段，但我们目前没有记录多个更改的方案，例如如果操作被回滚。在完美的世界中，可以在每次保存后重建记录，等等。

Some info on the DB:

关于数据库的一些信息：

• Needs to have the capacity to grow by thousands of records per week
• 50-60 Tables
• Main revisioned tables may have several million records each
• Reasonable amount of foreign keys and indexes set
• Using PostgreSQL 8.x

• 需要有能力每周增加数千条记录
• 50-60桌
• 主要修订表每个可能有几百万条记录
• 合理数量的外键和索引设置
• 使用 PostgreSQL 8.x

采纳答案by mk.

In the past I have used triggers to construct db update/insert/delete logging.

过去我使用触发器来构建数据库更新/插入/删除日志记录。

You could insert a record each time one of the above actions is done on a specific table into a logging table that keeps track of the action, what db user did it, timestamp, table it was performed on, and previous value.

每次对特定表执行上述操作之一时，您都可以将记录插入记录表中，该记录表跟踪操作、数据库用户执行的操作、时间戳、执行的表以及先前的值。

There is probably a better answer though as this would require you to cache the value before the actual delete or update was performed I think. But you could use this to do rollbacks.

可能有更好的答案，因为我认为这需要您在执行实际删除或更新之前缓存该值。但是你可以用它来做回滚。

回答by Eric Z Beard

One strategy you could use is MVCC, Multi-Value Concurrency Control. In this scheme, you never do updates to any of your tables, you just do inserts, maintaining version numbers for each record. This has the advantage of providing an exact snapshot from any point in time, and it also completely sidesteps the update lock problems that plague many databases.

您可以使用的一种策略是 MVCC，即多值并发控制。在这个方案中，你永远不会更新你的任何表，你只做插入，维护每条记录的版本号。这具有从任何时间点提供准确快照的优点，并且还完全避免了困扰许多数据库的更新锁定问题。

But it makes for a huge database, and selects all require an extra clause to select the current version of a record.

但它造成了一个巨大的数据库，并且选择所有需要一个额外的子句来选择记录的当前版本。

回答by Martin Klinke

If you are using Hibernate, take a look at JBoss Envers. From the project homepage:

如果您使用的是 Hibernate，请查看JBoss Envers。从项目主页：

The Envers project aims to enable easy versioning of persistent JPA classes. All that you have to do is annotate your persistent class or some of its properties, that you want to version, with @Versioned. For each versioned entity, a table will be created, which will hold the history of changes made to the entity. You can then retrieve and query historical data without much effort.

Envers 项目旨在实现对持久 JPA 类的轻松版本控制。您所要做的就是使用@Versioned 注释您想要版本化的持久类或其某些属性。对于每个版本化实体，将创建一个表，该表将保存对实体所做更改的历史记录。然后，您可以毫不费力地检索和查询历史数据。

This is somewhat similar to Eric's approach, but probably much less effort. Don't know, what language/technology you use to access the database, though.

这有点类似于Eric 的方法，但可能要少得多的努力。不过，不知道您使用什么语言/技术来访问数据库。

回答by amitm

The only problem with using Triggers is that it adds to performance overhead of any insert/update/delete. For higher scalability and performance, you would like to keep the database transaction to a minimum. Auditing via triggers increase the time required to do the transaction and depending on the volume may cause performance issues.

使用触发器的唯一问题是它增加了任何插入/更新/删除的性能开销。为了获得更高的可伸缩性和性能，您希望将数据库事务保持在最低水平。通过触发器进行审计会增加执行事务所需的时间，并且根据交易量可能会导致性能问题。

another way is to explore if the database provides any way of mining the "Redo" logs as is the case in Oracle. Redo logs is what the database uses to recreate the data in case it fails and has to recover.

另一种方法是探索数据库是否提供任何方式来挖掘“重做”日志，就像在 Oracle 中的情况一样。重做日志是数据库用于重新创建数据的内容，以防万一失败并必须恢复。

回答by Jeff Davis

Similar to a trigger (or even with) you can have every transaction fire a logging event asynchronously and have another process (or just thread) actually handle the logging. There would be many ways to implement this depending upon your application. I suggest having the application fire the event so that it does not cause unnecessary load on your first transaction (which sometimes leads to locks from cascading audit logs).

类似于触发器（或什至与触发器），您可以让每个事务异步触发日志记录事件，并让另一个进程（或只是线程）实际处理日志记录。根据您的应用程序，有很多方法可以实现这一点。我建议让应用程序触发事件，这样它就不会对您的第一个事务造成不必要的负载（这有时会导致级联审计日志锁定）。

In addition, you may be able to improve performance to the primary database by keeping the audit database in a separate location.

此外，您可以通过将审计数据库保存在单独的位置来提高主数据库的性能。

回答by Chris Burgess

I use SQL Server, not PostgreSQL, so I'm not sure if this will work for you or not, but Pop Rivett had a great article on creating an audit trail here: Pop rivett's SQL Server FAQ No.5: Pop on the Audit Trail

我使用 SQL Server，而不是 PostgreSQL，所以我不确定这是否适合您，但是 Pop Rivett 有一篇关于在此处创建审计跟踪的很棒的文章： Pop rivett 的 SQL Server 常见问题 5：Pop on the Audit踪迹

Build an audit table, then create a trigger for each table you want to audit.

构建一个审计表，然后为每个要审计的表创建一个触发器。

Hint: use Codesmithto build your triggers.

提示：使用Codesmith构建触发器。