数据库小技巧之-带级联关系地备份视图创建脚本（支持postgresql、greenplum）_pg备份视图

一、前言

我们数据中心应用升级一直有个“顽疾”：各地区每次现场进行升级基本都会丢失一部分自定义视图（公司其他基于数据中心的应用单独创建的，数据中心升级脚本中不包含这些DDL脚本），每次升级完毕还原这些自定义视图都很费劲。
其实本来现场按版本进行增量升级也不会有这个问题（因为不会删库），但是现场每次升级中间迭代的版本都比较多，所以每个地区升级基本都是选择删库并执行目标版本的全新脚本（数据中心本身不生产数据，随时可以从数据源提取最新数据，所以不用考虑历史数据的问题），这样会导致丢失了所有非数据中心自带的视图信息。

目前已知解决办法有这么几个

• 现场每次逐版本执行升级脚本（貌似现场没有几个人愿意长期使用该方式）
• 每次升级之前先备份现有视图，删除并执行全量脚本后，还原对应视图（现场目前都是选择这个，但是痛点是自定义视图是有级联关系的，默认备份的视图是无序的，导致备份后的视图无法直接还原，恢复过程中会报错对象不存在）
• 优化数据中心升级流程，具备跨版本自动升级功能（这个不是一两天能搞定的事儿，不过还是希望我们的应用可以更友好一些）

目前来看只要能解决每次升级之前先备份现有视图，删除并执行全量脚本后，还原对应视图带来的痛点，那么可以有效地缓解现场数据中心运维人员的升级复杂度，让他们可以抽出更多的时间来做其他更有意义的事儿，所以本文章是在此背景下书写的。

二、正文

2.1、整体思路

现场提出来这个问题的时候，我的第一反应是找一下系统表中是否有存储视图之间的依赖关系，然后全量备份需要还原的视图后，使用依赖关系进行排序，并备份对应DDL脚本，最后复制脚本并手工执行。
可惜天不从人愿！谷歌了一下没找到能拿过来直接用的系统表，现场还着急使用。了解了一下需求，实际很少有两层以上的依赖，也就是深度基本最多就一层，然后就决定先怎么简单怎么来一版，后续再优化。

2.2、使用用户视图关键字匹配DDL脚本并排序（第一版）

• 一、创建生成视图的函数（先备份，再调整创建顺序）

CREATE OR REPLACE FUNCTION "public".fun_view_create_script_backup(SCHEMA_NAME IN VARCHAR(300))
RETURNS TABLE (C_DEFINITION TEXT) AS  
$$
DECLARE
  counter int := 1;
BEGIN
  -- 1、创建临时表
  DROP TABLE IF EXISTS T_TEMP_VIEW_SCRIPT;
  CREATE TEMP TABLE T_TEMP_VIEW_SCRIPT(
    OID OID, -- 视图的对象ID
    C_SCHEMA_NAME VARCHAR(300), -- 所属模式名称
    C_VIEW_NAME VARCHAR(300), -- 视图名称
    C_DEFINITION TEXT, -- 视图定义脚本
    N_ORDER INT -- 序号
  );

  -- 2、备份指定范围的视图（这里暂未过滤系统视图）
  IF SCHEMA_NAME <> '' THEN
    INSERT INTO T_TEMP_VIEW_SCRIPT 
    SELECT PG_CLASS.OID, PG_VIEWS.SCHEMANAME, PG_VIEWS.VIEWNAME, PG_VIEWS.DEFINITION, 1 FROM PG_VIEWS INNER JOIN PG_CLASS ON PG_VIEWS.VIEWNAME = PG_CLASS.RELNAME
      WHERE SCHEMANAME = SCHEMA_NAME;  
  ELSE
    INSERT INTO T_TEMP_VIEW_SCRIPT 
    SELECT PG_CLASS.OID, PG_VIEWS.SCHEMANAME, PG_VIEWS.VIEWNAME, PG_VIEWS.DEFINITION, 1 FROM PG_VIEWS INNER JOIN PG_CLASS ON PG_VIEWS.VIEWNAME = PG_CLASS.RELNAME;
  END if;

  -- 3、若存在对象名匹配，则更新序号
  UPDATE T_TEMP_VIEW_SCRIPT T_TEMP SET N_ORDER = 1 WHERE EXISTS (SELECT 1 FROM T_TEMP_VIEW_SCRIPT WHERE POSITION(T_TEMP.C_VIEW_NAME IN C_DEFINITION) > 0);
  
  RETURNS QUERY SELECT 'CREATE OR REPLACE VIEW ' || C_SCHEMA_NAME ||'.' || C_VIEW_NAME || ' AS ' || C_DEFINITION FROM T_TEMP_VIEW_SCRIPT  ORDER BY N_ORDER ASC;
END
$$ LANGUAGE PLPGSQL;

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

• 二、调用函数（传入需要备份的schema，为空则不过滤schema）

SELECT "public".fun_view_create_script_backup('db_jspt_znbb');
1

• 三、查询结果（这个结果可以直接拿出来执行的）

select 'CREATE OR REPLACE VIEW ' || c_schema_name ||'.' || c_view_name || ' AS ' || c_definition from "public".T_TEMP_VIEW_SCRIPT  order by n_order asc;
1

初版也能满足现场的基本诉求了，弄完以后告诉现场先用着，我有时间了再弄个升级版出来，然后我就各种忙，一直没时间折腾…

然后近期分公司另一个小伙伴也需要这个脚本，我给了他初版告诉他着急就先用，我这周抽时间给他优化一下（然而两周过去了我还没写出来，哈哈哈！！！这是一个悲伤的故事…）。

2.3、使用系统视图关联关系进行排序（第二版）

牛皮都吹出去了，人家还等着我的优化版本呢，然后这两天就抽时间重新搞了一版，好歹是个自己看得过去的版本了，发出来给大家分享一下，后续有时间再考虑二次优化（是的，初版我自己都看不过去，纯粹是为了实现功能而实现功能）。

• 一、创建生成视图备份脚本的函数

CREATE OR REPLACE FUNCTION "public".fun_view_create_script_backup(SCHEMA_NAME IN VARCHAR(300))
RETURNS setof RECORD AS  
$$
DECLARE
  counter int := 1; -- 当前循环次数计数器
  affected_count int := 0; -- 当前影响行数变量
  result_set record; -- 返回的结果集
BEGIN
  -- 1、处理临时表
  -- 若临时表已存在则先删除
  DROP TABLE IF EXISTS T_TEMP_VIEW_SCRIPT;
  -- 创建临时表（用于缓存视图备份脚本）
  CREATE TEMPORARY TABLE T_TEMP_VIEW_SCRIPT (
    OID OID, 
    C_SCHEMA_NAME VARCHAR(300), 
    C_VIEW_NAME VARCHAR(300), 
    C_DEFINITION TEXT,
    N_ORDER INT 
  );

  -- 2、备份期望参与恢复的视图
  -- SCHEMA_NAME 不为空的话则只过滤对应SCHEMA（存在重复代码，可以使用动态脚本规避，但是我不想弄了；SCHEMA可以弄成多选，我也不想弄，需要的自己折腾吧）
  -- 这个OID大于等于16384是根据网上的资料，从源码里面分析，16384以下的都是预留给系统对象使用的，咱们主要是为了备份用户自定义的对象，所以这里加上这个过滤。这个具体数值在后面的参考资料有
  -- 默认N_ORDER都给1，也就是默认我们认为它是没有依赖，不需要特殊排序的
  IF SCHEMA_NAME <> '' THEN
    INSERT INTO T_TEMP_VIEW_SCRIPT 
    SELECT PG_CLASS.OID, PG_VIEWS.SCHEMANAME, PG_VIEWS.VIEWNAME, PG_VIEWS.DEFINITION, 1 FROM PG_VIEWS INNER JOIN PG_CLASS ON PG_VIEWS.VIEWNAME = PG_CLASS.RELNAME
      WHERE SCHEMANAME = SCHEMA_NAME AND PG_CLASS.OID >= 16384;  
  ELSE
    INSERT INTO T_TEMP_VIEW_SCRIPT 
    SELECT PG_CLASS.OID, PG_VIEWS.SCHEMANAME, PG_VIEWS.VIEWNAME, PG_VIEWS.DEFINITION, 1 FROM PG_VIEWS INNER JOIN PG_CLASS ON PG_VIEWS.VIEWNAME = PG_CLASS.RELNAME
      WHERE PG_CLASS.OID >= 16384;
  END IF;

  -- 3、循环处理N_ORDER（如果发现依赖，则在原序号基础上+1，直至没有任何依赖为止）
  LOOP 
    -- UPDATE连表更新里面的子查询区域是可以拿出来直接执行的，那里面是包含了所有对象之间的依赖关系（去掉select * from T_TEMP_VIEW_SCRIPT那个就是整个库的，咱们这里加上是因为不需要那么多）
    -- 
    UPDATE T_TEMP_VIEW_SCRIPT TEMP_VIEW SET N_ORDER = N_ORDER + 1 
    FROM (SELECT b.refobjid, pc.relname
      FROM pg_depend a,pg_depend b,pg_class pc,pg_rewrite c, (select * from T_TEMP_VIEW_SCRIPT WHERE n_order = counter) d
      WHERE a.refclassid=1259      -- 1259是pg_class的oid
      AND a.classid=2618         -- 2618是pg_rewrite的oid   
      AND b.deptype='i'          -- 内部依赖
      AND a.objid=b.objid
      AND a.classid=b.classid
      AND a.refclassid=b.refclassid
      AND a.refobjid<>b.refobjid
      AND pc.oid=a.refobjid     
      AND c.oid=b.objid
      AND (a.refobjid >= 16384)
      AND d.oid = a.refobjid
      GROUP BY b.refobjid,pc.relname
    ) TEMP_REF
    WHERE N_ORDER = counter AND TEMP_VIEW.OID = TEMP_REF.refobjid;  
  
    -- 获取当前影响行数
    get diagnostics affected_count = row_COUNT;
   
    -- 如果需要打印的话，可以用下面的形式打印
    -- raise notice 'affected_ount: %  counter: %', affected_count, counter;
    
    -- 循环完毕后给计数器递增
    counter := counter + 1;
  -- 如果受影响行数小于1，则代表后续没有再需要更新的数据了，退出循环
  EXIT WHEN affected_count < 1;
  END LOOP;

  -- 4、把结果集格式化成可直接复制执行的，并返回
  FOR result_set IN (SELECT 'CREATE OR REPLACE VIEW ' || C_SCHEMA_NAME ||'.' || C_VIEW_NAME || ' AS ' || C_DEFINITION C_DEF, N_ORDER, C_VIEW_NAME FROM T_TEMP_VIEW_SCRIPT  ORDER BY N_ORDER ASC) LOOP
  RETURN NEXT RESULT_SET;
  END LOOP;
END
$$ LANGUAGE PLPGSQL;

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

• 二、使用函数

准备测试数据

CREATE TABLE T_TABLE_TEMP1(C_ID VARCHAR(32));
CREATE TABLE T_TABLE_TEMP2(C_ID VARCHAR(32));
CREATE TABLE T_TABLE_TEMP3(C_ID VARCHAR(32));
CREATE TABLE T_TABLE_TEMP4(C_ID VARCHAR(32));
CREATE OR REPLACE VIEW V_TABLE_TEMP1 AS SELECT * FROM T_TABLE_TEMP1;
CREATE OR REPLACE VIEW V_TABLE_TEMP3 AS SELECT * FROM T_TABLE_TEMP3;
CREATE OR REPLACE VIEW V_TABLE_TEMP3_1 AS SELECT * FROM V_TABLE_TEMP3;
CREATE OR REPLACE VIEW V_TABLE_TEMP4 AS SELECT * FROM T_TABLE_TEMP4;
CREATE OR REPLACE VIEW V_TABLE_TEMP4_1 AS SELECT * FROM V_TABLE_TEMP4;
CREATE OR REPLACE VIEW V_TABLE_TEMP4_1_1 AS SELECT * FROM V_TABLE_TEMP4_1;
1
2
3
4
5
6
7
8
9
10

执行函数

SELECT * from "public".fun_view_create_script_backup('') t(c_def text, n_order int, c_view_name varchar(300));
1

验证结果

三、补充说明

每种数据库都会提供很多管理元数据的对象（可能是表，可能是视图，也可能是函数或存储过程），我们首先要知道有这么一个概念，然后日常工作中要有意识地去识别有哪些工作可以使用系统对象来提升工作效率，并积极沉淀，这样日积月累我们的知识面及技术水平就会持续不断地提高（别人不会的你会，别人三天干完的活儿你半小时干完了，差距就体现出来了）。

另外这篇文章主要还是根据现场实际需要梳理的，大家如果也有其他相关的场景，可以参考一下这篇文章的实现逻辑，以及后面的参考资料，同时也欢迎一起探讨这几个系统表的关系（说实话我现在对这几个系统表的关系理解的还不是很透彻，只是顺着思路折腾出来了而已）。

greenplum数据库文档（官方说明） https://gp-docs-cn.github.io/docs/ref_guide/system_catalogs/pg_depend.html

华为BaussDB产品文档（跟greenplum及postgresql差异不大，但是注释有所丰富） http://support-it.huawei.com/docs/zh-cn/gaussdb-all/gaussdb200_6.5.1_documentation/zh-cn_topic_0183804428.html

PGSQL规则说明（就是那个pg_rewrite表的一个说明） https://www.jianshu.com/p/5df3e25e8838

PostgreSQL查询视图的依赖关系（目前的版本就是基于这个改造的） https://my.oschina.net/u/4339825/blog/3318797

如何为PostgreSQL创建一个内置函数？（这里面包含上面OID大于等于16384的源码） https://xiaowing.github.io/post/20170903_howto_create_a_postgres_builtin_function/

Pg depend display（PG官方对OID为16384的一个佐证） https://wiki.postgresql.org/wiki/Pg_depend_display