openGauss每日一练第 7 天 | 学习笔记 (表空间）

👉openGauss SQL学习参考

学习目标

学习openGauss表空间

课程学习

表空间用于管理数据对象，与磁盘上的一个目录对应

👉openGauss CREATE TABLESPACE 语法

CREATE TABLESPACE tablespace_name
    [ OWNER user_name ] [RELATIVE] LOCATION 'directory' [ MAXSIZE 'space_size' ]
    [with_option_clause];
复制

RELATIVE
使用相对路径，LOCATION 目录是相对于各个数据库节点数据目录下的。
目录层次：数据库节点的数据目录/pg_location/相对路径
相对路径最多指定两层。

连接openGauss

root@modb:~# su - omm
omm@modb:~$ gsql -r   
复制

1.创建表空间

––使用相对路径指定表空间的目录：数据库节点的数据目录/pg_location/相对路径
omm=# CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE
omm=# 

-–指定owner
omm=# CREATE ROLE joe IDENTIFIED BY 'abce@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# 
omm=# CREATE TABLESPACE ds_location2 OWNER joe RELATIVE LOCATION 'tablespace/tablespace_2';
CREATE TABLESPACE
omm=# 

-–查看表空间信息
omm=# \db
              List of tablespaces
     Name     | Owner |        Location         
--------------+-------+-------------------------
 ds_location1 | omm   | tablespace/tablespace_1
 ds_location2 | joe   | tablespace/tablespace_2
 pg_default   | omm   | 
 pg_global    | omm   | 
(4 rows)

omm=# 
复制

2.修改表空间属性

-–重命名表空间
omm=# ALTER TABLESPACE ds_location1 RENAME TO ds_location3;
ALTER TABLESPACE

–-修改表空间owner
omm=# CREATE ROLE jay IDENTIFIED BY 'abcd@789';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER TABLESPACE ds_location2 OWNER TO jay;
ALTER TABLESPACE
omm=# 

-–查看表空间信息
omm=# \db
              List of tablespaces
     Name     | Owner |        Location         
--------------+-------+-------------------------
 ds_location2 | jay   | tablespace/tablespace_2
 ds_location3 | omm   | tablespace/tablespace_1
 pg_default   | omm   | 
 pg_global    | omm   | 
(4 rows)

omm=# 
复制

3.在表空间中建表

omm=# create table ds_t1(id int, name char(30)) tablespace ds_location2;
CREATE TABLE
omm=# create table ds_t2(id int, name char(30)) tablespace ds_location3;
CREATE TABLE

--通过视图查看表所在的表空间
omm=# select * from pg_tables where tablename = 'ds_t1';
 schemaname | tablename | tableowner |  tablespace  | hasindexes | hasrules | hastriggers | tablecreator |            created            |         last_ddl_time     
    
------------+-----------+------------+--------------+------------+----------+-------------+--------------+-------------------------------+---------------------------
----
 public     | ds_t1     | omm        | ds_location2 | f          | f        | f           | omm          | 2021-12-12 21:07:35.943418+08 | 2021-12-12 21:07:35.943418
+08
(1 row)

omm=# select * from pg_tables where tablename = 'ds_t2';
 schemaname | tablename | tableowner |  tablespace  | hasindexes | hasrules | hastriggers | tablecreator |            created            |         last_ddl_time     
    
------------+-----------+------------+--------------+------------+----------+-------------+--------------+-------------------------------+---------------------------
----
 public     | ds_t2     | omm        | ds_location3 | f          | f        | f           | omm          | 2021-12-12 21:07:52.493026+08 | 2021-12-12 21:07:52.493026
+08
(1 row)

omm=# 
复制

4.删除表空间

-–在删除一个表空间之前，表空间里面不能有任何数据库对象
omm=# drop table ds_t1;
DROP TABLE
omm=# drop table ds_t2;
DROP TABLE
omm=# DROP TABLESPACE IF EXISTS ds_location2;
DROP TABLESPACE
omm=# DROP TABLESPACE IF EXISTS ds_location3;
DROP TABLESPACE
omm=# 
omm=# \db
      List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default | omm   | 
 pg_global  | omm   | 
(2 rows)

复制

课后作业

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

omm=# CREATE TABLESPACE tspc1 RELATIVE LOCATION 'tablespace/tspc1';
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/tspc2';
CREATE TABLESPACE
omm=# \db
          List of tablespaces
    Name    | Owner |     Location     
------------+-------+------------------
 pg_default | omm   | 
 pg_global  | omm   | 
 tspc1      | omm   | tablespace/tspc1
 tspc2      | lucy  | tablespace/tspc2
(4 rows)

omm=# 
复制

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

omm=# create table ds_t1(id int, name char(30)) tablespace tspc1;
CREATE TABLE
omm=# select * from pg_tables where tablename = 'ds_t1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator |            created            |         last_ddl_time       
  
------------+-----------+------------+------------+------------+----------+-------------+--------------+-------------------------------+-----------------------------
--
 public     | ds_t1     | omm        | tspc1      | f          | f        | f           | omm          | 2021-12-12 21:18:45.857153+08 | 2021-12-12 21:18:45.857153+0
8
(1 row)

omm=# 
复制

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

-–重命名表空间
omm=# ALTER TABLESPACE tspc1 RENAME TO tspc10;
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   | 
 tspc10     | omm   | tablespace/tspc1
 tspc2      | lily  | tablespace/tspc2
(4 rows)

omm=# 
复制

4.删除表空间

omm=# DROP TABLESPACE IF EXISTS tspc1;
NOTICE:  Tablespace "tspc1" does not exist, skipping.
DROP TABLESPACE
omm=# 
omm=# DROP TABLESPACE IF EXISTS tspc10;
ERROR:  tablespace "tspc10" is not empty
omm=# 
omm=# drop table ds_t1;
DROP TABLE
omm=# DROP TABLESPACE IF EXISTS tspc10;
DROP TABLESPACE
omm=# 
omm=# DROP TABLESPACE IF EXISTS tspc2;
DROP TABLESPACE
omm=# 
omm=# \db
      List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default | omm   | 
 pg_global  | omm   | 
(2 rows)

omm=# 
复制