-
openGauss每日一练第9天 | openGauss中一个表空间可以存储多个数据库
一、学习目标
进一步学习表空间与数据库对象的关系，了解oid的含义。
二、课程学习
1.创建表空间、多个数据库和用户
--进入数据库omm，创建表空间、测试数据库 su - omm gsql -r 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; CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1'; CREATE DATABASE musicdb WITH TABLESPACE = music_tbs; CREATE DATABASE musicdb1 WITH TABLESPACE = music_tbs; CREATE DATABASE musicdb2 WITH TABLESPACE = music_tbs; CREATE DATABASE musicdb3 WITH TABLESPACE = music_tbs; --执行下面的SQL语句，创建用户user1 ： drop user IF EXISTS user1; CREATE USER user1 IDENTIFIED BY 'kunpeng@1234'; --授予user1数据库系统的SYSADMIN权限： ALTER USER user1 SYSADMIN;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
实训环境示例：
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 music_tbs RELATIVE LOCATION 'tablespace/test_ts1'; CREATE TABLESPACE omm=# CREATE DATABASE musicdb WITH TABLESPACE = music_tbs; CREATE DATABASE omm=# CREATE DATABASE musicdb1 WITH TABLESPACE = music_tbs; CREATE DATABASE omm=# CREATE DATABASE musicdb2 WITH TABLESPACE = music_tbs; CREATE DATABASE omm=# CREATE DATABASE musicdb3 WITH TABLESPACE = music_tbs; CREATE DATABASE omm=# omm=# drop user IF EXISTS user1; NOTICE: role "user1" does not exist, skipping DROP ROLE omm=# CREATE USER user1 IDENTIFIED BY 'kunpeng@1234'; NOTICE: The encrypted password contains MD5 ciphertext, which is not secure. CREATE ROLE omm=# omm=# ALTER USER user1 SYSADMIN; omm=# ALTER ROLE omm=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------------------ ------------------------------------------+----------- gaussdb | Sysadmin | {} omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monito radmin, Operatoradmin, Policyadmin, UseFT | {} user1 | Sysadmin | {} omm=#- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
2.使用sql查看数据库所在的表空间
–查看数据库所在的表空间，可以看到一个表空间可以有多个数据库
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 (8 rows) omm=#- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
3.在文件系统中查看表空间中的多个数据库
--查看数据库、表空间的oid select oid,datname from pg_database; select oid,* from pg_tablespace ; select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid; --从文件系统，查看music_tbs表空间oid，可以看到一个表空间可以有多个数据库 cd /var/lib/opengauss/data/pg_tblspc/xxxxx/ cd PG_9.2* ls #说明：xxxxx是表空间 music_tbs的oid- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
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 (8 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 (3 rows) d.dattablespace=t.oid; tablespace,spcname from pg_database d, pg_tablespace t where 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 (8 rows) omm=# \q omm@modb:~$ cd /var/lib/opengauss/data/pg_tblspc/16389 omm@modb:/var/lib/opengauss/data/pg_tblspc/16389$ cd PG_9.2_201611171_gaussdb/ omm@modb:/var/lib/opengauss/data/pg_tblspc/16389/PG_9.2_201611171_gaussdb$ ls 16390 16391 16392 16393 pgsql_tmp omm@modb:/var/lib/opengauss/data/pg_tblspc/16389/PG_9.2_201611171_gaussdb$- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
三、课后作业
1. 创建表空间newtbs1
CREATE TABLESPACE newtbs1 RELATIVE LOCATION ‘tablespace/test_ts2’;
omm=# CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/test_ts2'; CREATE TABLESPACE omm=# \db List of tablespaces Name | Owner | Location ------------+-------+--------------------- music_tbs | omm | tablespace/test_ts1 newtbs1 | omm | tablespace/test_ts2 pg_default | omm | pg_global | omm | (4 rows) omm=#- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
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 omm=# CREATE DATABASE newdb2 WITH TABLESPACE = newtbs1; CREATE DATABASE omm=# CREATE DATABASE newdb3 WITH TABLESPACE = newtbs1; CREATE DATABASE omm=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+-------+----------+---------+-------+------------------- musicdb | omm | UTF8 | C | C | musicdb1 | omm | UTF8 | C | C | musicdb2 | omm | UTF8 | C | C | musicdb3 | omm | UTF8 | C | C | 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 (11 rows) omm=#- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
3. 使用sql查看表空间newtbs1上有几个数据库
select oid,datname from pg_database;
select oid,* from pg_tablespace ;
select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.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; 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)omm=# omm=#- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
4. 在文件系统中查看表空间newtbs1中的多个数据库
cd /var/lib/opengauss/data/pg_tblspc/16398/
cd PG_9.2_201611171_gaussdb/
lsomm@modb:~$ cd /var/lib/opengauss/data/pg_tblspc/16398/ omm@modb:/var/lib/opengauss/data/pg_tblspc/16398$ cd PG_9.2_201611171_gaussdb/ omm@modb:/var/lib/opengauss/data/pg_tblspc/16398/PG_9.2_201611171_gaussdb$ ls 16399 16400 16401 pgsql_tmp omm@modb:/var/lib/opengauss/data/pg_tblspc/16398/PG_9.2_201611171_gaussdb$- 1
- 2
- 3
- 4
- 5
- 6
-
相关阅读:
【web渗透思路】任意账号的注册、登录、重置、查看
Prometheus监控docker容器
ElementUI之首页导航+左侧菜单
STC89C51基础及项目第12天：IIC协议、OLED显示字符和图片
SpringFramework之基于配置类管理Bean
React SSR - 写个 Demo 一学就会
工业智能网关在能耗“双控”的智能化应用
4.django-模板
如何监控Tomcat的性能？
(Java)数据类型与变量
- 原文地址：https://blog.csdn.net/qq_40220309/article/details/128163679
