eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 循序渐进 openGauss : 通过 openGauss 的 Docker 环境快速入门-墨天轮 | Blog首页 | 中国移动国产OLTP数据库中标公告:南大金仓阿里,万里开源中兴 分获大单-墨天轮 »

循序渐进 openGauss : pg_relation_filepath 获取表文件的具体位置-墨天轮
modb.pro

墨天轮原文链接:https://www.modb.pro/db/27571
在 openGauss 中,一个数据表会对应到一个或者多个系统文件中,可以通过 pg_class 字典表来获取表对象的层级信息。

在 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

历史上的今天...
    >> 2014-07-17文章:
    >> 2013-07-17文章:
    >> 2012-07-17文章:
    >> 2011-07-17文章:
    >> 2008-07-17文章:
    >> 2006-07-17文章:
           Dearbook的新书推介
    >> 2005-07-17文章:
           今天的晚餐-渝信川菜

By enmotech on 2020-07-17 12:17 | Comments (0) | modb.pro | 3401 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com