PostgreSQL15——管理表空间

一、基本概念

在 PostgreSQL 中，它是通过表空间（Tablespaces）来实现逻辑对象（表、索引等）与物理文件之间的映射。创建数据库或者数据表（包括索引）的时候，可以为其指定一个表空间
（tablespace）。表空间决定了这些对象在文件系统中的存储路径。

在PostgreSQL 中，表空间（tablespace）表示数据文件的存放目录，这些数据文件代表了数据库的对象，例如表或索引。当我们访问表时，系统通过它所在的表空间定位到对应数据文件所在的位置。

表空间的引入为 PostgreSQL 的管理带来了以下好处：

• 如果数据库集群所在的初始磁盘分区或磁盘卷的空间不足，又无法进行扩展，可以在其他分区上创建一个新的表空间以供使用。
• 管理员可以根据数据库对象的使用统计优化系统的性能。例如，可以将访问频繁的索引存放到一个快速且可靠的磁盘上，比如昂贵的固态硬盘。与此同时，将很少使用或者对性能要求不高的归档数据表存储到廉价的低速磁盘上。

PostgreSQL 在集群初始化时将所有的数据文件和配置文件存储到它的数据目录中，通常是环境变量PGDATA 的值。默认创建了两个表空间：

• pg_default， template1 和 template0 默认的表空间，也是创建其他数据库时的默认表空间；对应的目录为PGDATA/base。
• pg_global，用于存储一些集群级别的共享系统表（system catalogs），例如 pg_database、pg_control；对应的目录为PGDATA/global。

初始安装后，使用 psql 查询默认创建的表空间：

同时也可以通过操作系统命令查看相应的目录：

其中的 base 和 global 目录分别对应表空间 pg_default 和 pg_global。

二、创建表空间

创建新的表空间使用CREATE TABLESPACE 语句：

CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION 'directory';

表空间的名称不能以 pg_开头，它们是系统表空间的保留名称；LOCATION 参数必须指定绝对路径名，指定的目录必须是一个已经存在的空目录，PostgreSQL 操作系统用户（postgres）必须是该目录的拥有者，以便能够进行文件的读写。

接下来，我们使用目录/var/lib/pgsql/创建一个新的表空间 app_tbs。先创建所需的目录：

注意目录的所有者和权限。然后使用具有 CREATEDB 权限的用户创建表空间，此处我们使用 postgres 执行以下操作：

create tablespace app_tbs location '/var/lib/pgsql/app_tbs';

我们查看一下操作系统中的变化：

在表空间对应的目录中，创建一个特定版本的子目录。与此同时，在数据目录下的 pg_tblspc 子目录中，创建了一个指向表空间目录的符号链接，名称为表空间的 OID（16743）：

默认情况下，执行 CREATE TABLESPACE 语句的用户为该表空间的拥有者，也可以使用OWNER 选项指定拥有者。

对于普通用户，需要授予表空间上的对象创建权限才能使用该表空间。我们为用户 admin01 授予表空间 app_tbs 上的使用权限：

使用admin01 用户连接到数据库 testdb，然后在表空间app_tbs 中创建一个新的数据表t：

PostgreSQL 支持在 CREATE DATABASE、 CREATE TABLE、 CREATE INDEX 以及 ADD CONSTRAINT 语句中指定 tablespace_name 选项，覆盖默认的表空间（pg_default）。也可以使用相应的 ALTER …语句将对象从一个表空间移到另一个表空间。

如果不想每次创建对象时手动指定表空间，可以使用配置参数 default_tablespace：

set default_tablespace = app_tbs;

三、修改表空间

如果需要修改表空间的定义，可以使用 ALTER TABLESPACE 语句：

-- 用于表空间的重命名
ALTER TABLESPACE name RENAME TO new_name;

-- 用于修改表空间的拥有者
ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER };

-- 后两个语句用于设置表空间的参数
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] );

ALTER TABLESPACE name RESET ( tablespace_option [, ... ] );

将表空间 app_tbs 重命名为 admin01_tbs：

只有表空间的拥有者或超级用户才能修改表空间的定义。接下来将表空间 admin01_tbs 的拥有者修改为 admin01：

PostgreSQL 支 持设 置的 表空 间参 数包 括 seq_page_cost、 random_page_cost 以 及
effective_io_concurrency。它们用于查询计划器选择执行计划时的代价评估。

目前，PostgreSQL 还不支持使用语句修改表空间的存储路径。但是，可以通过手动的方式移动表空间的位置：

1. 停止 PostgreSQL 服务器进程；
2. 移动文件系统中的数据文件位置；
3. 修改 PGDATA/pg_tblspc 目录中的符号链接文件（需要提前获取文件名），指向新的目录；
4. 启动 PostgreSQL 服务器进程。

四、删除表空间

对于不再需要的表空间，可以使用 DROP TABLESPACE 语句进行删除：

DROP TABLESPACE [ IF EXISTS ] name;
-- IF EXISTS 可以避免删除不存在的表空间时产生错误信息。

只有表空间的拥有者或超级用户能够删除表空间。删除表空间之前需要确保其中不存在任何数据库对象，否则无法删除。删除表空间时，同时会删除文件系统中对应的表空间子目录。