postgresql14管理（五）-tablespace

基本概念

表空间tablespace在postgresql中，表示数据库对象（比如表或索引）的存放目录。当表被访问时，系统通过表空间定位到对应数据文件所在的位置。

优势：
1、如果数据库集群所在的初始磁盘分区或磁盘卷的空间不足，又无法进行扩展，可以在其他分区上创建一个新的表空间以供使用；
2、可以根据数据库对象的使用统计优化系统的性能。例如，可以将访问频繁的索引存放到固态硬盘，而对于将很少使用，或对性能要求不高的归档数据表存储到廉价的机械硬盘；

介绍：
在pg集群初始化时将所有的数据文件和配置文件存储到数据目录中，一般是环境变量PGDATA的值。默认创建了两个表空间：
1、pg_default：template1 和 template0 默认的表空间，也是创建其他数据库时的默认表空间，对应的目录为 PGDATA/base；
2、pg_global：用于存储一些集群级别的共享系统表system catalogs（pg_database、pg_control），对应的目录为PGDATA/global；

#查看表空间
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+------------------------
pg_default | postgres |
pg_global | postgres |
(2 rows)
#也可进入到对应目录ls -l /var/lib/pgsql/14/data/
1
2
3
4
5
6
7
8
9

创建

CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION 'directory'

/*
pg_开头的表空间是系统保留，所以不能用来开头；
LOCATION参数必须指定绝对路径名，指定的目录必须是一个已经存在的空目录；
操作系统用户必须是该目录的拥有者，以便能够进行文件的读写；
*/
1
2
3
4
5
6
7
8
9

CREATE TABLESPACE app_tbs LOCATION 'D:\PostgreSQL\14\data\app_tbs';
--成功创建，目录下会出现PG_14_date的目录，同时，目录pgtblspc下会有相应的连接
1
2

--普通用户需要提前获得权限
GRANT CREATE ON TABLESPACE app_tbs TO tony;
1
2

CREATE DATABASE、CREATE TABLE、CREATE INDEX 以及 ADD
CONSTRAINT语句中默认的表空间为pg_default，可以通过tablespace_name指定。也可以用ALTER语句将对象从一个表空间移到另一个表空间。

--如果不想手动指定表空间，可以使用配置参数default_tablespace
SET default_tablespace = app_tbs2;
1
2

修改

--重命名
ALTER TABLESPACE name RENAME TO new_name;
ALTER TABLESAPCE app_tbs RENAME TO hr_tbs;

--更改拥有者
ALTER TABLESPACE name OWNER TO {new_owner | CURRENT_USER | SESSION_USER};
1
2
3
4
5
6

--参数优化
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] );
ALTER TABLESPACE name RESET ( tablespace_option [, ... ]

/*
支持设置的表空间参数：seq_page_cost、random_page_cost、effective_io_concurrency。均用于查询计划器选择执行计划时的代价评估。
*/
1
2
3
4
5
6
7

修改存储路径：目前不支持通过语句修改，可以以手动方式修改

#1、停止pg服务
pg_ctl stop
#2、移动目录
mv /var/lib/pgsql/app_tbs/ /var/lib/pgsql/tony_tbs
#3、更新软链接至新目录
ln -snf /var/lib/pgsql/tony_tbs /var/lib/pgsql/14/data/pg_tblspc/164
#4、检查
ls -lh /var/lib/pgsql/14/data/pg_tblspc/164
#5、启动pg服务
pg_ctl start

#查看表空间
postgres=# \db
1
2
3
4
5
6
7
8
9
10
11
12
13

/*
删除表：IF EXISTS用作删除不存在的表空间；
只有表空间的拥有者或超级用户能够删除表空间；
删除表空间时，同时会删除文件系统中对应的表空间子目录；
需要确保其中不存在任何数据库对象，否则无法删除；
*/
DROP TABLESPACE [ IF EXISTS ] name
1
2
3
4
5
6
7

--先删表
DROP TABLE 表名;
--或先将表迁移至其他表空间
1
2
3