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

1.创建表空间newtbs1
drop DATABASE IF EXISTS musicdb;
drop DATABASE IF EXISTS musicdb1;
drop DATABASE IF EXISTS musicdb2;
drop DATABASE IF EXISTS musicdb3;
drop tablespace IF EXISTS music_tbs;
drop tablespace IF EXISTS ds_location1;
drop tablespace IF EXISTS newtbs1;

create tablespace newtbs1 relative location 'tablespace/newtbs1';

omm=# 
omm=# 
omm=# 
omm=# 
omm=# \db
             List of tablespaces
    Name     | Owner |        Location        
-------------+-------+------------------------
 crm_tbs     | omm   | tablespace/crm_tbs
 musicdb_tbs | omm   | tablespace/musicdb_tbs
 pg_default  | omm   | 
 pg_global   | omm   | 
(4 rows)

omm=# drop tablespace crm_tbs;
DROP TABLESPACE
omm=# drop tablespace musicdb_tbs;
DROP TABLESPACE
omm=# create tablespace newtbs1 relative location 'tablespace/newtbs1';
CREATE TABLESPACE

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=# create database newdb1 with tablespace newtbs1;
create database newdb2 with tablespace newtbs1;
create database newdb3 with tablespace newtbs1;
CREATE DATABASE
omm=# create database newdb2 with tablespace newtbs1;
CREATE DATABASE
omm=# create database newdb3 with tablespace newtbs1;

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

omm=# select d.datname,t.spcname,spcowner from pg_database d,pg_tablespace t where d.dattablespace=t.oid;
  datname  |  spcname   | spcowner 
-----------+------------+----------
 template1 | pg_default |       10
 omm       | pg_default |       10
 template0 | pg_default |       10
 newdb1    | newtbs1    |       10
 postgres  | pg_default |       10
 newdb2    | newtbs1    |       10
 newdb3    | newtbs1    |       10
(7 rows)

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

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

 cd /var/lib/opengauss/data

cd pg_tblspc/xxxx   ----表空间的oid

cd PG_9.2*
ls

omm=# select name,setting from pg_settings where name like '%dir%'; 
               name                |               setting                
-----------------------------------+--------------------------------------
 asp_log_directory                 | /home/omm/pg_log/asp_data/gaussdb
 audit_directory                   | pg_audit
 data_directory                    | /var/lib/opengauss/data
 dfs_partition_directory_length    | 512
 dirty_page_percent_max            | 0.9
 enable_access_server_directory    | off
 enable_nonsysadmin_execute_direct | off
 log_directory                     | pg_log
 perf_directory                    | /home/omm/pg_log/pg_perf/gaussdb
 query_log_directory               | /home/omm/pg_log/sql_monitor/gaussdb
 stats_temp_directory              | pg_stat_tmp
 unix_socket_directory             | 
 vacuum_cost_page_dirty            | 20
(13 rows)

omm=# quit
omm-# \quit
omm@911d320f8566:~$ gsql -r 
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:46 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# show data_directory 
omm-# \quit
omm@911d320f8566:~$ cd  /var/lib/opengauss/data
omm@911d320f8566:/var/lib/opengauss/data$ ls
PG_VERSION     gs_gazelle.conf     pg_clog       pg_hba.conf    pg_log        pg_replslot   pg_tblspc        postgresql.conf.lock  undo
base           gswlm_userinfo.cfg  pg_csnlog     pg_ident.conf  pg_logical    pg_serial     pg_twophase      postmaster.opts
gaussdb.state  mot.conf            pg_ctl.lock   pg_llog        pg_multixact  pg_snapshots  pg_xlog          postmaster.pid
global         pg_audit            pg_errorinfo  pg_location    pg_notify     pg_stat_tmp   postgresql.conf  postmaster.pid.lock
omm@911d320f8566:/var/lib/opengauss/data$ cd pg_tblspc/
omm@911d320f8566:/var/lib/opengauss/data/pg_tblspc$ ls
24600
omm@911d320f8566:/var/lib/opengauss/data/pg_tblspc$ gsql -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:46 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# \d pg_tablespace  
  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers 
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] | 
 spcoptions | text[]    | 
 spcmaxsize | text      | 
 relative   | boolean   | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid) TABLESPACE pg_global
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname) TABLESPACE pg_global
Replica Identity: NOTHING
Tablespace: "pg_global"

omm=# select oid,spcname from pg_tablespace;
  oid  |  spcname   
-------+------------
  1663 | pg_default
  1664 | pg_global
 24600 | newtbs1
(3 rows)

omm=# exit
omm-# \quit
omm@911d320f8566:/var/lib/opengauss/data/pg_tblspc$ cd 24600
omm@911d320f8566:/var/lib/opengauss/data/pg_tblspc/24600$ ls
PG_9.2_201611171_gaussdb
omm@911d320f8566:/var/lib/opengauss/data/pg_tblspc/24600$ cd PG_9.2_201611171_gaussdb/
omm@911d320f8566:/var/lib/opengauss/data/pg_tblspc/24600/PG_9.2_201611171_gaussdb$ ls
24601  24602  24603  pgsql_tmp
omm@911d320f8566:/var/lib/opengauss/data/pg_tblspc/24600/PG_9.2_201611171_gaussdb$