openGauss每日一练第9天

学习目标

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

课后作业

1.创建表空间newtbs1

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=# 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 newtbs1 RELATIVE LOCATION 'tablespace/test_ts1';
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
 newdb1    |         16389 | newtbs1
 template0 |          1663 | pg_default
 newdb2    |         16389 | newtbs1
 newdb3    |         16389 | newtbs1
 postgres  |          1663 | pg_default
(7 rows)
复制

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

omm=#  select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
     1 | template1
 16384 | omm
 16390 | newdb1
 14555 | template0
 16391 | newdb2
 16392 | newdb3
 14560 | postgres
(7 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 | newtbs1    |       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
 newdb1    |         16389 | newtbs1
 template0 |          1663 | pg_default
 newdb2    |         16389 | newtbs1
 newdb3    |         16389 | newtbs1
 postgres  |          1663 | pg_default
(7 rows)


root@modb:~# cd /var/lib/opengauss/data/pg_tblspc/16389/
root@modb:/var/lib/opengauss/data/pg_tblspc/16389# cd  PG_9.2* 
root@modb:/var/lib/opengauss/data/pg_tblspc/16389/PG_9.2_201611171_gaussdb# ls
16390  16391  16392  pgsql_tmp
root@modb:/var/lib/opengauss/data/pg_tblspc/16389/PG_9.2_201611171_gaussdb# 

复制