PostgreSQL 中的表空間允許在檔案系統中定義用來存放表示資料庫物件的檔案的位置。在 PostgreSQL 中表空間實際上就是給表指定一個儲存目錄。

作用

透過使用表空間，管理員可以控制一個 PostgreSQL 安裝的磁碟佈局。這麼做至少有兩個用處。

1. 如果初始化集簇所在的分割槽或者卷用光了空間，而又不能在邏輯上擴充套件或者做別的什麼操作，那麼表空間可以被建立在一個不同的分割槽上，直到系統可以被重新配置。

2. 表空間允許管理員根據資料庫物件的使用模式來最佳化效能。例如，一個很頻繁使用的索引可以被放在非常快並且非常可靠的磁碟上，如一種非常貴的固態裝置。同時，一個很少使用的或者對效能要求不高的儲存歸檔資料的表可以儲存在一個便宜但比較慢的磁碟系統上。

表空間跟資料庫的關係

• 在 Oracle 資料庫中；一個表空間只屬於一個資料庫使用；而一個資料庫可以擁有多個表空間。屬於"一對多"的關係

• 在 PostgreSQL 叢集中；一個表空間可以讓多個資料庫使用；而一個資料庫可以使用多個表空間。屬於"多對多"的關係。

系統表空間

• 表空間 pg_default 是用來儲存系統目錄物件、使用者表、使用者表index、和臨時表、臨時表index、內部臨時表的預設空間。對應儲存目錄$PADATA/base/

• 表空間pg_global用來存放系統字典表；對應儲存目錄$PADATA/global/

檢視錶空間

列出現有的表空間

postgres=# \db
             List of tablespaces
    Name    |  Owner   |      Location       
------------+----------+---------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tp_lottu   | lottu    | /data/pg_data/lottu
(3 rows)
postgres=# select oid,* from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions 
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        | 
  1664 | pg_global  |       10 |        | 
 16385 | tp_lottu   |    16384 |        | 
(3 rows)

建立表空間

語法

CREATE TABLESPACE tablespace_name [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ] LOCATION 'directory';

示例

postgres=# \c lottu postgres
You are now connected to database "lottu" as user "postgres".
lottu=# CREATE TABLESPACE tsp01 OWNER lottu LOCATION '/data/pg_data/tsp';
CREATE TABLESPACE

目錄"/data/pg_data/tsp"必須是一個已有的空目錄，並且屬於 PostgreSQL 作業系統使用者

$ mkdir -p /data/pg_data/tsp
$ chown -R postgres:postgres /data/pg_data/tsp

許可權分配

表空間的建立本身必須作為一個資料庫超級使用者完成，但在建立完之後之後你可以允許普通資料庫使用者來使用它.要這樣做，給資料庫普通使用者授予表空間上的CREATE許可權。表、索引和整個資料庫都可以被分配到特定的表空間.

示例使用者"rax":為普通使用者。

lottu=# \c lottu01 rax
You are now connected to database "lottu01" as user "rax".
lottu01=> create table test_tsp(id int) tablespace tsp01;
ERROR:  permission denied for tablespace tsp01
lottu01=> \c lottu01 postgres
You are now connected to database "lottu01" as user "postgres".
lottu01=# GRANT CREATE ON TABLESPACE tsp01 TO rax;
GRANT
lottu01=# \c lottu01 rax
You are now connected to database "lottu01" as user "rax".
lottu01=> create table test_tsp(id int) tablespace tsp01;
CREATE TABLE

為資料庫指定預設表空間

語法

ALTER DATABASE name SET TABLESPACE new_tablespace

示例

以資料庫lottu01為例:

ALTER DATABASE lottu01 SET TABLESPACE tsp01;
lottu01=> \c lottu01 lottu
You are now connected to database "lottu01" as user "lottu".

注意

注意1：執行該操作；不能連著對應資料庫操作

lottu01=# ALTER DATABASE lottu01 SET TABLESPACE tsp01;
ERROR:  cannot change the tablespace of the currently open database
lottu01=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".

注意2：執行該操作；對應的資料庫不能存在表或者索引已經指定預設的表空間

postgres=# ALTER DATABASE lottu01 SET TABLESPACE tsp01;
ERROR:  some relations of database "lottu01" are already in tablespace "tsp01"
HINT:  You must move them back to the database's default tablespace before using this command.
postgres=# \c lottu01
You are now connected to database "lottu01" as user "postgres".
lottu01=# drop table test_tsp ;
DROP TABLE
lottu01=# create table test_tsp(id int);
CREATE TABLE
lottu01=# \c postgres postgres 
You are now connected to database "postgres" as user "postgres".

注意3：執行該操作；必須是沒有人連著對應的資料庫

postgres=# ALTER DATABASE lottu01 SET TABLESPACE tsp01;
ERROR:  database "lottu01" is being accessed by other users
DETAIL:  There is 1 other session using the database.
postgres=# ALTER DATABASE lottu01 SET TABLESPACE tsp01;
ALTER DATABASE

檢視資料庫預設表空間

lottu01=# select d.datname,p.spcname from pg_database d, pg_tablespace p where d.datname='lottu01' and p.oid = d.dattablespace;
 datname | spcname 
---------+---------
 lottu01 | tsp01
(1 row)

移動表空間

我們知道表空間pg_default是用來儲存系統目錄物件、使用者表、使用者表index、和臨時表、臨時表index、內部臨時表的預設空間。若沒指定預設表空間；表就所屬的表空間就是pg_default。"當然也可以透過引數設定"。而不是資料庫預設的表空間。這個時候我們可以將表移到預設的表空間

語法

ALTER TABLE name SET TABLESPACE new_tablespace;

將表從一個表空間移到另一個表空間

lottu01=# create table test_tsp03(id int) tablespace tp_lottu;
CREATE TABLE
lottu01=# alter table test_tsp03 set tablespace tsp01;
ALTER TABLE

注意：該操作時會鎖表。

臨時表空間

PostgreSQL 的臨時表空間，透過引數 temp_tablespaces 進行配置，PostgreSQL 允許使用者配置多個臨時表空間。配置多個臨時表空間時，使用逗號隔開。如果沒有配置 temp_tablespaces 引數，臨時表空間對應的是預設的表空間 pg_default。PostgreSQL 的臨時表空間用來儲存臨時表或臨時表的索引，以及執行SQL時可能產生的臨時檔案例如排序，聚合，雜湊等。為了提高效能，一般建議將臨時表空間放在 SSD 或者 IOPS，以及吞吐量較高的分割槽中。

$ mkdir -p /data/pg_data/temp_tsp
$ chown -R postgres:postgres /data/pg_data/temp_tsp
postgres=# CREATE TABLESPACE temp01 LOCATION '/data/pg_data/temp_tsp';
CREATE TABLESPACE
postgres=# show temp_tablespaces ;
 temp_tablespaces 
------------------
(1 row)

設定臨時表空間

• 會話級生效

  set temp_tablespaces = 'temp01'

• 永久生效

1. 修改引數檔案postgresql.conf

2. 執行pg_ctl reload

   [postgres@Postgres201 data]$ grep "temp_tablespace" postgresql.conf
   temp_tablespaces = 'temp01'     # a list of tablespace names, '' uses

postgres=# show temp_tablespaces ;
 temp_tablespaces 
------------------
 temp01
(1 row)

轉自：https://www.cnblogs.com/lottu/p/9239535.html