pg_partman 是一款用于创建和管理基于时间和基于序列的表分区集的插件，旨在简化基于时间或数字/ID 的表分区管理。
pg_partman 插件仅支持在 PostgreSQL 14 及以上版本的实例中使用。如需使用该插件，请注意在创建实例时选择合适的实例版本。
pg_partman 插件依赖 dblink 插件。可通过以下语句创建 dblink 插件。
CREATE EXTENSION dblink; -- 配置 dblink 相关参数 INSERT INTO test_schema.dblink_mapping_jobmon (host, username, pwd) VALUES ('127.0.0.1', 'test_user', 'test@123');
在使用 pg_partman 插件时，建议搭配 pg_jobmon 插件使用。可通过以下语句创建 pg_jobmon 插件。
CREATE SCHEMA test_schema; CREATE EXTENSION pg_jobmon SCHEMA test_schema;
创建插件
CREATE EXTENSION pg_partman;
删除插件
DROP EXTENSION pg_partman;
以下是一个基于时间分区的示例，通过该示例可以快速了解插件能力。
创建分区表
-- 先创建分区表 CREATE TABLE test_schema.time_partition_table( col1 int, col2 text default 'stuff', col3 timestamptz NOT NULL DEFAULT now() )PARTITION BY RANGE (col3); -- 创建非唯一索引 CREATE INDEX ON test_schema.time_partition_table(col3);
test_db=> -- 查看表结构 test_db=> \d+ test_schema.time_partition_table Partitioned table "test_schema.time_partition_table" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+------------- col1 | integer | | | | plain | | | col2 | text | | | 'stuff'::text | extended | | | col3 | timestamp with time zone | | not null | now() | plain | | | Partition key: RANGE (col3) Indexes: "time_partition_table_col3_idx" btree (col3) Number of partitions: 0
创建模板表
-- 创建模板表用于 properties 继承 CREATE TABLE test_schema.time_partition_table_template (LIKE test_schema.time_partition_table); -- 为模板创建主键用于后续 properties 继承 ALTER TABLE test_schema.time_partition_table_template ADD PRIMARY KEY (col1);
test_db=> \d+ test_schema.time_partition_table_template Table "test_schema.time_partition_table_template" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+--------------------------+-----------+----------+---------+----------+-------------+--------------+------------- col1 | integer | | not null | | plain | | | col2 | text | | | | extended | | | col3 | timestamp with time zone | | not null | | plain | | | Indexes: "time_partition_table_template_pkey" PRIMARY KEY, btree (col1) Access method: heap
创建子表
-- 为分区表创建子表 SELECT test_schema.create_parent( p_parent_table := 'test_schema.time_partition_table', p_control := 'col3', p_interval := '1 day', p_template_table := 'test_schema.time_partition_table_template' ); \d+ test_schema.time_partition_table
test_db=> \d+ test_schema.time_partition_table Partitioned table "test_schema.time_partition_table" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+------------- col1 | integer | | | | plain | | | col2 | text | | | 'stuff'::text | extended | | | col3 | timestamp with time zone | | not null | now() | plain | | | Partition key: RANGE (col3) Indexes: "time_partition_table_col3_idx" btree (col3) Partitions: test_schema.time_partition_table_p20250413 FOR VALUES FROM ('2025-04-13 00:00:00+08') TO ('2025-04-14 00:00:00+08'), test_schema.time_partition_table_p20250414 FOR VALUES FROM ('2025-04-14 00:00:00+08') TO ('2025-04-15 00:00:00+08'), test_schema.time_partition_table_p20250415 FOR VALUES FROM ('2025-04-15 00:00:00+08') TO ('2025-04-16 00:00:00+08'), test_schema.time_partition_table_p20250416 FOR VALUES FROM ('2025-04-16 00:00:00+08') TO ('2025-04-17 00:00:00+08'), test_schema.time_partition_table_p20250417 FOR VALUES FROM ('2025-04-17 00:00:00+08') TO ('2025-04-18 00:00:00+08'), test_schema.time_partition_table_p20250418 FOR VALUES FROM ('2025-04-18 00:00:00+08') TO ('2025-04-19 00:00:00+08'), test_schema.time_partition_table_p20250419 FOR VALUES FROM ('2025-04-19 00:00:00+08') TO ('2025-04-20 00:00:00+08'), test_schema.time_partition_table_p20250420 FOR VALUES FROM ('2025-04-20 00:00:00+08') TO ('2025-04-21 00:00:00+08'), test_schema.time_partition_table_p20250421 FOR VALUES FROM ('2025-04-21 00:00:00+08') TO ('2025-04-22 00:00:00+08'), test_schema.time_partition_table_default DEFAULT
test_db=> -- 查看子表，父表和模板表的索引都在子表中成功创建 test_db=> \d+ test_schema.time_partition_table_p20250419 Table "test_schema.time_partition_table_p20250419" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+------------- col1 | integer | | not null | | plain | | | col2 | text | | | 'stuff'::text | extended | | | col3 | timestamp with time zone | | not null | now() | plain | | | Partition of: test_schema.time_partition_table FOR VALUES FROM ('2025-04-19 00:00:00+08') TO ('2025-04-20 00:00:00+08') Partition constraint: ((col3 IS NOT NULL) AND (col3 >= '2025-04-19 00:00:00+08'::timestamp with time zone) AND (col3 < '2025-04-20 00:00:00+08'::timestamp with time zone)) Indexes: "time_partition_table_p20250419_pkey" PRIMARY KEY, btree (col1) "time_partition_table_p20250419_col3_idx" btree (col3) Access method: heap
通过 pg_jobmon 查看创建过程
test_db=> -- 可通过 pg_jobmon 查看任务内容 test_db=> select * from test_schema.job_log; job_id | owner | job_name | start_time | end_time | status | pid --------+-----------+--------------------------------------------------------+-------------------------------+-------------------------------+--------+-------- 2 | test_user | PARTMAN CREATE TABLE: TEST_SCHEMA.TIME_PARTITION_TABLE | 2025-04-17 21:19:06.471888+08 | 2025-04-17 21:19:06.711413+08 | OK | 579004 1 | test_user | PARTMAN SETUP PARENT: TEST_SCHEMA.TIME_PARTITION_TABLE | 2025-04-17 21:19:06.43185+08 | 2025-04-17 21:19:06.753888+08 | OK | 579004 (2 rows)
以下是一个以离线方式将非分区表转换成分区表的示例。
构造测试数据
-- 创建普通表用于后续测试 CREATE TABLE test_schema.original_table( col1 bigint not null, col2 text not null, col3 timestamptz DEFAULT now(), col4 text ); CREATE INDEX ON test_schema.original_table(col1); -- 构造部分数据 INSERT INTO test_schema.original_table (col1, col2, col3, col4) VALUES (generate_series(1,100000), 'stuff'||generate_series(1,100000), now(), 'stuff');
数据迁移
-- 以下步骤开始迁移 -- 1. 重命名待迁移表 ALTER TABLE test_schema.original_table RENAME to old_nonpartitioned_table; -- 2. 新建与原表同名分区表，并创建子表 CREATE TABLE test_schema.original_table( col1 bigint not null, col2 text not null, col3 timestamptz DEFAULT now(), col4 text ) PARTITION BY RANGE (col1); CREATE INDEX ON test_schema.original_table (col1); SELECT test_schema.create_parent( p_parent_table := 'test_schema.original_table', p_control := 'col1', p_interval := '10000' ); \d+ test_schema.original_table -- 3. 调用函数partition_data_proc迁移数据 CALL test_schema.partition_data_proc( p_parent_table := 'test_schema.original_table', p_loop_count := 200, p_interval := '1000', p_source_table := 'test_schema.old_nonpartitioned_table' );
数据校验
test_db=> -- 检查数据 test_db=> SELECT count(*) FROM test_schema.old_nonpartitioned_table; count ------- 0 (1 row) test_db=> SELECT count(*) FROM test_schema.original_table; count -------- 100000 (1 row) test_db=> \d+ test_schema.original_table Partitioned table "test_schema.original_table" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+--------------------------+-----------+----------+---------+----------+-------------+--------------+------------- col1 | bigint | | not null | | plain | | | col2 | text | | not null | | extended | | | col3 | timestamp with time zone | | | now() | plain | | | col4 | text | | | | extended | | | Partition key: RANGE (col1) Indexes: "original_table_col1_idx1" btree (col1) Partitions: test_schema.original_table_p0 FOR VALUES FROM ('0') TO ('10000'), test_schema.original_table_p10000 FOR VALUES FROM ('10000') TO ('20000'), test_schema.original_table_p100000 FOR VALUES FROM ('100000') TO ('110000'), test_schema.original_table_p20000 FOR VALUES FROM ('20000') TO ('30000'), test_schema.original_table_p30000 FOR VALUES FROM ('30000') TO ('40000'), test_schema.original_table_p40000 FOR VALUES FROM ('40000') TO ('50000'), test_schema.original_table_p50000 FOR VALUES FROM ('50000') TO ('60000'), test_schema.original_table_p60000 FOR VALUES FROM ('60000') TO ('70000'), test_schema.original_table_p70000 FOR VALUES FROM ('70000') TO ('80000'), test_schema.original_table_p80000 FOR VALUES FROM ('80000') TO ('90000'), test_schema.original_table_p90000 FOR VALUES FROM ('90000') TO ('100000'), test_schema.original_table_default DEFAULT test_db=> SELECT count(*) FROM test_schema.original_table_p10000; count ------- 10000 (1 row)