« 循序渐进 openGauss : 通过 openGauss 的 Docker 环境快速入门-墨天轮 | Blog首页 | 中国移动国产OLTP数据库中标公告:南大金仓阿里,万里开源中兴分获大单 »
循序渐进 openGauss : pg_relation_filepath 获取表文件的具体位置-墨天轮
作者:enmotech | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2020/07/_opengauss_pg_r.html
链接:https://www.eygle.com/archives/2020/07/_opengauss_pg_r.html
墨天轮原文链接: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文章:
>> 2005-07-17文章:
By enmotech on 2020-07-17 12:17 | Comments (0) | modb.pro | 3401 |