您的当前位置：首页 PostgreSQL分区表实现——pg_pathman分区表管理

PostgreSQL分区表实现——pg_pathman分区表管理

来源：意榕旅游网

pg_pathman 创建分区表时，需要指定主表的名字，主表必须已存在，主表可以有数据，也可以是空表。如果主表有数据，那么可以配置是否需要在创建分区时，将数据迁移到分区，（不建议对⼤表这么做）。

如果主表有很多数据，建议使⽤后台⾮堵塞式的迁移⽅法。（调⽤partition_table_concurrently()函数进⾏迁移）。

如果在创建分区表前，使⽤set_init_callback(relation regclass, callback regproc DEFAULT 0)设置了回调函数，则创建分区时，每个分区表创建是，会⾃动调⽤对应的回调函数。

回调函数的传⼊参数和模式如下

$part_init_callback$(args JSONB) RETURNS VOID传⼊参数如下

/* RANGE-partitioned table abc (for exp: child abc_4) */{

\"parent\": \"abc\ \"parttype\": \"2\ \"partition\": \"abc_4\ \"range_max\": \"401\ \"range_min\": \"301\"}

/* HASH-partitioned table abc (for exp: child abc_0) */{

\"parent\": \"abc\ \"parttype\": \"1\ \"partition\": \"abc_0\"}

1. range分区

有4个管理函数⽤来创建范围分区

指定起始值、间隔、分区个数

create_range_partitions(relation REGCLASS, -- 主表OID attribute TEXT, -- 分区列名

start_value ANYELEMENT, -- 开始值

p_interval ANYELEMENT, -- 间隔；任意类型，适合任意类型的分区表 p_count INTEGER DEFAULT NULL, -- 分多少个区

partition_data BOOLEAN DEFAULT TRUE) -- 是否⽴即将数据从主表迁移到分区, 不建议这么使⽤, 建议使⽤⾮堵塞式的迁移( 调⽤partition_table_concurrently() )create_range_partitions(relation REGCLASS, -- 主表OID attribute TEXT, -- 分区列名

start_value ANYELEMENT, -- 开始值

p_interval INTERVAL, -- 间隔；interval 类型，⽤于时间分区表 p_count INTEGER DEFAULT NULL, -- 分多少个区

partition_data BOOLEAN DEFAULT TRUE) -- 是否⽴即将数据从主表迁移到分区, 不建议这么使⽤, 建议使⽤⾮堵塞式的迁移( 调⽤partition_table_concurrently() )

指定起始值、终值、间隔

create_partitions_from_range(relation REGCLASS, -- 主表OID attribute TEXT, -- 分区列名

start_value ANYELEMENT, -- 开始值 end_value ANYELEMENT, -- 结束值

p_interval ANYELEMENT, -- 间隔；任意类型，适合任意类型的分区表

partition_data BOOLEAN DEFAULT TRUE) -- 是否⽴即将数据从主表迁移到分区, 不建议这么使⽤, 建议使⽤⾮堵塞式的迁移( 调⽤partition_table_concurrently(

create_partitions_from_range(relation REGCLASS, -- 主表OID attribute TEXT, -- 分区列名

start_value ANYELEMENT, -- 开始值 end_value ANYELEMENT, -- 结束值

p_interval INTERVAL, -- 间隔；interval 类型，⽤于时间分区表

partition_data BOOLEAN DEFAULT TRUE) -- 是否⽴即将数据从主表迁移到分区, 不建议这么使⽤, 建议使⽤⾮堵塞式的迁移( 调⽤partition_table_concurrently(

例⼦

创建需要分区的主表

postgres=# create table part_test(id int, info text, crt_time timestamp not null); -- 分区列必须有not null约束 CREATE TABLE

插⼊⼀批测试数据，模拟已经有数据了的主表

postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id); INSERT 0 10000

postgres=# select * from part_test limit 10; id | info | crt_time

----+----------------------------------+---------------------------- 1 | 36fe1adedaa5b848caec4941f87d443a | 2016-10-25 10:27:13.206713 2 | c7d7358e196a9180efb4d0a10269c889 | 2016-10-25 11:27:13.206893 3 | 005bdb063550579333264b895df5b75e | 2016-10-25 12:27:13.206904 4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 2016-10-25 13:27:13.20691 5 | 857214d8999348ed3cb0469b520dc8e5 | 2016-10-25 14:27:13.206916 6 | 4495875013e96e625afbf2698124ef5b | 2016-10-25 15:27:13.206921 7 | 82488cf7e44f87d9b879c70a9ed407d4 | 2016-10-25 16:27:13.20693 8 | a0b92547c8f17f79814dfbb12b8694a0 | 2016-10-25 17:27:13.206936 9 | 2ca09e0b85042b476fc235e75326b41b | 2016-10-25 18:27:13.206942

10 | 7eb762e1ef7dca65faf413f236dff93d | 2016-10-25 19:27:13.206947(10 rows)

注意:

1. 分区列必须有not null约束

2. 分区个数必须能覆盖已有的所有记录

创建分区，每个分区包含1个⽉的跨度数据 postgres=# select

create_range_partitions('part_test'::regclass, -- 主表OID 'crt_time', -- 分区列名

'2016-10-25 00:00:00'::timestamp, -- 开始值

interval '1 month', -- 间隔；interval 类型，⽤于时间分区表 24, -- 分多少个区 false) ; -- 不迁移数据

NOTICE: sequence \"part_test_seq\" does not exist, skipping create_range_partitions ------------------------- 24(1 row)

postgres-# \\d+ part_test

Table \"public.part_test\"

crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_1, part_test_10, part_test_11, part_test_12, part_test_13, part_test_14, part_test_15, part_test_16, part_test_17, part_test_18, part_test_19, part_test_2, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_3, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9

由于不迁移数据，所以数据还在主表

postgres=# select count(*) from only part_test; count ------- 10000(1 row)

使⽤⾮堵塞式的迁移接⼝

partition_table_concurrently(relation REGCLASS, -- 主表OID

batch_size INTEGER DEFAULT 1000, -- ⼀个事务批量迁移多少记录

sleep_time FLOAT8 DEFAULT 1.0) -- 获得⾏锁失败时，休眠多久再次获取，重试60次退出任务。

postgres=# select partition_table_concurrently('part_test'::regclass, 10000, 1.0);

NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test'); partition_table_concurrently ------------------------------

(1 row)

迁移结束后，主表数据已经没有了，全部在分区中postgres=# select count(*) from only part_test; count ------- 0(1 row)

数据迁移完成后，建议禁⽤主表，这样执⾏计划就不会出现主表了postgres=# select set_enable_parent('part_test'::regclass, false);

set_enable_parent -------------------

(1 row)

postgres=# explain select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; QUERY PLAN --------------------------------------------------------------------------------- Append (cost=0.00..16.18 rows=1 width=45)

-> Seq Scan on part_test_1 (cost=0.00..16.18 rows=1 width=45)

Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)(3 rows)建议

1. 分区列必须有not null约束

2. 分区个数必须能覆盖已有的所有记录3. 建议使⽤⾮堵塞式迁移接⼝4. 建议数据迁移完成后，禁⽤主表

2. hash分区

有1个管理函数⽤来创建范围分区

指定起始值、间隔、分区个数

create_hash_partitions(relation REGCLASS, -- 主表OID attribute TEXT, -- 分区列名

partitions_count INTEGER, -- 打算创建多少个分区

partition_data BOOLEAN DEFAULT TRUE) -- 是否⽴即将数据从主表迁移到分区, 不建议这么使⽤, 建议使⽤⾮堵塞式的迁移( 调⽤partition_table_concurrently() )例⼦

创建需要分区的主表

postgres=# create table part_test(id int, info text, crt_time timestamp not null); -- 分区列必须有not null约束 CREATE TABLE

插⼊⼀批测试数据，模拟已经有数据了的主表

postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id); INSERT 0 10000

postgres=# select * from part_test limit 10; id | info | crt_time

----+----------------------------------+---------------------------- 1 | 29ce4edc70dbfbe78912beb7c4cc95c2 | 2016-10-25 10:47:32.873879 2 | e0990a6fb5826409667c9eb150fef386 | 2016-10-25 11:47:32.874048 3 | d25f577a01013925c203910e34470695 | 2016-10-25 12:47:32.874059 4 | 501419c3f7c218e562b324a1bebfe0ad | 2016-10-25 13:47:32.874065 5 | 5e5e22bdf110d66a5224a657955ba158 | 2016-10-25 14:47:32.87407 6 | 55d2d4fd5229a6595e0dd56e13d32be4 | 2016-10-25 15:47:32.874076 7 | 1dfb9a783af55b123c7a888afe1eb950 | 2016-10-25 16:47:32.874081 8 | 41eeb0bf395a4ab1e08691125ae74bff | 2016-10-25 17:47:32.874087 9 | 83783d69cc4f9bb41a3978fe9e13d7fa | 2016-10-25 18:47:32.874092 10 | affc9406d5b3412ae31f7d7283cda0dd | 2016-10-25 19:47:32.874097(10 rows)

注意:

1. 分区列必须有not null约束

创建128个分区

postgres=# select

create_hash_partitions('part_test'::regclass, -- 主表OID 'crt_time', -- 分区列名

128, -- 打算创建多少个分区 false) ; -- 不迁移数据 create_hash_partitions ------------------------ 128(1 row)

postgres=# \\d+ part_test

Table \"public.part_test\"

crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_0, part_test_1, part_test_10, part_test_100, part_test_101, part_test_102, part_test_103, part_test_104, part_test_105, part_test_106, part_test_107, part_test_108, part_test_109,

part_test_11, part_test_110, part_test_111, part_test_112, part_test_113, part_test_114, part_test_115, part_test_116, part_test_117, part_test_118, part_test_119, part_test_12, part_test_120, part_test_121, part_test_122, part_test_123, part_test_124, part_test_125, part_test_126, part_test_127, part_test_13, part_test_14, part_test_15, part_test_16, part_test_17, part_test_18, part_test_19, part_test_2, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_25, part_test_26, part_test_27, part_test_28, part_test_29, part_test_3, part_test_30, part_test_31, part_test_32, part_test_33, part_test_34, part_test_35, part_test_36, part_test_37, part_test_38, part_test_39, part_test_4, part_test_40, part_test_41, part_test_42, part_test_43, part_test_44, part_test_45, part_test_46, part_test_47, part_test_48, part_test_49, part_test_5, part_test_50, part_test_51, part_test_52, part_test_53, part_test_54, part_test_55, part_test_56, part_test_57, part_test_58, part_test_59, part_test_6, part_test_60, part_test_61, part_test_62, part_test_63, part_test_64, part_test_65, part_test_66, part_test_67, part_test_68, part_test_69, part_test_7, part_test_70, part_test_71, part_test_72, part_test_73,

part_test_74, part_test_75, part_test_76, part_test_77, part_test_78, part_test_79, part_test_8, part_test_80, part_test_81, part_test_82, part_test_83, part_test_84, part_test_85, part_test_86, part_test_87, part_test_88, part_test_89, part_test_9, part_test_90, part_test_91, part_test_92, part_test_93, part_test_94, part_test_95, part_test_96, part_test_97, part_test_98, part_test_99

由于不迁移数据，所以数据还在主表

postgres=# select count(*) from only part_test; count ------- 10000(1 row)

使⽤⾮堵塞式的迁移接⼝

partition_table_concurrently(relation REGCLASS, -- 主表OID

batch_size INTEGER DEFAULT 1000, -- ⼀个事务批量迁移多少记录

sleep_time FLOAT8 DEFAULT 1.0) -- 获得⾏锁失败时，休眠多久再次获取，重试60次退出任务。

postgres=# select partition_table_concurrently('part_test'::regclass, 10000, 1.0);

NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test'); partition_table_concurrently ------------------------------

(1 row)

迁移结束后，主表数据已经没有了，全部在分区中postgres=# select count(*) from only part_test; count ------- 0(1 row)

数据迁移完成后，建议禁⽤主表，这样执⾏计划就不会出现主表了postgres=# select set_enable_parent('part_test'::regclass, false); set_enable_parent -------------------

(1 row)

只查单个分区

-> Seq Scan on part_test_122 (cost=0.00..1.91 rows=1 width=45)

Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)(3 rows)

分区表约束如下

很显然pg_pathman⾃动完成了转换，如果是传统的继承，select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; 这种写法是不能筛选分区的。 postgres=# \\d+ part_test_122

Table \"public.part_test_122\"

Check constraints:

\"pathman_part_test_122_3_check\" CHECK (get_hash_part_idx(timestamp_hash(crt_time), 128) = 122)Inherits: part_test建议

1. 分区列必须有not null约束2. 建议使⽤⾮堵塞式迁移接⼝3. 建议数据迁移完成后，禁⽤主表

4. pg_pathman不会受制于表达式的写法，所以select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;这样的写法也是能⾛哈希分区的。5. hash分区列不局限于int类型的列，会使⽤hash函数⾃动转换。

3. 数据迁移到分区

如果创建分区表时，未将主表数据迁移到分区，那么可以使⽤⾮堵塞式的迁移接⼝，将数据迁移到分区可能类似如下做法

with tmp as (delete from 主表 limit xx nowait returning *) insert into 分区 select * from tmp

或者使⽤ select array_agg(ctid) from 主表 limit xx for update nowati 进⾏标⽰然后执⾏delete和insert。

1. 函数接⼝如下

partition_table_concurrently(relation REGCLASS, -- 主表OID

batch_size INTEGER DEFAULT 1000, -- ⼀个事务批量迁移多少记录

sleep_time FLOAT8 DEFAULT 1.0) -- 获得⾏锁失败时，休眠多久再次获取，重试60次退出任务。

2. 例⼦

postgres=# select partition_table_concurrently('part_test'::regclass, 10000, 1.0);

NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test'); partition_table_concurrently ------------------------------

(1 row)

3. 如何停⽌迁移任务，调⽤如下函数接⼝

stop_concurrent_part_task(relation REGCLASS)

4. 查看后台的数据迁移任务

4. 分裂范围分区

例如某个分区太⼤了，想分裂为两个分区，可以使⽤这种⽅法仅⽀持范围分区表

split_range_partition(partition REGCLASS, -- 分区oid split_value ANYELEMENT, -- 分裂值

partition_name TEXT DEFAULT NULL) -- 分裂后新增的分区表名例⼦

postgres=# \\d+ part_test

Table \"public.part_test\"

postgres=# \\d+ part_test_1

Table \"public.part_test_1\"

\"pathman_part_test_1_3_check\" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)Inherits: part_test分裂

postgres=# select split_range_partition('part_test_1'::regclass, -- 分区oid '2016-11-10 00:00:00'::timestamp, -- 分裂值 'part_test_1_2'); -- 分区表名 split_range_partition ----------------------------------------------- {\"2016-10-25 00:00:00\2016-11-25 00:00:00\(1 row)

分裂后的两个表如下

postgres=# \\d+ part_test_1

Table \"public.part_test_1\"

\"pathman_part_test_1_3_check\" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-10 00:00:00'::timestamp without time zone)Inherits: part_test

postgres=# \\d+ part_test_1_2

Table \"public.part_test_1_2\"

\"pathman_part_test_1_2_3_check\" CHECK (crt_time >= '2016-11-10 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zonInherits: part_test

数据会⾃动迁移到另⼀个分区

postgres=# select count(*) from part_test_1; count ------- 373(1 row)

postgres=# select count(*) from part_test_1_2; count ------- 360(1 row)继承关系如下

postgres=# \\d+ part_test

Table \"public.part_test\"

part_test_1_2, -- 新增的表 part_test_2, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_3, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9

5. 合并范围分区

⽬前仅⽀持范围分区调⽤如下接⼝

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

merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS) 例⼦

postgres=# select merge_range_partitions('part_test_2'::regclass, 'part_test_12'::regclass) ;ERROR: merge failed, partitions must be adjacent

CONTEXT: PL/pgSQL function merge_range_partitions_internal(regclass,regclass,regclass,anyelement) line 27 at RAISESQL statement \"SELECT public.merge_range_partitions_internal($1, $2, $3, NULL::timestamp without time zone)\"PL/pgSQL function merge_range_partitions(regclass,regclass) line 44 at EXECUTE不是相邻分区，报错

相邻分区可以合并

postgres=# select merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass) ; merge_range_partitions ------------------------

(1 row)

合并后，会删掉其中⼀个分区表

postgres=# \\d part_test_1_2

Did not find any relation named \"part_test_1_2\".

postgres=# \\d part_test_1

Table \"public.part_test_1\"

crt_time | timestamp without time zone | not nullCheck constraints:

\"pathman_part_test_1_3_check\" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)Inherits: part_test

postgres=# select count(*) from part_test_1; count ------- 733(1 row)

6. 向后添加范围分区

添加分区接⼝，⽀持指定表空间

append_range_partition(parent REGCLASS, -- 主表OID

partition_name TEXT DEFAULT NULL, -- 新增的分区表名, 默认不需要输⼊

tablespace TEXT DEFAULT NULL) -- 新增的分区表放到哪个表空间, 默认不需要输⼊例⼦

postgres=# select append_range_partition('part_test'::regclass); append_range_partition ------------------------ public.part_test_25(1 row)

postgres=# \\d+ part_test_25

Table \"public.part_test_25\"

\"pathman_part_test_25_3_check\" CHECK (crt_time >= '2018-10-25 00:00:00'::timestamp without time zone AND crt_time < '2018-11-25 00:00:00'::timestamp without time zoneInherits: part_test

postgres=# \\d+ part_test_24

Table \"public.part_test_24\"

\"pathman_part_test_24_3_check\" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zoneInherits: part_test

7. 向前添加范围分区

在头部追加分区。接⼝

prepend_range_partition(parent REGCLASS, partition_name TEXT DEFAULT NULL, tablespace TEXT DEFAULT NULL)例⼦

postgres=# select prepend_range_partition('part_test'::regclass); prepend_range_partition ------------------------- public.part_test_26(1 row)

postgres=# \\d+ part_test_26

Table \"public.part_test_26\"

\"pathman_part_test_26_3_check\" CHECK (crt_time >= '2016-09-25 00:00:00'::timestamp without time zone AND crt_time < '2016-10-25 00:00:00'::timestamp without time zoneInherits: part_test

postgres=# \\d+ part_test_1

Table \"public.part_test_1\"

\"pathman_part_test_1_3_check\" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)Inherits: part_test

8. 添加分区

指定分区起始值的⽅式添加分区，只要创建的分区和已有分区不会存在数据交叉就可以创建成功。

也就是说使⽤这种⽅法，不要求强制创建连续的分区，例如已有分区覆盖了2010-2015的范围，你可以直接创建⼀个2020年的分区表，不需要覆盖2015到2020的范围。接⼝如下

add_range_partition(relation REGCLASS, -- 主表OID start_value ANYELEMENT, -- 起始值 end_value ANYELEMENT, -- 结束值

partition_name TEXT DEFAULT NULL, -- 分区名

tablespace TEXT DEFAULT NULL) -- 分区创建在哪个表空间下例⼦

postgres=# select add_range_partition('part_test'::regclass, -- 主表OID '2020-01-01 00:00:00'::timestamp, -- 起始值 '2020-02-01 00:00:00'::timestamp); -- 结束值 add_range_partition --------------------- public.part_test_27(1 row)

postgres=# \\d+ part_test_27

Table \"public.part_test_27\"

\"pathman_part_test_27_3_check\" CHECK (crt_time >= '2020-01-01 00:00:00'::timestamp without time zone AND crt_time < '2020-02-01 00:00:00'::timestamp without time zoneInherits: part_test

9. 删除分区

1. 删除单个范围分区

接⼝如下

drop_range_partition(partition TEXT, -- 分区名称

delete_data BOOLEAN DEFAULT TRUE) -- 是否删除分区数据，如果false，表⽰分区数据迁移到主表。 Drop RANGE partition and all of its data if delete_data is true.例⼦

删除分区，数据迁移到主表

postgres=# select drop_range_partition('part_test_1',false);

NOTICE: 733 rows copied from part_test_1 drop_range_partition ---------------------- part_test_1(1 row)

postgres=# select drop_range_partition('part_test_2',false);NOTICE: 720 rows copied from part_test_2 drop_range_partition ---------------------- part_test_2(1 row)

postgres=# select count(*) from part_test; count ------- 10000(1 row)

删除分区，分区数据也删除，不迁移到主表

postgres=# select drop_range_partition('part_test_3',true); drop_range_partition ---------------------- part_test_3(1 row)

postgres=# select count(*) from part_test; count ------- 9256(1 row)

postgres=# select count(*) from only part_test; count ------- 1453(1 row)

2. 删除所有分区，并且指定是否要将数据迁移到主表

接⼝如下

drop_partitions(parent REGCLASS,

delete_data BOOLEAN DEFAULT FALSE)

Drop partitions of the parent table (both foreign and local relations). If delete_data is false, the data is copied to the parent table first. Default is false.例⼦

postgres=# select drop_partitions('part_test'::regclass, false); -- 删除所有分区表，并将数据迁移到主表NOTICE: function public.part_test_upd_trig_func() does not exist, skippingNOTICE: 744 rows copied from part_test_4NOTICE: 672 rows copied from part_test_5NOTICE: 744 rows copied from part_test_6NOTICE: 720 rows copied from part_test_7NOTICE: 744 rows copied from part_test_8NOTICE: 720 rows copied from part_test_9NOTICE: 744 rows copied from part_test_10NOTICE: 744 rows copied from part_test_11NOTICE: 720 rows copied from part_test_12NOTICE: 744 rows copied from part_test_13NOTICE: 507 rows copied from part_test_14NOTICE: 0 rows copied from part_test_15NOTICE: 0 rows copied from part_test_16NOTICE: 0 rows copied from part_test_17NOTICE: 0 rows copied from part_test_18NOTICE: 0 rows copied from part_test_19NOTICE: 0 rows copied from part_test_20NOTICE: 0 rows copied from part_test_21NOTICE: 0 rows copied from part_test_22NOTICE: 0 rows copied from part_test_23NOTICE: 0 rows copied from part_test_24NOTICE: 0 rows copied from part_test_25NOTICE: 0 rows copied from part_test_26NOTICE: 0 rows copied from part_test_27 drop_partitions ----------------- 24(1 row)

postgres=# select count(*) from part_test; count ------- 9256(1 row)

postgres=# \\dt part_test_4

No matching relations found.

10. 绑定分区(已有的表加⼊分区表)

将已有的表，绑定到已有的某个分区主表。

已有的表与主表要保持⼀致的结构，包括dropped columns。 (查看pg_attribute的⼀致性)如果设置了回调函数，会触发。接⼝如下

attach_range_partition(relation REGCLASS, -- 主表OID partition REGCLASS, -- 分区表OID start_value ANYELEMENT, -- 起始值 end_value ANYELEMENT) -- 结束值例⼦

postgres=# create table part_test_1 (like part_test including all);CREATE TABLE

postgres=# \\d+ part_test

Table \"public.part_test\"

postgres=# \\d+ part_test_1

Table \"public.part_test_1\"

postgres=# select attach_range_partition('part_test'::regclass, 'part_test_1'::regclass, '2019-01-01 00:00:00'::timestamp, '2019-02-01 00:00:00'::timestamp); attach_range_partition ------------------------ part_test_1(1 row)

绑定分区时，

⾃动创建继承关系，⾃动创建约束 postgres=# \\d+ part_test_1

Table \"public.part_test_1\"

\"pathman_part_test_1_3_check\" CHECK (crt_time >= '2019-01-01 00:00:00'::timestamp without time zone AND crt_time < '2019-02-01 00:00:00'::timestamp without time zone)Inherits: part_test

11. 解绑分区(将分区变成普通表)

将分区从主表的继承关系中删除, 不删数据，删除继承关系，删除约束接⼝如下

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

postgres=# select count(*) from part_test; count ------- 9256(1 row)

postgres=# select count(*) from part_test_2; count ------- 733(1 row)

postgres=# select detach_range_partition('part_test_2'); detach_range_partition ------------------------ part_test_2(1 row)

postgres=# select count(*) from part_test_2; count ------- 733(1 row)

postgres=# select count(*) from part_test; count

------- 8523(1 row)接⼝函数内容

postgres=# \\sf detach_range_partition

CREATE OR REPLACE FUNCTION public.detach_range_partition(partition regclass) RETURNS text

LANGUAGE plpgsqlAS $function$DECLARE

v_attname TEXT; parent_relid REGCLASS;

BEGIN

parent_relid := public.get_parent_of_partition(partition); /* Acquire lock on parent */

PERFORM public.lock_partitioned_relation(parent_relid); v_attname := attname

FROM public.pathman_config WHERE partrel = parent_relid;

IF v_attname IS NULL THEN

RAISE EXCEPTION 'table \"%\" is not partitioned', parent_relid::TEXT; END IF;

/* Remove inheritance */

EXECUTE format('ALTER TABLE %s NO INHERIT %s', partition::TEXT,

parent_relid::TEXT);

/* Remove check constraint */

EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %s', partition::TEXT,

public.build_check_constraint_name(partition, v_attname)); /* Invalidate cache */

PERFORM public.on_update_partitions(parent_relid); RETURN partition;END

$function$

12. 更新触发器

如果分区字段要被更新，需要创建更新触发器，否则不需要。接⼝函数如下

create_hash_update_trigger(parent REGCLASS)

Creates the trigger on UPDATE for HASH partitions.

The UPDATE trigger isn't created by default because of the overhead. It's useful in cases when the key attribute might change.create_range_update_trigger(parent REGCLASS)Same as above, but for a RANGE-partitioned table.例⼦

创建更新触发器前，如果更新分区字段后的值跨分区了，会报约束错误。postgres=# select * from part_test_3 limit 10; id | info | crt_time

-----+----------------------------------+---------------------------- 734 | 52288de52fccf3d47efe897e1320a0fd | 2016-11-25 00:11:34.113856 735 | 16f4fffda933356192af8d1991c673cf | 2016-11-25 01:11:34.113862 736 | 08ec10184500ef43a6efde38dc43df33 | 2016-11-25 02:11:34.113867 737 | e658c7fb7f44ae3145401bf348cfa9dd | 2016-11-25 03:11:34.113872 738 | 81ff4c5cb3404230341aa95c28f86931 | 2016-11-25 04:11:34.113877 739 | 931652d6ba49f8155b1486d30fd23bab | 2016-11-25 05:11:34.113883 740 | c616c01d98016ff0022aa5449d53ca8f | 2016-11-25 06:11:34.113888 741 | 358e44b68259587233a0f571e8a86a81 | 2016-11-25 07:11:34.113893 742 | 719bb75e67c23c1f76e4eb81cb22004e | 2016-11-25 08:11:34.113899 743 | 1fc90c401eec2927fe9bb726651e4936 | 2016-11-25 09:11:34.113904(10 rows)

postgres=# update part_test set crt_time='2016-01-25 00:11:34.113856' where id=734;

ERROR: new row for relation \"part_test_3\" violates check constraint \"pathman_part_test_3_3_check\"DETAIL: Failing row contains (734, 52288de52fccf3d47efe897e1320a0fd, 2016-01-25 00:11:34.113856).创建更新触发器后，正常

postgres=# select create_range_update_trigger('part_test'::regclass); create_range_update_trigger -------------------------------- public.part_test_upd_trig_func(1 row)

postgres=# update part_test set crt_time='2016-01-25 00:11:34.113856' where id=734;UPDATE 0

postgres=# select * from part_test where id=734; id | info | crt_time

-----+----------------------------------+---------------------------- 734 | 52288de52fccf3d47efe897e1320a0fd | 2016-01-25 00:11:34.113856(1 row)

通常业务设计时，不应该允许分区字段的变更。

13. 永久禁⽌分区表pg_pathman插件

你可以针对单个分区主表禁⽤pg_pathman。接⼝函数如下

disable_pathman_for(relation TEXT)

Permanently disable pg_pathman partitioning mechanism for the specified parent table and remove the insert trigger if it exists. All partitions and data remain unchanged.

postgres=# \\sf disable_pathman_for

CREATE OR REPLACE FUNCTION public.disable_pathman_for(parent_relid regclass) RETURNS void

LANGUAGE plpgsql STRICT

AS $function$BEGIN

PERFORM public.validate_relname(parent_relid);

DELETE FROM public.pathman_config WHERE partrel = parent_relid; PERFORM public.drop_triggers(parent_relid); /* Notify backend about changes */

PERFORM public.on_remove_partitions(parent_relid);END

$function$例⼦

postgres=# select disable_pathman_for('part_test');NOTICE: drop cascades to 23 other objects

DETAIL: drop cascades to trigger part_test_upd_trig on table part_test_3drop cascades to trigger part_test_upd_trig on table part_test_4drop cascades to trigger part_test_upd_trig on table part_test_5drop cascades to trigger part_test_upd_trig on table part_test_6drop cascades to trigger part_test_upd_trig on table part_test_7drop cascades to trigger part_test_upd_trig on table part_test_8drop cascades to trigger part_test_upd_trig on table part_test_9drop cascades to trigger part_test_upd_trig on table part_test_10drop cascades to trigger part_test_upd_trig on table part_test_11drop cascades to trigger part_test_upd_trig on table part_test_12drop cascades to trigger part_test_upd_trig on table part_test_13drop cascades to trigger part_test_upd_trig on table part_test_14drop cascades to trigger part_test_upd_trig on table part_test_15drop cascades to trigger part_test_upd_trig on table part_test_16drop cascades to trigger part_test_upd_trig on table part_test_17drop cascades to trigger part_test_upd_trig on table part_test_18drop cascades to trigger part_test_upd_trig on table part_test_19drop cascades to trigger part_test_upd_trig on table part_test_20drop cascades to trigger part_test_upd_trig on table part_test_21drop cascades to trigger part_test_upd_trig on table part_test_22drop cascades to trigger part_test_upd_trig on table part_test_23drop cascades to trigger part_test_upd_trig on table part_test_24drop cascades to trigger part_test_upd_trig on table part_test_25 disable_pathman_for ---------------------

(1 row)

postgres=# \\d+ part_test

Table \"public.part_test\"

part_test_22, part_test_23, part_test_24, part_test_25, part_test_26, part_test_27, part_test_28, part_test_29, part_test_3, part_test_30, part_test_31, part_test_32, part_test_33, part_test_34, part_test_35, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9

postgres=# \\d+ part_test_10

Table \"public.part_test_10\"

\"pathman_part_test_10_3_check\" CHECK (crt_time >= '2017-06-25 00:00:00'::timestamp without time zone AND crt_time < '2017-07-25 00:00:00'::timestamp without time zoneInherits: part_test

禁⽤pg_pathman后，继承关系和约束不会变化，只是pg_pathman不介⼊custom scan 执⾏计划。禁⽤pg_pathman后的执⾏计划

postgres=# explain select * from part_test where crt_time='2017-06-25 00:00:00'::timestamp; QUERY PLAN --------------------------------------------------------------------------------- Append (cost=0.00..16.00 rows=2 width=45)

-> Seq Scan on part_test (cost=0.00..0.00 rows=1 width=45)

Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone) -> Seq Scan on part_test_10 (cost=0.00..16.00 rows=1 width=45)

Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)(5 rows)

disable_pathman_for没有可逆操作，请慎⽤。

14. 全局禁⽌pg_pathman

与禁⽤单个分区主表不同，全局禁⽌只需要调整参数即可，不需要修改pg_pathman的元数据，同时它是可逆操作。pg_pathman.enable例⼦

$ vi $PGDATA/postgresql.confpg_pathman.enable = off$ pg_ctl reload

分区表⾼级管理

1. 禁⽤主表

当主表的数据全部迁移到分区后，可以禁⽤主表。接⼝函数如下

set_enable_parent(relation REGCLASS, value BOOLEAN)

Include/exclude parent table into/from query plan.

In original PostgreSQL planner parent table is always included into query plan even if it's empty which can lead to additional overhead. You can use disable_parent() if you are never going to use parent table as a storage.

Default value depends on the partition_data parameter that was specified during initial partitioning in create_range_partitions() or create_partitions_from_range() functions. If the partition_data parameter was true then all data have already been migrated to partitions and parent table disabled. Otherwise it is enabled.例⼦

select set_enable_parent('part_test', false);

2. ⾃动扩展分区

范围分区表，允许⾃动扩展分区。

如果新插⼊的数据不在已有的分区范围内，会⾃动创建分区。

set_auto(relation REGCLASS, value BOOLEAN)

Enable/disable auto partition propagation (only for RANGE partitioning). It is enabled by default.例⼦

postgres=# \\d+ part_test

Table \"public.part_test\"

crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_10, part_test_11, part_test_12, part_test_13, part_test_14, part_test_15, part_test_16, part_test_17, part_test_18, part_test_19, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_25, part_test_26, part_test_3, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9

postgres=# \\d+ part_test_26

Table \"public.part_test_26\"

\"pathman_part_test_26_3_check\" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zoneInherits: part_test

postgres=# \\d+ part_test_25

Table \"public.part_test_25\"

\"pathman_part_test_25_3_check\" CHECK (crt_time >= '2018-08-25 00:00:00'::timestamp without time zone AND crt_time < '2018-09-25 00:00:00'::timestamp without time zoneInherits: part_test

插⼊⼀个不在已有分区范围的值，会根据创建分区时的interval⾃动扩展若⼲个分区，这个操作可能很久很久。 postgres=# insert into part_test values (1,'test','2222-01-01'::timestamp);等了很久

21298 digoal 20 0 93.1g 184m 127m R 98.7 0.1 0:33.34 postgres: bgworker: SpawnPartitionsWorker 插⼊结束后，扩展了好多好多分区，原因是插⼊的值跨度范围太⼤了。 postgres=# \\d+ part_test

Table \"public.part_test\"

..................................... 很多

不建议开启⾃动扩展范围分区

3. 回调函数 (创建每个分区时都会触发)

回调函数是在每创建⼀个分区时会⾃动触发调⽤的函数。

例如可以⽤在ddl逻辑复制中，将DDL语句记录下来，存放到表中。回调函数如下

set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)

Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE). The callback must have the following signature: part_init_callback(args JSONB) RETURNS VOID.

Parameter arg consists of several fields whose presence depends on partitioning type:/* RANGE-partitioned table abc (child abc_4) */{

\"parent\": \"abc\ \"parttype\": \"2\ \"partition\": \"abc_4\ \"range_max\": \"401\ \"range_min\": \"301\"}

/* HASH-partitioned table abc (child abc_0) */{

\"parent\": \"abc\ \"parttype\": \"1\ \"partition\": \"abc_0\"}例⼦

回调函数

postgres=# create or replace function f_callback_test(jsonb) returns void as$$

declarebegin

create table if not exists rec_part_ddl(id serial primary key, parent name, parttype int, partition name, range_max text, range_min text); if ($1->>'parttype')::int = 1 then

raise notice 'parent: %, parttype: %, partition: %', $1->>'parent', $1->>'parttype', $1->>'partition';

insert into rec_part_ddl(parent, parttype, partition) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name); elsif ($1->>'parttype')::int = 2 then

raise notice 'parent: %, parttype: %, partition: %, range_max: %, range_min: %', $1->>'parent', $1->>'parttype', $1->>'partition', $1->>'range_max', $1->>'range_min';

insert into rec_part_ddl(parent, parttype, partition, range_max, range_min) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name, $1->>'range_max end if;end;

$$ language plpgsql strict;

测试表

postgres=# create table tt(id int, info text, crt_time timestamp not null);CREATE TABLE

设置测试表的回调函数

select set_init_callback('tt'::regclass, 'f_callback_test'::regproc);

创建分区

postgres=# select

create_range_partitions('tt'::regclass, -- 主表OID 'crt_time', -- 分区列名

'2016-10-25 00:00:00'::timestamp, -- 开始值

interval '1 month', -- 间隔；interval 类型，⽤于时间分区表 24, -- 分多少个区 false) ; create_range_partitions ------------------------- 24(1 row)

检查回调函数是否已调⽤

postgres=# select * from rec_part_ddl;

id | parent | parttype | partition | range_max | range_min ----+--------+----------+-----------+---------------------+--------------------- 1 | tt | 2 | tt_1 | 2016-11-25 00:00:00 | 2016-10-25 00:00:00 2 | tt | 2 | tt_2 | 2016-12-25 00:00:00 | 2016-11-25 00:00:00 3 | tt | 2 | tt_3 | 2017-01-25 00:00:00 | 2016-12-25 00:00:00 4 | tt | 2 | tt_4 | 2017-02-25 00:00:00 | 2017-01-25 00:00:00 5 | tt | 2 | tt_5 | 2017-03-25 00:00:00 | 2017-02-25 00:00:00 6 | tt | 2 | tt_6 | 2017-04-25 00:00:00 | 2017-03-25 00:00:00 7 | tt | 2 | tt_7 | 2017-05-25 00:00:00 | 2017-04-25 00:00:00 8 | tt | 2 | tt_8 | 2017-06-25 00:00:00 | 2017-05-25 00:00:00 9 | tt | 2 | tt_9 | 2017-07-25 00:00:00 | 2017-06-25 00:00:00 10 | tt | 2 | tt_10 | 2017-08-25 00:00:00 | 2017-07-25 00:00:00 11 | tt | 2 | tt_11 | 2017-09-25 00:00:00 | 2017-08-25 00:00:00 12 | tt | 2 | tt_12 | 2017-10-25 00:00:00 | 2017-09-25 00:00:00 13 | tt | 2 | tt_13 | 2017-11-25 00:00:00 | 2017-10-25 00:00:00 14 | tt | 2 | tt_14 | 2017-12-25 00:00:00 | 2017-11-25 00:00:00 15 | tt | 2 | tt_15 | 2018-01-25 00:00:00 | 2017-12-25 00:00:00

16 | tt | 2 | tt_16 | 2018-02-25 00:00:00 | 2018-01-25 00:00:00 17 | tt | 2 | tt_17 | 2018-03-25 00:00:00 | 2018-02-25 00:00:00 18 | tt | 2 | tt_18 | 2018-04-25 00:00:00 | 2018-03-25 00:00:00 19 | tt | 2 | tt_19 | 2018-05-25 00:00:00 | 2018-04-25 00:00:00 20 | tt | 2 | tt_20 | 2018-06-25 00:00:00 | 2018-05-25 00:00:00 21 | tt | 2 | tt_21 | 2018-07-25 00:00:00 | 2018-06-25 00:00:00 22 | tt | 2 | tt_22 | 2018-08-25 00:00:00 | 2018-07-25 00:00:00 23 | tt | 2 | tt_23 | 2018-09-25 00:00:00 | 2018-08-25 00:00:00 24 | tt | 2 | tt_24 | 2018-10-25 00:00:00 | 2018-09-25 00:00:00(24 rows)

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文