在數據庫運維工作中，經常會有數據目錄使用率較高需要調整的情況，通常會給數據庫建立多個表空間，并分别位于不同的盤上，這時需要做的工作就是調整庫中現有表和索引的表空間。今天主要針對PG表空間做一下總結。

PostgreSQL中的表空間允許在文件系統中定義用來存放表示數據庫對象的文件的位置。在PostgreSQL中表空間實際上就是給表指定一個存儲目錄。

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

1. 如果初始化集簇所在的分區或者卷用光了空間，而又不能在邏輯上擴展或者做别的什麼操作，那麼表空間可以被創建在一個不同的分區上，直到系統可以被重新配置。
2. 表空間允許管理員根據數據庫對象的使用模式來優化性能。例如，一個很頻繁使用的索引可以被放在非常快并且非常可靠的磁盤上，如一種非常貴的固态設備。同時，一個很少使用的或者對性能要求不高的存儲歸檔數據的表可以存儲在一個便宜但比較慢的磁盤系統上。

用一句話來講：能合理利用磁盤性能和空間,制定最優的物理存儲方式來管理數據庫表和索引。

在Oracle數據庫中；一個表空間隻屬于一個數據庫使用；而一個數據庫可以擁有多個表空間。屬于"一對多"的關系

在PostgreSQL集群中；一個表空間可以讓多個數據庫使用；而一個數據庫可以使用多個表空間。屬于"多對多"的關系。

與Oracle數據庫中的表空間被獨占不同，PostgreSQL的表空間是可以被共享的。

當創建了一個表空間後，這個表空間可以被多個數據庫、表、索引等數據庫對象使用。達到對象的分離與歸類的目的。

在PostgreSQL中有兩個系統自建表空間：pg_global和pg_default。

前者是系統全局表空間，存儲了關鍵的共享系統目錄。後者是系統全局表空間，存儲了關鍵的共享系統目錄。後者是系統默認表空間，可通過set default tablespace=tablespacename來指定為其他表空間，在建立數據庫、表、索引等數據庫對象時，

若不指定表空間參數，則系統自動将對象創建到默認表空間中。

總結：

• 在初始化PG後，默認創建了兩個表空間pg_default和pg_global。
• 如果在創建表時候沒有指定表空間，則默認是pg_default。
• 數據庫群中表的管理默認都是在pg_global中。
• pg_default表空間的物理位置在$PGDATA\base。
• pg_global表空間的物理位置在$PGDATA\global。

語法：

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

--創建目錄

mkdir tbs_hwb mkdir ind_hwb chmod 777 tbs_hwb/ chown pg:pg tbs_hwb/ chmod 777 ind_hwb/ chown pg:pg ind_hwb/

--創建表空間

create tablespace tbs_hwb owner hwb location '/PostgreSQL/data/tbs_hwb'; create tablespace ind_hwb owner hwb location '/PostgreSQL/data/ind_hwb'; create table t1(id int) tablespace tbs_hwb; create index ind_t1 on t1(id) tablespace ind_hwb; ---可以将表和索引放在不同的表空間

has_tablespace_privilege(user, tablespace, privilege) boolean 用戶是否有訪問表空間的權限 CREATE

has_tablespace_privilege(tablespace, privilege) boolean 當前用戶是否有訪問表空間的權限 CREATE

postgres=# create user sqluser nosuperuser noreplication nocreatedb nocreaterole login encrypted password 'sqlpasswd'; --創建一個普通用戶 CREATE ROLE postgres=# select has_tablespace_privilege('sqluser','tbs_hwb','create'); --sqluser沒有tbs_hwb表空間的權限 has_tablespace_privilege -------------------------- f (1 row) postgres=# grant create on tablespace tbs_hwb to sqluser; ---授權給sqluser GRANT postgres=# select has_tablespace_privilege('sqluser','tbs_hwb','create'); --sqluser可以使用tbs_hwb表空間了 has_tablespace_privilege -------------------------- t (1 row) postgres=# select has_tablespace_privilege('tbs_hwb','create'); ---當前用戶postgres 擁有表空間tbs_hwb的權限 has_tablespace_privilege -------------------------- t (1 row)

pg_tablespace_size(oid) bigint 指定 OID 代表的表空間使用的磁盤空間

pg_tablespace_size(name) bigint 指定名字的表空間使用的磁盤空間

postgres=# select oid,* from pg_tablespace; postgres=# select pg_tablespace_size(16437)/1024 ||'KB'; ---表空間tbs_hwb的oid為16437 postgres=# select pg_tablespace_size('tbs_hwb')/1024||'KB'; ---也可以直接使用表空間名 postgres=# select pg_size_pretty(pg_tablespace_size('tbs_hwb'));

PostgreSQL 提供類似" \ "命令很方便得到相關信息，命令如下：

說明：如果這個表的表空間為當前數據庫的默認表空間，那麼上面則不會顯示 Tablespace 信息。

1、查詢數據庫的默認表空間

postgres=# select datname,dattablespace from pg_database where datname='hwb'; postgres=# select oid,spcname from pg_tablespace where oid=1663;

2、查詢在默認表空間的表和索引

postgres=# select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a where a.relkind in ('r', 'i') and reltablespace='0' order by a.relpages desc;

說明：限制條件 reltablespace='0'，即可查找出位于當前數據庫默認表空間的數據庫表和索引。

3、查詢不在默認表空間的表和索引

postgres=# select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb where a.relkind in('r','i') and a.reltablespace >1664 order by a.relpages desc;

4、查詢在某個表空間上的對象

postgres=# select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb where a.relkind in ('r', 'i') and a.reltablespace=tb.oid and tb.spcname='tbs_hwb' order by a.relpages desc;

postgres=# drop tablespace ind_hwb; ERROR: tablespace "ind_hwb" is not empty --需要先清空表空間内的對象 postgres=# drop index ind_t1; DROP INDEX postgres=# drop tablespace ind_hwb; DROP TABLESPACE

PostgreSQL的臨時表空間，通過參數temp_tablespaces進行配置，PostgreSQL允許用戶配置多個臨時表空間。配置多個臨時表空間時，使用逗号隔開。如果沒有配置temp_tablespaces 參數，臨時表空間對應的是默認的表空間pg_default。

PostgreSQL的臨時表空間用來存儲臨時表或臨時表的索引，以及執行SQL時可能産生的臨時文件例如排序，聚合，哈希等。為了提高性能，一般建議将臨時表空間放在SSD或者IOPS，以及吞吐量較高的分區中。

1、創建臨時表空間

$ 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)

2、會話級生效設置臨時表空間

postgres=# set temp_tablespaces = 'temp01'; SET

3、永久生效設置臨時表空間

修改參數文件postgresql.conf，執行pg_ctl reload

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

4、查看臨時表空間

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

篇幅有限，這塊内容就介紹到這了，後面再介紹一下監控表空間方面的内容，感興趣的朋友可以關注一下~如果你覺得這篇文章對你有幫助, 請小小打賞下。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!