第七课 表空间
学习目标
学习openGauss表空间
课程学习
表空间用于管理数据对象，与磁盘上的一个目录对应
连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r
学习内容
1.创建表空间
–使用相对路径指定表空间的目录：数据库节点的数据目录/pg_location/相对路径
CREATE TABLESPACE ds_location1 RELATIVE LOCATION ‘tablespace/tablespace_1’;
–指定owner
CREATE ROLE joe IDENTIFIED BY ‘abce@123’;
CREATE TABLESPACE ds_location2 OWNER joe RELATIVE LOCATION ‘tablespace/tablespace_2’;
–查看表空间信息
\db
2.修改表空间属性
–重命名表空间
ALTER TABLESPACE ds_location1 RENAME TO ds_location3;
–修改表空间owner
CREATE ROLE jay IDENTIFIED BY ‘abcd@789’;
ALTER TABLESPACE ds_location2 OWNER TO jay;
–查看表空间信息
\db
3.在表空间中建表
create table ds_t1(id int, name char(30)) tablespace ds_location2;
–通过视图查看表所在的表空间
select * from pg_tables where tablename = ‘ds_t1’;
4.删除表空间
–在删除一个表空间之前，表空间里面不能有任何数据库对象
drop table ds_t1;
DROP TABLESPACE IF EXISTS ds_location2;
DROP TABLESPACE IF EXISTS ds_location3;
课后作业
1.创建表空间，表空间tspc1使用相对路径指定所在目录，表空间tspc2指定owner为Lucy
2.在表空间tspc1中建表，并使用视图pg_tables查看信息
3.重命名tspc1，修改tspc2的用户为Lily，使用\db查看表空间信息
4.删除表空间
CREATE TABLESPACE tspc1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE ROLE Lucy IDENTIFIED BY 'abcd@789';
CREATE TABLESPACE tspc2 OWNER Lucy RELATIVE LOCATION 'tablespace/tablespace_2';
\db
create table ds_t1(id int, name char(30)) tablespace tspc1;
select * from pg_tables where tablename = 'ds_t1';
\db
\d
ALTER TABLESPACE tspc1 RENAME TO tspc11;
CREATE ROLE Lily IDENTIFIED BY 'abcd@789';
ALTER TABLESPACE tspc2 OWNER TO Lily;
\db
drop table ds_t1;
DROP TABLESPACE IF EXISTS tspc11;
DROP TABLESPACE IF EXISTS tspc2;
复制课后作业记录
omm=#
omm=#
omm=# CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE
omm=#
omm=# CREATE ROLE joe IDENTIFIED BY 'abce@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
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=#
omm=#
omm=# ALTER TABLESPACE ds_location1 RENAME TO ds_location3;
ALTER TABLESPACE
omm=#
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
--------------+-------+-------------------------
omm=# ds_location2 | jay | tablespace/tablespace_2
ds_location3 | omm | tablespace/tablespace_1
pg_default | omm |
pg_global | omm |
(4 rows)
omm=#
omm=# create table ds_t1(id int, name char(30)) tablespace ds_location2;
CREATE TABLE
omm=#
omm=# select * from pg_tables where tablename = 'ds_t1';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecrea
tor | created | last_ddl_time
------------+-----------+------------+--------------+------------+----------+-------------+----------
----+-------------------------------+-------------------------------
public | ds_t1 | omm | ds_location2 | f | f | f | omm
| 2021-12-28 14:32:53.814317+08 | 2021-12-28 14:32:53.814317+08
(1 row)
omm=#
omm=# DROP TABLESPACE IF EXISTS ds_location2;
ERROR: tablespace "ds_location2" is not empty
omm=#
omm=#
omm=# drop table ds_t1;
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)
omm=#
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------
gaussdb | Sysadmin
| {}
jay | Cannot login
| {}
joe | Cannot login
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatora
dmin, Policyadmin, UseFT | {}
omm=#
omm=#
omm=#
omm=# CREATE TABLESPACE tspc1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE
omm=#
omm=# CREATE ROLE Lucy IDENTIFIED BY 'abcd@789';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=# CREATE TABLESPACE tspc2 OWNER Lucy RELATIVE LOCATION 'tablespace/tablespace_2';
CREATE TABLESPACE
omm=#
omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+-------------------------
pg_default | omm |
pg_global | omm |
tspc1 | omm | tablespace/tablespace_1
tspc2 | lucy | tablespace/tablespace_2
(4 rows)
omm=#
omm=#
omm=# create table ds_t1(id int, name char(30)) tablespace tspc1;
CREATE TABLE
omm=# select * from pg_tables where tablename = 'ds_t1';
--+------------------------------+------------------------------
public | ds_t1 | omm | tspc1 | f | f | f | omm
| 2021-12-28 15:06:35.49287+08 | 2021-12-28 15:06:35.49287+08
(1 row)
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreato
r | created | last_ddl_time
------------+-----------+------------+------------+------------+----------+-------------+------------
omm=#
omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+-------------------------
pg_default | omm |
pg_global | omm |
tspc1 | omm | tablespace/tablespace_1
tspc2 | lucy | tablespace/tablespace_2
(4 rows)
omm=#
omm=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+-------+-------+-------+----------------------------------
public | ds_t1 | table | omm | {orientation=row,compression=no}
(1 row)
omm=#
omm=# ALTER TABLESPACE tspc1 RENAME TO tspc11;
ALTER TABLESPACE
omm=#
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=#
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)
omm=#
omm=#
omm=# DROP TABLESPACE IF EXISTS tspc11;
ERROR: tablespace "tspc11" is not empty
omm=#
omm=#
omm=# drop table ds_t1;
DROP TABLE
omm=# DROP TABLESPACE IF EXISTS tspc11;
DROP TABLESPACE
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=#
omm=# \d
No relations found.
omm=#
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------
gaussdb | Sysadmin
| {}
jay | Cannot login
| {}
joe | Cannot login
| {}
lily | Cannot login
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatora
dmin, Policyadmin, UseFT | {}
lucy | Cannot login
| {}
omm=#
复制最后修改时间：2021-12-30 21:44:25
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
2025年3月国产数据库大事记
墨天轮编辑部
872次阅读
2025-04-03 15:21:16
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
286次阅读
2025-04-17 10:41:41
openGauss 7.0.0-RC1 版本正式发布!
Gauss松鼠会
201次阅读
2025-04-01 12:27:03
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
云和恩墨
184次阅读
2025-04-16 09:52:02
openGauss 7.0.0-RC1 版本体验：一主一备快速安装指南
孙莹
179次阅读
2025-04-01 10:30:07
鲲鹏RAG一体机解决方案正式发布 openGauss DataVec向量数据库助力DeepSeek行业应用
Gauss松鼠会
123次阅读
2025-03-31 10:00:29
荣誉时刻！openGauss认证证书快递已发，快来看看谁榜上有名！
墨天轮小教习
106次阅读
2025-04-23 17:39:13
openGauss6.0.0适配操作系统自带的软件，不依赖三方库
来杯拿铁
75次阅读
2025-04-18 10:49:53
opengauss使用gs_probackup进行增量备份恢复
进击的CJR
70次阅读
2025-04-09 16:11:58
Postgresql数据库单个Page最多存储多少行数据
maozicb
56次阅读
2025-04-23 16:02:19
