Postgres 监听模式记录DDL日志

• 使用事件触发器记录用户DDL日志

• 创建DDL日志记录表

•   create schema listener;
    
    -- 日志表
    create table listener.ddl_log(
        id serial8,
        "user" text,
        txid bigint,
        schema_name text,
        ddl_type text,
        object varchar,
        ddl_query text,
        query_time timestamp
    );
    
    create function listener.flush_ddl_log()
    returns void
    language sql
    as
        $$
            truncate listener.ddl_log;
        $$;
  

• DDL捕获函数

• create 或 replace的DDL捕获

•   create function listener.listen()
    returns event_trigger
    language plpgsql
    as
        $$
            declare
                query text;
                command record;
            begin
                query := current_query();
                if exists(select * from pg_event_trigger_ddl_commands()) then
                    for command in select * from pg_event_trigger_ddl_commands()
                    loop
                    insert into listener.ddl_log("user", txid, schema_name, ddl_type, object, ddl_query, query_time)
                    VALUES (current_user,txid_current(),command.schema_name,command.command_tag,command.object_identity,query, statement_timestamp());
                    end loop ;
                end if;
            end;
        $$
    security definer;
  

• drop的DDL捕获

•   create function listener.listen_drop()
    returns event_trigger
    language plpgsql
    as
        $$
            declare
                query text;
                command record;
            begin
                query := current_query();
                if exists(select * from pg_event_trigger_dropped_objects()) is not null then
                    for command in select * from pg_event_trigger_dropped_objects()
                    loop
                    insert into listener.ddl_log("user", txid, schema_name, ddl_type, object, ddl_query, query_time)
                    VALUES (current_user,txid_current(),command.schema_name,'DROP '||command.object_type,command.object_identity,query, statement_timestamp());
                    end loop ;
                end if;
            end;
        $$
    security definer;
  

• 事物触发器的创建

•   create event trigger ddl_listen on ddl_command_end execute procedure listener.listen();
    create event trigger ddl_listen_drop on sql_drop execute procedure listener.listen_drop();
  

• 测试环境

•   -- 清空日志过程
    create procedure listener.flush_ddl_log()
    language sql
    as
        $$
            alter sequence listener.ddl_log_id_seq restart ;
            truncate listener.ddl_log;
        $$;
  

• ddl测试

• 创建表

  • 	  create table listener.tb_testa(
    	      id int,
    	      val int
    	  );
    

  • 查看日志表

  • 	  postgres=# select * from listener.ddl_log;
    	   id |   user   | txid  | schema_name |   ddl_type   |      object       |             ddl_query             |         query_time
    	  ----+----------+-------+-------------+--------------+-------------------+-----------------------------------+----------------------------
    	    2 | postgres | 90888 | listener    | CREATE TABLE | listener.tb_testa | create table listener.tb_testa(\r+| 2022-11-15 12:24:31.445329
    	      |          |       |             |              |                   |     id int,\r                    +|
    	      |          |       |             |              |                   |     val int\r                    +|
    	      |          |       |             |              |                   | )                                 |
    	  (1 行记录)
    

• 再创建一个带序列的表

  • 	  create table listener.tb_testb(
    	      id serial,
    	      val int
    	  );
    

  • 查看日志表

  • 	  postgres=# select * from listener.ddl_log;
    	   id |   user   | txid  | schema_name |    ddl_type     |          object          |             ddl_query             |         query_time
    	  ----+----------+-------+-------------+-----------------+--------------------------+-----------------------------------+----------------------------
    	    2 | postgres | 90888 | listener    | CREATE TABLE    | listener.tb_testa        | create table listener.tb_testa(\r+| 2022-11-15 12:24:31.445329
    	      |          |       |             |                 |                          |     id int,\r                    +|
    	      |          |       |             |                 |                          |     val int\r                    +|
    	      |          |       |             |                 |                          | )                                 |
    	    3 | postgres | 90902 | listener    | CREATE SEQUENCE | listener.tb_testb_id_seq | create table listener.tb_testb(\r+| 2022-11-15 12:29:29.538258
    	      |          |       |             |                 |                          |     id serial,\r                 +|
    	      |          |       |             |                 |                          |     val int\r                    +|
    	      |          |       |             |                 |                          | )                                 |
    	    4 | postgres | 90902 | listener    | CREATE TABLE    | listener.tb_testb        | create table listener.tb_testb(\r+| 2022-11-15 12:29:29.538258
    	      |          |       |             |                 |                          |     id serial,\r                 +|
    	      |          |       |             |                 |                          |     val int\r                    +|
    	      |          |       |             |                 |                          | )                                 |
    	    5 | postgres | 90902 | listener    | ALTER SEQUENCE  | listener.tb_testb_id_seq | create table listener.tb_testb(\r+| 2022-11-15 12:29:29.538258
    	      |          |       |             |                 |                          |     id serial,\r                 +|
    	      |          |       |             |                 |                          |     val int\r                    +|
    	      |          |       |             |                 |                          | )                                 |
    	  (4 行记录)
    	  
    	  
    

  • 可以看到这个表的创建由三个ddl组成

• 删除表

  • 	  call listener.flush_ddl_log();
    	  drop table listener.tb_testa;
    

  • 查看日志表

  • 	  postgres=# select * from listener.ddl_log;
    	   id |   user   | txid  | schema_name |  ddl_type  |       object        |          ddl_query           |         query_time
    	  ----+----------+-------+-------------+------------+---------------------+------------------------------+----------------------------
    	    2 | postgres | 90918 | listener    | DROP table | listener.tb_testa   | drop table listener.tb_testa | 2022-11-15 12:37:14.487202
    	    3 | postgres | 90918 | listener    | DROP type  | listener.tb_testa   | drop table listener.tb_testa | 2022-11-15 12:37:14.487202
    	    4 | postgres | 90918 | listener    | DROP type  | listener.tb_testa[] | drop table listener.tb_testa | 2022-11-15 12:37:14.487202
    

• 其余的测试可自行进行，有错误的地方欢迎一起交流;