openGauss每日一练第8天 | 一个数据库可以存储在多个表空间

原创张猛 2022-12-01

410

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

--进入数据库omm，创建测试表空间newtbs1、 ds_location1
[omm@ogdb1 ~]$ gsql -d omm -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

--查看当前表空间
omm=# \db
            List of tablespaces
    Name     | Owner |      Location       
-------------+-------+---------------------
 music_tbs   | omm   | tablespace/test_ts1
 music_tbs07 | omm   | tablespace/_ts07
 pg_default  | omm   | 
 pg_global   | omm   | 
(4 rows)


--创建测试表空间newtbs1、ds_location1
omm=# create tablespace newtbs1 relative location 'tablespace/newtbs1_01';
CREATE TABLESPACE
omm=# 
omm=# create tablespace ds_location1 relative location 'tablespace/ds_location1_01';
CREATE TABLESPACE
omm=# 

--再次查询当前表空间
omm=# \db
                List of tablespaces
     Name     | Owner |          Location          
--------------+-------+----------------------------
 ds_location1 | omm   | tablespace/ds_location1_01
 music_tbs    | omm   | tablespace/test_ts1
 music_tbs07  | omm   | tablespace/_ts07
 newtbs1      | omm   | tablespace/newtbs1_01
 pg_default   | omm   | 
 pg_global    | omm   | 
(6 rows)

复制

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

--创建测试数据库newdb1,并查看当前数据库
omm=# create database newdb1 with tablespace=newtbs1;
CREATE DATABASE

omm=# \l+
                                                           List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges | Size  | Tablespace  |                Description                 
-----------+-------+-----------+---------+-------+-------------------+-------+-------------+--------------------------------------------
 musicdb07 | omm   | SQL_ASCII | C       | C     |                   | 13 MB | music_tbs07 | 
 newdb1    | omm   | SQL_ASCII | C       | C     |                   | 13 MB | newtbs1     | 
 omm       | omm   | SQL_ASCII | C       | C     |                   | 13 MB | pg_default  | 
 postgres  | omm   | SQL_ASCII | C       | C     |                   | 17 MB | pg_default  | default administrative connection database
 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +| 13 MB | pg_default  | default template for new databases
           |       |           |         |       | omm=CTc/omm       |       |             | 
 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +| 13 MB | pg_default  | unmodifiable empty database
           |       |           |         |       | omm=CTc/omm       |       |             | 
(6 rows)

omm=# select datname, dattablespace, spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid;
  datname  | dattablespace |   spcname   
-----------+---------------+-------------
 template1 |          1663 | pg_default
 omm       |          1663 | pg_default
 template0 |          1663 | pg_default
 postgres  |          1663 | pg_default
 musicdb07 |         16453 | music_tbs07
 newdb1    |         16476 | newtbs1
(6 rows)
复制

3.创建用户user5，并授予SYSADMIN权限，访问数据库newdb1，在表空间ds_location1上，创建一个表newt1（表结构自定义）

--执行下面的SQL语句，创建用户user5,授予user5数据库系统的SYSADMIN权限：
omm=# create user user5 identified by 'aabb@123';
CREATE ROLE
omm=# alter user user5 sysadmin;
ALTER ROLE

--使用用户user5访问数据库newdb1, 并创建测试表。
omm=# \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".

newdb1=> create table newt1 (col1 char(10)) tablespace ds_location1;
CREATE TABLE
newdb1=> 
复制

4.查看表所在的表空间

--查看表所在的表空间
newdb1=> select * from pg_tables where tablename='newt1';
 schemaname | tablename | tableowner |  tablespace  | hasindexes | hasrules | hastriggers | tablecreator |            created            |         last_ddl_time         
------------+-----------+------------+--------------+------------+----------+-------------+--------------+-------------------------------+-------------------------------
 public     | newt1     | user5      | ds_location1 | f          | f        | f           | user5        | 2022-12-01 19:31:56.964858+08 | 2022-12-01 19:31:56.964858+08
(1 row)

复制

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

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

newdb1=> 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='ds_location1' order by a.relpages desc;
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 newt1   | r       |        0 | 0 bytes        |         16477 |    16479
(1 row)
复制

5.2、查看表空间newtbs1上的对象，

--创建测试表，未指定表空间。
newdb1=> create table newt2(col2 varchar(20));
CREATE TABLE

--创建测试表，指定表空间。
newdb1=> create table newt3(col2 varchar(20)) tablespace newtbs1;
newdb1=> insert into newt3 values('aa');
INSERT 0 1

--查看表空间对象，截图如下，可以看到两个新建对象。
newdb1=> select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner  
newdb1-> from pg_class a  
newdb1-> where a.relkind in ('r', 'i')  
newdb1-> and reltablespace='0'  
newdb1-> order by a.relpages desc;
复制