Postgresql - Table Partitioning （四）

下面看一下对分区表的维护

删除分区：

DROP TABLE measurement_y2018m02;

# 将表从分区中分离，但不删除表。

ALTER TABLE measurement DETACH PARTITION measurement_y2018m02;

# 创建新的分区

mkdir -p /data/pg_ts_fasttablespace

chown -R postgres:postgres pg_ts_fasttablespace

create tablespace fasttablespace owner mytest LOCATION '/data/pg_ts_fasttablespace';

CREATE TABLE measurement_y2019m03 PARTITION OF measurement FOR VALUES FROM ('2019-03-01') TO ('2019-04-01') TABLESPACE fasttablespace;

# 创建新的分区

CREATE TABLE measurement_y2019m04 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS) TABLESPACE fasttablespace;

# 修改分区

ALTER TABLE measurement_y2019m03 ADD CONSTRAINT y2019m03 CHECK ( logdate >= DATE '2008-03-01' AND logdate < DATE '2008-04-01' );

# 复制数据

\copy measurement_y2008m02 from 'measurement_y2008m02'

-- possibly some other data preparation work

# 增加分区

ALTER TABLE measurement ATTACH PARTITION measurement_y2019m04 FOR VALUES FROM ('2019-04-01') TO ('2019-05-01' );