pg_pathman测试例子 | 转

#创建测试表

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;
查看数据迁移进度
disable_pathman_for
select disable_pathman_for('trip_agg_data_tim'::regclass);禁用分区

作者：Super_Tim
链接：https://www.jianshu.com/p/f0f94db69efa