如何在 PostgreSQL 上实现表修改的审核/版本控制
我们正在 PostgreSQL 上使用 Java/Spring/Hibernate 实现一个新系统。一旦对表中的记录进行修改/删除，该系统就需要复制每个记录。稍后，报告将查询审核表以向用户显示数据。
我计划通过在表上设置一个触发器来实现此审计/版本控制功能，该触发器会将修改的行（已删除的行）复制到“TO”一个名为 ENTITY_VERSIONS 的表，该表将有大约 20 个名为 col1、col2 的列、col3、col4 等，它们将存储上述表中的列； 但是，问题是如果有超过1个表需要版本控制，并且只有1个TARGET表(ENTITY_VERSIONS)来存储所有表的版本，我该如何设计TARGET表？
或者对于每个需要版本控制的表都有一个版本表的副本更好？
如果可以共享一些用于实现审计/版本控制的 PostgreSQL 触发器（和关联的存储过程）代码，那将是额外的好处。
PS：我查看了在 SQL Server 中实现审计表的建议？ 有点像答案，只是我不知道 OldValue 和 NewValue 应该是什么类型？
PPS：如果表使用软删除（幻像删除）而不是硬删除，您的建议会改变吗？
We're implementing a New system using Java/Spring/Hibernate on PostgreSQL. This system needs to make a copy of Every Record as soon as a modification/deletion is done on the record(s) in the Tables(s). Later, the Audit Table(s) will be queried by Reports to display the data to the users.
I was planning to implement this auditing/versioning feature by having a trigger on the table(s) which would make a copy of the modified row(deleted row) "TO" a TABLE called ENTITY_VERSIONS which would have about 20 columns called col1, col2, col3, col4, etc which would store the columns from the above Table(s); However, the problem is that if there is more than 1 Table to be versioned and ONLY 1 TARGET table(ENTITY_VERSIONS) to store all the tables' versions, how do I design the TARGET table ?
OR is it better that there will be a COPY of the VERSION Table for each Table that needs versioning ?
It will be bonus if some pointers towards PostgreSQL Triggers (and associated Stored Procedure ) code for implementing the auditing/versioning can be shared.
P.S : I looked at Suggestions for implementing audit tables in SQL Server? and kinda like the answer except I would NOT know what type should OldValue and NewValue be ?
P.P.S : If the Tables use SOFT DELETEs (phantom deletes) instead of HARD deletes, do any of your advice change ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我将拥有每个表的副本来保存您希望保留的该表的版本。维护和使用全局版本控制表听起来有点像一场噩梦。
Postgres 文档中的 此链接 显示了一些审核触发器示例Postgres。
I would have a copy of each table to hold the versions of that table you wish to keep. It sounds like a bit of a nightmare to maintain and use a global versioning table.
This link in the Postgres documentation shows some audit trigger examples in Postgres.
在全局表中，所有列都可以作为 hstore 类型存储在单列中。我刚刚尝试过审核，效果很好，我推荐它。很棒的审计表示例通过简单地将触发器添加到您想要开始保留审计历史记录的表上来跟踪单个表中的所有更改。所有更改都存储为 hstore 类型 - 适用于 v 9.1+
此链接
In global table all columns can be stored in single column as hstore type. I just tried audit and I it is works great, I recommend it. Awesome audit table example tracks all changes in single table by simply adding a trigger onto the tables you want to begin to keep audit history on. all changes are stored in as hstore type- works for v 9.1+
this link