openGauss每日一练第 9 天 | 一个表空间可以存储多个数据库

课后作业
1.创建表空间newtbs1
2.创建3个数据库newdb1、newdb2、newdb3，默认表空间为newtbs1
3.使用sql查看表空间newtbs1上有几个数据库
4.在文件系统中查看表空间newtbs1中的多个数据库
复制

0、虚拟机关机了，重启后GaussDB服务没了，看了下重启及状态检查的操作，记录如下（后续补充开机自启动，先写作业）：

[omm@Test-GaussDB-VM ~]$ gs_om -t status --detail
[   Cluster State   ]

cluster_state   : Unavailable
redistributing  : No
current_az      : AZ_ALL

[  Datanode State   ]

    node           node_ip         port      instance                            state
------------------------------------------------------------------------------------------------------
1  Test-GaussDB-VM 192.168.11.101  15400      6001 /opt/huawei/install/data/dn   P Primary Manually stopped
[omm@Test-GaussDB-VM ~]$
[omm@Test-GaussDB-VM ~]$ gs_om -t status
-----------------------------------------------------------------------

cluster_name    : Jeff_Test_CL
cluster_state   : Unavailable
redistributing  : No

-----------------------------------------------------------------------
[omm@Test-GaussDB-VM ~]$ gs_om -t start
Starting cluster.
=========================================
[SUCCESS] Test-GaussDB-VM
2022-12-08 16:56:37.875 6391a6c5.1 [unknown] 139633641919616 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2022-12-08 16:56:37.875 6391a6c5.1 [unknown] 139633641919616 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2022-12-08 16:56:37.879 6391a6c5.1 [unknown] 139633641919616 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (3436 Mbytes) is larger.
=========================================
Successfully started.
[omm@Test-GaussDB-VM ~]$ gs_om -t status
-----------------------------------------------------------------------

cluster_name    : Jeff_Test_CL
cluster_state   : Normal
redistributing  : No

-----------------------------------------------------------------------
[omm@Test-GaussDB-VM ~]$ gs_om -t status --detail
[   Cluster State   ]

cluster_state   : Normal
redistributing  : No
current_az      : AZ_ALL

[  Datanode State   ]

    node           node_ip         port      instance                            state
------------------------------------------------------------------------------------------------------
1  Test-GaussDB-VM 192.168.11.101  15400      6001 /opt/huawei/install/data/dn   P Primary Normal

[omm@Test-GaussDB-VM ~]$ gs_om -t stop
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
[omm@Test-GaussDB-VM ~]$ gs_om -t status
-----------------------------------------------------------------------

cluster_name    : Jeff_Test_CL
cluster_state   : Unavailable
redistributing  : No

-----------------------------------------------------------------------
[omm@Test-GaussDB-VM ~]$ gs_om -t start
Starting cluster.
=========================================
[SUCCESS] Test-GaussDB-VM
2022-12-08 16:57:19.289 6391a6ef.1 [unknown] 139796638413952 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2022-12-08 16:57:19.289 6391a6ef.1 [unknown] 139796638413952 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2022-12-08 16:57:19.290 6391a6ef.1 [unknown] 139796638413952 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (3436 Mbytes) is larger.
=========================================
Successfully started.
[omm@Test-GaussDB-VM ~]$
[omm@Test-GaussDB-VM ~]$ gs_om -t restart
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
Starting cluster.
=========================================
[SUCCESS] Test-GaussDB-VM
2022-12-08 17:08:45.691 6391a99d.1 [unknown] 139926319395968 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2022-12-08 17:08:45.691 6391a99d.1 [unknown] 139926319395968 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2022-12-08 17:08:45.692 6391a99d.1 [unknown] 139926319395968 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (3436 Mbytes) is larger.
=========================================
Successfully started.
[omm@Test-GaussDB-VM ~]$ gs_om -t status
-----------------------------------------------------------------------

cluster_name    : Jeff_Test_CL
cluster_state   : Normal
redistributing  : No

-----------------------------------------------------------------------
[omm@Test-GaussDB-VM ~]$ gsql -d omm -p 15400 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=#

复制

1、环境准备

-- 进入数据库omm，创建表空间、测试数据库
[omm@Test-GaussDB-VM ~]$ gsql -d omm -p 15400 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 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;
drop DATABASE IF EXISTS  musicdb1;
drop DATABASE IF EXISTS  musicdb2;
drop DATABASE IF EXISTS  musicdb3;
drop DATABASE IF EXISTS  newdb1;
drop tablespace IF EXISTS music_tbs;
drop tablespace IF EXISTS newtbs1;
revoke all on database omm from user5;
drop owned by user5 cascade;
drop user user5;
\c postgres
revoke all on database postgres from user1;
drop owned by user1 cascade;
drop user user1;
复制

2、创建表空间、数据库

CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/newtbs1';
CREATE DATABASE newdb1 WITH TABLESPACE = newtbs1 ;
CREATE DATABASE newdb2 WITH TABLESPACE = newtbs1 ;
CREATE DATABASE newdb3 WITH TABLESPACE = newtbs1 ;
复制

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
复制

4、查询表空间中有多少数据库

select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid;
复制