原作者：戴秋龙

• 背景
• 问题概述
• 解决方案
• 步骤
  • 1.测试环境生成模型元数据
  • 2.现场(远程)元数据入库
  • 3.验证字段类型不统一
  • 4.验证缺失字段
  • 5.验证缺失表
  • 6.通过差异对比生成差异拉起脚本
• 参考文档

背景

业务应用上线前尝试“灰度发布”。 经过一系列封板，发包。在调试时出现异常，开发经过排查发现是测试库和现场(远程)库表结构不一致导致的异常。测试库中导出该表后补发现场。但是遇到同样的异常。 核对后发现数张关键表字段不一致。

问题概述

难点：整库近4000张表，核对的工作量较大。现场（远程）经过半年多的升级，升级脚本记录难以溯源。也不知道哪些脚本错升，漏升。如何保障现场表结构和测试环境一致， 顺利完成“灰度发布”。

解决方案

方案1
从半年多的发包的脚本中整理出脚本，分析出脚本执行顺序，核对脚本内容判断出漏执行的脚本，拉起表结构。
缺点：脚本是属于半结构化数据，半结构化数据对比难度较大。无法预估完成时间。
方案2
从模型元数据下手，转换成结构化数据。 对比结构化数据完成表模型对比

步骤

1.测试环境生成模型元数据

create table  current_tab_columns
as  
select   c.relname tab_name ,  
pn.nspname  tab_schema,   
 td.description  tab_desc, 
 a.attname tab_cloumn, 
SUBSTRING( format_type ( a.atttypid, a.atttypmod ) FROM '.*' )  AS  cloumn_type, 
d.description  cloumn_desc
from   pg_class c
inner join   pg_catalog.pg_namespace pn  on   c.relnamespace  = pn.oid
inner join   pg_catalog.pg_authid  onr  on  onr.oid = c.relowner 
left    join   pg_catalog.pg_description td  on   td.objoid = c.oid  and td.objsubid =0 
left   join    pg_catalog.pg_attribute   a  on  a.attnum >0  and  a.attrelid = c.oid 
left   join    pg_description d     on  d.objoid = a.attrelid  and d.objsubid = a.attnum
left   join    pg_type t    on  a.atttypid = t.oid
where  c.relkind  in ('r', 'v', 'm', 'p')
 and  pn.nspname  not   in ('power_sch_bak', 'power_tech_datatrans' ,'pg_catalog' , 'public','information_schema')   
order by  pn.nspname,  c.relname,   a.attnum ; 

2.现场(远程)元数据入库

现场配合把元数据导出execl文件，结合kettle导入到数据库中。

3.验证字段类型不统一

with taba as (
select   home_tab.tab_schema,  home_tab.tab_name, home_tab.tab_cloumn  , home_tab.cloumn_type  ,
hn_tab.tab_schema  htab_schema,  hn_tab.tab_name htab_name, hn_tab.tab_cloumn   htab_cloumn, hn_tab.cloumn_type hcloumn_type
from  current_tab   home_tab 
left join    hn_tab   
  on  home_tab.tab_name  =  hn_tab.tab_name  and  home_tab.tab_schema = hn_tab.tab_schema  
	and  home_tab.tab_cloumn = hn_tab.tab_cloumn    
	order by    home_tab.tab_schema,  home_tab.tab_name, home_tab.tab_cloumn
)  select * from  taba where   htab_name  is not null   and   htab_cloumn is not null   and   cloumn_type != hcloumn_type;

图中可以看到同一张表的字段类型不一致。

4.验证缺失字段

with nottab as   
 (
  select  distinct   home_tab.tab_schema,  home_tab.tab_name from  current_tab home_tab  
  where  (home_tab.tab_schema , tab_name)  not in  ( select       tab_schema,   tab_name from  hn_tab )
)
 , clm_chk_tab  as ( 
 select   distinct   home_tab.tab_schema,  home_tab.tab_name   from  current_tab home_tab  
 where  (home_tab.tab_schema , tab_name, tab_cloumn)  not in  ( select       tab_schema,   tab_name, tab_cloumn from  hn_tab )--- 家里多的字段去掉
 and (home_tab.tab_schema , tab_name) not in ( select  tab_schema,  tab_name from  nottab)  
 )    
select   home_tab.tab_schema,  home_tab.tab_name, home_tab.tab_cloumn  , home_tab.cloumn_type  ,
hn_tab.tab_schema  htab_schema,  hn_tab.tab_name htab_name, hn_tab.tab_cloumn   htab_cloumn, hn_tab.cloumn_type hcloumn_type
from  current_tab   home_tab 
left join    hn_tab   
  on  home_tab.tab_name  =  hn_tab.tab_name  and  home_tab.tab_schema = hn_tab.tab_schema  
	and  home_tab.tab_cloumn = hn_tab.tab_cloumn  
where   (home_tab.tab_schema , home_tab.tab_name) in ( select   tab_schema,  tab_name from  clm_chk_tab ) 
---- and  hn_tab.tab_cloumn is null 
order by    home_tab.tab_schema,  home_tab.tab_name, home_tab.tab_cloumn

其中对于不上的字段，就是缺少的字段

5.验证缺失表

  select  distinct   home_tab.tab_schema,  home_tab.tab_name from  current_tab home_tab  
 where  (home_tab.tab_schema , tab_name)  not in  ( select       tab_schema,   tab_name from  hn_tab )
 order by 1,2

6.通过差异对比生成差异拉起脚本

核对SQl对比出的差异，生成相应的差异拉起脚本.

参考文档

表结构查询

表结构差异对比