在PG数据库中如何实现可以查看表的创建时间、修改时间、VACUUM、分析时间等DDL时间？
Tags： DDLPGPostgreSQL修改时间创建时间
👉 本文共约645个字，系统预计阅读时间或需3分钟。
简介
相信用过GP数据库的DBA们都用过pg_stat_last_operation或者pg_stat_last_shoperation这两个系统表去查看数据库对象的元数据信息。pg_stat_last_operation主要是跟踪记录表和视图的元数据信息，pg_stat_last_shoperation则是跟踪记录角色和表空间的元数据信息。 具体可以参考：https://www.xmmup.com/greenplumchakanbiaodechuangjianshijian.html#zong_jie
但是在PG数据库中是没有这两个系统表的，所以每次要查找表的创建及其他信息不是凭记忆力就是一一查看日志记录，如果时间久远的话工作量就相对较大了。那么怎么才能实现和GP类似的效果呢？
实验
以下实验基于PostgreSQL 12.11版本
创建记录DDL语句的表
1 2 3 4 5 6 7 8 | CREATE TABLE pg_stat_last_operation ( id serial PRIMARY KEY, object_type text, schema_name VARCHAR(50), action_name name NOT NULL, object_identity text, statime timestamp with time zone ); |
创建记录DDL语句的函数get_object_time_func
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE OR REPLACE FUNCTION get_object_time_func() RETURNS event_trigger LANGUAGE plpgsql AS $$ DECLARE obj record; BEGIN FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands () LOOP INSERT INTO public.pg_stat_last_operation (object_type, schema_name,action_name,object_identity,statime) SELECT obj.object_type, obj.schema_name, obj.command_tag,obj.object_identity,now(); END LOOP; END; $$; CREATE FUNCTION |
创建触发器，在执行DDL语句时将记录写到函数中的表中
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE EVENT TRIGGER get_object__history_trigger ON ddl_command_end EXECUTE PROCEDURE get_object_time_func(); CREATE FUNCTION get_object_for_drops() RETURNS event_trigger LANGUAGE plpgsql AS $$ DECLARE obj record; BEGIN FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP INSERT INTO public.pg_stat_last_operation (object_type, schema_name,action_name,object_identity,statime) SELECT obj.object_type, obj.schema_name,tg_tag,obj.object_identity,now(); END LOOP; END; $$; CREATE EVENT TRIGGER get_object_trigger_for_drops ON sql_drop EXECUTE PROCEDURE get_object_for_drops(); |
创建表进行测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | -- 新建表 postgres=# create table t1(id int); CREATE TABLE postgres=# select * from pg_stat_last_operation; id | object_type | schema_name | action_name | object_identity | statime ----+-------------+-------------+--------------+-----------------+------------------------------- 1 | table | public | CREATE TABLE | public.t1 | 2023-01-05 17:18:40.825139+08 (1 row) -- 修改表owner、授权等 postgres=# alter table t1 owner to test; ALTER TABLE postgres=# grant SELECT on t1 TO test1; GRANT postgres=# revoke SELECT on t1 from test1; REVOKE postgres=# create view v_t1 as select * from t1; CREATE VIEW postgres=# select * from pg_stat_last_operation; id | object_type | schema_name | action_name | object_identity | statime ----+-------------+-------------+--------------+-----------------+------------------------------- 1 | table | public | CREATE TABLE | public.t1 | 2023-01-05 17:18:40.825139+08 2 | table | public | ALTER TABLE | public.t1 | 2023-01-05 17:22:21.944326+08 3 | TABLE | | GRANT | | 2023-01-05 17:23:58.276351+08 4 | TABLE | | REVOKE | | 2023-01-05 17:24:57.607371+08 5 | view | public | CREATE VIEW | public.v_t1 | 2023-01-05 17:26:11.191602+08 (5 rows) postgres=# drop table t1; NOTICE: DROP TABLE dropped object: table public.t1 public.t1 NOTICE: DROP TABLE dropped object: type public.t1 public.t1 NOTICE: DROP TABLE dropped object: type public._t1 public.t1[] DROP TABLE postgres=# select * from pg_stat_last_operation; id | object_type | schema_name | action_name | object_identity | statime ----+-------------+-------------+-----------------+-------------------------------+------------------------------- 1 | table | public | CREATE TABLE | public.t1 | 2023-01-05 17:18:40.825139+08 2 | table | public | ALTER TABLE | public.t1 | 2023-01-05 17:22:21.944326+08 3 | TABLE | | GRANT | | 2023-01-05 17:23:58.276351+08 4 | TABLE | | REVOKE | | 2023-01-05 17:24:57.607371+08 5 | view | public | CREATE VIEW | public.v_t1 | 2023-01-05 17:26:11.191602+08 6 | table | public | DROP TABLE | public.t1 | 2023-01-05 18:05:49.611115+08 7 | type | public | DROP TABLE | public.t1 | 2023-01-05 18:05:49.611115+08 8 | type | public | DROP TABLE | public.t1[] | 2023-01-05 18:05:49.611115+08 (8 rows) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
