查看相关目录

pg版本 16.9

查看数据目录
ctmd=# SHOW data_directory;
    data_directory     
-----------------------
 /workspace/pgsql/data
(1 row)

查看表路径，以及大小
ctmd=# SELECT 
pg_relation_filepath('test') AS file_path,
pg_size_pretty(pg_total_relation_size('test')) AS size;
    file_path     |  size   
------------------+---------
 base/16384/40960 | 2833 MB
(1 row)

ctmd=#          
ctmd=# SELECT 
  schemaname || '.' || relname AS table_name,
  pg_relation_filepath(relid) AS file_path,
  pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables;
    table_name     |                  file_path                  |  size   
-------------------+---------------------------------------------+---------
 public.foo        | pg_tblspc/24578/PG_16_202307071/16384/24579 | 109 MB
 public.test       | base/16384/40960                            | 2833 MB
 public.test_unlog | base/16384/40967                            | 0 bytes
(3 rows)


ctmd=#  
ctmd=# select oid,spcname,spcowner,pg_tablespace_location(oid) FROM pg_tablespace;
  oid  |  spcname   | spcowner | pg_tablespace_location 
-------+------------+----------+------------------------
  1663 | pg_default |       10 | 
  1664 | pg_global  |       10 | 
 24578 | fastspace  |       10 | /workspace/pgsql/data
(3 rows)

ctmd=# 
根据db查询库名
ctmd=# SELECT oid FROM pg_database WHERE datname='ctmd';
  oid  
-------
 16384
(1 row)
ctmd=# SELECT 
  (SELECT setting FROM pg_settings WHERE name='data_directory') || 
  '/base/' || oid AS path 
FROM pg_database 
WHERE datname = 'ctmd';
               path               
----------------------------------
 /workspace/pgsql/data/base/16384
(1 row)