如何在PostgreSQL上实现表修改的审计/版本控制 [英] How to implement Auditing/versioning of Table Modifications on PostgreSQL

问题描述

我们正在使用PostgreSQL上的Java / Spring / Hibernate实现一个新的系统。该系统需要在对表中的记录进行修改/删除时立即对每个记录进行复制。稍后，报表将查询审计表，以向用户显示数据。

我计划通过触发器实现此审计/版本控制功能将创建修改行（删除的行）TO的表（称为ENTITY_VERSIONS），该表将具有称为col1，col2，col3，col4等的大约20列，其将存储来自上面的列表; 但是，问题是如果有多于1个要进行版本控制的表，并且只有1个TARGET表（ENTITY_VERSIONS）来存储所有表的版本，那么如何设计TARGET表？

或者更好的是，需要版本控制的每个表的VERSION表会有一个COPY？

如果可以共享用于实现审计/版本控制的PostgreSQL触发器（和相关的存储过程）代码的一些指针，那么它将是额外的。

PS：我看着在SQL Server中实现审计表的建议？和有点像答案除非我不知道OldValue和NewValue是什么类型？

PPS：如果表使用SOFT DELETE（幻像删除）而不是HARD删除，请做任何建议更改？

解决方案

我会有一个表的副本来保存你想保留的表的版本。这听起来像一个噩梦维护和使用全局版本表。

此链接在Postgres文档中显示一些审计触发器示例在Postgres。

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 ?

解决方案

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.

这篇关于如何在PostgreSQL上实现表修改的审计/版本控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！