聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

postgresql 检索当前数据库中所有的分区表

2020-05-19 00:24 浏览: 1342 次 我要评论(0 条) 字号:

pg 10由于没有hash分区,而pg_pathman一直都是支持多种分区的。所以如果某些pg 11以前的系统,可能会混合部署pg原生分区和pg_pathman。
要检查这种混合部署环境中的分区情况,可以用下面的sql:

select b.parent::text as part_table,
     'native partition' as part_tool,
CASE WHEN a.partstrat='r' THEN 'range'
     WHEN a.partstrat='l' THEN 'list'
     WHEN a.partstrat='h' THEN 'hash'
     ELSE 'other'
     END as part_type,
     b.cnt as part_cnt
from
pg_partitioned_table a,
(SELECT
    parent.oid,
    parent.relname          AS parent,
    COUNT(*) as cnt
FROM pg_inherits
    JOIN pg_class parent                 ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child                  ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent        ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child         ON nmsp_child.oid   = child.relnamespace
GROUP BY  oid,parent) b
where a.partrelid=b.oid
union all
select
     parent::text,
     'pg_pathman',
CASE WHEN parttype=1 THEN 'hash'
     WHEN parttype=2 THEN 'range'
     ELSE 'other'
END ,
count(*)
from pathman_partition_list
group by parent,parttype

显示结果如下:

dyats=> select version();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
 
      part_table       |    part_tool     | part_type | part_cnt
-----------------------+------------------+-----------+----------
 mytabaa1_kkj_dbddak   | native partition | range     |        7
 kkj_dbddak            | native partition | range     |       59
 drop_sgs_djaygsrf_bak | native partition | range     |        9
 sgs_djaygsrf          | pg_pathman       | hash      |     1024
 mumybaad_sgs_djaygsrf | pg_pathman       | hash      |      128
(5 rows)
 
dyats=>
dyats=>
dyats=>

dyats=>

可以看到有5个partition table,其中3个是native partition,2个是用来pg_pathman,并且还可以看到分区的类型,和分区数量。



网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复