postgresql-管理表空间

基本概念

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

PostgreSQL 中的表空间与其他数据库系统不太一样，它更偏向于一个物理上的概念
表空间的引入为 PostgreSQL 的管理带来了以下好处：

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

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

• pg_default，template1 和 template0 默认的表空间，也是创建其他数据库时的默认表空间；
  对应目录为安装目录下data文件下的base文件夹
  
• pg_global，用于存储一些集群级别的共享系统表（system catalogs），例如 pg_database、
  pg_control； 对应目录为安装目录下data文件下的global文件夹
  
  官网关于文件和目录的介绍

创建表空间

创建新的表空间使用create tablespace

CREATE TABLESPACE 表空间名称
OWNER 用户名称
LOCATION 目录;

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

-- 创建表空间
create tablespace app_tbs location 'D:\tools\postgresql\data\app_tbs';

在表空间对应的目录中，创建一个特定版本的子目录（PG_版本_创建时间）

用户授权

--对于普通用户，需要授予表空间上的对象创建权限才能使用该表空间。我们为用户 tony 授予表空间 app_tbs 上的使用权限：
grant create on tablespace app_tbs to tony;

移动表空间

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

-- alter table if exists 表空间名.表名  set tablespace 新的表空间
alter table if exists public.products set tablespace app_tbs;

对于临时表和索引，使用配置参数 temp_tablespaces 进行控制，参考官方文档

修改表空间

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

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] )
ALTER TABLESPACE name RESET ( tablespace_option [, ... ] )

第一个语句用于表空间的重命名；第二个语句用于修改表空间的拥有者；最后两个语句用于
设置表空间的参数。
只有表空间的拥有者或超级用户才能修改表空间的定义。

--将表空间 app_tbs 重命名为 hr_tbs
alter tablespace app_tbs rename to hr_tbs;

表空间名称变了，存储的位置没有变

PostgreSQL 支 持 设 置 的 表 空 间 参 数 包 括seq_page_cost
random_page_cost
effective_io_concurrency

移动表空间位置

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

• 停止 PostgreSQL 服务器进程；
  

• 移动文件系统中的数据文件位置；

• 修改 安装目录下data文件夹下表空间的链接，指向新的目录
  

• 启动 PostgreSQL 服务器进程
  

删除表空间

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

DROP TABLESPACE [ IF EXISTS ] name

IF EXISTS 可以避免删除不存在的表空间时产生错误信息。
只有表空间的拥有者或超级用户能够删除表空间。删除表空间之前需要确保其中不存在任何
数据库对象，否则无法删除
其他数据库中也可能存在依赖于被删除表空间的对象，同样需要先进行处理，才能删除表空
间。
删除表空间时，同时会删除文件系统中对应的表空间子目录。