AntDB上使用表空间

查看表空间的语法帮助

postgres=# \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]
           

创建表空间

连接coordinator：

需要在集群涉及到的主机上都存在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
           

在各个主机上创建目录：

再次连接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
( rows)

postgres=# select * from pg_tablespace ;
  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 tst_tbs    |       10 |        | 
( rows)
           

文件系统：

coord节点：

[shboss@localhost1 pg_tblspc]$ pwd
/home/shboss/antdb/data/coord/pg_tblspc
           

datanode 节点：

lrwxrwxrwx  shboss shboss  Jul  :  -> /home/shboss/antdb/data/tablespace
[[email protected] pg_tblspc]$ pwd
/home/shboss/antdb/data/db1/pg_tblspc
           

表空间路径：

[shboss@localhost1 pg_tblspc]$ cd /home/shboss/antdb/data/tablespace
[shboss@localhost1 tablespace]$ ll
total 
drwx------  shboss shboss  Jul  : PG_9.coord1
drwx------  shboss shboss  Jul  : PG_9.coord5
drwx------  shboss shboss  Jul  : PG_9.db1_2
[shboss@localhost1 tablespace]$ tree .
.
├── PG_9.coord1
├── PG_9.coord5
└── PG_9.db1_2
           

在表空间内建表

查看表空间中的数据文件：

[shboss@localhost1 tablespace]$ tree .
.
├── PG_9.coord1
│   └── 
│       └── 
├── PG_9.coord5
│   └── 
│       └── 
└── PG_9.db1_2
    └── 
        └── 
           

使用其他普通用户在tst_tbs 表空间下创建表：

bmsql5=> create table test_tbs (id int) tablespace tst_tbs;
ERROR:  permission denied for tablespace tst_tbs
bmsql5=>
           

解决办法1：给普通用户赋权

postgres=# grant create on tablespace tst_tbs to bmsql5_ora_fdw;
GRANT
           

再次建表成功：

bmsql5=> create table test_tbs (id int) tablespace tst_tbs;
CREATE TABLE
bmsql5=> \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 DATANODES

bmsql5=> 
           

在创建之前，可以查询当前用户表空间tst_tbs 是否有权限：

bmsql5=> select has_tablespace_privilege('tst_tbs','create');
 has_tablespace_privilege 
--------------------------
 t
(1 row)
           

revoke权限后再次查询：

-- superuser
postgres=# 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：修改表空间的属主为表的创建用户

再次建表成功：

bmsql5=> create table test_tbs (id int) tablespace tst_tbs;
CREATE TABLE
bmsql5=> 
           

解决办法3：修改数据库的默认表空间

alter database bmsql5 set tablespace tst_tbs;

postgres=# alter database bmsql5 set tablespace tst_tbs;
ALTER DATABASE
Time:  ms
postgres=# 
postgres=# \l+ 
                                                            List of databases
   Name    | Owner  | Encoding | Collate | Ctype | Access privileges |  Size   | Tablespace |                Description                 
-----------+--------+----------+---------+-------+-------------------+---------+------------+--------------------------------------------
 bmsql5    | shboss | UTF8     | C       | C     |                   |  GB   | tst_tbs    | 
 db1       | shboss | UTF8     | C       | C     |                   |  MB | pg_default | 
 postgres  | shboss | UTF8     | C       | C     |                   |  MB   | pg_default | default administrative connection database
 template0 | shboss | UTF8     | C       | C     | =c/shboss        +|  MB   | pg_default | unmodifiable empty database
           |        |          |         |       | shboss=CTc/shboss |         |            | 
 template1 | shboss | UTF8     | C       | C     | =c/shboss        +|  MB   | pg_default | default template for new databases
           |        |          |         |       | shboss=CTc/shboss |         |            | 
 testdb    | shboss | UTF8     | C       | C     |                   |  MB  | pg_default | 
( rows)
           

这个操作的耗时取决于数据库的大小和磁盘的性能。且在数据文件移动过程中，该数据库无法连接。

[shboss@localhost1 tablespace]$ psql -d bmsql5
psql: FATAL:  database "bmsql5" does not exist
DETAIL:  It seems to have just been dropped or renamed.
           

再次建表成功：

bmsql5=> create table test_tbs (id int) tablespace tst_tbs;
CREATE TABLE
bmsql5=> drop table test_tbs;
DROP TABLE
bmsql5=> create table test_tbs (id int);
CREATE TABLE
           

碰到的问题：

更改的数据库上有连接的时候，没法修改：

postgres=# alter database bmsql5 set tablespace tst_tbs;
ERROR:  database "bmsql5" is being accessed by other users
DETAIL:  There is  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,spcname
from pg_class c, pg_tablespace tbs
where c.reltablespace=tbs.oid
and 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 TABLE
bmsql5=# 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 .
.

 directories,  files
[shboss@localhost1 tablespace]$ pwd
/home/shboss/antdb/data/tablespace
           

目录已经为空。