获取指定表的blocks所在机器的信息

基本信息

系统 : CentOS Linux release 7.4.1708 (Core)
产品 : OushuDB
模块 : DB
子模块 : -

描述详述

xxx
xxx

原因

xxx
xxx

解决方案

with db_path as (
select (fselocation||'/'||b.oid||'/'||d.oid) as path,
  b.oid
from pg_filespace_entry a
join pg_tablespace b
on a.fsefsoid=b.spcfsoid
join pg_database d on d.datname=current_database()   
where a.fsedbid=0
)
 SELECT
 c.oid,c.relfilenode,n.nspname||'.'||c.relname AS tab,
 t.oid tspc,c.reltablespace,e.fsefsoid fspc,
 pg_size_pretty(pg_relation_size(n.nspname||'.'||c.relname)) as "tabSize",
 'hdfs fsck '||d.path||'/'||c.relfilenode ||' -files -blocks -locations' s_code
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t ON t.oid = (case when c.reltablespace=0 then (select oid from pg_tablespace where spcname='dfs_default') else  c.reltablespace end)
   LEFT JOIN pg_filespace_entry e on e.fsefsoid=t.spcfsoid
   LEFT JOIN db_path d on d.oid=(case when c.reltablespace=0 then (select oid from pg_tablespace where spcname='dfs_default') else  c.reltablespace end)
  WHERE c.relkind = 'r'::"char"
        and n.nspname||'.'||c.relname not in
       (select schemaname||'.'||tablename
        from pg_partitions
        group by 1
        )
 and n.nspname||'.'||c.relname='SCHEMA.TABLE' ;