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

1.创建表空间 newtbs1

--创建表空间 newtbs1
CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/newtbs1';

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

CREATE DATABASE newdb1  WITH TABLESPACE = newtbs1;
CREATE DATABASE newdb2  WITH TABLESPACE = newtbs1;
CREATE DATABASE newdb3  WITH TABLESPACE = newtbs1;
omm=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 newdb1    | omm   | UTF8     | C       | C     | 
 newdb2    | omm   | UTF8     | C       | C     | 
 newdb3    | omm   | UTF8     | C       | C     | 
 omm       | omm   | UTF8     | C       | C     | 
 postgres  | omm   | UTF8     | C       | C     | 
 template0 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
 template1 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
(7 rows)

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

omm=# select spcname,datname 
omm-# from pg_database d, pg_tablespace t 
omm-# where 
omm-# d.dattablespace=t.oid
omm-# and spcname='newtbs1';
 spcname | datname 
---------+---------
 newtbs1 | newdb1
 newtbs1 | newdb2
 newtbs1 | newdb3
(3 rows)

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

--查看数据库oid
omm=# select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
     1 | template1
 16384 | omm
 16393 | newdb1
 14555 | template0
 16401 | newdb2
 16402 | newdb3
 14560 | postgres
--查看表空间oid
omm=# select oid,* from pg_tablespace ;
  oid  |   spcname    | spcowner | spcacl | spcoptions | spcmaxsize | relative 
-------+--------------+----------+--------+------------+------------+----------
  1663 | pg_default   |       10 |        |            |            | f
  1664 | pg_global    |       10 |        |            |            | f
 16391 | newtbs1      |       10 |        |            |            | t
 16392 | ds_location1 |       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
 newdb1    |         16391 | newtbs1
 template0 |          1663 | pg_default
 newdb2    |         16391 | newtbs1
 newdb3    |         16391 | newtbs1
 postgres  |          1663 | pg_default
(7 rows)

--从文件系统，查看newtbs1表空间oid，可以看到一个表空间可以有多个数据库
$ cd /var/lib/opengauss/data/pg_tblspc/16391/
$ ls
PG_9.2_201611171_gaussdb
$ cd * 
$ ls -lrt
total 48
drwx------ 2 omm omm 16384 Dec  2 09:56 16393
drwx------ 2 omm omm 16384 Dec  2 10:22 16401
drwx------ 2 omm omm 16384 Dec  2 10:22 16402