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

CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/tbs_newtbs1';
CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';
musicdb1=# \db+
                               List of tablespaces
     Name     | Owner |        Location         | Access privileges | Description
--------------+-------+-------------------------+-------------------+-------------
 ds_location1 | omm   | tablespace/tablespace_1 |                   |
 music_tbs    | omm   | tablespace/test_ts1     |                   |
 newtbs1      | omm   | tablespace/tbs_newtbs1  |                   |
 pg_default   | omm   |                         |                   |
 pg_global    | omm   |                         |                   |
(5 rows)

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

CREATE DATABASE newdb1  WITH TABLESPACE = newtbs1;

musicdb1=# CREATE DATABASE newdb1  WITH TABLESPACE = newtbs1;
CREATE DATABASE
musicdb1=# \l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges
-----------+-------+----------+------------+------------+-------------------
 newdb1    | omm   | UTF8     | zh_CN.utf8 | zh_CN.utf8 |
 postgres  | omm   | UTF8     | zh_CN.utf8 | zh_CN.utf8 |
 template0 | omm   | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/omm           +
           |       |          |            |            | omm=CTc/omm
 template1 | omm   | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/omm           +
           |       |          |            |            | omm=CTc/omm
 zyfdb     | omm   | UTF8     | zh_CN.utf8 | zh_CN.utf8 |
(10 rows)

3 创建用户user5

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

CREATE USER user5 IDENTIFIED BY 'kunpeng@1234';
ALTER USER user5 SYSADMIN;

newdb1-> \conninfo
You are connected to database "newdb1" as user "user5" via socket in "/opt/huawei/tmp" at port "26000".
newdb1->
 
create table t1 (id int);
create table newt1 (col1 char(10)) tablespace ds_location1;

4 查看表所在的表空间

newdb1=> select schemaname,tablename ,tableowner,tablespace,tablecreator ,created from pg_tables where tablename in( 't1','newt1');
 schemaname | tablename | tableowner |  tablespace  | tablecreator |            created
------------+-----------+------------+--------------+--------------+-------------------------------
 public     | t1        | user5      |              | user5        | 2022-12-02 17:13:57.725969+08
 public     | newt1     | user5      | ds_location1 | user5        | 2022-12-02 17:14:39.312258+08
(2 rows)

如何设置建表默认在 数据库的默认表空间上？

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

newdb1=> create table zyf_1 (id int) tablespace newtbs1;

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

newdb1=> select oid,a.* from pg_tablespace a;
  oid  |   spcname    | spcowner | spcacl | spcoptions | spcmaxsize | relative
-------+--------------+----------+--------+------------+------------+----------
  1663 | pg_default   |       10 |        |            |            | f
  1664 | pg_global    |       10 |        |            |            | f
 16401 | music_tbs    |       10 |        |            |            | t
 32785 | ds_location1 |       10 |        |            |            | t
 32792 | newtbs1      |       10 |        |            |            | t
(5 rows)


select schemaname,tablename ,tableowner,tablespace,tablecreator ,created from pg_tables where tablename in( 't1','newt1','zyf_1');


newdb1=> select  a.usename , a.usesysid  from pg_user a ;
 usename | usesysid
---------+----------
 omm     |       10
 zyf     |    16393
 user5   |    32794  <<<<<<
  (3 rows)


newdb1=>  select  oid,relname , relowner,reltablespace from pg_class where relowner=32794;
  oid  | relname | relowner | reltablespace
-------+---------+----------+---------------
 32798 | t1      |    32794 |             0
 32801 | newt1   |    32794 |         32785  >>>对应 pg_tablespace 
 32807 | zyf_1   |    32794 |             0
(3 rows)


newdb1=> select oid, *from pg_tablespace;
  oid  |   spcname    | spcowner | spcacl | spcoptions | spcmaxsize | relative
-------+--------------+----------+--------+------------+------------+----------
  1663 | pg_default   |       10 |        |            |            | f
  1664 | pg_global    |       10 |        |            |            | f
 16401 | music_tbs    |       10 |        |            |            | t
 32785 | ds_location1 |       10 |        |            |            | t
 32792 | newtbs1      |       10 |        |            |            | t
(5 rows)

[omm@node1 pg_tblspc]$ ls -l
total 0
lrwxrwxrwx 1 omm dbgrp 59 Nov 26 14:43 16401 -> /opt/huawei/install/data/dn/pg_location/tablespace/test_ts1
lrwxrwxrwx 1 omm dbgrp 63 Dec  1 10:30 32785 -> /opt/huawei/install/data/dn/pg_location/tablespace/tablespace_1
lrwxrwxrwx 1 omm dbgrp 62 Dec  2 17:09 32792 -> /opt/huawei/install/data/dn/pg_location/tablespace/tbs_newtbs1
[omm@node1 pg_tblspc]$

>>>> 32793 是什么 ？ 是 newdb1 的 oid 
[omm@node1 PG_9.2_201611171_dn_6001]$ ls
32793  pgsql_tmp 
[omm@node1 PG_9.2_201611171_dn_6001]$ cd 32793/
[omm@node1 32793]$



openGauss=# select oid,datname from pg_database;
  oid  |  datname
-------+-----------
     1 | template1
 16384 | zyfdb
 16402 | musicdb
 15621 | template0
 16403 | musicdb1
 16404 | musicdb2
 16405 | musicdb3
 15626 | postgres
 16427 | musicdb10
 32793 | newdb1
(10 rows)


[omm@node1 32793]$ ls -l *32807*
-rw------- 1 omm dbgrp 0 Dec  2 17:23 32807      ---> zyf_1表

[omm@node1 32793]$ ls -l *32798*
-rw------- 1 omm dbgrp 0 Dec  2 17:13 32798      ---> t1 表
[omm@node1 32793]$
[omm@node1 32793]$

 32801 | newt1   在哪里
找到   ds_location1 对应的  ds_location1   32785  

[omm@node1 PG_9.2_201611171_dn_6001]$ pwd
/opt/huawei/install/data/dn/pg_tblspc/32785/PG_9.2_201611171_dn_6001
omm@node1 PG_9.2_201611171_dn_6001]$ ls -l
total 0
drwx------ 2 omm dbgrp 19 Dec  1 10:35 16402
drwx------ 2 omm dbgrp 19 Dec  2 17:14 32793  ---> 对应newdb1数据库
drwx------ 2 omm dbgrp  6 Dec  1 10:30 pgsql_tmp

[omm@node1 PG_9.2_201611171_dn_6001]$
[omm@node1 PG_9.2_201611171_dn_6001]$ cd 32793/
[omm@node1 32793]$ ls -lrt
total 0
-rw------- 1 omm dbgrp 0 Dec  2 17:14 32801   ---> new1 表
[omm@node1 32793]$