#创建测试表
create table tbl_pathman_test (id bigint,info text, ctime timestamp without time zone not null);
1. #按天分区,创建30个分区
#插入12条数据,每天一条
insert into tbl_pathman_test select id,md5(random()::text),clock_timestamp() + (id||' day')::interval from generate_series(1,12) t(id);
#插入12条数据,每天一条
hank=> select * from tbl_pathman_test; id | info | ctime ----+----------------------------------+---------------------------- 1 | 9cd4aaad3d0b8b31853cdb5d974c181e | 2021-06-23 16:48:03.132375 2 | 08479167e48b43daacc5ff3107d9a7c7 | 2021-06-24 16:48:03.132473
#按天分区,创建30个分区
select pathman.create_range_partitions('tbl_pathman_test'::regclass, 'ctime', '2021-06-23 00:00:00'::timestamp, interval '1 day', 30, false);
#分区如下
Child tables: tbl_pathman_test_1,
https://www.modb.pro/db/79225
select partition_table_concurrently ('trip_agg_data_tim'::regclass,10000, 1.0);
迁移数据到子表
10000 --一个事务批量迁移多少记录
1.0 --获得行锁失败时,休眠多久再次获取,重试60次退出任务
pathman_concurrent_part_tasks
select * from pathman_concurrent_part_tasks;
迁移数据到子表
10000 --一个事务批量迁移多少记录
1.0 --获得行锁失败时,休眠多久再次获取,重试60次退出任务
pathman_concurrent_part_tasks
select * from pathman_concurrent_part_tasks;
查看数据迁移进度
select * from pathman.pathman_concurrent_part_tasks;
作者:Super_Tim
链接:https://www.jianshu.com/p/f0f94db69efa