Postgresql 12 Tablespace Study 004 Tips
Postgresql 表空间
表空间
表空间是逻辑概念
我们的数据需要存放在介质中或者文件目录中，这个需要用表空间来指定一个存储目录
-
如果初始化集簇所在的分区或者卷用光了空间，而又不能在逻辑上扩展或者做别的什么操作，那么表空间可以被创建在一个不同的分区上，直到系统可以被重新配置。
-
表空间允许管理员根据数据库对象的使用模式来优化性能。例如，一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上，如一种非常贵的固态设备。同时，一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统上。
Oracle 和 Postgresql比较 Oracle 表空间 | Postgresql 表空间 | 异同 —|—|— 一个表空间只属于一个数据库使用|一个表空间可以让多个数据库使用| 不同 一个数据库可以拥有多个表空间|一个数据库可以使用多个表空间| 相同 与数据库的关系是一对多|与数据库的关系是多对多| 不同
创建完表空间之后，create database/table/index时候就可以指定表空间，把这些对象存储在这特指的表空间。
创建表空间
语法:
postgres=# \h create tablespace
Command: CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
[ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
LOCATION 'directory'
[ WITH ( tablespace_option = value [, ... ] ) ]
postgres=#
tablespace_name : 表空间名,不能以pg_开头，否则报如下错误
postgres=# create tablespace pg_tbs01 location '/var/lib/pgsql/10/data/pg_tbs01';
WARNING: tablespace location should not be inside the data directory
ERROR: unacceptable tablespace name "pg_tbs01"
DETAIL: The prefix "pg_" is reserved for system tablespaces.
postgres=#
owner的 用户名: 指定表空间所属用户名，没指定默认是当前用户
directory : 表空间对应路径，指定路径必须是绝对路径，而且是空目录
postgres=# create tablespace tbs_01 location 'tbs_01';
ERROR: tablespace location must be an absolute path
postgres=#
tablespace_option:
创建表空间示例
- 用root用户创建pgdata目录，然后修改owner为postgres
- 创建tab01表空间
- 列出表空间信息
- 可以看到刚才创建表空间在
$PGDATA/pg_tblspc目录下创建了符号链接到pgdata/10/tab_01目录下 - 注意点:
/pgdata/10/tab_01必须是空目录 - 自动创建子目录
PG_10_201707211(命名方式: PG_主版本号_目录版本号)
-bash-4.2$ mkdir /pgdata/10 -p
mkdir: cannot create directory ‘/pgdata’: Permission denied
-bash-4.2$ exit
logout
[root@5829cabdf441 /]# mkdir -p /pgdata/10/tab_01
[root@5829cabdf441 /]# chown -R postgres.postgres /pgdata
[root@5829cabdf441 /]# su - postgres
Last login: Thu Apr 9 03:24:10 UTC 2020 on pts/0
-bash-4.2$ psql
psql (10.12)
Type "help" for help.
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
postgres=# create tablespace tab_01 location '/pgdata/10/tab_01';
CREATE TABLESPACE
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+-------------------
pg_default | postgres |
pg_global | postgres |
tab_01 | postgres | /pgdata/10/tab_01
(3 rows)
postgres=# select oid,* from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16384 | tab_01 | 10 | |
(3 rows)
postgres=#
在$PGDATA/pg_tblspc目录下有符号链接到/pgdata/10/tab_01/ 通过oid: 16388知道链接到哪个目录。
-bash-4.2$ ls -ltr /pgdata/10/tab_01/
total 0
drwx------ 2 postgres postgres 6 Apr 9 03:36 PG_10_201707211
-bash-4.2$ ls -tlr $PGDATA/pg_tblspc
total 0
lrwxrwxrwx 1 postgres postgres 17 Apr 9 03:36 16384 -> /pgdata/10/tab_01
-bash-4.2$
示例创建tab_02的owner为lyn的表空间
-bash-4.2$ mkdir /pgdata/10/tab_02
-bash-4.2$ psql
psql (10.12)
Type "help" for help.
postgres=#
postgres=# create user lyn;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
lyn | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
postgres=# create tablespace tab_02 owner lyn location '/pgdata/10/tab_02';
CREATE TABLESPACE
postgres=#
删除表空间示例
语法:
postgres=# \h drop tablespace
Command: DROP TABLESPACE
Description: remove a tablespace
Syntax:
DROP TABLESPACE [ IF EXISTS ] name
postgres=#
- 删除刚才创建的表空间
tab_02 - 列出表空间信息
- 第二次删除表空间
tab_02报错 - 第三次删除表空间
tab_02没报错(使用了IF EXISTS) - 活用
IF EXISTS
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+-------------------
pg_default | postgres |
pg_global | postgres |
tab_01 | postgres | /pgdata/10/tab_01
tab_02 | lyn | /pgdata/10/tab_02
(4 rows)
postgres=# drop tablespace tab_02;
DROP TABLESPACE
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+-------------------
pg_default | postgres |
pg_global | postgres |
tab_01 | postgres | /pgdata/10/tab_01
(3 rows)
postgres=# drop tablespace tab_02;
ERROR: tablespace "tab_02" does not exist
postgres=# drop tablespace if exists tab_02;
NOTICE: tablespace "tab_02" does not exist, skipping
DROP TABLESPACE
postgres=#
修改表空间示例
语法:
postgres=# \h alter tablespace
Command: ALTER TABLESPACE
Description: change the definition of a tablespace
Syntax:
ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] )
ALTER TABLESPACE name RESET ( tablespace_option [, ... ] )
postgres=#
- 查看用户 (lyn和postgres)
- 通过
pg_tablespace的spcowner列，查看表空间的属主用户是(postgres) - 修改表空间的owner为
lyn用户 - 重命名表空间为
tab_lyn - 最后删除表空间
tab_lyn
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
lyn | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# select oid,* from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16384 | tab_01 | 10 | |
(3 rows)
postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
lyn | 16385 | f | f | f | f | ******** | |
(2 rows)
postgres=# alter tablespace tab_01 owner to lyn;
ALTER TABLESPACE
postgres=# select oid,* from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16384 | tab_01 | 16385 | |
(3 rows)
postgres=# alter tablespace tab_01 rename to tab_lyn;
ALTER TABLESPACE
postgres=# drop tablespace if exists tab_lyn;
DROP TABLESPACE
postgres=#
Have a good work&life! 2020/09 via LinHong