学习目标
学习表空间与数据库对象的关系。
在musicdb数据库中创建的所有的表，没有指定表空间的名字，因此都创建在数据库默认的表空间music_tbs中，当我们在musicdb数据库中创建表warehouse_t1的时候，明确指定在表空间ds_location1中创建时，这个表会存储在这个指定的表空间。即一个数据库中的对象，可以位于不同的表空间.
存在数据的表空间无法删除，必须删除数据（如表）后，执行删除；
表空间应为数据库对应的存储单元，可指定不通表空间；PG中不同数据库中创建的对象，只要可以指定tablespace参数，均可写入不同的表空间当中；
课程学习记录：
环境检查
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=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatora
dmin, Policyadmin, UseFT | {}
创建表空间，数据库，用户及赋权
omm=# CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdb WITH TABLESPACE = music_tbs;
CREATE DATABASE
omm=# CREATE USER user1 IDENTIFIED BY 'kunpeng@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=# ALTER USER user1 SYSADMIN;
ALTER ROLE
omm=#
omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+---------------------
music_tbs | omm | tablespace/test_ts1
pg_default | omm |
pg_global | omm |
(3 rows)
omm=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
musicdb | omm | UTF8 | C | C |
omm=# omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(5 rows)
\du
List of roles
Role name | Attributes
| Member of
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatora
dmin, Policyadmin, UseFT | {}
user1 | Sysadmin
| {}
omm=#
创建新表空间
omm=# CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE
omm=#
omm=# \db
\ List of tablespaces
Name | Owner | Location
--------------+-------+-------------------------
ds_location1 | omm | tablespace/tablespace_1
music_tbs | omm | tablespace/test_ts1
pg_default | omm |
pg_global | omm |
(4 rows)
使用user1用户，访问musicdb数据库 ，在表空间ds_location1上创建表warehouse_t1:
omm=# \c musicdb user1
Password for user user1:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user1".
musicdb=> create table warehouse_t1 (col1 char(10)) tablespace ds_location1;
CREATE TABLE
musicdb=> \d
List of relations
Schema | Name | Type | Owner | Storage
--------+--------------+-------+-------+----------------------------------
public | warehouse_t1 | table | user1 | {orientation=row,compression=no}
(1 row)
查看musicdb数据库目前有哪些表：
musicdb=> musicdb-> select table_catalog, table_schema, table_name, table_type
from information_schema.tables
musicdb-> where table_schema not in ('pg_catalog', 'information_schema','dbe_perf');
table_catalog | table_schema | table_name | table_type
---------------+-----------------+--------------+------------
musicdb | db4ai | snapshot | BASE TABLE
musicdb | dbe_pldeveloper | gs_errors | BASE TABLE
musicdb | dbe_pldeveloper | gs_source | BASE TABLE
musicdb | public | warehouse_t1 | BASE TABLE
(4 rows)
查询表在那个表空间
系统表在默认表空间，非系统表在指定的表空间中（否则在默认表空间）
musicdb=> select * from pg_tables where tablename = 'warehouse_t1';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablec
reator | created | last_ddl_time
------------+--------------+------------+--------------+------------+----------+-------------+-------
-------+-------------------------------+-------------------------------
public | warehouse_t1 | user1 | ds_location1 | f | f | f | user1
| 2022-12-01 15:41:24.210155+08 | 2022-12-01 15:41:24.210155+08
(1 row)
创建表warehouse_t12未指定表空间，则在默认表空间（不显示默认表空间名）
musicdb=> create table warehouse_t12 (col1 char(10));
CREATE TABLE
musicdb=> select * from pg_tables where tablename = 'warehouse_t12';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecr
eator | created | last_ddl_time
------------+---------------+------------+------------+------------+----------+-------------+--------
------+-------------------------------+-------------------------------
public | warehouse_t12 | user1 | | f | f | f | user1
| 2022-12-01 15:42:51.686038+08 | 2022-12-01 15:42:51.686038+08
(1 row)
查看openGuass数据库的默认表空间
musicdb=> 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
postgres | 1663 | pg_default
(5 rows)
查询数据库的默认表空间上的对象
musicdb-> musicdb-> and reltablespace='0'
order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace
| relowner
------------------------------------------------+---------+----------+----------------+--------------
-+----------
pg_attribute | r | 184 | 1472 kB | 0
| 10
pg_proc | r | 140 | 1120 kB | 0
| 10
pg_depend | r | 59 | 472 kB | 0
| 10
pg_class | r | 52 | 416 kB | 0
| 10
pg_attribute_relid_attnam_index | i | 44 | 352 kB | 0
| 10
pg_proc_proname_all_args_nsp_index | i | 41 | 328 kB | 0
| 10
pg_proc_proname_args_nsp_new_index | i | 39 | 312 kB | 0
| 10
pg_proc_proname_args_nsp_index | i | 39 | 312 kB | 0
| 10
pg_rewrite | r | 37 | 296 kB | 0
| 10
pg_depend_depender_index | i | 34 | 272 kB | 0
......
(316 rows)
查询表空间ds_location1上的对像
musicdb=> select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
musicdb-> from pg_class a, pg_tablespace tb
musicdb-> where a.relkind in ('r', 'i')
musicdb-> musicdb-> and a.reltablespace=tb.oid
and tb.spcname='ds_location1'
musicdb-> order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
--------------+---------+----------+----------------+---------------+----------
warehouse_t1 | r | 0 | 0 bytes | 16395 | 16391
(1 row)
课后作业:
omm=# \db
List of tablespaces
Name | Owner | Location
--------------+-------+-------------------------
ds_location1 | omm | tablespace/tablespace_1
music_tbs | omm | tablespace/test_ts1
pg_default | omm |
pg_global | omm |
(4 rows)
omm=#
omm=#
omm=# CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/tablespace_1';
ERROR: find conflict linkpath in pg_tblspc, try a different path.
表空间路径不能重复；
omm-# \help DROP TABLESPACE
Command: DROP TABLESPACE
Description: remove a tablespace
Syntax:
DROP TABLESPACE [ IF EXISTS ] tablespace_name;
omm-# d^C
omm=#
omm=# \db
List of tablespaces
Name | Owner | Location
--------------+-------+-------------------------
ds_location1 | omm | tablespace/tablespace_1
music_tbs | omm | tablespace/test_ts1
pg_default | omm |
pg_global | omm |
(4 rows)
omm=# drop tablespace IF EXISTS ds_location1;
omm=# ERROR: tablespace "ds_location1" is not empty
omm=#
omm=#
\db
List of tablespaces
Name | Owner | Location
--------------+-------+-------------------------
ds_location1 | omm | tablespace/tablespace_1
music_tbs | omm | tablespace/test_ts1
pg_default | omm |
pg_global | omm |
(4 rows)
尝试执行删除表空间，但因为对象表空间有数据无法删除；尝试删除表数据后，删除表空间成功；
musicdb=> select * from pg_tables where tablename = 'warehouse_t1';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablec
reator | created | last_ddl_time
------------+--------------+------------+--------------+------------+----------+-------------+-------
-------+-------------------------------+-------------------------------
public | warehouse_t1 | user1 | ds_location1 | f | f | f | user1
| 2022-12-01 15:41:24.210155+08 | 2022-12-01 15:41:24.210155+08
(1 row)
musicdb=> drop table warehouse_t1;
DROP TABLE
musicdb=> drop tablespace ds_location1;
musicdb=> DROP TABLESPACE
musicdb=>
创建指定表空间：
omm=# CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/newtbs_1';
CREATE TABLESPACE
omm=# CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE
omm=# \db
music_tbs | omm | tablespace/test_ts1
newtbs1 | omm | tablespace/newtbs_1
pg_default | omm |
pg_global | omm |
(5 rows)
List of tablespaces
Name | Owner | Location
--------------+-------+-------------------------
ds_location1 | omm | tablespace/tablespace_1omm=#
创建一个数据库newdb1，默认表空间为newtbs1:
omm=# CREATE DATABASE newdb1 WITH TABLESPACE = newtbs1;
CREATE DATABASE
omm=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
musicdb | omm | UTF8 | C | C |
newdb1 | omm | UTF8 | C | C |
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
omm=# (6 rows)
创建用户user5，并授予SYSADMIN权限，访问数据库newdb1，在表空间ds_location1上，创建一个表newt1（表结构自定义）
CREATE USER user5 IDENTIFIED BY 'kunpeng@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user5 SYSADMIN;
ALTER ROLE
omm=# \c newdb1 user5;
Password for user user5:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "newdb1" as user "user5".
newdb1=> \du
List of roles
Role name | Attributes
| Member of
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatora
dmin, Policyadmin, UseFT | {}
user1 | Sysadmin
| {}
user5 | Sysadmin
| {}
newdb1=> create table newt1 (col1 char(10)) tablespace ds_location1;
CREATE TABLE
newdb1=> \d
List of relations
Schema | Name | Type | Owner | Storage
--------+-------+-------+-------+----------------------------------
public | newt1 | table | user5 | {orientation=row,compression=no}
(1 row)
查看表所在的表空间
newdb1=> select * from pg_tables where tablename = 'newt1';
public | newt1 | user5 | ds_location1 | f | f | f | user5
| 2022-12-01 16:03:28.29805+08 | 2022-12-01 16:03:28.29805+08
(1 row)
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecrea
tor | created | last_ddl_time
------------+-----------+------------+--------------+------------+----------+-------------+----------
----+------------------------------+------------------------------
查看表空间newtbs1、 ds_location1上的对象
newdb1-> select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
where a.relkind in ('r', 'i')
newdb1-> and a.reltablespace=tb.oid
newdb1-> and tb.spcname='newtbs1'
newdb1-> order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
(0 rows)
newdb1=> select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
newdb1-> from pg_class a, pg_tablespace tb
newdb1-> newdb1-> where a.relkind in ('r', 'i')
and a.reltablespace=tb.oid
newdb1-> and tb.spcname='ds_location1'
newdb1-> order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
newt1 | r | 0 | 0 bytes | 16406 | 16408
(1 row)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
