课后作业

1.创建表空间newtbs1、 ds_location1，查看表空间

omm=# create tablespace newtbs1 RELATIVE LOCATION 'tablespace/zouyang_newtbs1';
CREATE TABLESPACE
omm=# create tablespace ds_location1 RELATIVE LOCATION 'tablespace/zouyang_location1';
CREATE TABLESPACE
omm=# \db
List of tablespaces
Name | Owner | Location
omm=# --------------+-------+------------------------------
ds_location1 | omm | tablespace/yangkai_location1
newtbs1 | omm | tablespace/yangkai_newtbs1
pg_default | omm |
pg_global | omm |
(4 rows)

2.创建一个数据库newdb1，默认表空间为newtbs1

omm=# create database newdb1 with tablespace newtbs1;
CREATE DATABASE
omm=#

3.创建用户user5，并授予SYSADMIN权限，访问数据库newdb1，在表空间ds_location1上，创建一个表newt1（表结构自定义）

omm=# create database newdb1 with tablespace newtbs1;
CREATE DATABASE
omm=# 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=#

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=> create table newt1(id int,name varchar(20)) tablespace ds_location1;
CREATE TABLE
newdb1=>

4.查看表所在的表空间

newdb1=> select tablename,tablespace from pg_tables where tablename ='newt1';
tablename | tablespace
-----------+--------------
newt1 | ds_location1
(1 row)

5.查看表空间newtbs1、 ds_location1上的对象

newdb1=> select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb where a.relkind in ('r', 'i') and a.reltablespace=tb.oid and tb.spcname in('newtbs1','ds_location1') order by a.relpages desc;

 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
newt1 | r | 0 | 0 bytes | 16446 | 16455
(1 row)