Postgresql表空间

有时我们需要把不同的表放到不同的存储介质或文件系统下， 这时就需要用到表空间， 在PostgreSQL中， 表空间实际上是为表指定一个存储目录。
在创建数据库时可以为其指定默认的表空间。
创建表、 创建索引的时候可以指定表空间， 这样表、 索引就可以存储到表空间对应的目录下了。

创建表空间的语法如下：

CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION 'directory'

例：

CREATE TABLESPACE tbs_data location '/data/pgdata';

创建数据库时可以指定默认的表空间， 这样以后在此数据库中创建表、 索引时就可以自动存储到表空间指定的目录下：

create database db01 tablespace tbs_data;

改变数据库的默认表空间的语法如下：

ALTER DATABASE db01 set TABLESPACE tbs_data;

注：在执行该操作时， 不能有用户连接到这个数据库上， 否则会报如下错误：

ERROR: database "db01" is being accessed by other users
DETAIL: There is 1 other session using the database.

另外， 改变数据库的默认表空间时， 数据库中已有表的表空间不会改变。

创建表时也可以指定表空间， 命令如下：

create table test01(id int, note text) tablespace tbs_data;

创建索引时同样可以指定表空间， 命令如下：

create index idx_test01_id on test01(id) tablespace tbs_data;

创建唯一约束时可指定约束索引的表空间， 命令如下：

ALTER TABLE test01 ADD CONSTRAINT unique_test01_id unique(id) USING INDEX TABLESPACE tbs_data;

增加主键时也可以指定主键索引的表空间， 命令如下：

ALTER TABLE test01 ADD CONSTRAINT pk_test01_id primary key(id) USING INDEX TABLESPACE tbs_data;

把表从一个表空间移到另一个表空间的命令如下：

alter table test01 set tablespace pg_default;

注意， 在移动表的时候表会被锁定， 对此表的所有操作都将无法执行， 包括SELECT操作， 所以请考虑在合适的时机做这个操作。

---

查询一张表在哪个表空间下：

SELECT 
    c.relname AS 表名,
    CASE 
        WHEN c.reltablespace = 0 THEN (SELECT spcname FROM pg_tablespace WHERE oid = d.dattablespace)
        ELSE t.spcname 
    END AS 表空间名称
FROM 
    pg_class c
LEFT JOIN 
    pg_tablespace t ON c.reltablespace = t.oid
LEFT JOIN 
    pg_database d ON d.datname = current_database()  -- 关联当前数据库的默认表空间
WHERE 
    c.relname = 't1'  -- 替换为实际表名
    AND c.relkind = 'r';  -- 过滤普通表（r=普通表）

查看表空间下有哪些对象（以pg_default为例）：

 SELECT 
    nspname AS schema_name,
    relname AS object_name,
    CASE relkind
        WHEN 'r' THEN '普通表'
        WHEN 'i' THEN '索引'
        WHEN 'm' THEN '物化视图'
        WHEN 'S' THEN '序列'
        WHEN 't' THEN 'TOAST表'
        ELSE relkind::TEXT
    END AS object_type,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE 
    c.reltablespace = 0  -- 替换为实际 OID，如果是pg_default写0
    AND nspname NOT IN ('pg_catalog', 'information_schema')  -- 排除系统模式
ORDER BY pg_total_relation_size(c.oid) DESC;

查看一个表空间的oid：

SELECT oid, spcname 
FROM pg_tablespace 
WHERE spcname = 'pg_data01';    --替换成实际表空间名

查看所有表空间及路径：

SELECT
    spcname AS "Tablespace Name",
    pg_tablespace.spcacl AS "Access Privileges",
    pg_tablespace_location(pg_tablespace.oid) AS "Location"
FROM
    pg_tablespace;