PostgreSQL-表空间

表空间

基本概念

不同的数据库表空间有不同的定义。

在 postgres 中，表空间允许在文件系统中定义数据库对象存储的位置，实质上就是指定了一个目录。

与数据库的关系

在 postgres 中，一个表空间可以让多个数据库使用，而一个数据库也可以使用多个表空间，属于“多对多”的关系

在 oracle 中，一个表空间只属于一个数据库，而一个数据库可以使用多个表空间，属于“一对多”的关系

应用场景

1. 存储磁盘没有空间时，可以使用表空间把数据存到其他地方；

如数据库一般装在root下，root相当于是一个磁盘分区，当root满时，可以使用表空间把数据存到 home 分区。

2. 利用表空间对数据库进行性能优化

如频繁使用的数据表或者索引放在高性能的硬盘上，而较少使用的放在普通硬盘上。

初始表空间

postgres 自带了两个表空间，pg_default， pg_global

表空间pg_default是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。对应存储目录$PADATA/base/

表空间pg_global用来存放系统字典表；对应存储目录$PADATA/global/

pg_tblspc 就是表空间，初始是空的，当手动增加表空间时，该目录下会自动生成一个软连接，指向表空间设定的路径。

设定表空间的一系列命令

Postgres环境

首先，设定表空间需要在 postgres 操作环境中进行，所以第一步，进入 postgres 操作环境

首先切换到postgres用户，然后输入psql，最终出现 postgres=#，表示是一个正确的环境

[root@localhost ~]# su postgres
bash-4.2$ psql
could not change directory to "/root"
psql (9.2.24)
Type "help" for help.

postgres=#

注意，在此环境中执行的是sql命令，而非linux命令。而且 sql语句后面必须带；

如果在后续操作过程中，postgres=#的=变成其他，代表输入命令没有被执行，可能命令有误，如下

postgres-# create tablespace tbs_test owner postgres location '/var/www/test/
postgres'# CREATE TABLESPACE zns_road  LOCATION '/var/www/test/'

ctrl+c 结束即可。

查看表空间

postgres=# \db
          List of tablespaces
    Name    |  Owner   |    Location    
------------+----------+----------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs_test   | postgres | /var/www/test
 zns_road   | postgres | /home/postgres
(4 rows)

这里我已经添加了几个表空间，所以除了初始的2个外，还有其他的。

也可以使用 sql 查看

postgres=# select oid,* from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions 
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        | 
  1664 | pg_global  |       10 |        | 
 24692 | tbs_test   |       10 |        | 
 24697 | zns_road   |       10 |        | 
(4 rows)

创建表空间

学习记录

开始我在 root 下，创建一个文件夹，然后 link 到 home 下的一个文件夹，没想到 root 下的软连接无法修改所有者和属组成postgres，这样操作直接修改的是被 link 的文件夹的所有者和属组，这样，表空间就没有权限使用这个目录，777也不行，当时折腾半天。

[root@localhost ~]# mkdir /var/www/psgl
[root@localhost ~]# chown postgres:postgres /var/www/psgl
[root@localhost ~]# chmod 777 /var/www/psgl/
[root@localhost ~]# ln -sfn /home/yanshuangwu/db/ /var/www/psgl

各种情况都试了，最后还是不行，没有权限。

postgres=# create tablespace test10 location '/var/www/psgl';
ERROR:  could not set permissions on directory "/var/www/psgl": Permission denied

【后来我发现可以，下面会说到】

最后我直接在 home 下建立目录，没想到竟然可以，详细过程如下：

1. 首先建立目录，然后修改所有者和属组；

// 首先这个目录是要被 postgres 使用的，所以要修改所有者和属组为 postgres，当然修改成777或许可以，我没试；

// 我在操作时，犯了一个错误，我当时在个人账户下建了一个目录，修改所有者和属组，然后不行，我改成777，还是不行，后来我意识到上层目录　　　　　 /home/myname 权限有问题，这很容易理解，但当时没想到，折腾半天

// 由于修改上层目录权限不是很好，我直接在 home 下建了一个目录，然后修改所有者和属组，相当于是给 postgres 开了账户

$ mkdir path
$ chown postgres:postgres path

【在写博客时，我重新理了下思路，发现我在root下link到home时，home下的文件夹是放在我个人账户下的，虽然该目录全权限，但是上层目录权限不对，这才导致创建表空间时没有权限，所以我修改后重新试了下，可以，完美收场。】

2. 创建表空间

CREATE TABLESPACE tablespace_name [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ] LOCATION 'directory'    ### 创建命令


postgres=# create tablespace zns_road owner postgres location '/home/postgres';
CREATE TABLESPACE

一个表空间对应一个目录；

一个表对应多个文件，以每个文件1G的形式存储，单表最大支持32T；

查看表存储路径

postgres=# select pg_relation_filepath('test');
             pg_relation_filepath             
----------------------------------------------
 pg_tblspc/24697/PG_9.2_201204301/12926/24698
(1 row)

3. 创建数据库或者表时指定表空间

CREATE DATABASE myDB TABLESPACE tbs;

以后在该数据库中创建表、索引时，会自动存储到这个表空间下。

可以直接创建表

postgres=# create table test(a int) tablespace zns_road;
CREATE TABLE

在客户端查看该数据库即可看到表空间

4. 修改已有数据库或表的表空间

ALTER DATABASE myDB set TABLESPACE tbs;

postgres=# alter table road_point set tablespace zns_road;
ALTER TABLE

注意，修改表空间时，必须断开所有连接。

如果不断开，所有操作被锁死。

操作记录

上述操作只是把现有表的表空间改变了，没有改变现有表中已存在数据的存储位置，仍然存在以前的表空间中，相当于是复制了一份到新的表空间中

要想把已存在的数据移动（剪切）到新的表空间，操作如下

alter table table_name move tablespace tablespace_name;

很奇怪，后面我重新试验了下，切换表空间之后，数据自动迁移到新的表空间，原空间没有了，相当于直接剪切了。

我又查了下，上句 move 是 orcle 的用法，有待核实。

5. 创建索引时指定表空间

CREATE INDEX idx_teacher on Teacher(ID) TABLESPACE tbs;

其他操作类似，详情百度。

参考资料：

https://www.cnblogs.com/lottu/p/9239535.html　　　　更多详细教程

https://blog.csdn.net/liyazhen2011/article/details/82746061

https://www.cnblogs.com/alianbog/p/4771084.html

转载于:https://www.cnblogs.com/yanshw/p/11351136.html

表空间