postgresql 集群、数据库、分区表、非分区表大小

集群大小

select current_setting('cluster_name'),
       sum(pg_database_size(a.oid)) as pg_size,
       pg_size_pretty(sum(pg_database_size(a.oid))) as pg_size_pretty 
  from pg_database a 
;

数据库

select current_setting('cluster_name'),
       a.datname,
       pg_database_size(a.oid) as pg_db_size,
       pg_size_pretty(pg_database_size(a.oid)) as pg_db_size_pretty 
  from pg_database a 
order by 2 desc;

分区表

with tmp_t0 as (
  select pn1.nspname as parent_nspname,pc1.oid as parent_reloid,pc1.relname as parent_relname,
         pn2.nspname as nspname,pc2.oid as reloid,pc2.relname as relname
    from ( select t0.*
             from pg_inherits t0,
                  pg_class t1
             where 1=1
               and t0.inhparent = t1.oid
               and t1.relkind in ('p')
             ) pin
         left outer join pg_class pc1
                         on pin.inhparent = pc1.oid
         left outer join pg_namespace  pn1
                         on pc1.relnamespace = pn1.oid
         left outer join pg_class pc2
                         on pin.inhrelid = pc2.oid
         left outer join pg_namespace  pn2
                         on pc2.relnamespace = pn2.oid
    order by pn1.nspname ,pc1.relname                

)
select current_database() as database_name,
       '分区表' as table_type,
       tt.parent_nspname,
       tt.parent_relname,
       sum(pg_relation_size(tt.reloid)) as relation_size,
       pg_size_pretty(sum(pg_relation_size(tt.reloid))) as relation_size_pretty,
       sum(pg_total_relation_size(tt.reloid)) as total_relation_size,
       pg_size_pretty(sum(pg_total_relation_size(tt.reloid))) as total_relation_size_pretty
  from tmp_t0 tt
where 1=1
and tt.parent_nspname not in (
          'information_schema',
          'pg_catalog',
          'pg_temp_1',
          'pg_toast',
          'pg_toast_temp_1'
       )
group by tt.parent_nspname,tt.parent_relname
order by tt.parent_nspname,tt.parent_relname
;

非分区表

select current_database() as database_name,
       '非分区表' as table_type,
       n.nspname ,
       c.relname ,
       pg_relation_size(c.oid) as relation_size,
       pg_size_pretty(pg_relation_size(c.oid)) as relation_size_pretty,
       pg_total_relation_size(c.oid) as total_relation_size,
       pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size_pretty
  from pg_class c
       left join pg_namespace n  on n.oid=c.relnamespace
       left join pg_tablespace t on t.oid=c.reltablespace 
 where 1=1
   and c.relkind in ('r','m','t','f')
   and c.oid not in (
          select distinct inhparent from pg_inherits union all
          select inhrelid from pg_inherits
       )
   and n.nspname not in (
          'information_schema',
          'pg_catalog',
          'pg_temp_1',
          'pg_toast',
          'pg_toast_temp_1'
       )    
order by n.nspname ,
         pg_relation_size(c.oid) desc
;