学习目标
表空间是数据的容器。掌握表空间的管理，包括创建表空间、删除表空间、重命名表空间、查看表空间的情况。
pg_tablespace_size(name)描述：指定名称的表空间使用的磁盘空间。返回值类型：bigint备注：pg_tablespace_size接受一个数据库的OID或者名称，然后返回该对象使用的全部磁盘空间。
pg_relation_size(oid)描述：指定OID代表的表或者索引所使用的磁盘空间。返回值类型：bigint
pg_size_pretty(bigint)描述：将以64位整数表示的字节值转换为具有单位的易读格式。返回值类型：text
数据库对象函数
课程学习
- 通过使用表空间，管理员可以控制一个数据库安装的磁盘布局。
- 表空间对应于一个文件系统目录，假定数据库节点数据目录/pg_location/mount1/path1是用户拥有读写权限的空目录。
- openGauss自带了两个表空间：pg_default和pg_global。
- 默认表空间pg_default：用来存储非共享系统表、用户表、用户表index、临时表、临时表index、内部临时表的默认表空间。对应存储目录为实例数据目录下的base目录。
- 共享表空间pg_global：用来存放共享系统表的表空间。对应存储目录为实例数据目录下的global目录。
课程练习
0、环境准备
openGauss=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
postgres | omm | SQL_ASCII | C | C |
template0 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
(3 rows)
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)
1、创建表空间t_tbspace、用户test，并使用test，在这个表空间上创建表t1
1) 创建表空间t_tbspace
openGauss=# create tablespace t_tbspace relative location 'tablespace/t_tbspace';
CREATE TABLESPACE
2) 创建用户test,将“t_tbspace”表空间的访问权限赋予数据用户test。
openGauss=# create user test identified by 'kunpeng@1234';
CREATE ROLE
openGauss=# grant create on tablespace t_tbspace to test;
GRANT
3) 使用用户test登录,创建t1表
openGauss=# \c postgres test
Password for user test:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "test".
openGauss=> create table t1(col1 char(10)) tablespace t_tbspace;
CREATE TABLE
2、查看表空间t_tbspace的oid和大小
1) test用户查看表空间t_tbspace的oid和大小
openGauss=> \conninfo 查看到当前用户为test
You are connected to database "postgres" as user "test" via socket in "/opt/huawei/tmp" at port "26000".
openGauss=> select pg_tablespace_size('t_tbspace'); 查询表空间大小
pg_tablespace_size
--------------------
8192
(1 row)
openGauss=> select oid,* from pg_tablespace
openGauss-> ;
oid | spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
-------+------------+----------+------------------------+------------+------------+----------
1663 | pg_default | 10 | | | | f
1664 | pg_global | 10 | | | | f
16515 | t_tbspace | 10 | {omm=C/omm,test=C/omm} | | | t
(3 rows)
2) omm用户查看表空间t_tbspace的oid和大小
openGauss=> \c postgres omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
openGauss=# select pg_tablespace_size('t_tbspace');
pg_tablespace_size
--------------------
8192
(1 row)
openGauss=# select oid,* from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
-------+------------+----------+------------------------+------------+------------+----------
1663 | pg_default | 10 | | | | f
1664 | pg_global | 10 | | | | f
16515 | t_tbspace | 10 | {omm=C/omm,test=C/omm} | | | t
(3 rows)
3、查看数据库在默认表空间下有哪些对象
openGauss=# WITH TV_OBJECTINDEFAULTTS AS
openGauss-# (SELECT RELNAME,
openGauss(# RELKIND,
openGauss(# RELPAGES,
openGauss(# PG_RELATION_SIZE(A.OID),
openGauss(# PG_SIZE_PRETTY(PG_RELATION_SIZE(A.OID)),
openGauss(# RELTABLESPACE,
openGauss(# RELOWNER
openGauss(# FROM PG_CLASS A
openGauss(# WHERE A.RELKIND IN ('r', 'i')
openGauss(# AND RELTABLESPACE = '0')
openGauss-# SELECT *
openGauss-# FROM TV_OBJECTINDEFAULTTS
openGauss-# WHERE RELNAME NOT LIKE 'pg_%'
openGauss-# AND RELNAME NOT LIKE 'gs_%'
openGauss-# AND RELNAME NOT LIKE 'sql_%'
openGauss-# ORDER BY RELPAGES DESC;
relname | relkind | relpages | pg_relation_size | pg_size_pretty | reltablespace | relowner
------------------------------------------+---------+----------+------------------+----------------+---------------+----------
streaming_gather_agg_index | i | 2 | 16384 | 16 kB | 0 | 10
statement_history_time_idx | i | 1 | 8192 | 8192 bytes | 0 | 10
snapshot_id_key | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_cont_query_matrelid_index | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_cont_query_lookupidxid_index | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_cont_query_schema_change_index | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_reaper_status_id_index | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_reaper_status_oid_index | i | 1 | 8192 | 8192 bytes | 0 | 10
snapshot_pkey | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_stream_oid_index | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_stream_relid_index | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_cont_query_relid_index | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_cont_query_defrelid_index | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_cont_query_id_index | i | 1 | 8192 | 8192 bytes | 0 | 10
streaming_cont_query_oid_index | i | 1 | 8192 | 8192 bytes | 0 | 10
plan_table_data | r | 0 | 0 | 0 bytes | 0 | 10
streaming_stream | r | 0 | 0 | 0 bytes | 0 | 10
streaming_reaper_status | r | 0 | 0 | 0 bytes | 0 | 10
snapshot | r | 0 | 0 | 0 bytes | 0 | 10
streaming_cont_query | r | 0 | 0 | 0 bytes | 0 | 10
statement_history | r | 0 | 0 | 0 bytes | 0 | 10
(21 rows)
4、查看数据库在非默认表空间下有哪些对象
openGauss=# SELECT RELNAME,
openGauss-# RELKIND,
openGauss-# RELPAGES,
openGauss-# PG_RELATION_SIZE(A.OID),
openGauss-# PG_SIZE_PRETTY(PG_RELATION_SIZE(A.OID)),
openGauss-# RELTABLESPACE,
openGauss-# RELOWNER
openGauss-# FROM PG_CLASS A, PG_TABLESPACE TB
openGauss-# WHERE A.RELKIND IN ('r', 'i')
openGauss-# AND A.RELTABLESPACE = TB.OID
openGauss-# AND TB.SPCNAME = 't_tbspace'
openGauss-# ORDER BY A.RELPAGES DESC;
relname | relkind | relpages | pg_relation_size | pg_size_pretty | reltablespace | relowner
---------+---------+----------+------------------+----------------+---------------+----------
t1 | r | 0 | 0 | 0 bytes | 16515 | 16516
(1 row)
pg_tablespace_size(name)描述：指定名称的表空间使用的磁盘空间。返回值类型：bigint备注：pg_tablespace_size接受一个数据库的OID或者名称，然后返回该对象使用的全部磁盘空间。
pg_relation_size(oid)描述：指定OID代表的表或者索引所使用的磁盘空间。返回值类型：bigint
pg_size_pretty(bigint)描述：将以64位整数表示的字节值转换为具有单位的易读格式。返回值类型：text
数据库对象函数
5、重命名表空间
1)重命名前查看当前文件系统名称
[omm@hecs-30579 15626]$ ls
16520
[omm@hecs-30579 15626]$ pwd
/opt/huawei/install/data/dn/pg_tblspc/16515/PG_9.2_201611171_dn_6001/15626
...
[omm@hecs-30579 15626]$ ls
16520
[omm@hecs-30579 15626]$ pwd
/opt/huawei/install/data/dn/pg_location/tablespace/t_tbspace/PG_9.2_201611171_dn_6001/15626
2) 重命名表空间
openGauss=# alter tablespace t_tbspace rename to tv_tbs;
ALTER TABLESPACE
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------------------
pg_default | omm |
pg_global | omm |
tv_tbs | omm | tablespace/t_tbspace
(3 rows)
3)重命后前查看当前文件系统名称
[omm@hecs-30579 15626]$ ls
16520
[omm@hecs-30579 15626]$ pwd
/opt/huawei/install/data/dn/pg_location/tablespace/t_tbspace/PG_9.2_201611171_dn_6001/15626
...
[omm@hecs-30579 15626]$ ls
16520
[omm@hecs-30579 15626]$ pwd
/opt/huawei/install/data/dn/pg_tblspc/16515/PG_9.2_201611171_dn_6001/15626
6、删除表空间
1)非空表空间删除时提示非空
openGauss=# drop tablespace if exists tv_tbs;
ERROR: tablespace "tv_tbs" is not empty
2)删除表空间内的对象,再删除表空间
a) 尝试用非owner用户删除表空间,失败
openGauss=# \c postgres test;
Password for user test:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "test".
openGauss=>
openGauss=>
openGauss=> drop tablespace tv_tbs;
ERROR: permission denied for tablespace tv_tbs
DETAIL: N/A
b) 将非owner用户授权sysadmin,再次尝试删除,提示为表空间非空,删除表空间内对象后表空间删除成功
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
openGauss=# alter user test sysadmin;
ALTER ROLE
openGauss=# \c postgres test;
openGauss=> drop tablespace tv_tbs;
ERROR: tablespace "tv_tbs" is not empty
openGauss=> drop table t1;
DROP TABLE
openGauss=> drop tablespace tv_tbs;
DROP TABLESPACE
拓展实验
表空间可以创建在文件系统的一个空目录,如果目录非空能否创建
原本设想创建表空间删除后留下的空目录,在其中touch一个文件,然后create tablespace时指定原路径,看会提示什么,实操过程以为表空间enmotbs已经drop(其实还没有),就在文件系统目录下touch tv.test.notempty
[omm@hecs-30579 PG_9.2_201611171_dn_6001]$ ls
pgsql_tmp tv.test.notempty
创建表空间指定非空目录,得到提示linkpath冲突
openGauss=# create tablespace tbs_test relative location 'tablespace/enmotbs';
ERROR: find conflict linkpath in pg_tblspc, try a different path.
查看表空间情况,才发现enmotbs还没有DROP
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-------+--------------------
app_ts | omm | tablespace/app_ts1
enmotbs | omm | tablespace/enmotbs
pg_default | omm |
pg_global | omm |
(4 rows)
尝试drop tablespace enmotbs,touch的文件被报错了
openGauss=# drop tablespace enmotbs;
WARNING: open directory failed "/opt/huawei/install/data/dn/pg_location/tablespace/enmotbs/PG_9.2_201611171_dn_6001/tv.test.notempty"
ERROR: could not open directory "pg_tblspc/16523/PG_9.2_201611171_dn_6001/tv.test.notempty": Not a directory
在gsql下通过! rm删除文件,没有确认提示,文件直接被删除
openGauss=# \! rm /opt/huawei/install/data/dn/pg_location/tablespace/enmotbs/PG_9.2_201611171_dn_6001/tv.test.notempty
openGauss=# \! ls /opt/huawei/install/data/dn/pg_location/tablespace/enmotbs/PG_9.2_201611171_dn_6001
pgsql_tmp
再次drop tablespace,操作成功
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-------+--------------------
app_ts | omm | tablespace/app_ts1
pg_default | omm |
pg_global | omm |
(3 rows)
按照最开始的设想再次实验,先在/opt/huawei/install/data/dn/pg_location/tablespace/enmotbs目录下touch一个文件
[omm@hecs-30579 enmotbs]$ pwd
/opt/huawei/install/data/dn/pg_location/tablespace/enmotbs
[omm@hecs-30579 enmotbs]$ ls
test.notempty
[omm@hecs-30579 enmotbs]$ cat test.notempty
this is a test text.
[omm@hecs-30579 enmotbs]$
尝试创建表空间,指定非空路径,提示创建成功
openGauss=# create tablespace tv_tbs relative location 'tablespace/enmotbs';
CREATE TABLESPACE
查看文件系统,发现在目录下创建了PG_9.2_201611171_dn_6001目录
[omm@hecs-30579 enmotbs]$ pwd
/opt/huawei/install/data/dn/pg_location/tablespace/enmotbs
[omm@hecs-30579 enmotbs]$ ls
PG_9.2_201611171_dn_6001 test.notempty
[omm@hecs-30579 enmotbs]$
再试在文件系统创建目录?还是可以理解为创建表空间时拥有文件系统创建的目录的权限就可以将命令执行成功?
最后修改时间：2022-12-05 16:07:38
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
