资深的PostgreSQL DBA都知道，当我们对PostgreSQL执行一条DDL语句时，PostgreSQL会在pg_class,pg_attribute等系统表执行DML操作，比如执行了CREATE TABLE t1(i int, j int);DDL语句，那么就会在pg_class里插入一条't1'的记录，会在pg_attribute里插入‘i’,'j'还有其他隐藏字段的记录。但是PostgreSQL所做的不仅仅是我们认知的这一点点东西，比如表带有索引，约束，text字段等会往一些表里插入不同的数据。也许你有一些甄别DDL引起的系统表的变化的方法，比如对系统表进行比对，今天给大家带来一种更直观的方法，以SQL的方式展现一条DDL语句对系统表所做的改动。废不多说，直接上货.....
分析方法
有熟悉walminer这个工具的同学，可能了解，walminer可以从wal日志中解析出SQL语句。悄悄的告诉大家PostgreSQL内核中对系统表和对普通表的修改都是一样的接口，都会在wal中产生同样的记录，把walminer的代码轻轻一改，就能解析系统表的变更了，最近我开放了系统表解析的接口，迫不及待跟大家分享一下...
方法演示
正常walminer解析普通表是同样的步骤，只不过在解析之前需要执行select walminer_with_catalog();通知walminer你需要系统表的更改。
-- 数据准备
select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/3D07AC28
(1 row)
postgres=# create table t200( i int, j int);
CREATE TABLE
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/3D08C0E8
(1 row)
-- 执行解析
postgres=# select walminer_with_catalog();
walminer_with_catalog
-----------------------
t
(1 row)
postgres=# select wal2sql('0/3D07AC28'::pg_lsn, '0/3D08C0E8');
NOTICE: Add wal from current pg_wal directory, do not suggest use this way in produce
NOTICE: Switch wal to 00000001000000000000003D on time 2020-08-05 22:04:06.506044+08
wal2sql
---------------------
pg_minerwal success
(1 row)
-- 见证奇迹
-- 系统表字段太多，导致结果不是很美观，但不妨碍其正确性
postgres=# select op_text from walminer_contents;
op_text
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
INSERT INTO pg_catalog.pg_type(oid ,typname ,typnamespace ,typowner ,typlen ,typbyval ,typtype ,typcategory ,typispreferred ,typisdefined ,typdelim ,typrelid ,typelem ,typarra
y ,typinput ,typoutput ,typreceive ,typsend ,typmodin ,typmodout ,typanalyze ,typalign ,typstorage ,typnotnull ,typbasetype ,typtypmod ,typndims ,typcollation ,typdefaultbin ,t
ypdefault ,typacl) VALUES(76134 ,'t200' ,2200 ,10 ,-1 ,false ,'c' ,'C' ,false ,true ,',' ,76132 ,0 ,76133 ,'record_in' ,'record_out' ,'record_recv' ,'record_send' ,'-' ,'-' ,'-
' ,'d' ,'x' ,false ,0 ,-1 ,0 ,0 ,null ,null ,null)
INSERT INTO pg_catalog.pg_depend(classid ,objid ,objsubid ,refclassid ,refobjid ,refobjsubid ,deptype) VALUES(1247 ,76134 ,0 ,1259 ,76132 ,0 ,'i')
INSERT INTO pg_catalog.pg_type(oid ,typname ,typnamespace ,typowner ,typlen ,typbyval ,typtype ,typcategory ,typispreferred ,typisdefined ,typdelim ,typrelid ,typelem ,typarra
y ,typinput ,typoutput ,typreceive ,typsend ,typmodin ,typmodout ,typanalyze ,typalign ,typstorage ,typnotnull ,typbasetype ,typtypmod ,typndims ,typcollation ,typdefaultbin ,t
ypdefault ,typacl) VALUES(76133 ,'_t200' ,2200 ,10 ,-1 ,false ,'b' ,'A' ,false ,true ,',' ,0 ,76134 ,0 ,'array_in' ,'array_out' ,'array_recv' ,'array_send' ,'-' ,'-' ,'array_ty
panalyze' ,'d' ,'x' ,false ,0 ,-1 ,0 ,0 ,null ,null ,null)
INSERT INTO pg_catalog.pg_depend(classid ,objid ,objsubid ,refclassid ,refobjid ,refobjsubid ,deptype) VALUES(1247 ,76133 ,0 ,1247 ,76134 ,0 ,'i')
INSERT INTO pg_catalog.pg_class(oid ,relname ,relnamespace ,reltype ,reloftype ,relowner ,relam ,relfilenode ,reltablespace ,relpages ,reltuples ,relallvisible ,reltoastrelid
,relhasindex ,relisshared ,relpersistence ,relkind ,relnatts ,relchecks ,relhasrules ,relhastriggers ,relhassubclass ,relrowsecurity ,relforcerowsecurity ,relispopulated ,relre
plident ,relispartition ,relrewrite ,relfrozenxid ,relminmxid ,relacl ,reloptions ,relpartbound) VALUES(76132 ,'t200' ,2200 ,76134 ,0 ,10 ,2 ,76132 ,0 ,0 ,0 ,0 ,0 ,false ,false
,'p' ,'r' ,2 ,0 ,false ,false ,false ,false ,false ,true ,'d' ,false ,0 ,'2987' ,'1' ,null ,null ,null)
INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'i' ,23
,-1 ,4 ,1 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,false ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'j' ,23
,-1 ,4 ,2 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,false ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'ctid'
,27 ,0 ,6 ,-1 ,0 ,-1 ,-1 ,false ,'p' ,'s' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'xmin'
,28 ,0 ,4 ,-2 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'cmin'
,29 ,0 ,4 ,-3 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'xmax'
,28 ,0 ,4 ,-4 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'cmax'
,29 ,0 ,4 ,-5 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'tableo
id' ,26 ,0 ,4 ,-6 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
INSERT INTO pg_catalog.pg_depend(classid ,objid ,objsubid ,refclassid ,refobjid ,refobjsubid ,deptype) VALUES(1259 ,76132 ,0 ,2615 ,2200 ,0 ,'n')
(14 rows)
一个例子不过瘾，再来看一下比较清晰明了的例子
-- 数据准备
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/3D08CA38
(1 row)
postgres=# alter table t200 drop column j;
ALTER TABLE
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/3D08F010
(1 row)
-- 执行解析
postgres=# select walminer_with_catalog();
walminer_with_catalog
-----------------------
t
(1 row)
postgres=# select wal2sql('0/3D08CA38'::pg_lsn,'0/3D08F010'::pg_lsn);
NOTICE: Add wal from current pg_wal directory, do not suggest use this way in produce
NOTICE: Switch wal to 00000001000000000000003D on time 2020-08-05 22:28:31.345227+08
wal2sql
---------------------
pg_minerwal success
(1 row)
-- 见证奇迹
postgres=# select op_text from walminer_contents;
op_text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------
UPDATE pg_catalog.pg_attribute
SET attrelid=76132, attname='........pg.dropped.2........', atttypid=0, attstattarget=0, attlen=4, attnum=2, attndims=0, attcacheoff=-1, atttypmod=-1, attbyval=true, attstorage='p', attalign='i', attnotnull=false, atthasdef=false, atthasmissing=false, attidentity='', attgenerated='', attisdropped=true, attislocal=true, attinhcount=0, attcollation=0, attacl=null, attoptions=null, attfdwoptions=null, attmissingval=null
WHERE attrelid=76132 AND attname='j' AND atttypid=23 AND attstattarget=-1 AND attlen=4 AND attnum=2 AND attndims=0 AND attcacheoff=-1 AND atttypmod=-1 AND attbyval=true AND attstorage='p' AND attalign='i' AND attnotnull=false AND atthasdef=false AND atthasmissing=false AND attidentity='' AND attgenerated='' AND attisdropped=false AND attislocal=true AND attinhcount=0 AND attcollation=0 AND attacl=null AND attoptions=null AND attfdwoptions=null AND attmissingval=null
(1 row)
postgres=#
引申
每一个DDL都对应者一组对系统表的DML的集合，理论上walminer可以完成直接输出DDL语句的工作。但是DDL语句真是太多了，这会是一个庞大的工作量。我准备从最基础的DDL语句入手，逐步完善walminer工具对DDL的解析，大家可以关注walminer项目不迷路，及时了解项目开发动态。
