课后作业

1.创建表空间newtbs1

openGauss=# CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/newtbs1_ts1';
CREATE TABLESPACE

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

openGauss=# CREATE DATABASE newdb1  WITH TABLESPACE = newtbs1;
CREATE DATABASE
openGauss=# CREATE DATABASE newdb2  WITH TABLESPACE = newtbs1;
CREATE DATABASE
openGauss=# CREATE DATABASE newdb3  WITH TABLESPACE = newtbs1;
CREATE DATABASE

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

--查看pg_database 可以观察dattablespace 字段的值
openGauss=# select * from pg_database ;
  datname  | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility |        datacl        | datfrozenxid64 | da
tminmxid
-----------+--------+----------+------------+----------+---------------+--------------+--------------+---------------+--------------+---------------+------------------+----------------------+----------------+---
---------
 template1 |     10 |        0 | C          | C        | t             | t            |           -1 |         15619 | 0            |          1663 | A                | {=c/omm,omm=CTc/omm} |          13017 |
       2
 template0 |     10 |        0 | C          | C        | t             | f            |           -1 |         15619 | 0            |          1663 | A                | {=c/omm,omm=CTc/omm} |          12662 |
       2
 omm       |  16418 |        0 | C          | C        | f             | t            |           -1 |         15619 | 12662        |         16390 | A                |                      |          12662 |
       2
 postgres  |     10 |        0 | C          | C        | f             | t            |           -1 |         15619 | 0            |          1663 | A                |                      |          13372 |
       2
 newdb1    |     10 |        0 | C          | C        | f             | t            |           -1 |         15619 | 12662        |         24679 | A                |                      |          12662 |
       2
 newdb2    |     10 |        0 | C          | C        | f             | t            |           -1 |         15619 | 12662        |         24679 | A                |                      |          12662 |
       2
 newdb3    |     10 |        0 | C          | C        | f             | t            |           -1 |         15619 | 12662        |         24679 | A                |                      |          12662 |
       2
(7 rows)

--查看pg_tablespace视图的oid，就可以看到24679 和上面database的关联
openGauss=# select oid,* from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative
-------+------------+----------+--------+------------+------------+----------
  1663 | pg_default |       10 |        |            |            | f
  1664 | pg_global  |       10 |        |            |            | f
 16390 | music_tbs  |       10 |        |            |            | t
 24679 | newtbs1    |       10 |        |            |            | t
(4 rows)


--联合起来，用DB关联TBS
openGauss=# select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid;
  datname  | dattablespace |  spcname
-----------+---------------+------------
 template1 |          1663 | pg_default
 template0 |          1663 | pg_default
 omm       |         16390 | music_tbs
 postgres  |          1663 | pg_default
 newdb1    |         24679 | newtbs1
 newdb2    |         24679 | newtbs1
 newdb3    |         24679 | newtbs1
(7 rows)

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

--文件系统中观察可以看到newtbs1_ts1的物理链接和24679这个oid
/datafs/ogdata/dn/pg_tblspc
[root@og3 pg_tblspc]# ls -ltr
total 0
lrwxrwxrwx. 1 omm dbgrp 49 Nov 28 23:21 16390 -> /datafs/ogdata/dn/pg_location/tablespace/test_ts1
lrwxrwxrwx  1 omm dbgrp 52 Dec  2 22:59 24679 -> /datafs/ogdata/dn/pg_location/tablespace/newtbs1_ts1

--进入该目录，观察，可以看到下面有4个目录tmp和 24680\81\82,这个明显是db的oid
[root@og3 pg_tblspc]# cd /datafs/ogdata/dn/pg_location/tablespace/newtbs1_ts1
[root@og3 newtbs1_ts1]# ls -ltr
total 0
drwx------ 6 omm dbgrp 62 Dec  2 23:00 PG_9.2_201611171_dn_6001
[root@og3 newtbs1_ts1]# cd PG_9.2_201611171_dn_6001/
[root@og3 PG_9.2_201611171_dn_6001]# ls -ltr
total 48
drwx------ 2 omm dbgrp     6 Dec  2 22:59 pgsql_tmp
drwx------ 2 omm dbgrp 12288 Dec  2 23:00 24680
drwx------ 2 omm dbgrp 12288 Dec  2 23:00 24681
drwx------ 2 omm dbgrp 12288 Dec  2 23:00 24682

--查看下db的oid，可以看到80.81.82 这三个
openGauss=# select oid,datname from pg_database;
  oid  |  datname
-------+-----------
     1 | template1
 15619 | template0
 16429 | omm
 15624 | postgres
 24680 | newdb1
 24681 | newdb2
 24682 | newdb3
(7 rows)

--当然也可以写一条SQL 通过TBS关联DB
openGauss=# select t.oid tbs_oid,t.spcname ,d.oid db_oid,d.datname   from pg_tablespace t ,pg_database d where t.oid = d.dattablespace;
 tbs_oid |  spcname   | db_oid |  datname
---------+------------+--------+-----------
    1663 | pg_default |      1 | template1
    1663 | pg_default |  15619 | template0
   16390 | music_tbs  |  16429 | omm
    1663 | pg_default |  15624 | postgres
   24679 | newtbs1    |  24680 | newdb1
   24679 | newtbs1    |  24681 | newdb2
   24679 | newtbs1    |  24682 | newdb3
(7 rows)