PostgreSQL表空间demo

基本概念

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

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

表空间的作用

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

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

应用场景

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

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

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

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

系统自带表空间

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

设定表空间的一系列命令

postgres环境

首先，设定表空间需要在 postgres 操作环境中进行，所以第一步，进入 postgres 操作环境
先切换到postgres用户，然后输入psql，最终出现 postgres=#，表示是一个正确的环境

[postgres@pg13 ~]$ psql
psql (13.3)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 tpcc      | tpcc     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

postgres=# \c - tpcc
You are now connected to database "postgres" as user "tpcc".
postgres=# \c tpcc
You are now connected to database "tpcc" as user "tpcc".

注意，在此环境中执行的是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 结束即可。

查看表空间

tpcc=# \db
                 List of tablespaces
     Name      |  Owner   |         Location         
---------------+----------+--------------------------
 db_tablespace | tpcc     | /home/postgres/dbdata
 pg_default    | postgres | 
 pg_global     | postgres | 
 tbs_index     | tpcc     | /home/postgres/indexdata
 tbs_test      | tpcc     | /home/postgres/newdata
(5 rows)

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

也可以使用 sql 查看

tpcc=# select oid,* from pg_tablespace;
  oid  |  oid  |    spcname    | spcowner | spcacl | spcoptions 
-------+-------+---------------+----------+--------+------------
  1663 |  1663 | pg_default    |       10 |        | 
  1664 |  1664 | pg_global     |       10 |        | 
 25072 | 25072 | tbs_test      |    16888 |        | 
 25073 | 25073 | tbs_index     |    16888 |        | 
 25078 | 25078 | db_tablespace |    16888 |        | 
(5 rows)

创建表空间

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

tpcc=# create tablespace tbs_test owner tpcc location '/home/postgres/newdata';
CREATE TABLESPACE
tpcc=# create tablespace tbs_index owner tpcc location '/home/postgres/indexdata';
CREATE TABLESPACE
tpcc=# create table test(a int) tablespace tbs_test;
CREATE TABLE
tpcc=# create index ind on test using btree (a);
CREATE INDEX

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

一个表空间目录下，每个库对应一个目录；

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

查看表存储路径

tpcc=# select pg_relation_filepath('test');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/25072/PG_13_202007201/16889/25074
(1 row)

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

tpcc=# create tablespace db_tablespace location '/home/postgres/dbdata';
CREATE TABLESPACE
tpcc=# CREATE DATABASE LightDB tablespace db_tablespace;
CREATE DATABASE

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

可以直接创建表

tpcc=# create table test_new(a int) tablespace db_tablespace;
CREATE TABLE

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

tpcc=# create table test_new(a int) tablespace db_tablespace;
CREATE TABLE
tpcc=# alter table test_new set tablespace tbs_test;
ALTER TABLE

注意：该操作时会锁表。