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

问题描述

我们正在 PostgreSQL 上使用 Java/Spring/Hibernate 实现一个新系统.一旦对表中的记录进行修改/删除，该系统就需要制作每个记录的副本.稍后，Reports 将查询审计表以向用户显示数据.

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.

我计划通过在表上设置一个触发器来实现此审计/版本控制功能，该触发器将复制修改后的行(已删除行)到"一个名为 ENTITY_VERSIONS 的表，该表将有大约 20 列称为col1、col2、col3、col4 等将存储上表中的列；然而，问题是如果要进行版本控制的表超过 1 个并且只有 1 个 TARGET 表(ENTITY_VERSIONS)来存储所有表的版本，我该如何设计 TARGET 表?

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 ?

如果可以共享一些指向用于实现审计/版本控制的 PostgreSQL 触发器(和相关的存储过程)代码的指针，那将是一个奖励.

It will be bonus if some pointers towards PostgreSQL Triggers (and associated Stored Procedure ) code for implementing the auditing/versioning can be shared.

PS:我查看了在 SQL Server 中实施审计表的建议? 有点像答案，只是我不知道 OldValue 和 NewValue 应该是什么类型?

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:如果表使用 SOFT DELETE(幻像删除)而不是 HARD 删除，您的建议是否有所改变?

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.

此链接 Postgres 文档中显示了一些审核Postgres 中的触发器示例.

This link in the Postgres documentation shows some audit trigger examples in Postgres.

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