表空间

基本概念

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

在 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