openGauss每日一练第7天｜openGauss表空间的操作

坚持学习openGauss数据库，坚持每天打卡。第七天学习openGauss表空间的操作。

连接openGauss

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=#

1.创建表空间，表空间tspc1使用相对路径指定所在目录，表空间tspc2指定owner为Lucy

omm=# create tablespace tspc1 relative location 'tablespace/tablespace_1';
CREATE TABLESPACE
omm=# create role Lucy identified by 'abce@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create tablespace tspc2 owner Lucy relative location 'tablespace/tablespace_2';
CREATE TABLESPACE

2.在表空间tspc1中建表，并使用视图pg_tables查看信息

omm=# create table tabtest(id int, name char(30)) tablespace tspc1;
CREATE TABLE
omm=# select * from pg_tables where tablename = 'tabtest';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator |            created   
         |         last_ddl_time         
------------+-----------+------------+------------+------------+----------+-------------+--------------+----------------------
---------+-------------------------------
 public     | tabtest   | omm        | tspc1      | f          | f        | f           | omm          | 2021-12-07 16:05:01.4
09098+08 | 2021-12-07 16:05:01.409098+08
(1 row)

3.重命名tspc1，修改tspc2的用户为Lily，使用\db查看表空间信息

omm=# alter tablespace tspc1 rename to tspc11;
ALTER TABLESPACE
omm=# create role Lily identified by 'abcd@789';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter tablespace tspc2 owner to Lily;
ALTER TABLESPACE
omm=# \db
             List of tablespaces
    Name    | Owner |        Location         
------------+-------+-------------------------
 pg_default | omm   | 
 pg_global  | omm   | 
 tspc11     | omm   | tablespace/tablespace_1
 tspc2      | lily  | tablespace/tablespace_2
(4 rows)

4.删除表空间

omm=# drop tablespace if exists tspc11;
ERROR:  tablespace "tspc11" is not empty
--在删除一个表空间之前，表空间里面不能有任何数据库对象
omm=# drop table tabtest;
DROP TABLE
omm=# drop tablespace if exists tspc11;
DROP TABLESPACE
omm=# drop tablespace if exists tspc2;
DROP TABLESPACE

通过学习和作业，更加深入的了解了openGauss创建表空间、修改表空间属性和删除表空间的知识。