如何在 postgresql 中记录数据更改？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13785855/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to log data change in postgresql?
提问by harry
This question may seem to be a possible duplicate of some other questions that are related to this topic. I've found some similar questions(some questions were asked years back and discussion on the topic seemed to be almost over). But no feasible solutions were found for my problem. I've a database with plenty of tables with huge amount of data in it. I need to log each and every changes that is happening to the datas that are stored in the tables of the particular database.
此问题似乎与与此主题相关的其他一些问题可能重复。我发现了一些类似的问题（几年前有人问过一些问题，关于这个话题的讨论似乎快结束了）。但是我的问题没有找到可行的解决方案。我有一个包含大量表的数据库，其中包含大量数据。我需要记录存储在特定数据库表中的数据发生的每一个变化。
For example, I have a table for storing employee details.
例如，我有一个用于存储员工详细信息的表。
id employeename
1 ab
And, this data is changed to
并且，此数据更改为
id employeename
1 cd
So i need to log this data.
所以我需要记录这些数据。
ie, employeename
即，员工姓名
ab
AB
is changed to
改为
cd
光盘
in the table employee details
在表员工详细信息中
I need to log the data every time a change is made to the contents stored in the tables. Is it really possible? If so, how can I do that? What are the steps involved in it? I'm pretty concerned about the size of the log files in such a case. In such a situation what can be a good alternative? I'm using postgresql8.4. Any good suggestion will help me a lot. Thanks in advance.
每次更改存储在表中的内容时，我都需要记录数据。真的有可能吗？如果是这样，我该怎么做？其中涉及哪些步骤？在这种情况下，我非常担心日志文件的大小。在这种情况下，什么是好的选择？我正在使用 postgresql8.4。任何好的建议都会对我有很大帮助。提前致谢。
采纳答案by Igor Romanchenko
Here is the example of a trigger, than logs such changes: Audit trigger.
这是触发器的示例，而不是记录此类更改：Audit trigger。
回答by Karl Adler
Very generic trigger function, found there: https://www.cybertec-postgresql.com/en/tracking-changes-in-postgresql/
非常通用的触发功能，在那里找到：https: //www.cybertec-postgresql.com/en/tracking-changes-in-postgresql/
Table to store the history:
用于存储历史记录的表：
CREATE SCHEMA logging;
CREATE TABLE logging.t_history (
id serial,
tstamp timestamp DEFAULT now(),
schemaname text,
tabname text,
operation text,
who text DEFAULT current_user,
new_val json,
old_val json
);
The trigger:
触发：
CREATE FUNCTION change_trigger() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT'
THEN INSERT INTO logging.t_history (
tabname, schemaname, operation, new_val
) VALUES (
TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW)
);
RETURN NEW;
ELSIF TG_OP = 'UPDATE'
THEN
INSERT INTO logging.t_history (
tabname, schemaname, operation, new_val, old_val
)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), row_to_json(OLD));
RETURN NEW;
ELSIF TG_OP = 'DELETE'
THEN
INSERT INTO logging.t_history
(tabname, schemaname, operation, old_val)
VALUES (
TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD)
);
RETURN OLD;
END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
Apply the trigger:
应用触发器：
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON your_table
FOR EACH ROW EXECUTE PROCEDURE change_trigger();
