查看表空间的语法帮助
postgres=# \h create tablespaceCommand: CREATE TABLESPACEDescription: define a new tablespaceSyntax:CREATE TABLESPACE tablespace_name [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ] LOCATION 'directory' [ WITH ( tablespace_option = value [, ... ] ) ] 创建表空间
连接coordinator：
create tablespace tst_tbs location '/home/shboss/antdb/data/tablespace';
需要在集群涉及到的主机上都存在location指定的目录，否则报错：
postgres=# create tablespace tst_tbs location '/home/shboss/antdb/data/tablespace';ERROR: Fail to process utility query on remote node.DETAIL: ERROR: directory "/home/shboss/antdb/data/tablespace" does not exist
在各个主机上创建目录：
remote_cmd "mkdir -p /home/shboss/antdb/data/tablespace"
再次连接coordinator创建表空间：
postgres=# create tablespace tst_tbs location '/home/shboss/antdb/data/tablespace';CREATE TABLESPACE
查看表空间信息
数据库级别：
postgres=# \db List of tablespaces Name | Owner | Location ------------+--------+------------------------------------ pg_default | shboss | pg_global | shboss | tst_tbs | shboss | /home/shboss/antdb/data/tablespace(3 rows)postgres=# select * from pg_tablespace ; spcname | spcowner | spcacl | spcoptions ------------+----------+--------+------------ pg_default | 10 | | pg_global | 10 | | tst_tbs | 10 | | (3 rows)
文件系统：
coord节点：
[shboss@localhost1 pg_tblspc]$ pwd/home/shboss/antdb/data/coord/pg_tblspc
datanode 节点：
lrwxrwxrwx 1 shboss shboss 34 Jul 23 15:25 164514 -> /home/shboss/antdb/data/tablespace[shboss@localhost1 pg_tblspc]$ pwd/home/shboss/antdb/data/db1/pg_tblspc
表空间路径：
[shboss@localhost1 pg_tblspc]$ cd /home/shboss/antdb/data/tablespace[shboss@localhost1 tablespace]$ lltotal 12drwx------ 2 shboss shboss 4096 Jul 23 15:25 PG_9.6_201608131_coord1drwx------ 2 shboss shboss 4096 Jul 23 15:25 PG_9.6_201608131_coord5drwx------ 2 shboss shboss 4096 Jul 23 15:25 PG_9.6_201608131_db1_2[shboss@localhost1 tablespace]$ tree ..├── PG_9.6_201608131_coord1├── PG_9.6_201608131_coord5└── PG_9.6_201608131_db1_2
在表空间内建表
create table test_tbs (id int) tablespace tst_tbs;
查看表空间中的数据文件：
[shboss@localhost1 tablespace]$ tree ..├── PG_9.6_201608131_coord1│ └── 13603│ └── 476507├── PG_9.6_201608131_coord5│ └── 13603│ └── 18025└── PG_9.6_201608131_db1_2 └── 13597 └── 164515
使用其他普通用户在tst_tbs 表空间下创建表：
bmsql5=> create table test_tbs (id int) tablespace tst_tbs;ERROR: permission denied for tablespace tst_tbsbmsql5=>
解决办法1：给普通用户赋权
postgres=# grant create on tablespace tst_tbs to bmsql5_ora_fdw;GRANT
再次建表成功：
bmsql5=> create table test_tbs (id int) tablespace tst_tbs;CREATE TABLEbmsql5=> \d+ test_tbs Table "bmsql5_ora_fdw.test_tbs" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- id | integer | | plain | | Tablespace: "tst_tbs"Distribute By: HASH(id)Location Nodes: ALL DATANODESbmsql5=>
在创建之前，可以查询当前用户表空间tst_tbs 是否有权限：
bmsql5=> select has_tablespace_privilege('tst_tbs','create'); has_tablespace_privilege -------------------------- t(1 row) revoke权限后再次查询：
-- superuserpostgres=# revoke create on tablespace tst_tbs from bmsql5_ora_fdw;REVOKE-- 普通用户bmsql5=> select has_tablespace_privilege('tst_tbs','create'); has_tablespace_privilege -------------------------- f(1 row) 解决办法2：修改表空间的属主为表的创建用户
alter tablespace tst_tbs owner to bmsql5_ora_fdw;
再次建表成功：
bmsql5=> create table test_tbs (id int) tablespace tst_tbs;CREATE TABLEbmsql5=>
解决办法3：修改数据库的默认表空间
alter database bmsql5 set tablespace tst_tbs;postgres=# alter database bmsql5 set tablespace tst_tbs;ALTER DATABASETime: 323772.143 mspostgres=# postgres=# \l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+--------+----------+---------+-------+-------------------+---------+------------+-------------------------------------------- bmsql5 | shboss | UTF8 | C | C | | 26 GB | tst_tbs | db1 | shboss | UTF8 | C | C | | 1529 MB | pg_default | postgres | shboss | UTF8 | C | C | | 43 MB | pg_default | default administrative connection database template0 | shboss | UTF8 | C | C | =c/shboss +| 30 MB | pg_default | unmodifiable empty database | | | | | shboss=CTc/shboss | | | template1 | shboss | UTF8 | C | C | =c/shboss +| 30 MB | pg_default | default template for new databases | | | | | shboss=CTc/shboss | | | testdb | shboss | UTF8 | C | C | | 108 MB | pg_default | (6 rows)
这个操作的耗时取决于数据库的大小和磁盘的性能。且在数据文件移动过程中，该数据库无法连接。
[shboss@localhost1 tablespace]$ psql -d bmsql5psql: FATAL: database "bmsql5" does not existDETAIL: It seems to have just been dropped or renamed.
再次建表成功：
bmsql5=> create table test_tbs (id int) tablespace tst_tbs;CREATE TABLEbmsql5=> drop table test_tbs;DROP TABLEbmsql5=> create table test_tbs (id int);CREATE TABLE
碰到的问题：
更改的数据库上有连接的时候，没法修改：
postgres=# alter database bmsql5 set tablespace tst_tbs;ERROR: database "bmsql5" is being accessed by other usersDETAIL: There is 1 other session using the database.
连接到修改的数据库上也无法执行修改操作：
bmsql5=# alter database bmsql5 set tablespace tst_tbs;ERROR: cannot change the tablespace of the currently open database
如果修改的数据库中已经有对象在目标表空间中，也无法执行操作：
postgres=# alter database bmsql5 set tablespace tst_tbs;ERROR: some relations of database "bmsql5" are already in tablespace "tst_tbs"HINT: You must move them back to the database's default tablespace before using this command.
查找指定表空间中的对象：
select relname,reltype,spcnamefrom pg_class c, pg_tablespace tbswhere c.reltablespace=tbs.oidand tbs.spcname='tst_tbs';
查看表的表空间
postgres=# \d test_tbs Table "public.test_tbs" Column | Type | Modifiers --------+---------+----------- id | integer | Tablespace: "tst_tbs"
如果表没有使用所在数据库的默认表空间，则会在\d 的时候显示。
修改表的表空间
bmsql5=# select pg_relation_filepath('bmsql_stock'); pg_relation_filepath ---------------------- base/27636/461935(1 row)bmsql5=# alter table bmsql_stock set tablespace tst_tbs;ALTER TABLEbmsql5=# select pg_relation_filepath('bmsql_stock'); pg_relation_filepath ------------------------------------------------------- pg_tblspc/476505/PG_9.6_201608131_coord1/27636/476515(1 row)bmsql5=# 表很大的话，会比较耗时，是个挪动文件的过程，同时发现filenode是变了的。
查看表空间大小
postgres=# select pg_tablespace_size('tst_tbs'); pg_tablespace_size -------------------- 36757504(1 row)postgres=# select pg_size_pretty(pg_tablespace_size('tst_tbs')); pg_size_pretty ---------------- 35 MB(1 row) 删除表空间
postgres=# drop tablespace tst_tbs;ERROR: tablespace "tst_tbs" is not empty
如果表空间中有对象删除，则无法删除。
根据上面提供的查找指定表空间中的对象 语句找出对象移走或删除后，再进行删除：
postgres=# drop tablespace tst_tbs;DROP TABLESPACE
查看主机上的文件：
[shboss@localhost1 tablespace]$ tree ..0 directories, 0 files[shboss@localhost1 tablespace]$ pwd/home/shboss/antdb/data/tablespace
目录已经为空。