PostgreSQL 表空間

wongchaofan發表於2024-07-20
SQL
資料庫叢集初始化時會自動建立兩個表空間：
  1. pg_global ：用於共享系統目錄。
  2. pg_default ：是 template1 和 template0 資料庫的預設表空間，如果沒有給出 TABLESPACE 子句，則用於在資料庫內建立的表、索引和臨時檔案的預設表空間。每當您在建立表/資料庫而未在建立表語句中指定表空間時，它將轉到 pg_default 表空間。

表空間建立的語法：

CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

在建立表空間之前，您必須建立作業系統級目錄，並且必須將目錄所有者許可權更改為 postgres 使用者

1.creating tbs1 directory:
cd /tab1/
mkdir tbs1

2.changing tbs1 permission to postgres user:
chown -R postgres:postgres tbs1

3.creating tablespace with name of  tbs1 ,tbs1 is a logical name you can change  whatever you want:
CREATE TABLESPACE tbs1 LOCATION '/tab1/tbs1';  #建立表空間

4.Listing postgresql tablespace:
postgres=# \db+                    #列出表空間
                                   List of tablespaces
    Name    |  Owner   |  Location  | Access privileges | Options |  Size   | Description
------------+----------+------------+-------------------+---------+---------+-------------
 pg_default | postgres |            |                   |         | 23 MB   |
 pg_global  | postgres |            |                   |         | 573 kB  |
 tbs1       | postgres | /tab1/tbs1 |                   |         | 0 bytes |
(3 rows)

建立有/無表空間的表：
大多數“CREATE”SQL 命令都帶有“TABLESPACE”選項，您可以使用該選項指定要在其中建立該 SQL 物件的表空間。讓我們嘗試幾個：

postgres=# create database dbname2 tablespace tbs1;   #建立資料庫dbname2,指定預設表空間
CREATE DATABASE
postgres=# \c dbname2
You are now connected to database "dbname2" as user "nijam".
dbname2=# create table t1 (a int);
CREATE TABLE
dbname2=# create table t2 (a int) tablespace tbs1;
CREATE TABLE
dbname2=# create table t3 (a int) tablespace tbs2;
CREATE TABLE

如果您想知道組成表的檔案的確切位置，可以使用 oid2name：
oid2name是一個實用程式，可幫助管理員檢查 PostgreSQL 使用的檔案結構。

$ oid2name -t t4
From database "postgres":
  Filenode  Table Name
----------------------
     24592          t4

$ find $PGDATA -name 2459*
/u02/pgdata/PG961/base/13322/24592

此外，oid2name 還會告訴您有關資料庫及其關聯的預設表空間的更多資訊：

$ oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13322       postgres  pg_default
  13321      template0  pg_default
      1      template1  pg_default

There are three main patterns paths in Tablespace:

    • 1.For files in the default tablespace: base/database_oid/table_and_index_files_oid
    • 2.For files in Non-default tablespace:The directory $PGDATA/pg_tblspc contains symbolic links that point to each of the non-built-in tablespaces defined in the cluster. pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid /table_and_index_files_oid
    • 3.For shared relations (see below): global/table_and_index_files_oid
postgres=#\! ls -l /u02/tbs1/
total 0
drwx------. 2 postgres postgres 6 Nov 25 11:03 PG_9.6_201608131
表空間的基本命令：
確定現有表空間的集合：
select oid,spcname from pg_tablespace;

下面的元命令對於列出現有表空間也很有用：
\db+

表空間重新命名：
alter tablespace tbs1 rename to tbs3;

更改表空間所有權：
alter tablespace tbs1 Owner to scott;

表空間重置：
alter tablepace tbs1 reset default_tablespace;

表空間刪除：
drop tablespace tbs1;
注意：直到所有使用表空間的資料庫中的所有物件都被刪除後，才能刪除表空間。

為特定使用者分配預設表空間：
ALTER ROLE someuser SET default_tablespace = tbs1;

表空間佔用的磁碟空間：
select pg_size_pretty(pg_tablespace_size('tbs1'));
（或）
/u02/tbs1/du -c -h
在使用建立一批表時暫時用於當前會話
SET default_tablespace = tbs2;
更改整個例項的預設表空間：
所有新建立的物件都進入新的表空間。
postgres=#  alter system set default_tablespace='tbs3';
ALTER SYSTEM
 
postgres=#  select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
 
postgres=#  show default_tablespace ;
 default_tablespace 
--------------------
 tbs3
(1 row)

How to find what tablespace a table/index is in on PostgreSQL?
For table:

SELECT tablespace FROM pg_tables WHERE tablename = 't1' AND schemaname = 'schema1';

相關文章