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=#
浙公网安备 33010602011771号