概述

本文档记录openGauss 3.0.0数据库每日一练第8天课程作业，学习表空间与数据库对象的关系。

课程练习

创建表空间newtbs1、 ds_location1，查看表空间

omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_2';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#\db
              List of tablespaces
     Name     | Owner |        Location         
--------------+-------+-------------------------
 ds_location1 | omm   | tablespace/tablespace_2
 music_tbs    | omm   | tablespace/zsdba
 music_tbs1   | omm   | tablespace/zsdba1
 newtbs1      | omm   | tablespace/tablespace_1
 pg_default   | omm   | 
 pg_global    | omm   | 
(6 rows)
复制

创建一个数据库newdb1，默认表空间为newtbs1

omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE newdb1  WITH TABLESPACE = newtbs1;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid;
  datname  | dattablespace |  spcname   
-----------+---------------+------------
 template1 |          1663 | pg_default
 zsdba     |          1663 | pg_default
 template0 |          1663 | pg_default
 music_db  |         16394 | music_tbs1
 music_db1 |         16394 | music_tbs1
 music_db2 |         16394 | music_tbs1
 postgres  |          1663 | pg_default
 musicdb10 |          1663 | pg_default
 musicdb6  |          1663 | pg_default
 musicdb7  |          1663 | pg_default
 newdb1    |         16469 | newtbs1  <==========
(11 rows)
复制

创建用户user5，并授予SYSADMIN权限

omm@local:/opt/huawei/tmp [postgres]=#create user user5 with sysadmin identified by 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#\c newdb1 user5
Password for user user5: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "newdb1" as user "user5".
user5@local:/opt/huawei/tmp [newdb1]=>create table newt1 (id int,name char(10)) tablespace ds_location1;
CREATE TABLE
user5@local:/opt/huawei/tmp [newdb1]=>select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner  
newdb1-> from pg_class a, pg_tablespace tb  
newdb1-> where a.relkind in ('r', 'i')  
newdb1-> and a.reltablespace=tb.oid  
newdb1-> and tb.spcname='ds_location1'  
newdb1-> order by a.relpages desc;
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 newt1   | r       |        0 | 0 bytes        |         16470 |    16472
(1 row)
复制

查看表所在的表空间

user5@local:/opt/huawei/tmp [newdb1]=>\x
Expanded display is on.
user5@local:/opt/huawei/tmp [newdb1]=>select * from pg_tables where tablename = 'newt1';
-[ RECORD 1 ]-+------------------------------
schemaname    | public
tablename     | newt1         <=============================
tableowner    | user5
tablespace    | ds_location1  <=============================
hasindexes    | f
hasrules      | f
hastriggers   | f
tablecreator  | user5
created       | 2022-12-01 11:25:44.724133+08
last_ddl_time | 2022-12-01 11:25:44.724133+08
复制

查看表空间newtbs1、 ds_location1上的对象

user5@local:/opt/huawei/tmp [newdb1]=>create table newt2 (id int,name char(10)) tablespace newtbs1;
CREATE TABLE
user5@local:/opt/huawei/tmp [newdb1]=>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'  
and relname = 'newt2'
order by a.relpages desc;
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 newt2   | r       |        0 | 0 bytes        |             0 |    16472
(1 row)

user5@local:/opt/huawei/tmp [newdb1]=>select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid;
  datname  | dattablespace |  spcname   
-----------+---------------+------------
 template1 |          1663 | pg_default
 zsdba     |          1663 | pg_default
 template0 |          1663 | pg_default
 music_db  |         16394 | music_tbs1
 music_db1 |         16394 | music_tbs1
 music_db2 |         16394 | music_tbs1
 postgres  |          1663 | pg_default
 musicdb10 |          1663 | pg_default
 musicdb6  |          1663 | pg_default
 musicdb7  |          1663 | pg_default
 newdb1    |         16469 | newtbs1   <==============
(11 rows)

user5@local:/opt/huawei/tmp [newdb1]=>select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),tb.spcname,reltablespace,relowner  
newdb1-> from pg_class a, pg_tablespace tb  
newdb1-> where  a.reltablespace=tb.oid  
newdb1-> and tb.spcname in ('ds_location1','newtbs1')
newdb1-> order by a.relpages desc;
 relname | relkind | relpages | pg_size_pretty |   spcname    | reltablespace | relowner 
---------+---------+----------+----------------+--------------+---------------+----------
 newt1   | r       |        0 | 0 bytes        | ds_location1 |         16470 |    16472
(1 row)

user5@local:/opt/huawei/tmp [newdb1]=>select t.oid,t.* from pg_tablespace t;
  oid  |   spcname    | spcowner | spcacl | spcoptions | spcmaxsize | relative 
-------+--------------+----------+--------+------------+------------+----------
  1663 | pg_default   |       10 |        |            |            | f
  1664 | pg_global    |       10 |        |            |            | f
 16393 | music_tbs    |       10 |        |            |            | t
 16394 | music_tbs1   |       10 |        |            |            | t
 16469 | newtbs1      |       10 |        |            |            | t <=====
 16470 | ds_location1 |       10 |        |            |            | t <=====
(6 rows)

user5@local:/opt/huawei/tmp [newdb1]=>select relname,reltablespace from pg_class where relname in ('newt1','newt2');
 relname | reltablespace 
---------+---------------
 newt1   |         16470
 newt2   |             0
(2 rows)

复制

注：数据库newdb1默认表空间newtbs1，表newt1使用表空间ds_location1，表newt2使用表空间newtbs1，在pg_class视图中，显示的reltablespace。