PostgreSQL根据表名查询出服务器上的数据文件

创建测试数据

默认表空间示例数据

psql -U postgres -c "create user hr with password 'hr';"
psql -U postgres -c "create database hrdb owner hr;"
psql -U hr -d hrdb -c "create schema authorization hr;"
psql -U hr -d hrdb

-- 创建测试表
create table employees
    ( employee_id    int8    primary key
    , first_name     varchar(20)
    , last_name      varchar(25) constraint     emp_last_name_nn  not null
    , email          varchar(25) constraint     emp_email_nn  not null
    , phone_number   varchar(20)
    , hire_date      date         constraint     emp_hire_date_nn  not null
    , job_id         int8         constraint     emp_job_nn  not null
    , salary         numeric(8,2)
    , commission_pct numeric(4,2)
    , manager_id     int8
    , department_id  int8
    , constraint     emp_salary_min check (salary > 0)
    ) ;
-- 生成测试数据
insert into employees
select generate_series(1,10000) as key,
       substr(md5(random()::text),2,5),
       substr(md5(random()::text),2,8),
	   substr(md5(random()::text),2,5)||'@163.com',
	   concat('1',ceiling(random()*9000000000+1000000000)),
	   date((random()*(2022-1990)+1990)::int||'-'||(random()*(12-1)+1)::int||'-'||(random()*(28-1)+1)::int),
	   (random()*(50-10)+10)::int,
	   (random()*(10000-3000)+3000)::numeric(8,2),
	   (random()*(1-0)+0)::numeric(4,2),
	   (random()*(100-1)+1)::int,
	   (random()*(10-1)+1)::int;

自定义表空间示例数据

[postgres@centos7 ~]$ psql
psql (12.17)
Type "help" for help.

postgres=# show data_directory;
   data_directory   
--------------------
 /postgresql/pgdata
(1 row)

postgres=# \! mkdir -p /postgresql/test_ts

postgres=# create user test with password 'test';
CREATE ROLE
postgres=# create tablespace test_ts owner test location '/postgresql/test_ts';
CREATE TABLESPACE
postgres=# create database testdb owner test tablespace test_ts;
CREATE DATABASE
[postgres@centos7 ~]$ psql -U test -d testdb -c "create schema authorization test;"
CREATE SCHEMA

-- 连接数据库创建表并插入数据
psql -U test -d testdb
create table test_t (id int, info text, c_time timestamp);
insert into test_t select generate_series(1,100000),md5(random()::text),clock_timestamp();
create table test_t1 (id int, info text, c_time timestamp) tablespace test_ts;
insert into test_t1 select generate_series(1,100000),md5(random()::text),clock_timestamp();

 如上testdb数据库test模式下创建的test_t表没有显式指定表空间,而test_t1表显式指定了自定义表空间 

在pg_class表的reltablespace字段存储的是表空间信息,如下

[postgres@centos7 ~]$ psql -d testdb
psql (12.17)
Type "help" for help.

testdb=# select reltablespace,current_database(),relnamespace::regnamespace,relname,oid,relfilenode from pg_class where relname ~ 'test_t';
 reltablespace | current_database | relnamespace | relname |  oid  | relfilenode 
---------------+------------------+--------------+---------+-------+-------------
             0 | testdb           | test         | test_t  | 24734 |       24734
             0 | testdb           | test         | test_t1 | 24740 |       24740
(2 rows)

如上这里为0,说明pg_class.reltablespace字段不可用,要获取表所在的表空间还是得使用pg_database.dattablespace字段

参考官方文档数据字典:

http://postgres.cn/docs/12/catalog-pg-class.html

http://postgres.cn/docs/12/catalog-pg-database.html

根据表名查询数据文件

使用的SQL:

with db_info as (
select oid,datname,dattablespace from pg_database where datname = current_database()
),
ts_info as (
select t1.oid,t1.spcname from pg_tablespace t1 join db_info t2 on t1.oid = t2.dattablespace
),
dn as (
select setting from pg_settings where name = 'data_directory'
)
select 
    concat('tablespace:',ts_info.spcname,' | ','tablespaceoid: ',ts_info.oid) as tablespace_info,
    concat('dbname:',db_info.datname,' | ','dboid: ',db_info.oid) as database_info,
    concat('schema: ',t.relnamespace::regnamespace::text,' | ','schemaoid: ',relnamespace) as schema_info,
    t.oid as table_oid,t.relfilenode as table_relfilenode,
	concat(dn.setting,'/',pg_relation_filepath(t.oid)) as file_path,
	t.relname as tablename
from pg_class t,db_info,dn,ts_info
where t.relnamespace::regnamespace::text = '模式名'
    and t.relname = '表名';

默认表空间查询结果

#hr.employees

-[ RECORD 1 ]-----+--------------------------------------------
tablespace_info   | tablespace:pg_default | tablespaceoid: 1663
database_info     | dbname:hrdb | dboid: 24708
schema_info       | schema: hr | schemaoid: 24709
table_oid         | 24726
table_relfilenode | 24726
file_path         | /postgresql/pgdata/base/24708/24726
tablename         | employees

hrdb=# \! ls -l /postgresql/pgdata/base/24708/24726
-rw------- 1 postgres postgres 1261568 Jul  4 20:33 /postgresql/pgdata/base/24708/24726
hrdb=#

自定义表空间查询结果

#test.test_t

-[ RECORD 1 ]-----+---------------------------------------------------------------
tablespace_info   | tablespace:test_ts | tablespaceoid: 24676
database_info     | dbname:testdb | dboid: 24677
schema_info       | schema: test | schemaoid: 24678
table_oid         | 24734
table_relfilenode | 24734
file_path         | /postgresql/pgdata/pg_tblspc/24676/PG_12_201909212/24677/24734
tablename         | test_t

testdb=# \! ls -l /postgresql/pgdata/pg_tblspc/24676/PG_12_201909212/24677/24734
-rw------- 1 postgres postgres 7659520 Jul  4 20:31 /postgresql/pgdata/pg_tblspc/24676/PG_12_201909212/24677/24734
testdb=#

注意:

表的relfilenode值并不总是与各自的oid匹配,表的oid在表创建后是不变的,表的relfilenode值会因为TRUNCATE, REINDEX, CLUSTER等命令而改变,而磁盘上表的数据文件名其实是跟着pg_class.relfilenode变化的

hrdb=# select oid,relfilenode,relname from pg_class where relname = 'employees';
  oid  | relfilenode |  relname  
-------+-------------+-----------
 24726 |       24726 | employees
(1 row)

hrdb=# truncate hr.employees;
TRUNCATE TABLE
hrdb=# select oid,relfilenode,relname from pg_class where relname = 'employees';
  oid  | relfilenode |  relname  
-------+-------------+-----------
 24726 |       24748 | employees
(1 row)

-- 查询表数据文件路径结果
-[ RECORD 1 ]-----+--------------------------------------------
tablespace_info   | tablespace:pg_default | tablespaceoid: 1663
database_info     | dbname:hrdb | dboid: 24708
schema_info       | schema: hr | schemaoid: 24709
table_oid         | 24726
table_relfilenode | 24748
file_path         | /postgresql/pgdata/base/24708/24748
tablename         | employees

hrdb=#
hrdb=# \! ls -l /postgresql/pgdata/base/24708/24748
-rw------- 1 postgres postgres 0 Jul  4 20:41 /postgresql/pgdata/base/24708/24748
hrdb=#
posted @ 2025-07-04 20:46  kahnyao  阅读(21)  评论(0)    收藏  举报