一文看懂PG的表空間物理佈局設計、佈局及管理

概述

PostgreSQL中的表空間實際上就是文件系統位置的一個目錄，可以保存所有其他對象的容器，如表、索引等，有默認表空間、共享系統表表空間、自定義表空間。

一般PG DBA會規劃一個存索引的表空間、存歸檔的表空間，一個很頻繁使用的索引可以被放在非常快並且非常可靠的磁盤上，一個很少使用的或者對性能要求不高的存儲歸檔數據的表可以存儲在一個便宜但比較慢的磁盤系統上。

DBA規劃表空間就是合理利用磁盤性能和空間,制定最優的物理存儲方式來管理數據庫表和索引。

一、Oracle VS PG 數據庫的物理佈局設計

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

在PostgreSQL集群中；每個數據庫都會在$PGDATA/base下面生成一個子目錄，文件名與數據庫oid一一對應，一個表空間可以讓多個數據庫使用；數據庫目錄可以分佈於多個表空間，也就是同一個數據庫oid可以存在於不同表空間。屬於"多對多"的關係。

二、Oracle VS PG系統自帶表空間

1、Oracle自帶系統表空間

1）system表空間

每個Oracle數據庫都會有SYSTEM表空間，而且SYSTEM表空間總是要保持在聯機模式下，因為其包含了數據庫運行所要求的基本信息，如：數據字典、聯機求助機制、所有回退段、臨時段、所有的用戶數據庫實體、其它ORACLE軟件產品要求的表等等。

2）SYSAUX表空間

作為SYSTEM表空間的輔助表空間，主要存放一些其他的 metadata 組件，如 OEM,Streams 等會默認存放在 SYSAUX 表空間裡。

3）臨時表空間

臨時表空間是用來管理數據庫排序操作以及用於存儲臨時表、中間排序結果等臨時對象,當ORACLE裡需要用到SORT的時候，並且當PGA中sort_area_size大小不夠時，將會把數據放入臨時表空間裡進行排序。像數據庫中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能會用到臨時表空間。當操作完成後，系統會自動清理臨時表空間中的臨時對象，自動釋放臨時段。

4）UNDO表空間

undo表空間中會自動分配undo段，這些undo段用來保存事務中的DML語句的undo信息，也就是來保存數據在被修改之前的值。在rollback，實例恢復(前滾)，一致性讀CR塊的構造時會使用到undo信息。由於undo的引入，從而Oracle的select語句實現一致性讀時，不需要任何鎖。

5）USERS表空間

創建用戶必須為其指定表空間，如果沒有顯性指定默認表空間，則指定為users表空間，此用戶所有信息都會放入到users表空間中。

2、PG自帶系統表空間

注意PG默認沒有temp表空間（或者說臨時段默認是存放在pg_default表空間）和undo表空間。

1）pg_default表空間

表空間pg_default是用來存儲系統目錄對象、用戶表、用戶表index、和臨時表、臨時表index、內部臨時表的默認空間。對應存儲目錄$PADATA/base/

（1）目錄名“1”是系統數據庫template1各relation的存儲目錄

（2）目錄數字“倒數第二”是系統數據庫template0各relation的存儲目錄

（3）其他數字目錄是用戶建立的數據庫目錄

（4）目錄的命名方法就是數據庫的oid值

（5）FSM的文件記錄每個數據塊的空閒空間

（6）VM文件讓vacuum高效工作，被標為無效的記錄需要用vacuum來進行清理，需要一個文件來標識哪些數據塊中存在無效記錄來達到高效的清理。

2）pg_global表空間

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

三、pg表空間物理佈局圖

四、PG表空間管理相關命令

1、查看錶空間

<code>\db
select
 * 
from
 pg_tablespace;/<code>

2、創建表空間

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

注意目錄"directory"必須是一個已有的空目錄，並且屬於PostgreSQL操作系統用戶

這裡把建用戶、表空間、建庫一起做一個實例介紹了。

<code>su - postgres
psql
 
CREATE
 
USER
 dbmt 
WITH
 
PASSWORD
 
'jkc800$uTxt$9K20'
;
 
CREATE
 
TABLESPACE
 dbmt OWNER dbmt LOCATION 
'/data/pgdata/dbmt'
;
 
CREATE
 
DATABASE
 dbmt 
with
 OWNER=dbmt 
ENCODING
=
'UTF-8'
 
TABLESPACE
=dbmt 
CONNECTION
 
LIMIT
=
-1
;
 
GRANT
 
ALL
 
PRIVILEGES 
 
ON
 
DATABASE
 dbmt 
TO
 dbmt;
\q/<code>

3、刪除表空間

<code> 
drop
 
tablespace
 tablespace_name;/<code>

4、為數據庫指定默認表空間

注意：

1)執行該操作不能連著對應數據庫操作

2)對應的數據庫不能存在表或者索引已經指定默認的表空間

3)執行該操作必須是沒有人連著對應的數據庫

<code> 
ALTER
 
DATABASE
 
name
 
SET
 
TABLESPACE
 new_tablespace;/<code>

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

注意：在移動表的時候會鎖表，此時對該表的所有操作都將被阻塞，包括select操作，所以請考慮在合適的實際做這個操作。

<code> 
ALTER
 
TABLE
 
name
 
SET
 
TABLESPACE
 new_tablespace;/<code>

6、臨時表空間管理

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

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

6.1、創建臨時表空間

<code>
CREATE
 
TABLESPACE
 temp01 LOCATION 
'/data/pgdata/temp'
;
 
show
 temp_tablespaces;/<code>

6.2、設置臨時表空間

<code> 
set
 temp_tablespaces = 
'temp01'
;
 
1）修改參數文件postgresql.conf
2）執行pg_ctl reload/<code>

覺得有用的朋友多幫忙轉發哦！後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~