学习目标
表空间是数据的容器。掌握表空间的管理，包括创建表空间、删除表空间、重命名表空间、查看表空间的情况。
学习内容
通过本章表空间的作用，了解表空间在opengauss数据库的管理：
1.创建用户，创建表空间。
2.在表空间上建表。
3.查看表空间的大小。
4.查看默认表空间有些数据对象
5.查看非默认表空间有些数据对象
6.重命名表空间
7.删除表空间
课后作业
1、创建表空间t_tbspace、用户test，并使用test，在这个表空间上创建表t1
openGauss=# CREATE USER test IDENTIFIED BY 'test@1234';
CREATE ROLE
openGauss=# CREATE TABLESPACE t_tbspace RELATIVE LOCATION 'tablespace/t_tbspace';
CREATE TABLESPACE
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-----------+----------------------
pg_default | opengauss |
pg_global | opengauss |
t_tbspace | opengauss | tablespace/t_tbspace
(3 rows)
openGauss=# \c postgres test
Password for user test:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "test".
openGauss=> create table t1(product_id integer,product_name char(20),category char(30)) tablespace t_tbspace;
ERROR: permission denied for tablespace t_tbspace
DETAIL: N/A
openGauss=> \qgsql -d postgres -p 6000 -rgsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# GRANT CREATE ON TABLESPACE t_tbspace TO test;
GRANT
openGauss=# \postgres test
Invalid command \postgres. Try \? for help.
openGauss=# \c postgres test
Password for user test:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "test".
openGauss=> create table t1(product_id integer,product_name char(20),category char(30)) tablespace t_tbspace;
CREATE TABLE
2、查看表空间t_tbspace的oid和大小
openGauss=> SELECT PG_TABLESPACE_SIZE('t_tbspace');
pg_tablespace_size
--------------------
25
(1 row)
openGauss=> insert into t1 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4
openGauss=> SELECT PG_TABLESPACE_SIZE('t_tbspace');
pg_tablespace_size
--------------------
8217
(1 row)3、查看数据库在默认表空间下有哪些对象
openGauss=> with objectInDefaultTS as
openGauss-> ( select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),
openGauss(> reltablespace,relowner
openGauss(> from pg_class a
openGauss(> where a.relkind in ('r', 'i') and reltablespace='0'
openGauss(> )
openGauss-> select *
openGauss-> from objectInDefaultTS
openGauss-> where relname not like 'pg_%' and relname not like 'gs_%' and relname not like 'sql_%'
openGauss-> order by relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
----------------------------------------------+---------+----------+----------------+---------------+----------
snap_global_stat_all_indexes | r | 6616 | 52 MB | 0 | 10
snap_global_statio_all_indexes | r | 6123 | 48 MB | 0 | 10
snap_class_vital_info | r | 5919 | 46 MB | 0 | 10
snap_global_stat_all_tables | r | 4813 | 38 MB | 0 | 10
snap_summary_stat_all_indexes | r | 4767 | 37 MB | 0 | 10
snap_summary_statio_all_indexes | r | 4685 | 37 MB | 0 | 10
snap_global_config_settings | r | 4382 | 34 MB | 0 | 10
snap_global_statio_all_tables | r | 3811 | 30 MB | 0 | 10
snap_summary_stat_all_tables | r | 3098 | 24 MB | 0 | 10
snap_summary_statio_all_tables | r | 2636 | 21 MB | 0 | 10
snap_summary_statement | r | 2198 | 17 MB | 0 | 10
snap_global_wait_events | r | 1329 | 10 MB | 0 | 10
snap_global_file_iostat | r | 1250 | 10000 kB | 0 | 10
snap_class_vital_info_idx | i | 1032 | 8256 kB | 0 | 10
snap_summary_file_iostat | r | 720 | 5808 kB | 0 | 10
snap_global_config_settings_idx | i | 684 | 5472 kB | 0 | 10
snap_summary_stat_all_indexes_idx | i | 567 | 4536 kB | 0 | 10
snap_global_statio_all_indexes_idx | i | 563 | 4504 kB | 0 | 10
snap_global_stat_all_indexes_idx | i | 561 | 4488 kB | 0 | 10
snap_summary_statio_all_indexes_idx | i | 551 | 4408 kB | 0 | 10
snap_global_stat_all_tables_idx | i | 426 | 3408 kB | 0 | 10
snap_summary_statio_all_tables_idx | i | 425 | 3400 kB | 0 | 10
snap_global_statio_all_tables_idx | i | 423 | 3384 kB | 0 | 10
snap_summary_stat_all_tables_idx | i | 416 | 3328 kB | 0 | 10
snap_global_shared_memory_detail | r | 368 | 2944 kB | 0 | 10
snap_global_wait_events_idx | i | 301 | 2408 kB | 0 | 10
tables_snap_timestamp | r | 237 | 1896 kB | 0 | 10
snap_summary_file_iostat_idx | i | 222 | 1776 kB | 0 | 10
snap_global_file_iostat_idx | i | 222 | 1776 kB | 0 | 10
snap_global_os_runtime | r | 133 | 1064 kB | 0 | 10
snap_global_statement_count | r | 79 | 632 kB | 0 | 10
snap_summary_statement_idx | i | 71 | 568 kB | 0 | 10
snap_global_thread_wait_status | r | 68 | 544 kB | 0 | 10
snap_summary_statement_count | r | 63 | 504 kB | 0 | 10
snap_global_shared_memory_detail_idx | i | 58 | 464 kB | 0 | 10
snap_global_os_threads | r | 43 | 344 kB | 0 | 10
snap_global_instance_time | r | 38 | 304 kB | 0 | 10
snap_global_stat_database | r | 34 | 280 kB | 0 | 10
snap_summary_stat_database | r | 24 | 200 kB | 0 | 10
snap_summary_transactions_running_xacts | r | 24 | 192 kB | 0 | 10
snap_global_transactions_running_xacts | r | 24 | 192 kB | 0 | 10
snap_global_stat_database_conflicts | r | 24 | 200 kB | 0 | 10
snap_global_os_runtime_idx | i | 18 | 144 kB | 0 | 10
snap_global_thread_wait_status_idx | i | 17 | 136 kB | 0 | 10
snap_global_os_threads_idx | i | 17 | 136 kB | 0 | 10
snap_summary_stat_database_conflicts | r | 16 | 136 kB | 0 | 10
snap_summary_user_login | r | 11 | 88 kB | 0 | 10
snap_summary_workload_sql_elapse_time | r | 11 | 88 kB | 0 | 10
snap_global_instance_time_idx | i | 11 | 88 kB | 0 | 10
snap_global_statement_count_idx | i | 11 | 88 kB | 0 | 10
snap_summary_statement_count_idx | i | 11 | 88 kB | 0 | 10
snap_global_stat_db_cu | r | 10 | 80 kB | 0 | 10
snap_global_workload_transaction | r | 10 | 80 kB | 0 | 10
snap_global_statio_all_sequences | r | 10 | 80 kB | 0 | 10
snap_global_transactions_running_xacts_idx | i | 9 | 72 kB | 0 | 10
snap_summary_transactions_running_xacts_idx | i | 9 | 72 kB | 0 | 10
snap_global_redo_status | r | 9 | 72 kB | 0 | 10
snap_global_rto_status | r | 9 | 72 kB | 0 | 10
snap_summary_workload_sql_count | r | 8 | 64 kB | 0 | 10
snap_summary_workload_transaction | r | 7 | 56 kB | 0 | 10
snap_summary_statio_all_sequences | r | 7 | 56 kB | 0 | 10
snap_global_bgwriter_stat | r | 7 | 56 kB | 0 | 10
snap_global_stat_db_cu_idx | i | 6 | 48 kB | 0 | 10
snap_summary_stat_database_conflicts_idx | i | 6 | 48 kB | 0 | 10
snap_summary_user_login_idx | i | 6 | 48 kB | 0 | 10
snap_global_file_redo_iostat | r | 6 | 48 kB | 0 | 10
snap_summary_stat_database_idx | i | 6 | 48 kB | 0 | 10
snap_global_stat_database_conflicts_idx | i | 6 | 48 kB | 0 | 10
snap_global_stat_database_idx | i | 6 | 48 kB | 0 | 10
snap_global_double_write_status | r | 5 | 40 kB | 0 | 10
snap_global_record_reset_time_idx | i | 5 | 40 kB | 0 | 10
snap_global_rel_iostat | r | 5 | 40 kB | 0 | 10
snap_summary_file_redo_iostat | r | 4 | 32 kB | 0 | 10
snap_global_record_reset_time | r | 4 | 32 kB | 0 | 10
snap_global_ckpt_status | r | 4 | 32 kB | 0 | 10
snap_global_pagewriter_status | r | 4 | 32 kB | 0 | 10
snap_summary_rel_iostat | r | 3 | 24 kB | 0 | 10
snap_global_pagewriter_status_idx | i | 2 | 16 kB | 0 | 10
snap_summary_rel_iostat_idx | i | 2 | 16 kB | 0 | 10
snap_global_rel_iostat_idx | i | 2 | 16 kB | 0 | 10
snap_summary_file_redo_iostat_idx | i | 2 | 16 kB | 0 | 10
snap_global_file_redo_iostat_idx | i | 2 | 16 kB | 0 | 10
snap_global_ckpt_status_idx | i | 2 | 16 kB | 0 | 10
snap_global_double_write_status_idx | i | 2 | 16 kB | 0 | 10
snap_global_redo_status_idx | i | 2 | 16 kB | 0 | 10
snap_global_rto_status_idx | i | 2 | 16 kB | 0 | 10
snap_statement_responsetime_percentile | r | 2 | 16 kB | 0 | 10
snap_statement_responsetime_percentile_idx | i | 2 | 16 kB | 0 | 10
snap_global_statio_all_sequences_idx | i | 2 | 16 kB | 0 | 10
snap_summary_statio_all_sequences_idx | i | 2 | 16 kB | 0 | 10
snap_summary_workload_transaction_idx | i | 2 | 16 kB | 0 | 10
snap_global_workload_transaction_idx | i | 2 | 16 kB | 0 | 10
snap_summary_workload_sql_elapse_time_idx | i | 2 | 16 kB | 0 | 10
snap_summary_workload_sql_count_idx | i | 2 | 16 kB | 0 | 10
snapshot_pkey | i | 2 | 16 kB | 0 | 10
snapshot | r | 2 | 16 kB | 0 | 10
streaming_gather_agg_index | i | 2 | 16 kB | 0 | 10
snap_global_bgwriter_stat_idx | i | 2 | 16 kB | 0 | 10
streaming_cont_query_matrelid_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
snap_global_replication_slots_idx | i | 1 | 8192 bytes | 0 | 10
snap_global_memory_node_detail_idx | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_lookupidxid_index | i | 1 | 8192 bytes | 0 | 10
snap_summary_transactions_prepared_xacts_idx | i | 1 | 8192 bytes | 0 | 10
snap_summary_stat_user_functions_idx | i | 1 | 8192 bytes | 0 | 10
snap_global_stat_user_functions_idx | 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
snap_global_replication_stat_idx | i | 1 | 8192 bytes | 0 | 10
snap_global_transactions_prepared_xacts_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
snap_global_recovery_status_idx | i | 1 | 8192 bytes | 0 | 10
snap_global_threadpool_status_idx | 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
snap_summary_stat_bad_block_idx | i | 1 | 8192 bytes | 0 | 10
snap_global_stat_bad_block_idx | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_oid_index | i | 1 | 8192 bytes | 0 | 10
snapshot_id_key | i | 1 | 8192 bytes | 0 | 10
table2 | r | 0 | 0 bytes | 0 | 10
test_tab | r | 0 | 8192 bytes | 0 | 10
snapshot | r | 0 | 0 bytes | 0 | 10
staffs_p1 | r | 0 | 0 bytes | 0 | 10
snap_global_replication_slots | r | 0 | 0 bytes | 0 | 10
test11 | r | 0 | 8192 bytes | 0 | 10
statement_history | r | 0 | 0 bytes | 0 | 10
test | r | 0 | 8192 bytes | 0 | 10
snap_global_memory_node_detail | r | 0 | 0 bytes | 0 | 10
test1 | r | 0 | 8192 bytes | 0 | 10
plan_table_data | r | 0 | 0 bytes | 0 | 10
snap_summary_stat_user_functions | r | 0 | 0 bytes | 0 | 10
snap_global_replication_stat | r | 0 | 0 bytes | 0 | 10
snap_global_stat_user_functions | r | 0 | 0 bytes | 0 | 10
streaming_stream | r | 0 | 0 bytes | 0 | 10
snap_global_threadpool_status | r | 0 | 0 bytes | 0 | 10
streaming_reaper_status | r | 0 | 0 bytes | 0 | 10
snap_global_stat_bad_block | r | 0 | 0 bytes | 0 | 10
snap_summary_transactions_prepared_xacts | r | 0 | 0 bytes | 0 | 10
streaming_cont_query | r | 0 | 0 bytes | 0 | 10
snap_summary_stat_bad_block | r | 0 | 0 bytes | 0 | 10
snap_global_recovery_status | r | 0 | 0 bytes | 0 | 10
snap_global_transactions_prepared_xacts | r | 0 | 0 bytes | 0 | 10
customer_t1 | r | 0 | 8192 bytes | 0 | 10
table1 | r | 0 | 0 bytes | 0 | 10
(148 rows)4、查看数据库在非默认表空间下有哪些对象
openGauss=> select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),
openGauss-> reltablespace,relowner
openGauss-> from pg_class a, pg_tablespace tb
openGauss-> where a.relkind in ('r', 'i')
openGauss-> and a.reltablespace=tb.oid
openGauss-> and tb.spcname='t_tbspace'
openGauss-> order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
t1 | r | 0 | 8192 bytes | 19082 | 19078
(1 row)5、重命名表空间
openGauss=> \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | |
pg_global | |
t_tbspace | |
(3 rows)
openGauss=> ALTER TABLESPACE t_tbspace RENAME TO app_tbs;
ERROR: permission denied for tablespace t_tbspace
DETAIL: N/A
openGauss=> \q
openGauss=# ALTER TABLESPACE t_tbspace RENAME TO app_tbs;
ALTER TABLESPACE
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-----------+----------------------
app_tbs | opengauss | tablespace/t_tbspace
pg_default | opengauss |
pg_global | opengauss |
(3 rows)6、删除表空间
openGauss=# drop table jack.foo ;
ERROR: schema "jack" does not exist
openGauss=# DROP TABLESPACE app_tbs;
ERROR: tablespace "app_tbs" is not empty
openGauss=#
openGauss=# drop table test.t1;
DROP TABLE
openGauss=# DROP TABLESPACE app_tbs;
DROP TABLESPACE
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-----------+----------
pg_default | opengauss |
pg_global | opengauss |
(2 rows)最后修改时间：2022-12-03 16:05:19
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
