openGauss每日一练第9天 | 学习心得体会

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

1.创建表空间newtbs1

-- 连接openGuass数据库
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 newdb1;
NOTICE:  database "newdb1" does not exist, skipping
DROP DATABASE
omm=# drop DATABASE IF EXISTS newdb2;
NOTICE:  database "newdb2" does not exist, skipping
DROP DATABASE
omm=# drop DATABASE IF EXISTS newdb3;
NOTICE:  database "newdb3" does not exist, skipping
DROP DATABASE
omm=# drop tablespace IF EXISTS newtbs1;
NOTICE:  Tablespace "newtbs1" does not exist, skipping.
DROP TABLESPACE
-- 创建表空间newtbs1
omm=# CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/newtbs1';
CREATE TABLESPACE

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

-- 创建数据库newdb1、newdb2和newdb3
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上有几个数据库

-- 查看表空间newtbs1上有几个数据库
omm=# select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid and spcname='newtbs1'; 
 datname | dattablespace | spcname 
---------+---------------+---------
 newdb1  |         16389 | newtbs1
 newdb2  |         16389 | newtbs1
 newdb3  |         16389 | newtbs1
(3 rows)

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

--查看数据库、表空间的oid  (表空间对应在磁盘中的目录名与该表空间的oid相同，数据存放在表空间的目录下的数据库子目录中，数据库子目录名称是数据库的oid)             
omm=# select t.oid tablespace_oid,spcname,d.oid database_oid,datname from pg_database d, pg_tablespace t where d.dattablespace=t.oid and spcname='newtbs1'; 
 tablespace_oid | spcname | database_oid | datname 
----------------+---------+--------------+---------
          16389 | newtbs1 |        16390 | newdb1
          16389 | newtbs1 |        16391 | newdb2
          16389 | newtbs1 |        16392 | newdb3
(3 rows)

--从文件系统，查看newtbs1表空间oid，可以看到一个表空间可以有多个数据库
omm@modb:~$ ls $PGDATA/pg_tblspc/16389/PG_9.2_201611171_gaussdb
16390  16391  16392  pgsql_tmp