pgMemento使用触发器来跟踪数据变化。
要在日志表中跟踪元组的不同版本，默认情况下会在每个审计表中创建一个名为“pgmemento_audit_id”的列。这比依赖于一个表的主键更容易，主键可能定义在多个不同数据类型的列上。
pgmemento模式包含6个日志表和2个视图:
—transaction_log [TABLE]:存储事务的元数据
—table_event_log [TABLE]:存储与事务相关的表事件的元数据
—row_log [TABLE]:存储被审计表的数据修改记录
—audit_schema_log [TABLE]:存储审计模式信息
—audit_table_log[TABLE]:存储审计表信息
—audit_column_log [TABLE]:保存被审计表的列信息
—audit_tables [VIEW]:显示当前被pgMemento审计的表的相关信息，其中包含事务范围的信息
—audit_tables_dependency[VIEW]:按照审计表之间的依赖关系（外键约束）的顺序列出审计表
安装pgMemento插件
$ unzip /home/postgres/pgmemento-0.7.3.zip
$ cd pgmemento-0.7.3/
$ make
$ make install
$ psql -d auditdb
psql (13.6)
Type "help" for help.
auditdb=# CREATE EXTENSION pgmemento;
CREATE EXTENSION
复制安装过程会在数据库中创建pgmemento模式
auditdb=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+-----------------------------------------------------------------------------------
pgmemento | 0.7.3 | pgmemento | Audit trail with schema versioning for PostgreSQL using transaction-based logging
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
supa_audit | 0.2.3 | public | Generic table auditing
uuid-ossp | 1.1 | public | generate universally unique identifiers (UUIDs)
(4 rows)
auditdb=# \dn
List of schemas
Name | Owner
-----------+----------
audit | postgres
pgmemento | postgres
public | postgres
(3 rows)
auditdb=# \dt+ pgmemento.*
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
-----------+------------------+-------+----------+-------------+------------+--------------------------------------------------------------------------------------------------------------------
pgmemento | audit_column_log | table | postgres | permanent | 8192 bytes | Stores information about audited columns, which is important when restoring previous versions of tuples and tables
pgmemento | audit_schema_log | table | postgres | permanent | 8192 bytes | Stores information about how pgMemento is configured in audited database schema
pgmemento | audit_table_log | table | postgres | permanent | 8192 bytes | Stores information about audited tables, which is important when restoring a whole schema or database
pgmemento | row_log | table | postgres | permanent | 8192 bytes | Stores the historic data a.k.a the audit trail
pgmemento | table_event_log | table | postgres | permanent | 8192 bytes | Stores metadata about different kind of events happening during one transaction against one table
pgmemento | transaction_log | table | postgres | permanent | 8192 bytes | Stores metadata about each transaction
(6 rows)
auditdb=# \dv+ pgmemento.*
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
-----------+-------------------------+------+----------+-------------+---------+------------------------------------------------------------------------------
pgmemento | audit_tables | view | postgres | permanent | 0 bytes | Lists which tables are audited by pgMemento (a.k.a. have an audit_id column)
pgmemento | audit_tables_dependency | view | postgres | permanent | 0 bytes | Lists the dependencies between audited tables which is important for reverts
(2 rows)
复制开启审计
准备测试数据
auditdb=# create user test;
auditdb=# create schema test AUTHORIZATION test;
CREATE SCHEMA
auditdb=# \c - test
You are now connected to database "auditdb" as user "test".
auditdb=> create table t (id int,name varchar(10));
CREATE TABLE
auditdb=> create table t1 (id int,name varchar(10));
CREATE TABLE
auditdb=> create table t2 (id int,name varchar(10));
CREATE TABLE
auditdb=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
test | t | table | test
test | t1 | table | test
test | t2 | table | test
(3 rows)
复制1.开启模式审计
SELECT pgmemento.init(
schemaname := 'test', -- 设置开启审计的模式名称,默认为'public'
audit_id_column_name := 'audit_trail_id', -- 设置表中添加的审计列名称,默认为 'pgmemento_audit_id'
log_old_data := TRUE, -- 设置是否记录旧记录,默认为 true
log_new_data := TRUE, -- 设置是否记录新数据,默认为 false
log_state := TRUE, -- 设置,默认为 false
trigger_create_table := TRUE, -- 设置该模式下的创建的新表是否自动开启审计,默认为 false
except_tables := ARRAY['t'] -- 设置该模式下哪些表不开启审计，默认为 empty
);
复制开启test模式下，除了t表之外所有表的审计功能：
auditdb=# SELECT pgmemento.init(
auditdb(# schemaname := 'test', -- 设置开启审计的模式名称,默认为'public'
auditdb(# audit_id_column_name := 'audit_trail_id', -- 设置表中添加的审计列名称,默认为 'pgmemento_audit_id'
auditdb(# log_old_data := TRUE, -- 设置是否记录旧记录,默认为 true
auditdb(# log_new_data := TRUE, -- 设置是否记录新数据,默认为 false
auditdb(# log_state := TRUE, -- 设置,默认为 false
auditdb(# trigger_create_table := TRUE, -- 设置该模式下的创建的新表是否自动开启审计,默认为 false
auditdb(# except_tables := ARRAY['t'] -- 设置该模式下哪些表不开启审计，默认为 empty
auditdb(# );
init
-------------------------------------------
pgMemento is initialized for test schema.
(1 row)
复制暂停test模式审计功能
auditdb=# select pgmemento.stop('test');
stop
---------------------------------------
pgMemento is stopped for test schema.
(1 row)
复制重启test模式的审计功能
auditdb=# select pgmemento.start('test');
start
---------------------------------------------------------
pgMemento is started and reinitialized for test schema.
(1 row)
复制关闭test模式的审计功能
auditdb=# select pgmemento.drop('test');
drop
----------------------------------------
pgMemento is dropped from test schema.
(1 row)
复制2.开启表数据审计
auditdb=# SELECT pgmemento.create_table_audit(
tablename := 't',
schemaname := 'public',
audit_id_column_name := 'audit_trail_id',
log_old_data := TRUE,
log_new_data := TRUE,
log_state := TRUE
);
create_table_audit
--------------------
(0 rows)
auditdb=# \d+ t1;
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------+-----------------------+-----------+----------+---------------------------------------------+----------+--------------+-------------
id | integer | | | | plain | |
audit_trail_id | bigint | | not null | nextval('pgmemento.audit_id_seq'::regclass) | plain | |
name | character varying(10) | | | | extended | |
Indexes:
"t1_audit_trail_id_key" UNIQUE CONSTRAINT, btree (audit_trail_id)
Triggers:
pgmemento_delete_trigger AFTER DELETE ON t1 FOR EACH ROW EXECUTE FUNCTION pgmemento.log_delete('audit_trail_id', 'true')
pgmemento_insert_trigger AFTER INSERT ON t1 FOR EACH ROW EXECUTE FUNCTION pgmemento.log_insert('audit_trail_id', 'true', 'true')
pgmemento_transaction_trigger BEFORE INSERT OR DELETE OR UPDATE OR TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE FUNCTION pgmemento.log_statement()
pgmemento_truncate_trigger BEFORE TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE FUNCTION pgmemento.log_truncate('audit_trail_id')
pgmemento_update_trigger AFTER UPDATE ON t1 FOR EACH ROW EXECUTE FUNCTION pgmemento.log_update('audit_trail_id', 'true', 'true')
Access method: heap
复制查看t1表的审计结果
auditdb=# insert into t values(1),(2);
INSERT 0 2
auditdb=# update t set id =0 where id=2;
UPDATE 1
auditdb=# select * from pgmemento.row_log;
id | audit_id | event_key | old_data | new_data
----+----------+----------------------------------------------------+--------------------------------------------------+---------------------------------------------
1 | 1 | 1652101924.938454;1652101924.938454;545;3;t1;test | | {"id": 1, "name": "q", "audit_trail_id": 1}
2 | 2 | 1652101924.938454;1652101924.938454;545;3;t1;test | | {"id": 2, "name": "m", "audit_trail_id": 2}
3 | 10 | 1652107723.670586;1652107723.670586;565;7;t1;test | {"id": 4, "name": "m", "pgmemento_audit_id": 10} |
4 | 7 | 1652107865.703702;1652107865.703702;567;8;t1;test | {"id": 1, "name": "q", "pgmemento_audit_id": 7} |
5 | 8 | 1652107865.703702;1652107865.703702;567;8;t1;test | {"id": 2, "name": "m", "pgmemento_audit_id": 8} |
6 | 9 | 1652107865.703702;1652107865.703702;567;8;t1;test | {"id": 3, "name": "q", "pgmemento_audit_id": 9} |
7 | 11 | 1652152032.045865;1652152032.045865;576;3;t;public | | {"id": 1, "audit_trail_id": 11}
8 | 12 | 1652152032.045865;1652152032.045865;576;3;t;public | | {"id": 2, "audit_trail_id": 12}
9 | 12 | 1652152063.070266;1652152063.070266;577;4;t;public | {"id": 2} | {"id": 0}
(9 rows)
复制暂停t1表的审计功能
auditdb=# SELECT pgmemento.drop_table_log_trigger('t1', 'public');
drop_table_log_trigger
------------------------
(0 rows)
复制重启t1表的审计功能
auditdb=# SELECT pgmemento.create_table_log_trigger(
table_name := 't1',
schema_name := 'public',
audit_id_column_name := 'audit_trail_id',
log_old_data := TRUE,
log_new_data := TRUE);
create_table_log_trigger
--------------------------
(0 rows)
复制关闭表审计
auditdb=# select pgmemento.drop_table_audit('t1','public','audit_trail_id',true,true);
drop_table_audit
------------------
(0 rows)
auditdb=# select * from t1;
id | name
----+------
1 |
0 |
-1 |
3 |
4 | a
(5 rows)
auditdb=# \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(10) | | | | extended | |
Access method: heap
复制恢复数据
使用指定审计事务ID范围恢复t1表的数据
auditdb=# \df pgmemento.revert_distinct_transactions
List of functions
Schema | Name | Result data type | Argument data types | Type
-----------+------------------------------+------------------+--------------------------------------------+------
pgmemento | revert_distinct_transactions | SETOF void | start_from_tid integer, end_at_tid integer | func
(1 row)
auditdb=# \df pgmemento.revert_transactions
List of functions
Schema | Name | Result data type | Argument data types | Type
-----------+---------------------+------------------+--------------------------------------------+------
pgmemento | revert_transactions | SETOF void | start_from_tid integer, end_at_tid integer | func
(1 row)
auditdb=# select * from t1;
id | audit_trail_id | name
----+----------------+------
1 | 11 |
0 | 12 |
-1 | 13 |
3 | 14 |
4 | 15 | a
(5 rows)
auditdb=# select t.transaction_id,t.table_operation,t.table_name,t.schema_name,r.old_data,r.new_data from pgmemento.table_event_log t,pgmemento.row_log r where r.event_key = t.event_key ;
transaction_id | table_operation | table_name | schema_name | old_data | new_data
----------------+-----------------+------------+-------------+--------------------------------------------------+-----------------------------------------------
4 | INSERT | t1 | test | | {"id": 1, "name": "q", "audit_trail_id": 1}
4 | INSERT | t1 | test | | {"id": 2, "name": "m", "audit_trail_id": 2}
23 | DELETE | t1 | test | {"id": 4, "name": "m", "pgmemento_audit_id": 10} |
24 | TRUNCATE | t1 | test | {"id": 1, "name": "q", "pgmemento_audit_id": 7} |
24 | TRUNCATE | t1 | test | {"id": 2, "name": "m", "pgmemento_audit_id": 8} |
24 | TRUNCATE | t1 | test | {"id": 3, "name": "q", "pgmemento_audit_id": 9} |
33 | INSERT | t | public | | {"id": 1, "audit_trail_id": 11}
33 | INSERT | t | public | | {"id": 2, "audit_trail_id": 12}
34 | UPDATE | t | public | {"id": 2} | {"id": 0}
35 | ADD COLUMN | t | public | | {"name": null}
35 | ADD COLUMN | t | public | | {"name": null}
37 | UPDATE | t1 | public | {"id": 2} | {"id": -1}
38 | UPDATE | t1 | public | {"id": -1} | {"id": 2}
39 | UPDATE | t1 | public | {"id": 2} | {"id": 2}
42 | UPDATE | t1 | public | {"id": 2} | {"id": -1}
43 | INSERT | t1 | public | | {"id": 3, "name": null, "audit_trail_id": 14}
43 | INSERT | t1 | public | | {"id": 4, "name": null, "audit_trail_id": 15}
44 | UPDATE | t1 | public | {"name": null} | {"name": "a"}
45 | DELETE | t1 | public | {"id": 4, "name": "a", "audit_trail_id": 15} |
45 | DELETE | t1 | public | {"id": 3, "name": null, "audit_trail_id": 14} |
45 | UPDATE | t1 | public | {"id": -1} | {"id": 2}
52 | UPDATE | t1 | public | {"id": 2} | {"id": -1}
52 | INSERT | t1 | public | | {"id": 3, "name": null, "audit_trail_id": 14}
52 | INSERT | t1 | public | | {"id": 4, "name": "a", "audit_trail_id": 15}
(24 rows)
auditdb=# SELECT pgmemento.revert_distinct_transactions(43,43);
revert_distinct_transactions
------------------------------
(0 rows)
auditdb=# select t.transaction_id,t.table_operation,t.table_name,t.schema_name,r.old_data,r.new_data from pgmemento.table_event_log t,pgmemento.row_log r where r.event_key = t.event_key ;
transaction_id | table_operation | table_name | schema_name | old_data | new_data
----------------+-----------------+------------+-------------+--------------------------------------------------+-----------------------------------------------
4 | INSERT | t1 | test | | {"id": 1, "name": "q", "audit_trail_id": 1}
4 | INSERT | t1 | test | | {"id": 2, "name": "m", "audit_trail_id": 2}
23 | DELETE | t1 | test | {"id": 4, "name": "m", "pgmemento_audit_id": 10} |
24 | TRUNCATE | t1 | test | {"id": 1, "name": "q", "pgmemento_audit_id": 7} |
24 | TRUNCATE | t1 | test | {"id": 2, "name": "m", "pgmemento_audit_id": 8} |
24 | TRUNCATE | t1 | test | {"id": 3, "name": "q", "pgmemento_audit_id": 9} |
33 | INSERT | t | public | | {"id": 1, "audit_trail_id": 11}
33 | INSERT | t | public | | {"id": 2, "audit_trail_id": 12}
34 | UPDATE | t | public | {"id": 2} | {"id": 0}
35 | ADD COLUMN | t | public | | {"name": null}
35 | ADD COLUMN | t | public | | {"name": null}
37 | UPDATE | t1 | public | {"id": 2} | {"id": -1}
38 | UPDATE | t1 | public | {"id": -1} | {"id": 2}
39 | UPDATE | t1 | public | {"id": 2} | {"id": 2}
42 | UPDATE | t1 | public | {"id": 2} | {"id": -1}
43 | INSERT | t1 | public | | {"id": 3, "name": null, "audit_trail_id": 14}
43 | INSERT | t1 | public | | {"id": 4, "name": null, "audit_trail_id": 15}
44 | UPDATE | t1 | public | {"name": null} | {"name": "a"}
45 | DELETE | t1 | public | {"id": 4, "name": "a", "audit_trail_id": 15} |
45 | DELETE | t1 | public | {"id": 3, "name": null, "audit_trail_id": 14} |
45 | UPDATE | t1 | public | {"id": -1} | {"id": 2}
52 | UPDATE | t1 | public | {"id": 2} | {"id": -1}
52 | INSERT | t1 | public | | {"id": 3, "name": null, "audit_trail_id": 14}
52 | INSERT | t1 | public | | {"id": 4, "name": "a", "audit_trail_id": 15}
55 | DELETE | t1 | public | {"id": 4, "name": "a", "audit_trail_id": 15} |
55 | DELETE | t1 | public | {"id": 3, "name": null, "audit_trail_id": 14} |
(26 rows)
auditdb=# select * from t1;
id | audit_trail_id | name
----+----------------+------
1 | 11 |
0 | 12 |
-1 | 13 |
(3 rows)
auditdb=# SELECT pgmemento.revert_distinct_transactions(55,55);
revert_distinct_transactions
------------------------------
(0 rows)
auditdb=# select * from t1;
id | audit_trail_id | name
----+----------------+------
1 | 11 |
0 | 12 |
-1 | 13 |
3 | 14 |
4 | 15 | a
(5 rows)
复制「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
文章被以下合辑收录
评论
墨天轮福利君
2年前
评论
0您好，您的文章已入选墨力原创作者计划合格奖，10墨值奖励已经到账请查收！
❤️我们还会实时派发您的流量收益。
2年前
点赞 评论
相关阅读
外国CTO也感兴趣的开源数据库项目——openHalo
小满未满、
498次阅读
2025-04-21 16:58:09
9.9 分高危漏洞，尽快升级到 pgAdmin 4 v9.2 进行修复
严少安
357次阅读
2025-04-11 10:43:23
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
337次阅读
2025-04-15 14:48:05
openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
严少安
309次阅读
2025-04-07 12:14:29
postgresql+patroni+etcd高可用安装
necessary
172次阅读
2025-03-28 10:11:23
转发有奖 | PostgreSQL 16 PGCM高级认证课程直播班招生中！
墨天轮小教习
152次阅读
2025-04-14 15:58:34
墨天轮PostgreSQL认证证书快递已发（2025年3月批）
墨天轮小教习
130次阅读
2025-04-03 11:43:25
SQL 优化之 OR 子句改写
xiongcc
95次阅读
2025-04-21 00:08:06
融合Redis缓存的PostgreSQL高可用架构
梧桐
90次阅读
2025-04-08 06:35:40
PostgreSQL拓展PGQ实现解析
chirpyli
87次阅读
2025-04-07 11:23:17
