tdsql-pg（tbase）常用脚本-1.txt

forever

181

5页

9次

2024-08-16

免费下载

1、查看主键分片键分区键信息

##查询所有表

with pkxx as(

select n.nspname,

max(nvl((select c1.relname from pg_inherits pi, pg_class c1 where

pi.inhparent = c1.oid and c.oid = pi.inhrelid) ,c.relname)) ftabname,

c.relname tabname,

max(pg_get_partkeydef (c.oid)) partkey,

max((select string_agg(attname , ',' order by a.attnum) from pg_index

i,pg_attribute a,pg_class ic where ic.oid = i.indexrelid

and i.indrelid = c.oid and a.attrelid = i.indexrelid and

i.indisprimary = 't')) pekey,

max((SELECT attname FROM pgxc_class pc, pg_attribute t1 WHERE

pc.pcattnum = t1.attnum AND pc.pcrelid = t1.attrelid and pc.pcrelid

= c.oid))

shardkey,

sum(case when relkind = 'r' then 1 else 0 end) bsl

from pg_class c,

pg_namespace n

where

c.relnamespace = n.oid

and c.relkind in ( 'r','p')

group by

n.nspname,

c.relname

)

select nspname ,ftabname tablename,sum(bsl) bsl,count(pekey) zjsl,max(shardkey)

shardkey,max(pekey) pekey,

max(partkey) partkey,string_agg((case when pekey is null and tabname<>ftabname

then tabname else null end), ',' order by tabname) parts

from pkxx

group by nspname,ftabname;

##查询单张表

with pkxx as(

select n.nspname,

max(nvl((select c1.relname from pg_inherits pi, pg_class c1 where pi.inhparent =

c1.oid and c.oid = pi.inhrelid) ,c.relname)) ftabname,

-- (select case when pi.inhparent::regclass is null then c.relname end from

pg_inherits pi where c.oid = pi.inhrelid) ftabname,

c.relname tabname,

max(pg_get_partkeydef (c.oid)) partkey,

max((select string_agg(attname , ',' order by a.attnum) from pg_index

i,pg_attribute a,pg_class ic where ic.oid = i.indexrelid

and i.indrelid = c.oid and a.attrelid = i.indexrelid and

i.indisprimary = 't')) pekey,

max((SELECT attname FROM pgxc_class pc, pg_attribute t1 WHERE

pc.pcattnum = t1.attnum AND pc.pcrelid = t1.attrelid and pc.pcrelid

= c.oid))

shardkey,

sum(case when relkind = 'r' then 1 else 0 end) bsl,

max(cast(obj_description(relfilenode,'pg_class') as varchar)) as comment

from pg_class c inner join

pg_namespace n on c.relnamespace = n.oid

where c.relkind in ( 'r','p')

group by

n.nspname,

c.relname,c.oid

)

select nspname ,ftabname tablename,max(comment ) 表注释,max(shardkey)

shardkey,max(pekey) pekey,

max(partkey) partkey from pkxx where nspname='' and ftabname=''

group by nspname,ftabname;

2、迁移主表及分区表 schema

##修改主表 sechema

select 'alter table ' ||schemaname||'.'||tablename|| ' set schema new_schema;'

from pg_tables

where schemaname='old_schema' order by tablename ;

##检查是否有主表与分区表不同 schema：

select np.nspname as tableschema,cp.relname as tablename,n.nspname as

partschema,c.relname as partname

,concat('alter table ',n.nspname,'.',c.relname ,' set schema ',np.nspname,';')

from pg_inherits i inner join pg_class c on i.inhrelid=c.oid inner

join pg_namespace n on c.relnamespace=n.oid

inner join pg_class cp on i.inhparent=cp.oid

inner join pg_namespace np on cp.relnamespace=np.oid

where np.nspname!=n.nspname order by 1,2;

##上一步如果查询出有主表与分区表不同 schema，查询出的更改 schema 语句去执行：

select concat('alter table ',n.nspname,'.',c.relname ,' set schema

',np.nspname,';')

from pg_inherits i inner join pg_class c

on i.inhrelid=c.oid

inner join pg_namespace n on c.relnamespace=n.oid

inner join pg_class cp on i.inhparent=cp.oid

inner join pg_namespace np on cp.relnamespace=np.oid

where np.nspname!=n.nspname;

3、查询表的最大分区结束时间及表的分区数量

##查询表的最大分区结束时间及表的分区数量，（配合下面脚本查询跨年时是否生成跨年后的分区）

select a.nspname,b.ftabname, min(fqjssj) 最小分区结束时间, max(fqjssj ) 最大分区结束

时间,count(*) 总分区数

from ( select p1.nspname nspname,p1.relname relname, 20000 attnum,

substr(substr(partdef,strpos(partdef,'=')

+1,100),0,strpos(substr(partdef,strpos(partdef,'=')+1,100),')')) fqkssj,

replace(substr(partdef,strpos(partdef,'<')+1,100),')','') fqjssj from

(select c.oid,n.nspname,

c.relname,pg_get_partition_constraintdef(c.oid) partdef

from pg_class c, pg_namespace n

where c.relkind in('r') and c.relnamespace =

n.oid) p1 ,

(select c.oid,inhparent ,pc.relname from pg_inherits i ,

pg_class c, pg_class pc

where i.inhrelid = c.oid and i.inhparent = pc.oid ) i1

where i1.oid = p1.oid and length(p1.partdef)>0 )a ,(select nspname

,c.relname tabname,nvl(pc.relname,c.relname)

ftabname from pg_class c left join (select ic.relname,ic.oid fid,i.inhrelid sid

from pg_inherits i , pg_class ic

1、查看主键分片键分区键信息

##查询所有表

with pkxx as(

select n.nspname,

max(nvl((select c1.relname from pg_inherits pi, pg_class c1 where

pi.inhparent = c1.oid and c.oid = pi.inhrelid) ,c.relname)) ftabname,

c.relname tabname,

max(pg_get_partkeydef (c.oid)) partkey,

max((select string_agg(attname , ',' order by a.attnum) from pg_index

i,pg_attribute a,pg_class ic where ic.oid = i.indexrelid

and i.indrelid = c.oid and a.attrelid = i.indexrelid and

i.indisprimary = 't')) pekey,

max((SELECT attname FROM pgxc_class pc, pg_attribute t1 WHERE

pc.pcattnum = t1.attnum AND pc.pcrelid = t1.attrelid and pc.pcrelid

= c.oid))

shardkey,

sum(case when relkind = 'r' then 1 else 0 end) bsl

from pg_class c,

pg_namespace n

where

c.relnamespace = n.oid

and c.relkind in ( 'r','p')

group by

n.nspname,

c.relname

)

select nspname ,ftabname tablename,sum(bsl) bsl,count(pekey) zjsl,max(shardkey)

shardkey,max(pekey) pekey,

max(partkey) partkey,string_agg((case when pekey is null and tabname<>ftabname

then tabname else null end), ',' order by tabname) parts

from pkxx

group by nspname,ftabname;

##查询单张表

with pkxx as(

select n.nspname,

max(nvl((select c1.relname from pg_inherits pi, pg_class c1 where pi.inhparent =

c1.oid and c.oid = pi.inhrelid) ,c.relname)) ftabname,

-- (select case when pi.inhparent::regclass is null then c.relname end from

pg_inherits pi where c.oid = pi.inhrelid) ftabname,

c.relname tabname,

max(pg_get_partkeydef (c.oid)) partkey,

max((select string_agg(attname , ',' order by a.attnum) from pg_index

i,pg_attribute a,pg_class ic where ic.oid = i.indexrelid

and i.indrelid = c.oid and a.attrelid = i.indexrelid and

i.indisprimary = 't')) pekey,

max((SELECT attname FROM pgxc_class pc, pg_attribute t1 WHERE

pc.pcattnum = t1.attnum AND pc.pcrelid = t1.attrelid and pc.pcrelid

= c.oid))

shardkey,

sum(case when relkind = 'r' then 1 else 0 end) bsl,

max(cast(obj_description(relfilenode,'pg_class') as varchar)) as comment

from pg_class c inner join

pg_namespace n on c.relnamespace = n.oid

where c.relkind in ( 'r','p')

group by

n.nspname,

c.relname,c.oid

)

select nspname ,ftabname tablename,max(comment ) 表注释,max(shardkey)

shardkey,max(pekey) pekey,

max(partkey) partkey from pkxx where nspname='' and ftabname=''

group by nspname,ftabname;

2、迁移主表及分区表 schema

##修改主表 sechema

select 'alter table ' ||schemaname||'.'||tablename|| ' set schema new_schema;'

from pg_tables

where schemaname='old_schema' order by tablename ;

##检查是否有主表与分区表不同 schema：

select np.nspname as tableschema,cp.relname as tablename,n.nspname as

partschema,c.relname as partname

,concat('alter table ',n.nspname,'.',c.relname ,' set schema ',np.nspname,';')

from pg_inherits i inner join pg_class c on i.inhrelid=c.oid inner

join pg_namespace n on c.relnamespace=n.oid

inner join pg_class cp on i.inhparent=cp.oid

inner join pg_namespace np on cp.relnamespace=np.oid

where np.nspname!=n.nspname order by 1,2;

##上一步如果查询出有主表与分区表不同 schema，查询出的更改 schema 语句去执行：

select concat('alter table ',n.nspname,'.',c.relname ,' set schema

',np.nspname,';')

from pg_inherits i inner join pg_class c

on i.inhrelid=c.oid

inner join pg_namespace n on c.relnamespace=n.oid

inner join pg_class cp on i.inhparent=cp.oid

inner join pg_namespace np on cp.relnamespace=np.oid

where np.nspname!=n.nspname;

3、查询表的最大分区结束时间及表的分区数量

##查询表的最大分区结束时间及表的分区数量，（配合下面脚本查询跨年时是否生成跨年后的分区）

select a.nspname,b.ftabname, min(fqjssj) 最小分区结束时间, max(fqjssj ) 最大分区结束

时间,count(*) 总分区数

from ( select p1.nspname nspname,p1.relname relname, 20000 attnum,

substr(substr(partdef,strpos(partdef,'=')

+1,100),0,strpos(substr(partdef,strpos(partdef,'=')+1,100),')')) fqkssj,

replace(substr(partdef,strpos(partdef,'<')+1,100),')','') fqjssj from

(select c.oid,n.nspname,

c.relname,pg_get_partition_constraintdef(c.oid) partdef

from pg_class c, pg_namespace n

where c.relkind in('r') and c.relnamespace =

n.oid) p1 ,

(select c.oid,inhparent ,pc.relname from pg_inherits i ,

pg_class c, pg_class pc

where i.inhrelid = c.oid and i.inhparent = pc.oid ) i1

where i1.oid = p1.oid and length(p1.partdef)>0 )a ,(select nspname

,c.relname tabname,nvl(pc.relname,c.relname)

ftabname from pg_class c left join (select ic.relname,ic.oid fid,i.inhrelid sid

from pg_inherits i , pg_class ic

of 5

免费下载

关注

评论