一般来说,当一张表较大时都建议设计成分区表,这样对于数据的查询、插入、管理等等都有很大便利。在oracle中,普通表转分区表通常有4种方法:
- expor/import迁移数据
- 直接从原本重建分区表(插入数据)
- 交换分区
- 在线重定义
同样,在pg中也支持将普通表改造成分区表。
1、原表重建
这种方法比较简单,直接从原表导入数据创建原生分区即可。
创建原表:
bill=# create table t1 (id int not null,
bill(# info text,
bill(# crt_time timestamp);
CREATE TABLE
插入数据:
bill=# insert into t1 select generate_series(1,29),md5(random()::text),clock_timestamp();
INSERT 0 29
备份原表:
bill=# create table t_bak as select * from t1;
SELECT 29
创建分区表:
bill=# create table t_range(
bill(# id int not null,
bill(# info text,
bill(# crt_time timestamp)
bill-# partition by range(id);
CREATE TABLE
bill=# create table t_range_1 PARTITION of t_range for VALUES FROM (1) to (10);
CREATE TABLE
bill=# create table t_range_2 PARTITION of t_range for VALUES FROM (10) to (20);
CREATE TABLE
bill=# create table t_range_3 PARTITION of t_range for VALUES FROM (20) to (30);
CREATE TABLE
导入数据:
bill=# insert into t_range select * from t1;
INSERT 0 29
命名还原:
bill=# alter table t1 rename to t_old;
ALTER TABLE
bill=# alter table t_range rename to t1;
ALTER TABLE
如果表中有索引等需要重建,检查无误后可将原表删除。
2、pg_pathman分区插件
这种方法有些类似oracle的在线重定义,使用起来也很简单,不过需要先安装pg_pathman插件。
bill=# create extension pg_pathman;
CREATE EXTENSION
pg_pathman使用函数:
partition_table_concurrently(
relation REGCLASS, -- 主表OID
batch_size INTEGER DEFAULT 1000, -- 一个事务批量迁移多少记录
sleep_time FLOAT8 DEFAULT 1.0) -- 获得行锁失败时,休眠多久再次获取,重试60次退出任务。
例子:
bill=# select partition_table_concurrently('t_part'::regclass,
1000,
1.0);
NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task('t_part');
partition_table_concurrently
------------------------------
(1 row)
迁移结束后,主表数据已经没有了,全部在分区中
bill=# select count(*) from only t_part;
count
-------
0
(1 row)
数据迁移完成后,建议禁用主表,这样执行计划就不会出现主表了
bill=# select set_enable_parent('t_part'::regclass, false);
set_enable_parent
-------------------
(1 row)
3、逻辑复制
这种方法和oracle的export/import类似,都是通过逻辑导出导入的方式,pg中通过逻辑复制将普通表数据同步到分区表即可。
总结:
目前pg中主要通过这三种方式来实现普通表转分区表,需要注意的是在oracle中我们常用的分区交换方法目前在pg中还没法实现,不过在greenplum支持分区交换的语法。