postgre 表空间 pgsql表空间

一、表空间的定义

PostgreSQL中的表空间允许数据库管理员在文件系统中定义用来存放表示数据库对象的文件的位置。一旦被创建，表空间就可以在创建数据库对象时通过名称引用。

通过使用表空间，管理员可以控制一个PostgreSQL安装的磁盘布局。 这么做至少有两个用处。首先，如果初始化集簇所在的分区或者卷用光了空间，而又不能在逻辑上扩展或者做别的什么操作，那么表空间可以被创建在一个不同的分区上，直到系统可以被重新配置。

其次，表空间允许管理员根据数据库对象的使用模式来优化性能。例如，一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上，如一种非常贵的固态设备。同时，一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统
上。

二、表空间的使用

1.创建语法：

CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]
    、

语法解析：

1.tablespace_name
要创建的表空间名称，名称不能以pg_开头，也不能和系统表空间名称冲突。

2.user_name
指定拥有该表空间的用户名称，如果没有指定默认为执行该命令的用户。只有超级用户可以创建表空间，但可以将表空间的拥有权授予普通用户。

3.directory
要被用做表空间的目录，该目录应该为空且必须为Postgresql系统用户拥有（有读写权限），而且该目录要是一个据对路径。

4.tablespace_option
要设置或者重置的表空间参数。当前，唯一可用的参数是seq_page_cost、random_page_cost 以及effective_io_concurrency。 为一个特定表空间设定其中一个值将覆盖规划器对该表空间中表页读取的 常规代价估计，常规代价估计是由同名的配置参数所建立（见 seq_page_cost、 random_page_cost、effective_io_concurrency）。如果一个表空间位于一个 比其他 I/O 子系统更慢或者更快的磁盘上，这些参数就能发挥作用。

创建示例：

1.创建表空间 tbs_data 指定目录路径为 /data/pgdate，首先创建目录，并设置正确的属主和属组：

mkdir /pgsql/pg_data
chown -R postgres:postgres /pgsql/pg_data

2.创建表空间：

CREATE TABLESPACE tbs_data LOCATION  '/pgsql/pg_data' ;

3.创建表空间，并指定所属用户为mydb：

CREATE TABLESPACE tbs_data OWNER mydb LOCATION  '/pgsql/pg_data' ;

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

create database mydb tablespace tbs_data;

修改一个数据库的默认表空间：

ALTER DATABASE mydb set tablespace tbs_data;

PS:在修改数据库默认表空间时需要保证此数据没有用户链接，否则会提示报错。而数据库中已有的表的表空间是不改变的。

创建表、索引、约束都可以指定存储的表空间，具体可参照各自的创建语法。

把指定表从一个表空间移动到另一个表空间的方式：

ALTER TABLE test01 SET TABLESPACE tbs_data；

PS:在移动表时，对表的所有操作都会阻塞，包括select查询操作。

三、表空间使用扩展

1.新表空间创建完成后，在数据库目录$PGDATE下的 pg_tblspc目录下会生成一个链接文件，链接到在创建表空间时指定的location目录 ‘/pgsql/pg_data’:

[postgres@local pg_tblspc]$ ll
total 0
lrwxrwxrwx 1 postgres postgres 14 Mar 12 11:36 16817 -> /pgsql/pg_data

其中的16817是表空间的OID，可以通过视图 pg_tablespace 查看所创建的表空间的OID是什么，同样也可以查看系统默认表空间的OID：

postgres=# select * from pg_tablespace;
  oid  |   spcname   | spcowner |                          spcacl                          | spcoptions 
-------+-------------+----------+----------------------------------------------------------+------------
  1663 | pg_default  |       10 |                                                          | 
  1664 | pg_global   |       10 |                                                          | 
 16817 | tbs_pg_mydb |       10 | {postgres=C/postgres,mytest=C/postgres,zhang=C/postgres} | 
(3 rows)

2.创建表时指定表的表空间：

mydb=> create table mytest (id int,name text) tablespace tbs_pg_mydb ;
CREATE TABLE

查看表在磁盘上的位置：

mydb=> select pg_relation_filepath('mytest');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/16817/PG_12_201909212/16443/16867
(1 row)

查询出的数据库的表在磁盘上的存放路径是这样组合的：

pg_tblspc/16817/PG_12_201909212/16443/16867
 
 pg_tblspc/16817/：数据库目录$PGDATA下的目录pg_tblspc，下面链接文件为16817

 PG_12_201909212/16443/16867：此路径是实际存放在表空间目录下，示例中表空间是tbs_pg_mydb，对应目录是 ”/pgsql/pgdata“，而”PG_12_201909212/16443/16867“就在”/pgsql/pgdata“目录下。

如果数据库指定表空间，创建表也没有指定表空间，那么创建表后，对应表所在磁盘位置是数据库目录$PGDATA目录下的base目录下：

postgres=# select pg_relation_filepath('employee');
 pg_relation_filepath 
----------------------
 base/13537/16656
(1 row)