概述

在数据库运维工作中，经常会有数据目录使用率较高需要调整的情况，通常会给数据库建立多个表空间，并分别位于不同的盘上，这时需要做的工作就是调整库中现有表和索引的表空间。今天主要针对PG表空间做一下总结。

表空间用途

PostgreSQL中的表空间允许在文件系统中定义用来存放表示数据库对象的文件的位置。在PostgreSQL中表空间实际上就是给表指定一个存储目录。

通过使用表空间，管理员可以控制一个PostgreSQL安装的磁盘布局。这么做至少有两个用处。

1. 如果初始化集簇所在的分区或者卷用光了空间，而又不能在逻辑上扩展或者做别的什么操作，那么表空间可以被创建在一个不同的分区上，直到系统可以被重新配置。
2. 表空间允许管理员根据数据库对象的使用模式来优化性能。例如，一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上，如一种非常贵的固态设备。同时，一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统上。

用一句话来讲：能合理利用磁盘性能和空间,制定最优的物理存储方式来管理数据库表和索引。

表空间跟数据库关系

在Oracle数据库中；一个表空间只属于一个数据库使用；而一个数据库可以拥有多个表空间。属于"一对多"的关系

在PostgreSQL集群中；一个表空间可以让多个数据库使用；而一个数据库可以使用多个表空间。属于"多对多"的关系。

表空间共享

与Oracle数据库中的表空间被独占不同，PostgreSQL的表空间是可以被共享的。

当创建了一个表空间后，这个表空间可以被多个数据库、表、索引等数据库对象使用。达到对象的分离与归类的目的。

在PostgreSQL中有两个系统自建表空间：pg_global和pg_default。

前者是系统全局表空间，存储了关键的共享系统目录。后者是系统全局表空间，存储了关键的共享系统目录。后者是系统默认表空间，可通过set default tablespace=tablespacename来指定为其他表空间，在建立数据库、表、索引等数据库对象时，

若不指定表空间参数，则系统自动将对象创建到默认表空间中。

总结：

• 在初始化PG后，默认创建了两个表空间pg_default和pg_global。
• 如果在创建表时候没有指定表空间，则默认是pg_default。
• 数据库群中表的管理默认都是在pg_global中。
• pg_default表空间的物理位置在$PGDATA\base。
• pg_global表空间的物理位置在$PGDATA\global。

创建表空间

语法：

CREATE TABLESPACE tablespace_name [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ] LOCATION 'directory'

--创建目录

mkdir tbs_hwb 
mkdir ind_hwb
chmod 777 tbs_hwb/ 
chown pg:pg tbs_hwb/ 
chmod 777 ind_hwb/ 
chown pg:pg ind_hwb/ 

--创建表空间

create tablespace tbs_hwb owner hwb location '/PostgreSQL/data/tbs_hwb'; 
create tablespace ind_hwb owner hwb location '/PostgreSQL/data/ind_hwb'; 
create table t1(id int) tablespace tbs_hwb; 
create index ind_t1 on t1(id) tablespace ind_hwb; ---可以将表和索引放在不同的表空间 

用户表空间权限

has_tablespace_privilege(user, tablespace, privilege)boolean用户是否有访问表空间的权限 CREATE

has_tablespace_privilege(tablespace, privilege)boolean 当前用户是否有访问表空间的权限 CREATE

postgres=# create user sqluser nosuperuser noreplication nocreatedb nocreaterole login encrypted password 'sqlpasswd'; --创建一个普通用户 
CREATE ROLE 
postgres=# select has_tablespace_privilege('sqluser','tbs_hwb','create'); --sqluser没有tbs_hwb表空间的权限 
has_tablespace_privilege 
-------------------------- 
f 
(1 row) 
postgres=# grant create on tablespace tbs_hwb to sqluser; ---授权给sqluser 
GRANT 
postgres=# select has_tablespace_privilege('sqluser','tbs_hwb','create'); --sqluser可以使用tbs_hwb表空间了 
has_tablespace_privilege 
-------------------------- 
t 
(1 row) 
postgres=# select has_tablespace_privilege('tbs_hwb','create'); ---当前用户postgres 拥有表空间tbs_hwb的权限 
has_tablespace_privilege 
-------------------------- 
t 
(1 row) 

表空间大小查询

pg_tablespace_size(oid)bigint指定 OID 代表的表空间使用的磁盘空间

pg_tablespace_size(name)bigint指定名字的表空间使用的磁盘空间

postgres=# select oid,* from pg_tablespace; 
postgres=# select pg_tablespace_size(16437)/1024 ||'KB'; ---表空间tbs_hwb的oid为16437
postgres=# select pg_tablespace_size('tbs_hwb')/1024||'KB'; ---也可以直接使用表空间名 
postgres=# select pg_size_pretty(pg_tablespace_size('tbs_hwb')); 

表所在表空间查询

PostgreSQL 提供类似" \ "命令很方便得到相关信息，命令如下：

说明：如果这个表的表空间为当前数据库的默认表空间，那么上面则不会显示 Tablespace 信息。

1、查询数据库的默认表空间

postgres=# select datname,dattablespace from pg_database where datname='hwb'; 
postgres=# select oid,spcname from pg_tablespace where oid=1663; 

2、查询在默认表空间的表和索引

postgres=# select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner 
from pg_class a where a.relkind in ('r', 'i') and reltablespace='0' order by a.relpages desc; 

说明：限制条件 reltablespace='0'，即可查找出位于当前数据库默认表空间的数据库表和索引。

3、查询不在默认表空间的表和索引

postgres=# select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, 
pg_tablespace tb where a.relkind in('r','i') and a.reltablespace >1664 order by a.relpages desc; 

4、查询在某个表空间上的对象

postgres=# select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner 
from pg_class a, pg_tablespace tb where a.relkind in ('r', 'i') 
and a.reltablespace=tb.oid and tb.spcname='tbs_hwb' order by a.relpages desc; 

删除表空间

postgres=# drop tablespace ind_hwb; 
ERROR: tablespace "ind_hwb" is not empty 
--需要先清空表空间内的对象 
postgres=# drop index ind_t1; 
DROP INDEX 
postgres=# drop tablespace ind_hwb; 
DROP TABLESPACE 

临时表空间

PostgreSQL的临时表空间，通过参数temp_tablespaces进行配置，PostgreSQL允许用户配置多个临时表空间。配置多个临时表空间时，使用逗号隔开。如果没有配置temp_tablespaces 参数，临时表空间对应的是默认的表空间pg_default。

PostgreSQL的临时表空间用来存储临时表或临时表的索引，以及执行SQL时可能产生的临时文件例如排序，聚合，哈希等。为了提高性能，一般建议将临时表空间放在SSD或者IOPS，以及吞吐量较高的分区中。

1、创建临时表空间

$ mkdir -p /data/pg_data/temp_tsp
$ chown -R postgres:postgres /data/pg_data/temp_tsp
postgres=# CREATE TABLESPACE temp01 LOCATION '/data/pg_data/temp_tsp';
CREATE TABLESPACE
postgres=# show temp_tablespaces ;
temp_tablespaces
------------------
(1 row)

2、会话级生效设置临时表空间

postgres=# set temp_tablespaces = 'temp01';
SET

3、永久生效设置临时表空间

修改参数文件postgresql.conf，执行pg_ctl reload

[postgres@Postgres201 data]$ grep "temp_tablespace" postgresql.conf
temp_tablespaces = 'temp01' # a list of tablespace names, '' uses

4、查看临时表空间

postgres=# show temp_tablespaces ;
temp_tablespaces
------------------
temp01
(1 row)

篇幅有限，这块内容就介绍到这了，后面再介绍一下监控表空间方面的内容，感兴趣的朋友可以关注一下~如果你觉得这篇文章对你有帮助, 请小小打赏下。