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

openGauss中一个数据库中可以存储在多个表空间中。一个数据库中的对象可以位于不同的表空间。

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

su - omm
gsql -r

CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';

--使用user1登录musicdb数据库
\c musicdb user1

--创建表空间newtbs1
musicdb=> create tablespace newtbs1 relative location 'tablespace/tablespace_2'; 
CREATE TABLESPACE

--查看存在的所有表空间
musicdb=> \db
              List of tablespaces
     Name     | Owner |        Location         
--------------+-------+-------------------------
 ds_location1 | omm   | tablespace/tablespace_1
 music_tbs    | omm   | tablespace/test_ts1
 newtbs1      | user1 | tablespace/tablespace_2
 pg_default   | omm   | 
 pg_global    | omm   | 
(5 rows)

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

--创建数据库newdb1，并指定数据库默认表空间newtbs1
musicdb=> create database newdb1 with tablespace newtbs1;  
musicdb=> CREATE DATABASE
musicdb=> 

或者

create database newdb1 with tablespace = newtbs1;

--查看数据库默认表空间
newdb1=> select datname,dattablespace,spcname from pg_database d, pg_tablese t where d.dattablespace=t.oid;
  datname  | dattablespace |  spcname   
-----------+---------------+------------
 template1 |          1663 | pg_default
 omm       |          1663 | pg_default
 musicdb   |         16389 | music_tbs
 template0 |          1663 | pg_default
 newdb1    |         16399 | newtbs1
 postgres  |          1663 | pg_default
newdb1=> (6 rows)

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

musicdb=> create user user5 identified by 'Kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

musicdb=> alter user user5 sysadmin;
ALTER ROLE

musicdb=> \c newdb1 user5
Password for user user1: 
newdb1=> Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "newdb1" as user "user5".

newdb1=> select current_database(),current_user,current_schema;
 current_database | current_user | current_schema 
------------------+--------------+----------------
 newdb1           | user5        | public
(1 row)


newdb1=> create table test1(c1 varchar(20));  
CREATE TABLE
newdb1=> create table newt1(c1 varchar(20)) tablespace ds_location1;  
CREATE TABLE
newdb1=> \dt
                         List of relations
 Schema | Name  | Type  | Owner |             Storage              
--------+-------+-------+-------+----------------------------------
 public | newt1 | table | user5 | {orientation=row,compression=no}
 public | test1 | table | user1 | {orientation=row,compression=no}
(2 rows)


--查看newdb1数据库下有哪些表
select table_catalog, table_schema, table_name, table_type
  from information_schema.tables
   where table_schema not in ('pg_catalog', 'information_schema','dbe_perf');

4、查看表所在的表空间

系统表在默认表空间，非系统表在指定的表空间中（否则在默认表空间）。

--创建表test1未指定表空间，则在默认表空间（不显示默认表空间名）
select * from pg_tables where tablename='test1';

--看到表newt1表空间是ds_location1
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 20:13:18.596092+08 | 2022-12-01 20:13:
18.596092+08
(1 row)

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'  
order by a.relpages desc;

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

--查看表空间newtbs1下的对象
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='newtbs1'  
order by a.relpages desc;


--查看表空间ds_location1下的对象
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;