课程作业
1.创建表空间，表空间tspc1使用相对路径指定所在目录，表空间tspc2指定owner为Lucy
omm=# CREATE TABLESPACE tspc1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE
omm=# CREATE ROLE Lucy IDENTIFIED BY 'yangkai_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE TABLESPACE tspc2 OWNER Lucy RELATIVE LOCATION 'tablespace/tablespace_2';
CREATE TABLESPACE
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)
2.在表空间tspc1中建表，并使用视图pg_tables查看信息
omm=# create table yangkai(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
------------+-----------+------------+------------+------------+----------+-------------+--------------+---------+---------------
(0 rows)
omm=# select * from pg_tables where tablename = 'yangkai';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator | created | last_ddl_time
------------+-----------+------------+------------+------------+----------+-------------+--------------+-------------------------------+-------------------------------
public | yangkai | omm | tspc1 | f | f | f | omm | 2021-12-07 17:24:46.889614+08 | 2021-12-07 17:24:46.889614+08
(1 row)
3.重命名tspc1，修改tspc2的用户为Lily，使用\db查看表空间信息
omm=# ALTER TABLESPACE tspc1 RENAME TO tspcyangkai;
ALTER TABLESPACE
omm=# CREATE ROLE Lily IDENTIFIED BY 'yangkai_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter TABLESPACE tspc2 OWNER to Lily ;
ALTER TABLESPACE
omm=# \db
tspc2 | lily | tablespace/tablespace_2
tspcyangkai | omm | tablespace/tablespace_1
(4 rows)
omm=# List of tablespaces
Name | Owner | Location
-------------+-------+-------------------------
pg_default | omm |
pg_global | omm |
4.删除表空间
omm=# drop table yangkai;
DROP TABLE
omm=# DROP TABLESPACE IF EXISTS tspc2;
DROP TABLESPACE
omm=# DROP TABLESPACE tspcyangkai;
DROP TABLESPACE
omm=#
