pg_auditor

pig build pkg pg_auditor         # 构建 RPM / DEB 包

pig repo add pgsql -u          # 添加仓库并更新缓存

CREATE EXTENSION pg_auditor CASCADE;  -- 依赖: hstore

CREATE EXTENSION pg_auditor CASCADE;  -- 同时安装 hstore

-- 开始审计一个表（默认审计所有 DML）
SELECT auditor.attach('fruit');

-- 仅审计特定操作
SELECT auditor.attach('fruit', ARRAY['INSERT', 'UPDATE']);

-- 仅审计特定列
SELECT auditor.attach('fruit', ARRAY['INSERT', 'UPDATE', 'DELETE'], ARRAY['name', 'weight']);

-- 停止审计
SELECT auditor.detach('fruit');

-- 管理单个语句/列
SELECT auditor.attach_statement('fruit', 'DELETE');
SELECT auditor.detach_statement('fruit', 'DELETE');
SELECT auditor.attach_column('fruit', 'weight');
SELECT auditor.detach_column('fruit', 'weight');

-- 防止 TRUNCATE
SELECT auditor.forbid_truncate('fruit');

SELECT transaction_id, operation, old_rec, new_rec FROM auditor.log;

-- 撤销当前会话中最后 N 个事务
SELECT auditor.undo();          -- 撤销最后1个
SELECT auditor.undo(3);         -- 撤销最后3个
SELECT auditor.undo(1, true);   -- 覆盖其他会话

-- 取消特定事务
SELECT auditor.cancel(5557);

-- 将数据恢复到特定事务或时间戳
SELECT auditor.flashback(5556);
SELECT auditor.flashback('2021-02-08 14:40:00'::timestamp);

SELECT * FROM auditor.evolution('fruit'::regclass, 'weight', 'orange'::text);
-- 显示给定主键的某列值的完整历史