第9天 | 学习openGauss中一个表空间可以存储多个数据库

学习目标

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

课程学习

1.创建表空间newtbs1

omm=# CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/newtbs_1';
CREATE TABLESPACE
omm=# \db
           List of tablespaces
    Name    | Owner |      Location       
------------+-------+---------------------
 music_tbs  | omm   | tablespace/test_ts1
 newtbs1    | omm   | tablespace/newtbs_1
 pg_default | omm   | 
 pg_global  | omm   | 
(4 rows)

omm=#

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

omm=# drop DATABASE  IF EXISTS  newdb1;
NOTICE:  database "newdb1" does not exist, skipping
omm=# DROP DATABASE
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=# 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
omm=#

--查看数据库所在的表空间，可以看到一个表空间可以有多个数据库

select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid;
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)

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
 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;  
  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 * from pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative 
------------+----------+--------+------------+------------+----------
 pg_default |       10 |        |            |            | f
 pg_global  |       10 |        |            |            | f
 music_tbs  |       10 |        |            |            | t
 newtbs1    |       10 |        |            |            | t
(4 rows)

omm=# select * from pg_database;
  datname  | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datl
astsysoid | datfrozenxid | dattablespace | datcompatibility |        datacl        | datfrozenxid64 | datmi
nmxid 
-----------+--------+----------+------------+----------+---------------+--------------+--------------+-----
 omm       |     10 |        7 | C          | C        | f             | t            |           -1 |     
    14555 | 11801        |          1663 | PG               |                      |          11801 |      
    2
----------+--------------+---------------+------------------+----------------------+----------------+------
------
 template1 |     10 |        7 | C          | C        | t             | t            |           -1 |     
    14555 | 0            |          1663 | PG               | {=c/omm,omm=CTc/omm} |          12156 |      
    2
    14555 | 0            |          1663 | PG               | {=c/omm,omm=CTc/omm} |          11801 |      
    2
 musicdb1  |     10 |        7 | C          | C        | f             | t            |           -1 |     
    14555 | 11801        |         16389 | PG               |                      |          11801 |      
    2
 musicdb2  |     10 |        7 | C          | C        | f             | t            |           -1 |     
    14555 | 11801        |         16389 | PG               |                      |          11801 |      
--More-- musicdb   |     10 |        7 | C          | C        | f             | t            |           -1 |     
    14555 | 11801        |         16389 | PG               |                      |          11801 |      
    2
 template0 |     10 |        7 | C          | C        | t             | f            |           -1 |     
    2
 musicdb3  |     10 |        7 | C          | C        | f             | t            |           -1 |     
    14555 | 11801        |         16389 | PG               |                      |          11801 |      
    2
 postgres  |     10 |        7 | C          | C        | f             | t            |           -1 |     
    14555 | 0            |          1663 | PG               |                      |          12511 |      
    2
 newdb1    |     10 |        7 | C          | C        | f             | t            |           -1 |     
    14555 | 11801        |         16398 | PG               |                      |          11801 |      
    2
 newdb2    |     10 |        7 | C          | C        | f             | t            |           -1 |     
    14555 | 11801        |         16398 | PG               |                      |          11801 |      
    2
 newdb3    |     10 |        7 | C          | C        | f             | t            |           -1 |     
    14555 | 11801        |         16398 | PG               |                      |          11801 |      
    2
(11 rows)


omm=#

--从文件系统，查看music_tbs表空间oid，可以看到一个表空间可以有多个数据库
 
cd /var/lib/opengauss/data/pg_tblspc/xxxxx/
cd  PG_9.2* 
ls
 #说明：xxxxx是表空间 music_tbs的oid

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

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

--从文件系统，查看newtbs1表空间oid，可以看到一个表空间可以有多个数据库
newtbs1表空间oid为16398
cd /var/lib/opengauss/data/pg_tblspc/16398
cd  PG_9.2* 
ls