PostgreSQL系統函數：pg_relation_filepath

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