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)

云数据库 PostgreSQL 版