1. 参考资料

参考了前辈们的总结
https://yq.aliyun.com/articles/62314
http://www.jianshu.com/p/7a8737250456

2. 安装配置

安装pg_pathman的rpm包

  1. rpm -ivh pg_pathman96-1.3.1-1.rhel6.x86_64.rpm

修改postgresql.conf配置文件,将pg_pathman添加到预加载lib中（注）

  1. su - postgres
  2. vim /pgsql/data/postgresql.conf
  3. ...
  4. shared_preload_libraries = 'pg_pathman,...'

重启PG使配置文件的修改生效

  1. pg_ctl restart

创建extension

  1. postgres=# create extension pg_pathman ;
  2. CREATE EXTENSION
  3. postgres=# \dx
  4.                    List of installed extensions
  5.     Name    | Version |   Schema   |         Description          
  6. ------------+---------+------------+------------------------------
  7.  pg_pathman | 1.3     | public     | Partitioning tool
  8.  plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
  9. (2 rows)

注意：pg_pathman必须由Postmaster进程初始化，所以一定要写在shared_preload_libraries参数中，否则在create extension时会报错

3. 创建分区表

目前pathman仅支持hash和range分区，像mysql的list分区可以用range代替，比如按照每月31天，分为31个list的情况，可以定义range的间隔为1，分区个数31

3.1 hash分区

  1. oas_route=# select create_hash_partitions
  2. ('oa_epp_user'::regclass, --主表OID
  3. 'PART_CODE',              --分区列名，必须有not null约束
  4. 10,                       --分区个数
  5. false);                   --不立即迁移数据
  6.  create_hash_partitions 
  7. ------------------------
  8.                      10
  9. (1 row)

3.2 range分区

  1. oas_route=# select create_range_partitions
  2. ('oasc_b_trade_order_storage'::regclass, --主表OID
  3. 'day',                                     --分区列名，必须有not null约束
  4. 1,                                         --起始值
  5. 1,                                         --间隔值
  6. 31,                                         --分区个数
  7. false);                                     --不立即迁移数据
  8.  create_range_partitions 
  9. -------------------------
  10.                       31
  11. (1 row)

注意:

  1. 分区列必须有not null约束
  2. 分区个数必须能覆盖已有的所有记录

3.3 迁移数据到分区表

partition_table_concurrently函数不会发生阻塞

  1. oas_route=# select partition_table_concurrently
  2. ('oa_epp_user'::regclass,                --主表OID
  3. 10000,                                    --一个事务批量迁移多少记录
  4.  1.0);                                    --获得行锁失败时，休眠多久再次获取，重试60次退出任务
  5. NOTICE:  worker started, you can stop it with the following command: select public.stop_concurrent_part_task('oa_epp_user');
  6.  partition_table_concurrently 
  7. ------------------------------
  9. (1 row)

停止数据迁移任务

  1. oas_route=#select stop_concurrent_part_task(relation REGCLASS);

查看后台的数据迁移任务

  1. oas_route=#select * from pathman_concurrent_part_tasks;
  2.  userid | pid | dbid | relid | processed | status 
  3. --------+-----+------+-------+-----------+--------
  4. (0 rows)

数据迁移完成之后，select only主表的话，应该已经没有数据了，所有的数据都在分区表中

  1. oas_route=# select count(*) from only oasc_b_recharge_order_storage;
  2.  count 
  3. -------
  4.      0
  5. (1 row)

数据迁移完成后，建议禁用主表，这样执行计划就不会出现主表了

  1. oas_route=# select set_enable_parent('oasc_b_recharge_order_storage'::regclass,false);
  2.  set_enable_parent 
  3. -------------------
  5. (1 row)

4. 管理分区

4.1 自动新增分区

目前仅支持range分区表，insert一条不在现有分区范围的数据，再查看主表的属性，发现新增了10个分区，分区规则和既有的分区规则一致

  1. test_db=# insert into tb1 values(20000000,'c2_char','c3_varchar');
  2. test_db=# \d+ tb1
  3.                              Table "public.tb1"
  4.  Column |         Type          | Modifiers | Storage  | Stats target | Description 
  5. --------+-----------------------+-----------+----------+--------------+-------------
  6.  c1     | integer               | not null  | plain    |              | 
  7.  c2     | character(10)         |           | extended |              | 
  8.  c3     | character varying(10) |           | extended |              | 
  9. Indexes:
  10.     "tb1_pkey" PRIMARY KEY, btree (c1)
  11. Child tables: tb1_1,
  12.               tb1_10,
  13.               tb1_11,
  14.               tb1_12,
  15.               tb1_13,
  16.               tb1_14,
  17.               tb1_15,
  18.               tb1_16,
  19.               tb1_17,
  20.               tb1_18,
  21.               tb1_19,
  22.               tb1_2,
  23.               tb1_20,
  24.               tb1_3,
  25.               tb1_4,
  26.               tb1_5,
  27.               tb1_6,
  28.               tb1_7,
  29.               tb1_8,
  30.               tb1_9

备注：不推荐这么使用，如果插入的数据跨度很大，那么会自动创建很多的分区出来

4.2 分裂范围分区


对于某个比较大的范围分区，可以使用函数将分区分为2个分区

  1. split_range_partition(partition      REGCLASS,            -- 分区oid
  2.                   split_value    ANYELEMENT,          -- 分裂值
  3.                   partition_name TEXT DEFAULT NULL)   -- 分裂后新增的分区表名

使用该方法，数据会自动迁移到另一个分区

4.3 合并范围分区

指定两个需要合并分区，必须为相邻分区

  1. merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)

4.4 向后添加范围分区


在末尾追加分区，沿用之前分区的规则

  1. append_range_partition(parent         REGCLASS,            -- 主表OID
  2.                    partition_name TEXT DEFAULT NULL,   -- 新增的分区表名, 默认不需要输入
  3.                    tablespace     TEXT DEFAULT NULL)   -- 新增的分区表放到哪个表空间, 默认不需要输入

4.5 向前添加分区


在头部追加分区，函数用法和3.4节中的类似

  1. prepend_range_partition(parent         REGCLASS,
  2.                     partition_name TEXT DEFAULT NULL,
  3.                     tablespace     TEXT DEFAULT NULL)

4.6 添加单独分区

需要确保，添加的该分区不会存在数据交叉

  1. add_range_partition(relation       REGCLASS,    -- 主表OID
  2.                 start_value    ANYELEMENT,  -- 起始值
  3.                 end_value      ANYELEMENT,  -- 结束值
  4.                 partition_name TEXT DEFAULT NULL,  -- 分区名
  5.                 tablespace     TEXT DEFAULT NULL)  -- 分区创建在哪个表空间下

4.7 删除分区

4.7.1 删除单个分区

  1. drop_range_partition(partition TEXT,   -- 分区名称
  2.                 delete_data BOOLEAN DEFAULT TRUE)  -- 是否删除分区数据，如果false，表示分区数据迁移到主表

4.7.2 删除所有分区

  1. drop_partitions(parent      REGCLASS,
  2.             delete_data BOOLEAN DEFAULT FALSE)

4.8 绑定已有的表到分区中


将已经存在的某张表绑定到某个分区表之中，需要确保表的结构是一致的，包括被drop掉的column

  1. attach_range_partition(relation    REGCLASS,    -- 主表OID
  2.                    partition   REGCLASS,    -- 分区表OID
  3.                    start_value ANYELEMENT,  -- 起始值
  4.                    end_value   ANYELEMENT)  -- 结束值

4.9 解绑分区

解绑分区，只是删除继承关系和约束，数据保留

  1. detach_range_partition(partition REGCLASS)  -- 指定分区名，转换为普通表

5. 小结

pg_pathman提供了一套方便的创建和管理分区表的函数，解决了传统的通过表继承来实现分区表功能的繁琐