修改表（数据库）的表空间

1、将表mytable迁移到新建表空间test2下（表空间test2的路径为/home/highgo/tbs2）查询表内容：highgo=# select * from mytable ; id ----  1  2  3(3 rows)创建表空间test2并迁移mytable到test2下：highgo=# create tablespace test2 location '/home/highgo/tbs2';CREATE TABLESPACEhighgo=# alter table mytable set tablespace test2;ALTER TABLE查询是否迁移成功highgo=# select pg_relation_filepath ('mytable');             pg_relation_filepath             ---------------------------------------------- pg_tblspc/49442/PG_9.5_201510051/13351/49443(1 row)[highgo@hgdb pg_tblspc]$ pwd/data/data/pg_tblspc[highgo@hgdb pg_tblspc]$ lllrwxrwxrwx 1 highgo highgo 17 Dec 18 13:49 49442 -> /home/highgo/tbs2drwx------ 3 highgo highgo 30 Dec  7 14:04 tmphighgo=# select tablename,tablespace from pg_tables where tablename in ('mytable'); tablename | tablespace -----------+------------ mytable   | test2(1 row)将表mytable迁移到默认表空间：highgo=# alter table mytable set tablespace pg_default;ALTER TABLEhighgo=# select tablename,tablespace from pg_tables where tablename in ('mytable'); tablename | tablespace -----------+------------ mytable   | (1 row)2、将highgo数据库的所有对象都移动到test2表空间中.highgo=# select * from mytable2; id ----  4  5  6(3 rows)highgo=# alter database highgo set tablespace test2;ERROR:  55006: cannot change the tablespace of the currently open databasetest=# alter database highgo set tablespace test2;ALTER DATABASEhighgo=# select pg_relation_filepath('mytable');             pg_relation_filepath             ---------------------------------------------- pg_tblspc/49442/PG_9.5_201510051/13351/49444(1 row)highgo=# select pg_relation_filepath('mytable2');             pg_relation_filepath             ---------------------------------------------- pg_tblspc/49442/PG_9.5_201510051/13351/49445(1 row)[highgo@hgdb pg_tblspc]$ pwd/data/data/pg_tblspc[highgo@hgdb pg_tblspc]$ lllrwxrwxrwx 1 highgo highgo 17 Dec 18 13:49 49442 -> /home/highgo/tbs2drwx------ 3 highgo highgo 30 Dec  7 14:04 tmp如上所示：mytable和mytable2 都已经迁移到了test2表空间下。By 天蝎座