openGauss 每日一练第 10 天｜openGauss 逻辑结构：表空间管理

原创代野（Tank） 2022-12-03

675

第 10 天学习打卡，第 3 课和第 8 课已经对表空间的概念做了部分介绍，第 10 天的内容正式开始了对标空间的学习。本课学习目标是“掌握表空间的管理，包括创建表空间、删除表空间、重命名表空间、查看表空间的情况”。

在线手册入口：创建和管理表空间。

作业

创建表空间 t_tbspace、用户 test，并使用 test，在这个表空间上创建表 t1

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# CREATE TABLESPACE t_tbspace RELATIVE LOCATION 'tablespace/t_tbspace1';
CREATE TABLESPACE
omm=# CREATE USER test IDENTIFIED BY 'Tank@Modb';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

omm=# GRANT CREATE ON TABLESPACE t_tbspace TO test;
GRANT
omm=# \c omm test
Password for user test: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "test".
omm=> CREATE TABLE foo(i int) TABLESPACE t_tbspace;
CREATE TABLE
omm=>

查看表空间 t_tbspace 的 oid 和大小

omm=# select oid,* from pg_tablespace;
  oid  |  spcname   | spcowner |         spcacl         | spcoptions | spcmaxsize | relative 
-------+------------+----------+------------------------+------------+------------+----------
  1663 | pg_default |       10 |                        |            |            | f
  1664 | pg_global  |       10 |                        |            |            | f
 16389 | t_tbspace  |       10 | {omm=C/omm,test=C/omm} |            |            | t
(3 rows)

查看数据库在默认表空间下有哪些对象

with objectInDefaultTS as
          ( select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),
                  reltablespace,relowner
            from pg_class a
            where a.relkind in ('r', 'i')  and reltablespace='0'
         )
 select * 
 from objectInDefaultTS 
   where relname not like 'pg_%' and relname not like 'gs_%' and relname not like 'sql_%'
 order by relpages desc;

查看数据库在非默认表空间下有哪些对象

omm=# select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),
omm-# omm-#          reltablespace,relowner
omm-#   from pg_class a, pg_tablespace tb
omm-#   where a.relkind in ('r', 'i')
  and a.reltablespace=tb.oid
omm-#   and tb.spcname='t_tbspace'
omm-#   order by a.relpages desc;
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 foo     | r       |        0 | 0 bytes        |         16389 |    16390
(1 row)

重命名表空间

omm=# ALTER TABLESPACE t_tbspace RENAME TO app_tbs;
ALTER TABLESPACE
omm=# \db
(3 rows)

omm=#             List of tablespaces
    Name    | Owner |       Location      
------------+-------+-----------------------
 app_tbs    | omm   | tablespace/t_tbspace1
 pg_default | omm   | 
 pg_global  | omm   | 

omm=#

删除表空间

-- 用户必须是表空间的 owner 或者系统管理员才能删除表空间。需要先删除表空间的对象，再删除表空间app_ts：

omm=# drop table test.foo;
DROP TABLE
omm=# DROP TABLESPACE app_tbs;
DROP TABLESPACE
omm=# \db
      List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default | omm   | 
 pg_global  | omm   | 
(2 rows)

命令总结

创建表空间

CREATE USER jack IDENTIFIED BY 'xxxxxxxxx';

查看表空间

SELECT spcname FROM pg_tablespace;

\db

查询表空间使用情况

SELECT PG_TABLESPACE_SIZE('example');

重命名表空间

ALTER TABLESPACE fastspace RENAME TO fspace;

删除表空间

DROP TABLESPACE fspace;

查看数据库在默认表空间下有哪些对象

with objectInDefaultTS as
          ( select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),
                  reltablespace,relowner
            from pg_class a
            where a.relkind in ('r', 'i')  and reltablespace='0'
         )
 select * 
 from objectInDefaultTS 
   where relname not like 'pg_%' and relname not like 'gs_%' and relname not like 'sql_%'
 order by relpages desc;

查看数据库在非默认表空间下有哪些对象

select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),
          reltablespace,relowner
  from pg_class a, pg_tablespace tb
  where a.relkind in ('r', 'i')
  and a.reltablespace=tb.oid
  and tb.spcname='t_tbspace'
  order by a.relpages desc;