在PG数据库中如何实现可以查看表的创建时间？

原文链接：https://pgfans.cn/a/2063

相信用过GP数据库的DBA们都用过pg_stat_last_operation或者pg_stat_last_shoperation这两个系统表去查看数据库对象的元数据信息。pg_stat_last_operation主要是跟踪记录表和视图的元数据信息，pg_stat_last_shoperation则是跟踪记录角色和表空间的元数据信息。

但是在PG数据库中是没有这两个系统表的，所以每次要查找表的创建及其他信息不是凭记忆力就是一一查看日志记录，如果时间久远的话工作量就相对较大了。那么怎么才能实现和GP类似的效果呢？

以下实验基于PostgreSQL 12.11版本

创建记录DDL语句的表

  
  
  

   
   
   
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

  
  
  

   
   
   
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语句时将记录写到函数中的表中

  
  
  

   
   
   
CREATE EVENT TRIGGER get_object__history_trigger ON ddl_command_end
   
   
   
EXECUTE PROCEDURE get_object_time_func();
  
  
  

创建表进行测试

  
  
  

   
   
   
新建表
   
   
   
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-0517: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 asselect*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-0517:18:40.825139+08
   
   
   
2| table       |public| ALTER TABLE  |public.t1       |2023-01-0517:22:21.944326+08
   
   
   
3| TABLE       || GRANT        ||2023-01-0517:23:58.276351+08
   
   
   
4| TABLE       || REVOKE       ||2023-01-0517:24:57.607371+08
   
   
   
5| view        |public| CREATE VIEW  |public.v_t1     |2023-01-0517:26:11.191602+08
   
   
   
(5 rows)
  
  
  

这里可以看到该表是有记录对表的相关DDL操作，需要注意的是虽然DDL语句中是包含DROP的,但是需要另外一个触发函数来实现。

  
  
  

   
   
   
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();
  
  
  

删除表测试

  
  
  

   
   
   
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-0517:18:40.825139+08
   
   
   
2| table       |public| ALTER TABLE  |public.t1       |2023-01-0517:22:21.944326+08
   
   
   
3| TABLE       || GRANT        ||2023-01-0517:23:58.276351+08
   
   
   
4| TABLE       || REVOKE       ||2023-01-0517:24:57.607371+08
   
   
   
5| view        |public| CREATE VIEW  |public.v_t1     |2023-01-0517:26:11.191602+08
   
   
   
6| table       |public| DROP TABLE      |public.t1                     |2023-01-0518:05:49.611115+08
   
   
   
7| type        |public| DROP TABLE      |public.t1                     |2023-01-0518:05:49.611115+08
   
   
   
8| type        |public| DROP TABLE      |public.t1[]|2023-01-0518:05:49.611115+08
   
   
   
(8 rows)
  
  
  

以上基本可以实现和GP中pg_stat_last_operation系统表一样的功能，但是这只针对于记录该表及函数触发器以后的DDL操作，之前的DDL语句是无法记录的。以前的就只能按照老方法去找了，如果是新启用的数据库可以一开始就创建该表及函数，这样就可以从一开始记录了，暂时写到这里，希望对大家有所帮助。