标签：postgresql postgres tbs t2 t1 test pg 将表 移动
创建新的表空间
[postgres@db1 ~]$ cd /app/pg/tbs_test
[postgres@db1 ~]$ mkdir tbs_test
- 使用root用户登录数据库，创建表空间并授权给u1用户使用
mydb=# create tablespace tbs_test location '/app/pg/tbs_test';
CREATE TABLESPACE
mydb=# grant create on tablespace tbs_test to u1;
GRANT
mydb=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+-------+----------------------+-------------------+---------+---------+-------------
pg_default | root | | | | 13 GB |
pg_global | root | | | | 967 kB |
tbs_test | root | /app/pg/tbs_test | root=C/root +| | 0 bytes |
| | | u1=C/root | | |
(3 rows)
创建测试表
- t1普通表 idx_t1 索引
- t2分区表 idx_t2 索引
- t3分区表 idx_t3 索引
CREATE TABLE t1 (id integer,school varchar(20),class varchar(20));
create index idx_t1 on t1(id);
CREATE TABLE t2 (
id integer,
state varchar(6)
) PARTITION BY RANGE (id) ;
create index idx_t2 on t2(id);
CREATE TABLE t2_p2111 PARTITION OF t2
FOR VALUES FROM (202111) TO (202112);
CREATE TABLE t2_p2112 PARTITION OF t2
FOR VALUES FROM (202112) TO (202201);
CREATE TABLE t3 (
id integer,
name varchar(6)
) PARTITION BY RANGE (id) ;
create index idx_t3 on t3(id);
CREATE TABLE t3_p1 PARTITION OF t3
FOR VALUES FROM (1) TO (2);
CREATE TABLE t3_p2 PARTITION OF t3
FOR VALUES FROM (2) TO (3);
移动普通表t1
mydb=# INSERT INTO t1 SELECT n, n|| '_sch',n|| '_cs' FROM generate_series(1,50000000) n;
INSERT 0 50000000
mydb=# \dt+ t1
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+---------+-------------
u1 | t1 | table | u1 | 2872 MB |
(1 row)
mydb=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/65573/83366
(1 row)
mydb=# alter table t1 set tablespace tbs_test;
ALTER TABLE
- 移动过程中监控两个表空间目录文件情况
- base 目录下，移动过程中表t1对应的83366文件一直存在
[postgres@db1 ~]$ cd /app/pg/data/base/65573
[postgres@db1 65573]$ ll -h|grep 83366
-rw------- 1 postgres postgres 1.0G Mar 15 16:48 83366
-rw------- 1 postgres postgres 1.0G Mar 15 16:48 83366.1
-rw------- 1 postgres postgres 824M Mar 15 16:48 83366.2
-rw------- 1 postgres postgres 744K Mar 15 16:48 83366_fsm
移动完成后，表t1 对应的83366文件不会消失，大小为 0
[postgres@db1 65573]$ ll -h|grep 83366
-rw------- 1 postgres postgres 0 Mar 15 16:49 83366
[postgres@db1 ~]$ cd /app/pg/tbs_test/PG_12_201909212/65573
[postgres@db1 65573]$ ll -h
-rw------- 1 postgres postgres 1.0G Mar 15 16:48 83370
-rw------- 1 postgres postgres 1.0G Mar 15 16:49 83370.1
-rw------- 1 postgres postgres 824M Mar 15 16:49 83370.2
-rw------- 1 postgres postgres 744K Mar 15 16:49 83370_fsm
移动完成后，确认t1表的文件路径，与上述目录相同
mydb=# select pg_relation_filepath('t1');
pg_relation_filepath
---------------------------------------------
pg_tblspc/83275/PG_12_201909212/65573/83370
(1 row)
- 查看普通表t1和索引idx_t1的存储路径，可以发现移动表t1时，表上的索引不会跟着移动，并且索引不会失效
mydb=# select a.oid,relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), (case when tb.spcname is null then 'pg_default' else tb.spcname end),pg_tablespace_location(tb.oid)
from pg_class a left join pg_tablespace tb
on a.reltablespace = tb.oid where
a.relkind in ('r','p','i') and relowner!=10 and relname in ('t1','idx_t1');
oid | relname | relkind | relpages | pg_size_pretty | spcname | pg_tablespace_location
-------+---------+---------+----------+----------------+------------+------------------------
83369 | idx_t1 | i | 137095 | 1071 MB | pg_default |
83366 | t1 | r | 367549 | 2871 MB | tbs_test | /app/pg/tbs_test
查看执行计划，可知 idx_t1 状态有效
mydb=# explain select * from t1 where id=1;
QUERY PLAN
------------------------------------------------------------------
Index Scan using idx_t1 on t1 (cost=0.56..8.58 rows=1 width=27)
Index Cond: (id = 1)
(2 rows)
shx=# alter index idx_t1 set tablespace tbs_test;
ALTER INDEX
shx=# select a.oid,relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), (case when tb.spcname is null then 'pg_default' else tb.spcname end),pg_tablespace_location(tb.oid)
from pg_class a left join pg_tablespace tb
on a.reltablespace = tb.oid where
a.relkind in ('r','p','i') and relowner!=10 and relname in ('t1','idx_t1');
oid | relname | relkind | relpages | pg_size_pretty | spcname | pg_tablespace_location
-------+---------+---------+----------+----------------+----------+------------------------
83366 | t1 | r | 367549 | 2871 MB | tbs_test | /app/pg/tbs_test
83369 | idx_t1 | i | 137095 | 1071 MB | tbs_test | /app/pg/tbs_test
移动分区表t2
mydb=# alter table t2 set tablespace tbs_test;
ALTER TABLE
- 查看t2相关对象的表空间情况，可以发现只有父表t2的表空间发生变化，子表的表空间未改变
mydb=# select a.oid,relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), (case when tb.spcname is null then 'pg_default' else tb.spcname end),pg_tablespace_location(tb.oid)
from pg_class a left join pg_tablespace tb
on a.reltablespace = tb.oid where
a.relkind in ('r','p','i','I') and relowner!=10 and relname like '%t2%' order by 2;
oid | relname | relkind | relpages | pg_size_pretty | spcname | pg_tablespace_location
-------+-----------------+---------+----------+----------------+------------+------------------------
83363 | idx_t2 | I | 0 | 0 bytes | pg_default |
83360 | t2 | p | 0 | 0 bytes | tbs_test | /app/pg/tbs_test
83372 | t2_p2111 | r | 0 | 0 bytes | pg_default |
83375 | t2_p2111_id_idx | i | 1 | 8192 bytes | pg_default |
83376 | t2_p2112 | r | 0 | 0 bytes | pg_default |
83379 | t2_p2112_id_idx | i | 1 | 8192 bytes | pg_default |
CREATE TABLE t2_p2201 PARTITION OF t2
FOR VALUES FROM (202201) TO (202202);
- 查看分区子表的表空间，可以发现新创建的子表存放于tbs_test中，但是子表上的索引不会更改表空间
mydb=# select a.oid,relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), (case when tb.spcname is null then 'pg_default' else tb.spcname end),pg_tablespace_location(tb.oid)
from pg_class a left join pg_tablespace tb
on a.reltablespace = tb.oid where
a.relkind in ('r', 'p','i','I') and relowner!=10 and relname like 't2_p2201%' order by 2;
oid | relname | relkind | relpages | pg_size_pretty | spcname | pg_tablespace_location
-------+-----------------+---------+----------+----------------+------------+------------------------
83380 | t2_p2201 | r | 0 | 0 bytes | tbs_test | /app/pg/tbs_test
83383 | t2_p2201_id_idx | i | 1 | 8192 bytes | pg_default |
移动分区表t3的子表
mydb=# alter table t3_p1 set tablespace tbs_test;
ALTER TABLE
- 查看t3表的表空间，可以发现分区子表t3_p1表空间已更改，但表上的索引不随之移动
mydb=# select a.oid,relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), (case when tb.spcname is null then 'pg_default' else tb.spcname end),pg_tablespace_location(tb.oid)
from pg_class a left join pg_tablespace tb
on a.reltablespace = tb.oid where
a.relkind in ('r', 'p','i','I') and relowner!=10 and relname like '%t3%' order by 2;
oid | relname | relkind | relpages | pg_size_pretty | spcname | pg_tablespace_location
-------+--------------+---------+----------+----------------+------------+------------------------
83387 | idx_t3 | I | 0 | 0 bytes | pg_default |
83384 | t3 | p | 0 | 0 bytes | pg_default |
83388 | t3_p1 | r | 0 | 0 bytes | tbs_test | /app/pg/tbs_test
83391 | t3_p1_id_idx | i | 1 | 8192 bytes | pg_default |
83392 | t3_p2 | r | 0 | 0 bytes | pg_default |
83395 | t3_p2_id_idx | i | 1 | 8192 bytes | pg_default |
总结
- 1）SET TABLESPACE 子句将表的表空间更改为指定的表空间，并将与表关联的数据文件移动到新的表空间
- 2）移动普通表或分区子表，会更改表空间，但是表上索引不会跟着移动，并且不会失效；如果要移动索引所在的表空间，需要另外执行alter index
- 3）分区表移动父表的话，子表不会跟着移动，之后使用 CREATE TABLE PARTITION OF创建子表的时候，会自动存放于指定的表空间中
标签：postgresql,
postgres,
tbs,
t2,
t1,
test,
pg,
将表,
移动
From： https://blog.51cto.com/u_13482808/7473787