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

学习目标

进一步学习表空间与数据库对象的关系，了解oid的含义。

oid:

• 对象标识符类型Object Identifier Types ，内部使用，并作为系统表的主键

• 别名类型对查找一个对象的OID非常方便

• 可以在用户定义的表中使用，不推荐使用

• o i d 不 会 添 加 到 用 户 自 己 创 建 的 表 里 ， 除 非 指 定 W I T H O I D S 或 者 d e f a u l t _ w i t h _ o i d s 打 开

• oid2name可以获取数据库、对象的OID

课程学习

1.创建表空间、多个数据库和用户

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，创建表空间、测试数据库
omm=# drop database if exists musicdb;
NOTICE:  database "musicdb" does not exist, skipping
DROP DATABASE
omm=# drop database if exists musicdb1;
NOTICE:  database "musicdb1" does not exist, skipping
DROP DATABASE
omm=# drop database if exists musicdb2;
NOTICE:  database "musicdb2" does not exist, skipping
DROP DATABASE
omm=# drop database if exists musicdb3;
NOTICE:  database "musicdb3" does not exist, skipping
DROP DATABASE
omm=# drop tablespace if exists music_tbs;
NOTICE:  Tablespace "music_tbs" does not exist, skipping.
DROP TABLESPACE
omm=# drop tablespace if exists ds_location1;
NOTICE:  Tablespace "ds_location1" does not exist, skipping.
DROP TABLESPACE
omm=# create tablespace music_tbs relative location 'tablespace/test_ts1';
CREATE TABLESPACE
omm=# create database musicdb with tablespace = music_tbs ;
CREATE DATABASE
omm=# create database musicdb1 with tablespace = music_tbs ;
CREATE DATABASE
omm=# create database musicdb2 with tablespace = music_tbs ;
CREATE DATABASE
omm=# create database musicdb3 with tablespace = music_tbs ;
CREATE DATABASE--执行下面的SQL语句，创建用户user1 ：
omm=# drop user if exists user1;
NOTICE:  role "user1" does not exist, skipping
DROP ROLE
omm=# create user user1 identified by 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE--授予user1数据库系统的SYSADMIN权限：
omm=# alter user user1 sysadmin ;
ALTER ROLE

2.使用sql查看数据库所在的表空间

--查看数据库所在的表空间，可以看到一个表空间可以有多个数据库
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
 musicdb   |         16389 | music_tbs
 template0 |          1663 | pg_default
 musicdb1  |         16389 | music_tbs
 musicdb2  |         16389 | music_tbs
 musicdb3  |         16389 | music_tbs
 postgres  |          1663 | pg_default
(8 rows)

3.在文件系统中查看表空间中的多个数据库

--查看数据库、表空间的oid
omm=# select oid,datname from pg_database ;
  oid  |  datname  
-------+-----------
     1 | template1
 16384 | omm
 16390 | musicdb
 14555 | template0
 16391 | musicdb1
 16392 | musicdb2
 16393 | musicdb3
 14560 | postgres
(8 rows)

omm=# select oid,* from pg_tablespace ;
  oid  |  spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative 
-------+------------+----------+--------+------------+------------+----------
  1663 | pg_default |       10 |        |            |            | f
  1664 | pg_global  |       10 |        |            |            | f
 16389 | music_tbs  |       10 |        |            |            | t
(3 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
 musicdb   |         16389 | music_tbs
 template0 |          1663 | pg_default
 musicdb1  |         16389 | music_tbs
 musicdb2  |         16389 | music_tbs
 musicdb3  |         16389 | music_tbs
 postgres  |          1663 | pg_default
(8 rows)
--从文件系统，查看music_tbs表空间oid，可以看到一个表空间可以有多个数据库
omm@modb:~$ cd /var/lib/opengauss/data/pg_tblspc/16389
omm@modb:/var/lib/opengauss/data/pg_tblspc/16389$ ls
PG_9.2_201611171_gaussdb
omm@modb:/var/lib/opengauss/data/pg_tblspc/16389$ cd PG_9.2_201611171_gaussdb/
omm@modb:/var/lib/opengauss/data/pg_tblspc/16389/PG_9.2_201611171_gaussdb$ ls
16390  16391  16392  16393  pgsql_tmp
 #说明：16389是表空间 music_tbs的oid

课后作业

1.创建表空间newtbs1

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 newtbs1 relative location 'tablespace/test_ts2';
CREATE TABLESPACE

2.创建3个数据库newdb1、newdb2、newdb3，默认表空间为newtbs1

omm=# create database newdb1 with tablespace = newtbs1 ;
CREATE DATABASE
omm=# create database newdb2 with tablespace = newtbs1 ;
CREATE DATABASE
omm=# create database newdb3 with tablespace = newtbs1 ;
CREATE DATABASE

3.使用sql查看表空间newtbs1上有几个数据库

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
 musicdb   |         16389 | music_tbs
 template0 |          1663 | pg_default
 musicdb1  |         16389 | music_tbs
 musicdb2  |         16389 | music_tbs
 musicdb3  |         16389 | music_tbs
 postgres  |          1663 | pg_default
 newdb1    |         16398 | newtbs1
 newdb2    |         16398 | newtbs1
 newdb3    |         16398 | newtbs1
(11 rows)

4.在文件系统中查看表空间newtbs1中的多个数据库

omm=# select oid,datname from pg_database ;
  oid  |  datname  
-------+-----------
     1 | template1
 16384 | omm
 16390 | musicdb
 14555 | template0
 16391 | musicdb1
 16392 | musicdb2
 16393 | musicdb3
 14560 | postgres
 16399 | newdb1
 16400 | newdb2
 16401 | newdb3
(11 rows)
omm=# select oid,* from pg_tablespace ;
  oid  |  spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative 
-------+------------+----------+--------+------------+------------+----------
  1663 | pg_default |       10 |        |            |            | f
  1664 | pg_global  |       10 |        |            |            | f
 16389 | music_tbs  |       10 |        |            |            | t
 16398 | newtbs1    |       10 |        |            |            | t
(4 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
 musicdb   |         16389 | music_tbs
 template0 |          1663 | pg_default
 musicdb1  |         16389 | music_tbs
 musicdb2  |         16389 | music_tbs
 musicdb3  |         16389 | music_tbs
 postgres  |          1663 | pg_default
 newdb1    |         16398 | newtbs1
 newdb2    |         16398 | newtbs1
 newdb3    |         16398 | newtbs1
(11 rows)

omm=# \q
omm@modb:~$ cd /var/lib/opengauss/data/pg_tblspc/16398
omm@modb:/var/lib/opengauss/data/pg_tblspc/16398$ ls
PG_9.2_201611171_gaussdb
omm@modb:/var/lib/opengauss/data/pg_tblspc/16398$ cd PG_9.2_201611171_gaussdb/
omm@modb:/var/lib/opengauss/data/pg_tblspc/16398/PG_9.2_201611171_gaussdb$ ls
16399  16400  16401  pgsql_tmp