暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGuass每日一练第9天|学习心得体会

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

原创 小严 2022-12-03
434

课程学习内容

软件版本 ：openGauss 3.0.0

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

数据库实操内容

环境准备

创建表空间、多个数据库和用户

#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r

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=#

–进入数据库omm，创建表空间、测试数据库

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;

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=# 
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=#

–执行下面的SQL语句，创建用户user1 ：

drop user IF EXISTS user1;

CREATE USER user1 IDENTIFIED BY ‘kunpeng@1234’;

omm=# drop user  IF EXISTS  user1;
NOTICE:  role "user1" does not exist, skipping
DROP ROLE

–授予user1数据库系统的SYSADMIN权限：

ALTER USER user1 SYSADMIN;

omm=# ALTER USER user1 SYSADMIN;
ERROR:  role "user1" does not exist
omm=#

使用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=#

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

–查看数据库、表空间的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;

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=#  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)

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=#

–从文件系统，查看music_tbs表空间oid，可以看到一个表空间可以有多个数据库

cd /var/lib/opengauss/data/pg_tblspc/xxxxx/

cd PG_9.2*

ls

#说明：xxxxx是表空间 music_tbs的oid

omm@modb:~$ cd /var/lib/opengauss/data/pg_tblspc/
omm@modb:/var/lib/opengauss/data/pg_tblspc$ ls
16389
omm@modb:/var/lib/opengauss/data/pg_tblspc$ cd 16389
omm@modb:/var/lib/opengauss/data/pg_tblspc/16389$ ls
PG_9.2_201611171_gaussdb
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$

课后作业

创建表空间newtbs1

omm=# CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/test_ts2';
CREATE TABLESPACE
omm=#

创建3个数据库newdb1、newdb2、newdb3，默认表空间为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=#

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

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    |         16394 | newtbs1
 newdb2    |         16394 | newtbs1
 newdb3    |         16394 | newtbs1
(11 rows)

omm=#

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

omm@modb:~$ cd /var/lib/opengauss/data/pg_tblspc/
omm@modb:/var/lib/opengauss/data/pg_tblspc$ ls
16389  16394
omm@modb:/var/lib/opengauss/data/pg_tblspc$ cd 16394
omm@modb:/var/lib/opengauss/data/pg_tblspc/16394$ ls
PG_9.2_201611171_gaussdb
omm@modb:/var/lib/opengauss/data/pg_tblspc/16394$ cd PG_9.2_201611171_gaussdb/
omm@modb:/var/lib/opengauss/data/pg_tblspc/16394/PG_9.2_201611171_gaussdb$ ls
16395  16396  16397  pgsql_tmp
omm@modb:/var/lib/opengauss/data/pg_tblspc/16394/PG_9.2_201611171_gaussdb$

课程学习体会

​ 通过本次的实训操作，学会了创建数据库的表空间与数据库对象的关系，了解oid的含义。

opengaussopengauss每日一练opengauss训练营
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论