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;

 

查看数据迁移进度
select * from pathman.pathman_concurrent_part_tasks;

禁用分区

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