【PostgreSQL】PostgreSQL使用dblink来比对两个数据库之间的ddl差异

---

引言

我们经常会遇到两个环境的ddl要拉齐，比如开发环境（dev）和生产环境（pro），下面我们可以通过PostgreSQL的dblink插件来实现。

一、创建视图tab_info_tzq_dev

create view tab_info_tzq_dev as 
select * 
  from dblink('host=IP地址 dbname=数据库名 port=5432 user=用户名 password=密码',
'SELECT n.nspname AS schemaname
          ,c.relname AS tablename
          ,(col_description(c.oid, 0))::character varying AS table_comment
          ,a.attnum AS colnum
          ,a.attname AS columnname
          ,concat_ws('''', t.typname) AS columntype
          ,(CASE
             WHEN a.attlen > 0 THEN
               a.attlen
             WHEN t.typname = ''bit'' THEN
               a.atttypmod
             ELSE
               a.atttypmod - 4
           END) AS columntlength
          ,CASE
            WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = ''bigint''::text) THEN ''INT8''::character varying
            WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = ''smallint''::text) THEN ''INT2''::character varying
            WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = ''integer''::text) THEN ''INT4''::character varying
            WHEN ("left"(((format_type(a.atttypid, a.atttypmod))::character varying)::text, 17) = ''character varying''::text) THEN (replace(((format_type(a.atttypid, a.atttypmod))::character varying)::text, ''character varying''::text, ''VARCHAR''::text))::character varying
            WHEN ("left"(((format_type(a.atttypid, a.atttypmod))::character varying)::text, 9) = ''character''::text) THEN (replace(((format_type(a.atttypid, a.atttypmod))::character varying)::text, ''character''::text, ''char''::text))::character varying
            ELSE (format_type(a.atttypid, a.atttypmod))::character varying
        END AS typelen
          ,(CASE
             WHEN a.attnotnull = TRUE THEN
               ''NOT NULL''
             ELSE
               NULL
           END) AS is_null
          ,(CASE
             WHEN (SELECT COUNT(pg_constraint.*)
                     FROM pg_constraint
                    INNER JOIN pg_class
                       ON pg_constraint.conrelid = pg_class.oid
                    INNER JOIN pg_attribute
                       ON pg_attribute.attrelid = pg_class.oid
                      AND pg_attribute.attnum = ANY(pg_constraint.conkey)
                    INNER JOIN pg_type
                       ON pg_type.oid = pg_attribute.atttypid
                    WHERE pg_class.relname = c.relname
                      AND pg_constraint.contype = ''p''
                      AND pg_attribute.attname = a.attname) > 0 THEN
               ''Y''
             ELSE
               NULL
           END) AS pkey
          ,col.is_identity AS auto_increment
          ,col.column_default AS column_default
          ,(SELECT description
              FROM pg_description
             WHERE objoid = a.attrelid
               AND objsubid = a.attnum) AS column_comment
      FROM pg_class                   c
          ,pg_attribute               a
          ,pg_type                    t
          ,information_schema.columns AS col
          ,pg_namespace               n
     WHERE 1 = 1
       AND a.attnum > 0
       AND a.attrelid = c.oid
       AND a.atttypid = t.oid
       AND col.table_name = c.relname
       AND col.column_name = a.attname
       AND col.table_schema = CURRENT_SCHEMA
       AND n.oid = c.relnamespace
       AND n.nspname = CURRENT_SCHEMA
			 and c.relname not like ''act\_%''
       and (c.relname like ''%\_t'' OR c.relname like ''%\_ti'' OR c.relname like ''%\_tmp'')
     ORDER BY c.relname ASC
             ,a.attnum  ASC
')
as t(
  schemaname varchar(64),
	tablename varchar(64),
	table_comment varchar(4000),
	colnum int8,
	columnname varchar(64),
	columntype varchar(256),
	columntlength int8,
	typelen varchar(256),
	is_null varchar(256),
	pkey varchar(256),
	auto_increment varchar(256),
	column_default varchar(256),
	column_comment varchar(4000)
);

查询：

select * from tab_info_tzq_dev;

二、创建视图tab_info_tzq_pro

create view tab_info_tzq_pro as 
select * 
  from dblink('host=IP地址 dbname=数据库名 port=5432 user=用户名 password=密码',
'SELECT n.nspname AS schemaname
          ,c.relname AS tablename
          ,(col_description(c.oid, 0))::character varying AS table_comment
          ,a.attnum AS colnum
          ,a.attname AS columnname
          ,concat_ws('''', t.typname) AS columntype
          ,(CASE
             WHEN a.attlen > 0 THEN
               a.attlen
             WHEN t.typname = ''bit'' THEN
               a.atttypmod
             ELSE
               a.atttypmod - 4
           END) AS columntlength
          ,CASE
            WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = ''bigint''::text) THEN ''INT8''::character varying
            WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = ''smallint''::text) THEN ''INT2''::character varying
            WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = ''integer''::text) THEN ''INT4''::character varying
            WHEN ("left"(((format_type(a.atttypid, a.atttypmod))::character varying)::text, 17) = ''character varying''::text) THEN (replace(((format_type(a.atttypid, a.atttypmod))::character varying)::text, ''character varying''::text, ''VARCHAR''::text))::character varying
            WHEN ("left"(((format_type(a.atttypid, a.atttypmod))::character varying)::text, 9) = ''character''::text) THEN (replace(((format_type(a.atttypid, a.atttypmod))::character varying)::text, ''character''::text, ''char''::text))::character varying
            ELSE (format_type(a.atttypid, a.atttypmod))::character varying
        END AS typelen
          ,(CASE
             WHEN a.attnotnull = TRUE THEN
               ''NOT NULL''
             ELSE
               NULL
           END) AS is_null
          ,(CASE
             WHEN (SELECT COUNT(pg_constraint.*)
                     FROM pg_constraint
                    INNER JOIN pg_class
                       ON pg_constraint.conrelid = pg_class.oid
                    INNER JOIN pg_attribute
                       ON pg_attribute.attrelid = pg_class.oid
                      AND pg_attribute.attnum = ANY(pg_constraint.conkey)
                    INNER JOIN pg_type
                       ON pg_type.oid = pg_attribute.atttypid
                    WHERE pg_class.relname = c.relname
                      AND pg_constraint.contype = ''p''
                      AND pg_attribute.attname = a.attname) > 0 THEN
               ''Y''
             ELSE
               NULL
           END) AS pkey
          ,col.is_identity AS auto_increment
          ,col.column_default AS column_default
          ,(SELECT description
              FROM pg_description
             WHERE objoid = a.attrelid
               AND objsubid = a.attnum) AS column_comment
      FROM pg_class                   c
          ,pg_attribute               a
          ,pg_type                    t
          ,information_schema.columns AS col
          ,pg_namespace               n
     WHERE 1 = 1
       AND a.attnum > 0
       AND a.attrelid = c.oid
       AND a.atttypid = t.oid
       AND col.table_name = c.relname
       AND col.column_name = a.attname
       AND col.table_schema = CURRENT_SCHEMA
       AND n.oid = c.relnamespace
       AND n.nspname = CURRENT_SCHEMA
			 and c.relname not like ''act\_%''
       and (c.relname like ''%\_t'' OR c.relname like ''%\_ti'' OR c.relname like ''%\_tmp'')
     ORDER BY c.relname ASC
             ,a.attnum  ASC
')
as t(
  schemaname varchar(64),
	tablename varchar(64),
	table_comment varchar(4000),
	colnum int8,
	columnname varchar(64),
	columntype varchar(256),
	columntlength int8,
	typelen varchar(256),
	is_null varchar(256),
	pkey varchar(256),
	auto_increment varchar(256),
	column_default varchar(256),
	column_comment varchar(4000)
);

查询：

select * from tab_info_tzq_pro;

三、比较差异

select 'DEV' AS source,t.* from tab_info_tzq_dev t
EXCEPT
select 'DEV' AS source,t.* from tab_info_tzq_pro t
union all
select 'PRO' AS source,t.* from tab_info_tzq_pro t
EXCEPT
select 'PRO' AS source,t.* from tab_info_tzq_dev t
ORDER BY tablename, columnname;

四、差异处理

根据比较差异得出的结果，能查到结果说明就有问题。依次进行处理，处理完之后查不到差异结果了才对。