openGauss每日一练第10天
今日目标：掌握表空间的管理
openGauss自带了两个表空间：pg_default和pg_global。
-
默认表空间-pg_default
用来存储非共享系统表，用户表、用户表索引、临时表、临时表Index、内部临时表的默认表空间。对应存储目录为实例数据目录下的base目录。
-
共享表空间
用来存放共享系统表的表空间。对应存储目录为实例数据目录下的global目录
系统表
-
系统表存储关于可用数据库的信息。
名称 类型 描述 oid oid 行标识符（隐含属性，必须明确选择）。 datname name 数据库名称。 datdba oid 数据库所有人，通常为其创建者。 encoding integer 数据库的字符编码方式。 datcollate name 数据库使用的排序顺序。 datctype name 数据库使用的字符分类。 datistemplate boolean 是否允许作为模板数据库。 datallowconn boolean 如果为假，则没有用户可以连接到这个数据库。这个字段用于保护template0数据库不被更改。 datconnlimit integer 该数据库上允许的最大并发连接数，-1表示无限制。 datlastsysoid oid 数据库里最后一个系统OID 。 datfrozenxid xid32 用于跟踪该数据库是否需要为了防止事务ID重叠而进行清理。当前版本该字段已经废弃使用，为保持前向兼容，保留此字段，新增datfrozenxid64用于记录此信息。 dattablespace oid 数据库的缺省表空间。 datcompatibility name 数据库兼容模式，当前支持四种兼容模式：A、B、C、PG，分别表示兼容O、MY、TD和POSTGRES。 datacl aclitem[] 访问权限。 datfrozenxid64 xid 用于跟踪该数据库是否需要为了防止事务ID重叠而进行清理。 datminmxid xid 该数据库中中所有在这个之前的多事务ID已经被一个事务ID替换。这用于跟踪该数据库是否需要为了防止事务ID重叠或者允许收缩pg_clog而进行清理。它是此数据库中所有表的pg_class.relminmxid中的最小值。 -
PG_TABLESPACE
PG_TABLESPACE系统表存储表空间信息。名称 类型 描述 oid oid 行标识符（隐含属性，必须明确选择）。 spcname name 表空间名称。 spcowner oid 表空间的所有者，通常是创建它的人。 spcacl aclitem[] 访问权限。具体请参见[GRANT](mk:@MSITStore:F:\openGauss学习\openGauss-document-zh-3.0.0\openGauss 3.0.0 开发者指南（企业版）01.chm::/zh-cn_topic_0289900312.html)和[REVOKE](mk:@MSITStore:F:\openGauss学习\openGauss-document-zh-3.0.0\openGauss 3.0.0 开发者指南（企业版）01.chm::/zh-cn_topic_0289900263.html)。 spcoptions text[] 表空间的选项。 spcmaxsize text 可使用的最大磁盘空间大小，单位Byte。 relative boolean 标识表空间指定的存储路径是否为相对路径。 -
PG_CLASS系统表存储数据库对象信息及其之间的关系
常用字段名称 类型 描述 oid oid 行标识符（隐含属性，必须明确选择）。 relname name 表、索引、视图等对象的名称。 relnamespace oid 包含这个关系的名称空间的OID。 reltype oid 对应这个表的行类型的数据类型（索引为零，因为索引没有pg_type记录）。 reloftype oid 复合类型的OID，0表示其他类型。 relowner oid 关系所有者。 relam oid 如果行是索引，则就是所用的访问模式（B-tree，hash等）。 relfilenode oid 这个关系在磁盘上的文件的名称，如果没有则为0。 reltablespace oid 这个关系存储所在的表空间。如果为零，则意味着使用该数据库的缺省表空间。如果关系在磁盘上没有文件，则这个字段没有什么意义。 relpages double precision 以页（大小为BLCKSZ）为单位的此表在磁盘上的大小，它只是优化器用的一个近似值。 reltuples double precision 表中行的数目，只是优化器使用的一个估计值。 relallvisible integer 被标识为全可见的表中的页的数量。此字段是优化器用来做SQL执行优化使用的。VACUUM、ANALYZE和一些DDL语句（例如，CREATE INDEX）会引起此字段更新。 reltoastrelid oid 与此表关联的TOAST表的OID ，如果没有则为0。TOAST表在一个从属表里“离线”存储大字段。 reltoastidxid oid 对于TOAST表是它的索引的OID，如果不是TOAST表则为0。 reldeltarelid oid Delta表的OID。Delta表附属于列存表。用于存储数据导入过程中的甩尾数据。 reldeltaidx oid Delta表的索引表OID。 relcudescrelid oid CU描述表的OID。CU描述表（Desc表）附属于列存表。用于控制表目录中存储数据的可见性。 relcudescidx oid CU描述表的索引表OID。 relhasindex boolean 如果它是一个表而且至少有（或者最近有过）一个索引，则为真。它是由CREATE INDEX设置的，但DROP INDEX不会立即将它清除。如果VACUUM进程检测一个表没有索引，将会把它将清理relhasindex字段，将值设置为假。 relisshared boolean 如果该表在openGauss中由所有数据库共享则为真。只有某些系统表（比如pg_database）是共享的。 relpersistence “char” p：表示永久表。u：表示非日志表。g：表示临时表。 relkind “char” r：表示普通表。i：表示索引。I：表示分区表GLOBAL索引。S：表示序列。v：表示视图。c：表示复合类型。t：表示TOAST表。f：表示外表。m：表示物化视图。 relnatts smallint 关系中用户字段数目（除了系统字段以外）。在pg_attribute里肯定有相同数目对应行。 relchecks smallint 表里的检查约束的数目；参阅pg_constraint表。 relhasoids boolean 如果为关系中每行都生成一个OID则为真。 relhaspkey boolean 如果这个表有一个（或者曾经有一个）主键，则为真。 relhasrules boolean 如表有规则就为真。是否有规则可参考系统表PG_REWRITE。 relhastriggers boolean True表示表中有触发器，或者曾经有过触发器。系统表pg_trigger中记录了表和视图的触发器。 relhassubclass boolean 如果有（或者曾经有）任何继承的子表，为真。 relcmprs tinyint 表示是否启用表的启用压缩特性。需要特别注意，当且仅当批量插入才会触发压缩，普通的CRUD并不能够触发压缩。0表示其他不支持压缩的表（主要是指系统表，不支持压缩属性的修改操作）。1表示表数据的压缩特性为NOCOMPRESS或者无指定关键字。2表示表数据的压缩特性为COMPRESS。 relhasclusterkey boolean 是否有局部聚簇存储。 relrowmovement boolean 针对分区表进行update操作时，是否允许行迁移。true：表示允许行迁移。false：表示不允许行迁移。 parttype “char” 表或者索引是否具有分区表的性质。p：表示带有分区表性质。n：表示没有分区表特性。v：表示该表为HDFS的Value分区表。s：表示该表为二级分区表。 relfrozenxid xid32 该表中所有在这个之前的事务ID已经被一个固定的（“frozen”）事务ID替换。该字段用于跟踪此表是否需要为了防止事务ID重叠（或者允许收缩pg_clog）而进行清理。如果该关系不是表则为零（InvalidTransactionId）。为保持前向兼容，保留此字段，新增relfrozenxid64用于记录此信息。 relacl aclitem[] 访问权限。查询的回显结果为以下形式： 1``rolename=xxxx/yyyy --赋予一个角色的权限 ``1``=xxxx/yyyy --赋予public的权限xxxx表示赋予的权限，yyyy表示授予这个权限的角色。权限的参数说明请参见[表2](mk:@MSITStore:F:\openGauss学习\openGauss-document-zh-3.0.0\openGauss 3.0.0 开发者指南（企业版）01.chm::/zh-cn_topic_0289899838.html#ZH-CN_TOPIC_0289899838__zh-cn_topic_0283136767_zh-cn_topic_0237122277_zh-cn_topic_0059778035_td89f8f6cc98f4a11a08b3c45d852a6cc)。relreplident “char” 逻辑解码中解码列的标识：d = 默认 （主键，如果存在）。n = 无。f = 所有列。i = 索引的indisreplident被设置或者为默认。 relfrozenxid64 xid 该表中所有在这个之前的事务ID已经被一个固定的（“frozen”）事务ID替换。该字段用于跟踪此表是否需要为了防止事务ID重叠（或者允许收缩pg_clog）而进行清理。如果该关系不是表则为零（InvalidTransactionId）。 relbucket oid pg_hashbucket中的桶信息。 relbucketkey int2vector 哈希分区列号。 relminmxid xid 该表中所有在这个之前的多事务ID已经被一个事务ID替换。这用于跟踪该表是否需要为了防止多事务ID重叠或者允许收缩pg_clog而进行清理。如果该关系不是表则为零（InvalidTransactionId）。
1.表空间常见操作
-
pg_tablespace_size 查看表空间使用的磁盘空间
根据表空间名称查看表空间使用的磁盘空间
select pg_tablespace_size(tablespacename);
//根据oid查看表空间大小
select pg_tablespace_size(oid); -
pg_size_pretty
描述：将以64位整数表示的字节值转换为具有单位的易读格式。
-
重命名表空间
alter tablaspace
rename to -
删除表空间 注意 用户必须是表空间的owner或者系统管理员才能删除表空间。需要先删除表空间的对象，再删除表空间
drop tablespace <表空间名称> //需要先删除表空间的对象，再删除表空间
2.课后作业
2.1.创建表空间t_tbspace、用户test，并使用test，在这个表空间上创建表t1
\db // 查看表表空间
//在命名空间上创建表
create table table(column…) tablespace
omm=# create tablespace t_tbspace relative location 'tablespace/tablespace_test';
CREATE TABLESPACE
omm=# create user test1 identified by 'gauss@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create table t1(col char(50)) tablespace t_tbspace ;
CREATE TABLE
omm=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+-------+----------------------------------
public | t1 | table | omm | {orientation=row,compression=no}
(1 row)
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
jack | | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
test1 | | {}
omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------------------------
pg_default | omm |
pg_global | omm |
t_tbspace | omm | tablespace/tablespace_test
(3 rows)
omm=#
2.2 查看表空间t_tbspace的oid和大小
根据表空间名称查看表空间使用的磁盘空间
select pg_tablespace_size(tablespacename);
//根据oid查看表空间大小
select pg_tablespace_size(oid);
omm=# select pg_tablespace_size('t_tbspace');
pg_tablespace_size
--------------------
8192
(1 row)
2.3 查看数据库在默认表空间下有哪些对象
select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a
where a.relkind in(‘r’,‘i’) and reltablespace=‘0’ and a.relname not like ‘pg_%’ and a.relname not like ‘gs_%’ and a.relname not like ‘sql_%’
order by relpages desc;
or 如下
with objectInDefaultTs as (select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a where a.relkind in(‘r’,‘i’) and reltablespace=‘0’)
select * from objectInDefaultTS
where relname not like ‘pg_%’ and relname not like ‘gs_%’ and relname not like ‘sql_%’
order by relpages desc;
omm=# select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
omm-# from pg_class a
omm-# where a.relkind in('r','i') and reltablespace='0'
omm-# and a.relname not like 'pg_%'
omm-# and a.relname not like 'gs_%' and a.relname not like 'sql_%'
omm-# order by relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
------------------------------------------+---------+----------+----------------+---------------+----------
streaming_gather_agg_index | i | 2 | 16 kB | 0 | 10
snapshot_id_key | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_lookupidxid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_schema_change_index | i | 1 | 8192 bytes | 0 | 10
streaming_reaper_status_id_index | i | 1 | 8192 bytes | 0 | 10
streaming_reaper_status_oid_index | i | 1 | 8192 bytes | 0 | 10
snapshot_pkey | i | 1 | 8192 bytes | 0 | 10
statement_history_time_idx | i | 1 | 8192 bytes | 0 | 10
streaming_stream_oid_index | i | 1 | 8192 bytes | 0 | 10
streaming_stream_relid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_relid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_defrelid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_id_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_oid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_matrelid_index | i | 1 | 8192 bytes | 0 | 10
plan_table_data | r | 0 | 0 bytes | 0 | 10
statement_history | r | 0 | 0 bytes | 0 | 10
streaming_stream | r | 0 | 0 bytes | 0 | 10
snapshot | r | 0 | 0 bytes | 0 | 10
streaming_reaper_status | r | 0 | 0 bytes | 0 | 10
streaming_cont_query | r | 0 | 0 bytes | 0 | 10
(21 rows)
omm=#
2.4. 查看数据库在非默认表空间下有哪些对象
select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a ,pg_tablespace t
where a.relkind in (‘r’,‘i’) and a.reltablespace = t.oid
order by a.relpages desc;
omm=# select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
omm-# from pg_class a ,pg_tablespace t
omm-# where a.relkind in ('r','i') and a.reltablespace = t.oid
omm-# order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
-----------------------------------------+---------+----------+----------------+---------------+----------
| 16 kB | 1664 | 10
pg_workload_group_name_index | i | 2 | 16 kB | 1664 | 10
pg_app_workloadgroup_mapping_oid_index | i | 2 | 16 kB | 1664 | 10
pg_auth_history_index | i | 2 | 16 kB | 1664 | 10
pg_shdepend_depender_index | i | 2 | 16 kB | 1664 | 10
pg_shdepend_reference_index | i | 2 | 16 kB | 1664 | 10
pg_authid_rolname_index | i | 2 | 16 kB | 1664 | 10
pg_authid_oid_index | i | 2 | 16 kB | 1664 | 10
pg_shdescription_o_c_index | i | 2 | 16 kB | 1664 | 10
pg_workload_group_oid_index | i | 2 | 16 kB | 1664 | 10
pg_auth_history_oid_index | i | 2 | 16 kB | 1664 | 10
pg_tablespace_oid_index | i | 2 | 16 kB | 1664 | 10
pg_tablespace_spcname_index | i | 2 pg_database_datname_index | i | 2 | 16 kB | 1664 | 10
pg_resource_pool_name_index | i | 2 | 16 kB | 1664 | 10
pg_pltemplate_name_index | i | 2 | 16 kB | 1664 | 10
pg_app_workloadgroup_mapping_name_index | i | 2 | 16 kB | 1664 | 10
pg_resource_pool_oid_index | i | 2 | 16 kB | 1664 | 10
pg_database_oid_index | i | 2 | 16 kB | 1664 | 10
pgxc_group_oid | i | 1 | 8192 bytes | 1664 | 10
pg_resource_pool | r | 1 | 8192 bytes | 1664 | 10
pg_workload_group | r | 1 | 8192 bytes | 1664 | 10
pg_pltemplate | r | 1 | 8192 bytes | 1664 | 10
pg_subscription_oid_index | i | 1 | 8192 bytes | 1664 | 10
pg_subscription_subname_index | i | 1 | 8192 bytes | 1664 | 10
pg_authid | r | 1 | 8192 bytes | 1664 | 10
pg_database | r | 1 | 8192 bytes | 1664 | 10
pg_toast_2964_index | i | 1 | 8192 bytes | 1664 | 10
pg_db_role_setting_databaseid_rol_index | i | 1 | 8192 bytes | 1664 | 10
pg_tablespace | r | 1 | 8192 bytes | 1664 | 10
pg_auth_members_role_member_index | i | 1 | 8192 bytes | 1664 | 10
pg_auth_members_member_role_index | i | 1 | 8192 bytes | 1664 | 10
pg_shdepend | r | 1 | 8192 bytes | 1664 | 10
pgxc_node_id_index | i | 1 | 8192 bytes | 1664 | 10
pg_shdescription | r | 1 | 8192 bytes | 1664 | 10
pg_toast_2396_index | i | 1 | 8192 bytes | 1664 | 10
pg_toast_9014_index | i | 1 | 8192 bytes | 1664 | 10
pgxc_group_name_index | i | 1 | 8192 bytes | 1664 | 10
pg_shseclabel_object_index | i | 1 | 8192 bytes | 1664 | 10
pg_job_oid_index | i | 1 | 8192 bytes | 1664 | 10
pg_job_id_index | i | 1 | 8192 bytes | 1664 | 10
pgxc_node_oid_index | i | 1 | 8192 bytes | 1664 | 10
pgxc_node_name_type_index | i | 1 | 8192 bytes | 1664 | 10
pg_job_proc_id_index | i | 1 | 8192 bytes | 1664 | 10
pg_job_proc_oid_index | i | 1 | 8192 bytes | 1664 | 10
gs_global_config | r | 1 | 8192 bytes | 1664 | 10
pg_replication_origin_roident_index | i | 1 | 8192 bytes | 1664 | 10
pg_replication_origin_roname_index | i | 1 | 8192 bytes | 1664 | 10
pg_user_status_index | i | 1 | 16 kB | 1664 | 10
pg_user_status_oid_index | i | 1 | 16 kB | 1664 | 10
pg_auth_history | r | 1 | 8192 bytes | 1664 | 10
pg_extension_data_source_oid_index | i | 1 | 8192 bytes | 1664 | 10
pg_extension_data_source_name_index | i | 1 | 8192 bytes | 1664 | 10
pg_app_workloadgroup_mapping | r | 1 | 8192 bytes | 1664 | 10
pgxc_group | r | 0 | 0 bytes | 1664 | 10
pgxc_node | r | 0 | 0 bytes | 1664 | 10
gs_obsscaninfo | r | 0 | 0 bytes | 1664 | 10
pg_auth_members | r | 0 pg_job_proc | r | 0 | 0 bytes | 1664 | 10
pg_extension_data_source | r | 0 | 0 bytes | 1664 | 10
pg_user_status | r | 0 | 8192 bytes | 1664 | 10
(66 rows)
| 0 bytes | 1664 | 10
t1 | r | 0 | 0 bytes | 16397 | 10
pg_shseclabel | r | 0 | 0 bytes | 1664 | 10
pg_db_role_setting | r | 0 | 0 bytes | 1664 | 10
pg_job | r | 0 | 0 bytes | 1664 | 10
pg_replication_origin | r | 0 | 0 bytes | 1664 | 10
pg_subscription | r | 0 | 0 bytes | 1664 | 10
omm=#
2.5查看t_tbspace表空间有哪些数据库对象
omm=# select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
omm-# from pg_class a ,pg_tablespace t
omm-# omm-# where a.relkind in ('r','i')
omm-# and a.reltablespace = t.oid
omm-# and t.spcname ='t_tbspace'
order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
t1 | r | 0 | 0 bytes | 16397 | 10
(1 row)
omm=#
2.6重命名表空间
alter tablaspace
rename to
omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------------------------
pg_default | omm |
pg_global | omm |
t_tbspace | omm | tablespace/tablespace_test
(3 rows)
omm=# alter tablespace t_tbspace rename to t_tbspace_new;
ALTER TABLESPACE
omm=# \db
List of tablespaces
Name | Owner | Location
---------------+-------+----------------------------
pg_default | omm |
pg_global | omm |
t_tbspace_new | omm | tablespace/tablespace_test
(3 rows)
omm=#
2.7 删除表空间
drop tablespace <表空间名称> //需要先删除表空间的对象，再删除表空间
-- 查看表空间
omm=# \db
List of tablespaces
Name | Owner | Location
---------------+-------+----------------------------
pg_default | omm |
pg_global | omm |
t_tbspace_new | omm | tablespace/tablespace_test
(3 rows)
-- 删除表空间 （失败，表空间下有数据库对象）
omm=# drop tablespace t_tbspace_new ;
ERROR: tablespace "t_tbspace_new" is not empty
-- 查看表空间下有哪些数据库对象
omm=# select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
omm-# from pg_class a ,pg_tablespace t
omm-# where a.reltablespace = t.oid
omm-# and t.spcname ='t_tbspace_new'
omm-# order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
t1 | r | 0 | 0 bytes | 16397 | 10
(1 row)
omm=#
omm=# drop table t1 ;
DROP TABLE
omm=# drop tablespace t_tbspace_new ;
DROP TABLESPACE
omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)
omm=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
