循序渐进 openGauss : pg_relation_filepath 获取表文件的具体位置

在 openGauss 中，一个数据表会对应到一个或者多个系统文件中，可以通过 pg_class 字典表来获取表对象的层级信息：

postgres=> select oid,relfilenode,relname from pg_class where relname='userlist';
  oid  | relfilenode | relname  
-------+-------------+----------
 16394 |       16400 | userlist
(1 row)

还可以通过 pg_relation_filepath 函数，来获取表文件路径信息。pg_relation_filepath() 类似于pg_relation_filenode()，返回对象的整个文件路径名。

postgres=> select pg_relation_filepath('userlist');
 pg_relation_filepath 
----------------------
 base/13888/16400
(1 row)

进一步的信息获取：

postgres=> SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'userlist';
 pg_relation_filepath | relpages 
----------------------+----------
 base/13888/16400     |     1060
(1 row)

通过 pg_class 和 pg_namespace 结合获得更详细的输出：

postgres=> SELECT pg_relation_filepath(c.oid) filepath,
postgres->        nspname, 
postgres->        relname, 
postgres->        relnamespace, 
postgres->        reltablespace, 
postgres->        (relpages * 8) / 1024 size_mb, 
postgres->        sum((relpages * 8) / 1024) over (partition by relnamespace) ns_total_size_mb,
postgres->        sum((relpages * 8) / 1024) over (partition by reltablespace) ts_total_size_mb,
postgres->        sum((relpages * 8) / 1024) over (partition by 'x') total_size_mb,
postgres->        sum((relpages * 8) / 1024) over (partition by 'x'order by relpages desc) running_total_size_mb
postgres-> FROM pg_class c
postgres-> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
postgres-> where nspname ='gaussdb';
     filepath     | nspname |   relname   | relnamespace | reltablespace | size_mb | ns_total_size_mb | ts_total_size_mb | total_size_mb | running
_total_size_mb 
------------------+---------+-------------+--------------+---------------+---------+------------------+------------------+---------------+--------
---------------
 base/13888/16400 | gaussdb | userlist    |        16387 |             0 | 8.28125 |          8.28125 |          8.28125 |       8.28125 |        
       8.28125
                  | gaussdb | dba_objects |        16387 |             0 |       0 |          8.28125 |          8.28125 |       8.28125 |        
       8.28125
(2 rows)

以上测试来自：

postgres=> select version();
                                                                                   version                                                        
                           
--------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
 PostgreSQL 9.2.4 (openGauss 1.0.0 build 38a9312a) compiled at 2020-05-27 14:57:08 commit 472 last mr 549  on aarch64-unknown-linux-gnu, compiled 
by g++ (GCC) 8.2.0, 64-bit