PostgreSQL:操作日志记录

Question

我需要确保在我的数据库中捕获所有数据修改(插入/更新/删除(。从更大的角度来看，一个过程将读取每个修改并将该信息映射到特定的消息结构，然后将其添加到像 Kafka 这样的队列中。不过，我不确定最好的方法。这个答案暗示了使用 test_decoding 插件。其他资源指向 WAL 和查询复制槽，但我不需要存档 WAL 或实际复制我的数据。我只需要该数据发生的所有事情的日志。像这样：

SELECT * FROM operations_log;
> | query                                                              |
> | INSERT INTO users (id, name, bio) VALUES (1, 'pat', 'a person')    |
> | UPDATE users SET name = 'Pat' WHERE id = 1                         |     

人们如何在PostgreSQL中管理这种需求？是否需要复制或 WAL 归档才能获得此好处？

Answer 1

我通常使用表上的 after 触发器来解决此问题，然后将旧行以及时间戳和任何其他相关信息插入到另一个审计表中。

因此，以您的用户表为例，我将创建一个users_audit 桌子：

create table users_audit (
    ts timestamptz default current_timestamp,
    like users
);

然后是触发功能

create function users_audit_trigger() returns trigger
language plpgsql as $$
insert into users_audit values (current_timestamp, OLD.*);
$$

最后是实际的触发器...

create trigger users_audit_tg after update or delete on users
for each row execute procedure users_audit_trigger();

以上未经测试，您可能需要记录更多信息， 例如用户进行更新或实际操作 更新或删除，因此详细信息可能会有所不同。

您可以使用逻辑复制在不同的 服务器，但基本思想保持不变。