SRE文档

深浅模式

9,表空间创建 ​

表空间：字面上理解就是表存储的物理空间，其实包括数据库的表、索引、序列等。

PostgreSQL使用表空间映射逻辑名称和磁盘物理位置。默认提供了两个表空间：

  • pg_default 表空间存储用户数据.
  • pg_global 表空间存储全局数据.

表空间名称不能以pg开头，因为这些名称为系统表空间保留。默认执行CREATE TABLESPACE语句的用户是表空间的拥有者。如果需要给其他用户赋权，可以值后面指定owner关键词。

directory_path是表空间使用空目录的绝对路径，PostgreSQL的用户必须拥有该目录的权限可以进行读写操作

可以将表空间创建在服务器的不同分区，这样做的好处有：

一、如果初始化集群所在分区已经用光，可以方便的其他分区上创建表空间已达到扩容的目的。

二、对于频繁访问的数据可以存储在性能较高、较快的磁盘分区上，而不常用的数据存储在便宜的较慢的磁盘分区上

bash
语法：

postgres=# \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
    [ OWNER user_name ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

用户必须有表空间所在目录访问权限，所以在创建表空间之前需要在对应分区下创建相应的目录，并为其分配权限。

[root@pg01 ~]# mkdir /home/ptgres/kongjian
[root@pg01 ~]# chown postgres:postgres /home/ptgres/kongjian
语法：

postgres=# \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
    [ OWNER user_name ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

用户必须有表空间所在目录访问权限，所以在创建表空间之前需要在对应分区下创建相应的目录，并为其分配权限。

[root@pg01 ~]# mkdir /home/ptgres/kongjian
[root@pg01 ~]# chown postgres:postgres /home/ptgres/kongjian
sql
-- 创建表空间
-- 想在sql命令行下，执行外部shell命令
postgres@postgres=>\! pwd
/home/postgres

--不指定用户
postgres@postgres=>CREATE DATABASE logistics TABLESPACE ts_primary;

-- 创建表空间属于哪个用户
postgres@postgres=>create tablespace tbs_test owner postgres location '/home/ptgres/kongjian';
CREATE TABLESPACE
-- 注意：同一个目录只能对应一个表空间

-- 查看表空间
postgres@postgres=>\db
              List of tablespaces
    Name    |  Owner   |       Location        
------------+----------+-----------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs_test   | postgres | /home/ptgres/kongjian
(3 rows)
-- 或者
postgres@postgres=>\db+

-- 或者
postgres@postgres=>select * from pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 ts01       |       10 |        | 
 ts02       |    16384 |        | 
(4 rows)

-- 或者指定具体的表空间名字
postgres@postgres=>\db+ ts_primary

-- 数据库集群目录下
[ptgres@pg01 ~]$ ls kongjian/
PG_12_201909212

-- 在此表空间内创建表
-- 切换用户和数据库
postgres@postgres=>\c database_name username;

postgres@postgres=>create table test(a int) tablespace tbs_test;
CREATE TABLE

[ptgres@pg01 13447]$ ls
/home/ptgres/kongjian/PG_12_201909212/13447/16421

-- 其中16421对应的是test表的relfilenode，13447是数据库postgres的oid
postgres@postgres=>select oid,datname from pg_database where datname = 'postgres'; 
  oid  | datname  
-------+----------
 13447 | postgres
(1 row)

postgres@postgres=>select relname,relfilenode from pg_class where relname='test'; 
 relname | relfilenode 
---------+-------------
 test    |       16421
(1 row)
-- 创建表空间
-- 想在sql命令行下，执行外部shell命令
postgres@postgres=>\! pwd
/home/postgres

--不指定用户
postgres@postgres=>CREATE DATABASE logistics TABLESPACE ts_primary;

-- 创建表空间属于哪个用户
postgres@postgres=>create tablespace tbs_test owner postgres location '/home/ptgres/kongjian';
CREATE TABLESPACE
-- 注意：同一个目录只能对应一个表空间

-- 查看表空间
postgres@postgres=>\db
              List of tablespaces
    Name    |  Owner   |       Location        
------------+----------+-----------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs_test   | postgres | /home/ptgres/kongjian
(3 rows)
-- 或者
postgres@postgres=>\db+

-- 或者
postgres@postgres=>select * from pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 ts01       |       10 |        | 
 ts02       |    16384 |        | 
(4 rows)

-- 或者指定具体的表空间名字
postgres@postgres=>\db+ ts_primary

-- 数据库集群目录下
[ptgres@pg01 ~]$ ls kongjian/
PG_12_201909212

-- 在此表空间内创建表
-- 切换用户和数据库
postgres@postgres=>\c database_name username;

postgres@postgres=>create table test(a int) tablespace tbs_test;
CREATE TABLE

[ptgres@pg01 13447]$ ls
/home/ptgres/kongjian/PG_12_201909212/13447/16421

-- 其中16421对应的是test表的relfilenode，13447是数据库postgres的oid
postgres@postgres=>select oid,datname from pg_database where datname = 'postgres'; 
  oid  | datname  
-------+----------
 13447 | postgres
(1 row)

postgres@postgres=>select relname,relfilenode from pg_class where relname='test'; 
 relname | relfilenode 
---------+-------------
 test    |       16421
(1 row)
  • 普通用户使用表空间
sql
-- 默认对ts01没有权限
database2=> create table t2(id text) tablespace ts01;
ERROR:  permission denied for tablespace ts01

database2=> \c - postgres
You are now connected to database "database2" as user "postgres".

database2=# alter database database2 set tablespace ts01; --连接在上面不能进行更改
ERROR:  cannot change the tablespace of the currently open database

database2=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
-- 默认对ts01没有权限
database2=> create table t2(id text) tablespace ts01;
ERROR:  permission denied for tablespace ts01

database2=> \c - postgres
You are now connected to database "database2" as user "postgres".

database2=# alter database database2 set tablespace ts01; --连接在上面不能进行更改
ERROR:  cannot change the tablespace of the currently open database

database2=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
  • 设置默认表空间
sql
postgres=# alter database database2 set tablespace ts01;
ALTER DATABASE

postgres=# \c database2 user2
You are now connected to database "database2" as user "user2".

database2=> create table t2(id text) tablespace ts01;
CREATE TABLE
postgres=# alter database database2 set tablespace ts01;
ALTER DATABASE

postgres=# \c database2 user2
You are now connected to database "database2" as user "user2".

database2=> create table t2(id text) tablespace ts01;
CREATE TABLE

9.1删除表空间 ​

sql
语法：
postgres=# \h drop tablespace
Command:     DROP TABLESPACE
Description: remove a tablespace
Syntax:
DROP TABLESPACE [ IF EXISTS ] name
语法：
postgres=# \h drop tablespace
Command:     DROP TABLESPACE
Description: remove a tablespace
Syntax:
DROP TABLESPACE [ IF EXISTS ] name
sql
-- 删除表空间前必须要删除该表空间下的所有数据库对象，否则无法删除
postgres@postgres=>drop tablespace if exists tbs_test;
ERROR:  tablespace "tbs_test" is not empty

-- 删除表
postgres@postgres=>drop table if exists test;
DROP TABLE

-- 删除刚才在此表空间创建的表test，然后再删除表空间
postgres@postgres=>drop tablespace if exists tbs_test;
DROP TABLESPACE
-- 删除表空间前必须要删除该表空间下的所有数据库对象，否则无法删除
postgres@postgres=>drop tablespace if exists tbs_test;
ERROR:  tablespace "tbs_test" is not empty

-- 删除表
postgres@postgres=>drop table if exists test;
DROP TABLE

-- 删除刚才在此表空间创建的表test，然后再删除表空间
postgres@postgres=>drop tablespace if exists tbs_test;
DROP TABLESPACE

9.2授权新用户表空间 ​

sql
--授权,管理员身份
postgres=# grant create on tablespace 表空间名字 to username; 
GRANT
--授权,管理员身份
postgres=# grant create on tablespace 表空间名字 to username; 
GRANT

#表空间对应与文件系统 PGDATA下的pg_tblspc目录下存放的是表空间的链接

bash
[postgres@fnddb pg_tblspc]$ pwd
/var/lib/pgsql/data/pg_tblspc
[postgres@fnddb pg_tblspc]$ ll
lrwxrwxrwx. 1 postgres postgres 21 Feb  6 22:52 16423 -> /var/lib/pgsql/tsdata
lrwxrwxrwx. 1 postgres postgres 23 Feb  6 23:04 16425 -> /var/lib/pgsql/tsdata02
[postgres@fnddb pg_tblspc]$ pwd
/var/lib/pgsql/data/pg_tblspc
[postgres@fnddb pg_tblspc]$ ll
lrwxrwxrwx. 1 postgres postgres 21 Feb  6 22:52 16423 -> /var/lib/pgsql/tsdata
lrwxrwxrwx. 1 postgres postgres 23 Feb  6 23:04 16425 -> /var/lib/pgsql/tsdata02

#初始化cluster时默认的表空间说明 database cluster初始化时,默认创建两个表空间

  • pg_global --系统字典表都存这里
  • pg_default --template0,template1默认表空间,因此通过create database 不带tablespace参数创建的数据库都使用此表空间

9.3 表空间迁移 ​

postgresql 的表空间其实就是操作系统的一个文件夹.有时候需要将热点数据移动到SSD盘.就会在数据库层面创建一个表空间指向SSD的文件夹目录

  • 查看表空间
sql
postgres=# \l+

--或者
postgres=# \db+

--或者 查看单独
postgres=# \db+ table_space_name
postgres=# \l+

--或者
postgres=# \db+

--或者 查看单独
postgres=# \db+ table_space_name

准备数据 ​

postgres=# create table tmp_t1 as select * from tmp_t0;

postgres=# create table tbl_test (id int, info text, c_time timestamp);
CREATE TABLE
postgres=# insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp() ;
INSERT 0 100000
postgres=# create table tmp_t1 as select * from tmp_t0;

postgres=# create table tbl_test (id int, info text, c_time timestamp);
CREATE TABLE
postgres=# insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp() ;
INSERT 0 100000

pg_default 的 Size 由 20m 增长到 30m

sql
postgres=# \d+ tbl_test
                                            Table "public.tbl_test"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 info   | text                        |           |          |         | extended |              | 
 c_time | timestamp without time zone |           |          |         | plain    |              | 
Access method: heap

-- 创建索引
postgres=# create index idx_tbl_t1_x1 on tbl_test using btree (id);
CREATE INDEX
postgres=# \d+ tbl_test
                                            Table "public.tbl_test"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 info   | text                        |           |          |         | extended |              | 
 c_time | timestamp without time zone |           |          |         | plain    |              | 
Indexes:
    "idx_tbl_t1_x1" btree (id)
Access method: heap
postgres=# \d+ tbl_test
                                            Table "public.tbl_test"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 info   | text                        |           |          |         | extended |              | 
 c_time | timestamp without time zone |           |          |         | plain    |              | 
Access method: heap

-- 创建索引
postgres=# create index idx_tbl_t1_x1 on tbl_test using btree (id);
CREATE INDEX
postgres=# \d+ tbl_test
                                            Table "public.tbl_test"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 info   | text                        |           |          |         | extended |              | 
 c_time | timestamp without time zone |           |          |         | plain    |              | 
Indexes:
    "idx_tbl_t1_x1" btree (id)
Access method: heap

添加索引之后，表空间大小，增加3M

创建新的表空间 ​

sql
postgres=# create tablespace tbs1 location '/home/ptgres/ts_primary';
CREATE TABLESPACE

-- 查看表空间位置
[ptgres@pg02 data]$ tree pg_tblspc
pg_tblspc
└── 16402 -> /home/ptgres/ts_primary

1 directory, 0 files
postgres=# create tablespace tbs1 location '/home/ptgres/ts_primary';
CREATE TABLESPACE

-- 查看表空间位置
[ptgres@pg02 data]$ tree pg_tblspc
pg_tblspc
└── 16402 -> /home/ptgres/ts_primary

1 directory, 0 files

移动表 ​

sql
postgres=# alter table tbl_test set tablespace tbs1 ;
ALTER TABLE
postgres=# \dt+ tbl_test;
postgres=# alter table tbl_test set tablespace tbs1 ;
ALTER TABLE
postgres=# \dt+ tbl_test;

[!WARNING] 注意：

可以看到 tbs1 的 Size 已经由 0 bytes 增长到 7512 kB.同时 pg_default 的 Size 由 33 MB 降到 25 MB

此时虽然 tbl_test 的数据已经从 pg_default 移动到 tbs1,但是 idx_tmp_t1_x1 依旧存在 pg_default 里. 需要重建下index

重建下index ​

sql
postgres=# \d+ tbl_test;
                                            Table "public.tbl_test"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 info   | text                        |           |          |         | extended |              | 
 c_time | timestamp without time zone |           |          |         | plain    |              | 
Indexes:
    "idx_tbl_t1_x1" btree (id)
    "idx_tmp_t1_x2" btree (id), tablespace "tbs1"
Tablespace: "tbs1"
Access method: heap

postgres=# drop index idx_tbl_t1_x1 ;
DROP INDEX
postgres=# \d+ tbl_test;
                                            Table "public.tbl_test"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 info   | text                        |           |          |         | extended |              | 
 c_time | timestamp without time zone |           |          |         | plain    |              | 
Indexes:
    "idx_tmp_t1_x2" btree (id), tablespace "tbs1"
Tablespace: "tbs1"
Access method: heap

postgres=# alter index idx_tmp_t1_x2 rename to idx_tbl_t1_x1;
ALTER INDEX
postgres=# \d+ tbl_test;
                                            Table "public.tbl_test"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 info   | text                        |           |          |         | extended |              | 
 c_time | timestamp without time zone |           |          |         | plain    |              | 
Indexes:
    "idx_tbl_t1_x1" btree (id), tablespace "tbs1"
Tablespace: "tbs1"
Access method: heap

postgres=# \db+
                                          List of tablespaces
    Name    |  Owner   |        Location         | Access privileges | Options |  Size   | Description 
------------+----------+-------------------------+-------------------+---------+---------+-------------
 pg_default | postgres |                         |                   |         | 23 MB   | 
 pg_global  | postgres |                         |                   |         | 623 kB  | 
 tbs1       | postgres | /home/ptgres/ts_primary |                   |         | 9720 kB | 
(3 rows)
postgres=# \d+ tbl_test;
                                            Table "public.tbl_test"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 info   | text                        |           |          |         | extended |              | 
 c_time | timestamp without time zone |           |          |         | plain    |              | 
Indexes:
    "idx_tbl_t1_x1" btree (id)
    "idx_tmp_t1_x2" btree (id), tablespace "tbs1"
Tablespace: "tbs1"
Access method: heap

postgres=# drop index idx_tbl_t1_x1 ;
DROP INDEX
postgres=# \d+ tbl_test;
                                            Table "public.tbl_test"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 info   | text                        |           |          |         | extended |              | 
 c_time | timestamp without time zone |           |          |         | plain    |              | 
Indexes:
    "idx_tmp_t1_x2" btree (id), tablespace "tbs1"
Tablespace: "tbs1"
Access method: heap

postgres=# alter index idx_tmp_t1_x2 rename to idx_tbl_t1_x1;
ALTER INDEX
postgres=# \d+ tbl_test;
                                            Table "public.tbl_test"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 info   | text                        |           |          |         | extended |              | 
 c_time | timestamp without time zone |           |          |         | plain    |              | 
Indexes:
    "idx_tbl_t1_x1" btree (id), tablespace "tbs1"
Tablespace: "tbs1"
Access method: heap

postgres=# \db+
                                          List of tablespaces
    Name    |  Owner   |        Location         | Access privileges | Options |  Size   | Description 
------------+----------+-------------------------+-------------------+---------+---------+-------------
 pg_default | postgres |                         |                   |         | 23 MB   | 
 pg_global  | postgres |                         |                   |         | 623 kB  | 
 tbs1       | postgres | /home/ptgres/ts_primary |                   |         | 9720 kB | 
(3 rows)
-- 待验证
postgres=# begin;
drop index idx_tbl_t1_x1;
alter index idx_tmp_t1_x2 rename to idx_tbl_t1_x1;
commit;
-- 待验证
postgres=# begin;
drop index idx_tbl_t1_x1;
alter index idx_tmp_t1_x2 rename to idx_tbl_t1_x1;
commit;

结束

参考: https://www.postgresql.org/docs/10/manage-ag-tablespaces.html

https://www.postgresql.org/docs/10/sql-createtablespace.html

https://www.postgresql.org/docs/10/sql-altertablespace.html