pg_relation_filepath:数据对象存储位置
| Name | Return Type | Description |
|---|---|---|
| pg_relation_filepath | text | file path name of specified relation |
创建表空间
postgres=# create tablespace tbs_test owner postgres location '/data/pgsql_5432/tbs_test';
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE查看当前instance表空间
postgres=\db
List of tablespaces
Name | Owner | Location
------------+----------+---------------------------
pg_default | postgres |
pg_global | postgres |
tbs_test | postgres | /data/pgsql_5432/tbs_test
(3 rows)查看instance database
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+----------+----------+---------+-------+-----------------------
jingjing01 | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
切换 jingjing01 库进行进行创建table t4
postgres=# \c jingjing01
You are now connected to database "jingjing01" as user "postgres".
jingjing01=#create table t4(id int) tablespace tbs_test;查看t4的物理文件存储位置
jingjing01=# select * from pg_relation_filepath('t4');
pg_relation_filepath
---------------------------------------------
pg_tblspc/16400/PG_11_201809051/16385/16401关于pg_tblspc/16400/PG_11_201809051/16385/16401 解释
pg_tblspc --表空间目录,这里其实是一个软连接
[root@172-16-8-112 pg_tblspc]# ll
total 0
lrwxrwxrwx 1 postgres postgres 25 Mar 12 17:35 16400 -> /data/pgsql_5432/tbs_test
16400 --表空间oid
jingjing01=# select oid from pg_tablespace where spcname ='tbs_test';
oid
-------
16400
(1 row)
PG_11_201809051 --数据库版本
16385 --当前连接的数据库oid
jingjing01=# select oid from pg_database where datname = 'jingjing01';
oid
-------
16385
(1 row)
16401--表文件的oid
[root@172-16-8-112 16385]# pwd
/data/pgsql_5432/tbs_test/PG_11_201809051/16385
[root@172-16-8-112 16385]# ll
total 0
-rw------- 1 postgres postgres 0 Mar 12 17:36 16401
