PostgreSQL表结构视图+物化视图+事件触发器+外部表实现远程查询外部表获取目标数据库的表结构的实时信息_postgres 外部视图

---

一、创建表结构视图，用超管用户postgres或者root，在对应的数据库下的public schema下执行

--drop view tab_info_v cascade;
CREATE or replace VIEW tab_info_v AS
 WITH schemainfo AS (
         SELECT pg_namespace.oid,
            pg_namespace.nspname
           FROM pg_namespace
        ), tbinfo AS (
         SELECT pg_class.oid,
            pg_class.relname,
            (col_description(pg_class.oid, 0))::character varying AS comment,
            pg_class.relkind,
            pg_class.relnamespace
           FROM pg_class
        ), colinfo AS (
         SELECT pg_attribute.attrelid,
            pg_attribute.attname,
            pg_attribute.attnum,
            (format_type(pg_attribute.atttypid, pg_attribute.atttypmod))::character varying AS typelen,
            (col_description(pg_attribute.attrelid, (pg_attribute.attnum)::integer))::character varying AS comment,
						CASE
						  when pg_attribute.attnotnull = 't' then 'NOT NULL' ELSE ''
						end AS attnotnull
           FROM pg_attribute
        )
 SELECT schemainfo.nspname AS schema,
    tbinfo.relname AS table_name,
    tbinfo.comment AS table_comment,
    colinfo.attnum AS column_number,
    colinfo.attname AS column_name,
        CASE
            WHEN ((colinfo.typelen)::text = 'bigint'::text) THEN 'INT8'::character varying
            WHEN ((colinfo.typelen)::text = 'smallint'::text) THEN 'INT2'::character varying
            WHEN ((colinfo.typelen)::text = 'integer'::text) THEN 'INT4'::character varying
            WHEN ((colinfo.typelen)::text = 'text'::text) THEN 'TEXT'::character varying
            WHEN ((colinfo.typelen)::text = 'oid'::text) THEN 'OID'::character varying
            WHEN ("left"((colinfo.typelen)::text, 17) = 'character varying'::text) THEN (replace((colinfo.typelen)::text, 'character varying'::text, 'VARCHAR'::text))::character varying
            WHEN ("left"((colinfo.typelen)::text, 9) = 'character'::text) THEN (replace((colinfo.typelen)::text, 'character'::text, 'CHAR'::text))::character varying
            WHEN ((colinfo.typelen)::text = 'timestamp without time zone'::text) THEN 'TIMESTAMP'::character varying
            WHEN ((colinfo.typelen)::text = 'timestamp(6) without time zone'::text) THEN 'TIMESTAMP'::character varying
            WHEN ((colinfo.typelen)::text = 'numeric(38,10)'::text) THEN 'NUMERIC(38,10)'::character varying
            ELSE colinfo.typelen
        END AS column_type,
	          colinfo.attnotnull,
    colinfo.comment AS column_comment
   FROM tbinfo,
    colinfo,
    schemainfo
  WHERE ((tbinfo.oid = colinfo.attrelid) AND (schemainfo.oid = tbinfo.relnamespace) AND (colinfo.attnum > 0) AND (tbinfo.relkind in ('r'::"char",'p'::"char")) AND (tbinfo.relname !~~ 'pg_%'::text) AND (tbinfo.relname !~~ 'sql_%'::text) AND (colinfo.attname !~~ '%.....pg.dropped%'::text))
  ORDER BY schemainfo.nspname, tbinfo.relname, colinfo.attnum
;
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
46
47
48
49
50

用超管用户postgres或者root,给用户授权

grant select on tab_info_v to plan;
1

二、根据上面的视图，在用户schema下，创建物化视图 tab_info_mv

CREATE MATERIALIZED VIEW tab_info_mv AS
  SELECT * FROM tab_info_v;
1
2

三、部署审计功能

3.1、在root用户或者postgres用户下执行，用超级管理员执行，在public schema下执行：创建审计表 ddl_audit_log

CREATE TABLE ddl_audit_log (
	audit_log_id serial,
	classid oid,
	objid oid,
	objsubid INT,
	object_type TEXT,
	SCHEMA_NAME TEXT,
	object_name TEXT,
	object_identity TEXT,
	ddl_tag TEXT,
	op_time TIMESTAMP,
	ip_addr VARCHAR ( 50 ),
	CONSTRAINT pk_ddl_audit_log_id PRIMARY KEY ( audit_log_id ) 
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.2、创建函数event_trigger_drop_function ()，针对drop table的事件，在public schema下执行：

CREATE OR REPLACE FUNCTION event_trigger_drop_function ( ) 
  RETURNS event_trigger 
  LANGUAGE plpgsql AS $$ 
  DECLARE
	obj record;
BEGIN
	INSERT INTO ddl_audit_log ( classid, objid, objsubid, object_type, SCHEMA_NAME, object_name      ,object_identity, ddl_tag, op_time, ip_addr ) 
		SELECT
		classid,
		objid,
		objsubid,
		object_type,
		SCHEMA_NAME,
		object_name,
		object_identity,
		tg_tag,
		now( ),
		( SELECT client_addr FROM pg_stat_activity WHERE pid = pg_backend_pid ( ) ) AS ip_addr 
	  FROM
		pg_event_trigger_dropped_objects ( );
	REFRESH MATERIALIZED VIEW 用户.tab_info_mv; -- 刷新物化视图
END;
$$;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

3.3、创建事件触发器drop_table_event_trigger，针对 drop table，在public schema下执行

create event trigger drop_table_event_trigger on sql_drop when tag in ('drop table')
execute procedure event_trigger_drop_function();
1
2

3.4、创建函数event_trigger_ddl_commands_function，针对’create table’,‘create table as’,‘alter table’,'comment’的事件，在public schema下执行

create or replace function event_trigger_ddl_commands_function()
	returns event_trigger
	language plpgsql
AS $$
	declare
		obj record;
	begin
		INSERT INTO ddl_audit_log ( classid, objid, objsubid, object_type, SCHEMA_NAME, object_name      ,object_identity, ddl_tag, op_time, ip_addr ) 
		select classid,objid,objsubid,object_type,schema_name,
		'',object_identity,tg_tag,now()
		,(select client_addr from pg_stat_activity where pid = pg_backend_pid()) as ip_addr
		--,command_tag,in_extension
		--,command
		from pg_event_trigger_ddl_commands();
		REFRESH MATERIALIZED VIEW 用户.tab_info_mv;  -- 刷新物化视图
	end;
$$;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.5、创建事件触发器，针对create table、alter table、comment的事件，在public schema下执行：

--drop event trigger ddl_event_trigger;
create event trigger ddl_event_trigger on ddl_command_end  
when tag in ('create table','create table as','alter table','comment')
execute procedure event_trigger_ddl_commands_function();

create event trigger ddl_start_event_trigger on ddl_command_start  
when tag in ('alter table')
execute procedure event_trigger_ddl_commands_function();
1
2
3
4
5
6
7
8

3.6、授权

grant insert,select on ddl_audit_log to 用户;
grant select,update on ddl_audit_log_audit_log_id_seq to 用户;
1
2

四、创建外部表。（在本机pg数据库环境以 postgres 用户登录，public schema下）

• 实现原理：postgres_fdw实现远程访问功能

4.1、创建扩展 postgres_fdw

CREATE EXTENSION postgres_fdw;
1

4.2、如果是普通用户使用 postgres_fdw 需要单独授权。授权给需要使用的用户。

grant usage on foreign data wrapper postgres_fdw to tzq;
1

查询外部数据包装器

select * from pg_foreign_data_wrapper;
1

4.3、查询扩展是否安装成功

--查询已安装的扩展
select * from pg_extension;
--所有版本的扩展
select * from pg_available_extensions where installed_version is not null;
1
2
3
4

4.4、创建 foreign server 外部服务器，外部服务是指连接外部数据源的连接信息

create server fs_instruction_db_dev foreign data wrapper postgres_fdw
options(host '7.213.7.219',port '5432',dbname 'instruction_db_dev');
----查询外部服务器
--select * from pg_foreign_server;
----修改外部服务器
--ALTER SERVER fs_pg_cashplan_db_dev OPTIONS (host 'xxx', dbname 'xxx');
----删除外部服务器
--DROP SERVER IF EXISTS fs_pg_cashplan_db_dev;
----重命名外部服务器
--ALTER SERVER fs_pg_cashplan_db_dev RENAME TO fs_cashplan_db_dev;
--ALTER SERVER fs_pg_tmc_db_b1 RENAME TO fs_tmc_db_b1;
--ALTER SERVER fs_pg_tmc_db_uat RENAME TO fs_tmc_db_uat;
1
2
3
4
5
6
7
8
9
10
11
12

4.5、需要给外部服务创建映射用户

create user mapping for postgres server fs_tzq_db_dev
options(user 'tzq',password 'Tzq12345@');
----查询用户映射
--select * from pg_user_mappings order by srvname;
----为服务器 foo的用户映射bob更改口令： 
--ALTER USER MAPPING FOR postgres SERVER fs_pg_cashplan_db_dev OPTIONS (SET password 'public');
----移除一个用于外部服务器的用户映射
--DROP USER MAPPING IF EXISTS FOR postgres SERVER fs_pg_cashplan_db_dev;
1
2
3
4
5
6
7
8

4.6、创建外部表，结构和源表（上面创建的物化视图：tab_info_mv）一致

CREATE foreign TABLE public.ft_tab_info_mv_insdev (
  schema varchar ,
  t_comment varchar ,
  table_name varchar ,
  column_name varchar ,
  type varchar ,
  c_comment varchar 
)
server fs_instruction_db_dev options (schema_name 'ins',table_name 'tab_info_mv');

--删除外部表
--drop foreign table ft_tab_info_t_plandev cascade;
--drop foreign table ft_tab_info_v_insdev cascade;
1
2
3
4
5
6
7
8
9
10
11
12
13

----7、查询外部表
select * from ft_tab_info_mv_insdev;
--查询所有外部表数据字典
select * from pg_foreign_table;
select (select c.relname from pg_class c where c.oid=f.ftrelid) as relname
      ,f.* 
  from pg_foreign_table f;
1
2
3
4
5
6
7

五、测试

5.1、在用户schema下创建表，删除表，修改注释等，会更新用户表下的物化视图：tab_info_mv

5.2、然后在本机环境下的public下创建的外部表ft_tab_info_mv_insdev会实时查到变更后的数据