HGDB表空间之间移动数据

一、迁移单个表至另一个表空间
highgo=# create tablespace test location '/home/highgo/test';
CREATE TABLESPACE
highgo=# select * from pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 test       |       10 |        | 
(3 rows)


highgo=# \d test
        Table "public.test"
 Column |     Type     | Modifiers 
--------+--------------+-----------
 id     | integer      | 
 name   | character(5) | 
Indexes:
    "t_i_2" btree (id)

highgo=# alter table test set tablespace test;
ALTER TABLE
highgo=# \d test
        Table "public.test"
 Column |     Type     | Modifiers 
--------+--------------+-----------
 id     | integer      | 
 name   | character(5) | 
Indexes:
    "t_i_2" btree (id)
Tablespace: "test"


二、迁移整个数据库至另一个表空间
highgo=# select datname,dattablespace from pg_database where datname='benchmarksql';
   datname    | dattablespace 
--------------+---------------
 benchmarksql |          1663
(1 row)


highgo=# select oid,spcname from pg_tablespace where oid=1663;
 oid  |  spcname   
------+------------
 1663 | pg_default
(1 row)

[root@sourcedb PG_9.4_201409291]# pwd
/home/highgo/test/PG_9.4_201409291
[root@sourcedb PG_9.4_201409291]# ll
total 4
drwx------ 2 highgo highgo 4096 Aug 10 16:29 13010
[root@sourcedb PG_9.4_201409291]# 

highgo=# alter database benchmarksql set tablespace test;
ALTER DATABASE
highgo=# 

[root@sourcedb PG_9.4_201409291]# ll
total 8
drwx------ 2 highgo highgo 4096 Aug 10 16:29 13010
drwx------ 2 highgo highgo 4096 Aug 10 16:32 41209

highgo=# select datname,dattablespace from pg_database where datname='benchmarksql';
   datname    | dattablespace 
--------------+---------------
 benchmarksql |         41255
(1 row)

highgo=# select oid,spcname from pg_tablespace where oid=41255;
  oid  | spcname 
-------+---------
 41255 | test
(1 row)

highgo=# select oid,spcname from pg_tablespace where oid=1663;
 oid  |  spcname   
------+------------
 1663 | pg_default
(1 row)


查看表空间对应的物理路径：
highgo=# \db
           List of tablespaces
    Name    | Owner  |     Location      
------------+--------+-------------------
 pg_default | highgo | 
 pg_global  | highgo | 
 test       | highgo | /home/highgo/test
(3 rows)

**********************************************************
将以上对象重新移动到pg_default表空间中：
highgo=# alter database benchmarksql set  tablespace pg_default;
ALTER DATABASE
highgo=# alter table test set tablespace pg_default;
ALTER TABLE
highgo=# \d test
        Table "public.test"
 Column |     Type     | Modifiers 
--------+--------------+-----------
 id     | integer      | 
 name   | character(5) | 
Indexes:
    "t_i_2" btree (id)

highgo=# select datname,dattablespace from pg_database where datname='benchmarksql';
   datname    | dattablespace 
--------------+---------------
 benchmarksql |          1663
(1 row)

highgo=# select oid,spcname from pg_tablespace where oid=1663;
 oid  |  spcname   
------+------------
 1663 | pg_default
(1 row)
*****************************************************************************
三、把整个表空间的数据全部移动到另一个表空间中
目前HGDB及社区版PG中均不支持此用法。

俄罗斯版PostgreSQL9.4中引入了一个新的功能：一次性把一个空间的多个对象迁移到另一个表空间。如果目录执行者是超级用户，那么源表空间所有的对象都会被迁移过去；否则仅会迁移执行者所拥有的对象。
将pg_default默认表空间中的所有对象迁移至secondry表空间，命令如下：
ALTER TABLESPACE name MOVE { ALL | TABLES | INDEXES | MATERIALIZED VIEWS } [ OWNED BY role_name [, ...] ] TO new_tablespace [ NOWAIT ]
alter tablespace pg_default MOVE ALL TO secondry;
注：在迁移过程中所涉及的database和表会被锁定。

参考链接
http://php5.kiev.ua/pgsql/sql-altertablespace.html

ALTER TABLESPACE -- change the definition of a tablespace or affect objects of a tablespace
SYNOPSIS

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO new_owner
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] )
ALTER TABLESPACE name RESET ( tablespace_option [, ... ] )
ALTER TABLESPACE name MOVE { ALL | TABLES | INDEXES | MATERIALIZED VIEWS } [ OWNED BY role_name [, ...] ] TO new_tablespace [ NOWAIT ]
DESCRIPTION