Postgresql自帶了pg_default、pg_global這兩個表空間

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

表空間pg_global是用來存放叢集級別的系統字典表(比如pg_database)的空間；對應儲存目錄$PADATA/global/

當建立一個表空間時，$PADATA/pg_tblspc目錄下會自動生成一個表空間對應的oid名稱的檔案，這個檔案是個軟連結符號，實際指向表空間設定的路徑。oid名稱軟連結的表空間實際目錄下面會再生成一個子目錄，這個子目錄命名規則是PG_Major version_Catalog version number(比如PG_10_201707211，可以透過pg_controldata命令看到Catalog version number)的格式，在這個PG_Major version_Catalog version number子目錄下會有繫結在這個表空間上的資料庫對應的oid的子目錄，建立表空間時表空間的名稱不能以pg_開頭否則會報錯DETAIL:  The prefix "pg_" is reserved for system tablespaces.

繫結在表空間dwtablespace上的資料庫dwdb的oid是24668
```sql
postgres=# select a.oid,a.datname as "Name",b.spcname as "Tablespace" FROM pg_catalog.pg_database a JOIN pg_catalog.pg_tablespace b on a.dattablespace = b.oid where b.spcname not like 'pg_%';
  oid  | Name |  Tablespace
-------+------+--------------
 24668 | dwdb | dwtablespace
```

透過命令pg_controldata看到Catalog version number是201809051
```shell
-bash-4.2$ pg_controldata
pg_control version number:            1100
Catalog version number:               201809051
Database system identifier:           7075338446155241348
Database cluster state:               in production
pg_control last modified:             Tue 21 Mar 2023 11:18:26 PM PDT
```
...

$PGDATA/pg_tblspc/目錄下表空間dwtablespace對應oid值24667，它軟連結到表空間的實際目錄/mnt/dw
```shell
-bash-4.2$ ll $PGDATA/pg_tblspc/
total 0
lrwxrwxrwx. 1 postgres postgres  7 Mar 21 23:12 24667 -> /mnt/dw
lrwxrwxrwx. 1 postgres postgres 17 Mar 21 00:12 24670 -> /Pgtablespace/dw2
```

表空間dwtablespace對應oid值24667下有子目錄PG_Major version_Catalog version number
```shell
-bash-4.2$ ll $PGDATA/pg_tblspc/24667/
total 0
drwx------. 3 postgres postgres 18 Mar 21 22:53 PG_11_201809051
```

子目錄PG_Major version_Catalog version number下有繫結在這個表空間上的資料庫dwdb的oid值的子目錄24668
```shell
-bash-4.2$ ll $PGDATA/pg_tblspc/24667/PG_11_201809051
total 12
drwx------. 2 postgres postgres 8192 Mar 21 22:53 24668
```

Oracle和postgresql的主備情況下
Oracle主庫建立表空間如果不指明檔案路徑則預設在db_create_file_dest下面，那麼備庫因為有引數db_file_name_convert和log_file_name_convert，所以備庫不需要建立相應目錄就可以直接建立表空間和對應的路徑
Oracle中，一個表空間只屬於一個資料庫，而一個資料庫可以使用多個表空間，屬於“一對多”的關係

Postgresql主庫建立表空間必須繫結目錄（和oracle的在expdp中的directory一樣），所以這個目錄必須在備庫也存在，否則備庫的db會down掉
Postgresql 中，一個表空間可以讓多個資料庫使用，而一個資料庫也可以使用多個表空間，屬於“多對多”的關係

安裝postgresql-contrib(yum install postgresql-contrib)後會有一個oid2name命令，這個命令可以解析PostgreSQL資料目錄中的OID和檔案結點，比如直接執行oid2name可以列出所有資料庫名稱和oid和資料庫對應的表空間，執行oid2name -s可以列出所有表空間名稱和oid，執行oid2name -d dwdb -f 3997 -x可以列出dwdb資料庫下id為3997的檔案對應的表名和所在的表空間，具體參考oid2name --help

預設表空間的概念和檢視和設定：
概念：預設表空間只是針對新建的表和索引而言，新建資料庫時如果不指定表空間名稱，新資料庫不會使用預設表空間而是繼承模板資料庫繼承其表空間設定；新建表和索引時，如果引數default_tablespace設定了有效的預設表空間，這些新建的表和索引會使用default_tablespace設定的預設表空間而不會使用這些表和索引對應的資料庫的表空間，也就是當default_tablespace被設定為非空字串並且有效，那麼它就為沒有顯式TABLESPACE子句的CREATE TABLE和CREATE INDEX命令提供一個隱式TABLESPACE子句。 
檢視：透過命令show default_tablespace可以看到引數default_tablespace指向的預設表空間，該值為空的話，說明就是預設表空間就是當前資料庫的表空間
設定：alter system set default_tablespace=newtablespace，該引數不需要重啟只需要執行select pg_reload_conf()就可以馬上生效並且重啟後也生效，如果是SET default_tablespace=newtablespace則只是在當前會話生效

Alter tablespace可以修改表空間的名稱和owner，修改表空間名稱和owner後表空間對應的oid不變，因為表空間的oid不變，所以表空間在OS上的路徑名稱不變。alter tablespace還不支援修改表空間的儲存路徑，但是，可以透過手動的方式移動表空間的位置：
1、停止 PostgreSQL 伺服器程式；
2、移動檔案系統中的資料檔案位置
3、修改 $PGDATA/pg_tblspc目錄中的軟連結符號oid(一定要記得這個oid)以指向新的目錄。如果不想刪除之前的軟連結符號$PGDATA/pg_tblspc/oid,則軟連結ln命令時使用-snf引數如：ln -snf /newdir/newdir /pgdata/pg_tblspc/oid，如果刪除$PGDATA/pg_tblspc/oid了，則直接使用-s引數就行如：ln -s /newdir/newdir /pgdata/pg_tblspc/oid
4、啟動 PostgreSQL 伺服器程式。

Postgresql表空間詳解

相關文章