Centos7安装配置pg_partman

一、pg_partman的介绍1.1、pg_partman是什么pg_partman是PostgreSQL生态中备受推崇的分区管理扩展插件专门用于简化和自动化基于时间或数值范围的分区表维护工作。它为DBA和开发人员提供了一整套强大的工具让大规模分区表的管理变得轻松高效。1.2、核心优势PostgreSQL作为企业级关系数据库在处理大规模时间序列数据时面临分区管理难题。pg_partman扩展插件应运而生带来三大革命性优势自动化运维告别手动创建分区表的繁琐工作pg_partman自动处理分区创建、数据迁移和旧分区清理。性能显著提升通过智能约束管理和查询优化分区表查询性能提升300%以上。零停机迁移支持在线将普通表转换为分区表业务运行完全不受影响。1.3、核心特性①.声明式分区支持pg_partman 5.0版本全面拥抱PostgreSQL 14的声明式分区彻底告别触发器方式。声明式分区性能更优是官方推荐的最佳实践。②.智能子表管理pg_partman会自动创建默认分区来捕获超出当前分区范围的数据并通过check_default()函数监控这些数据。当发现有效数据时可以使用partition_data_*系列函数轻松将其迁移到正确的分区。③.灵活的分区策略支持以下分区类型时间范围分区支持秒到千年的各种间隔ID范围分区支持整数和实验性的decimal类型列表分区仅限间隔为1的ID分区二、pg_partman的安装2.1、环境准备PostgreSQL 版本pg_partman 5.0及以上版本要求 PostgreSQL 14或更高版本 。它仅支持PostgreSQL内置的声明式分区native partitioning旧版本中基于触发器的方式已被弃用 。PostgreSQL 14.2 兼容的 pg_partman 版本是 ‌4.7.0 及以上‌。具体推荐版本最新稳定版 ‌pg_partman 4.7.1‌完全兼容 PostgreSQL 14.2若需要新特性可测试 ‌pg_partman 5.x‌但需验证兼容性部分功能可能需要 PostgreSQL 15本次安装以v4.7.1为例。2.2、安装pg_partmanwget https://github.com/pgpartman/pg_partman/archive/refs/tags/v4.7.1.tar.gz tar xzf v4.7.1.tar.gz cd pg_partman-4.7.1 # 指定postgresql配置路径安装 make PG_CONFIG/opt/pgsql/postgresql/bin/pg_config sudo make install PG_CONFIG/opt/pgsql/postgresql/bin/pg_config检测是否安装成功登录数据库执行以下SQL命令SELECT * FROM pg_available_extensions WHERE name pg_partman;2.3、配置共享库为了使用pg_partman的后台工作进程BGW来自动运行维护任务需要在postgresql.conf配置文件中预加载该库。# 必填启用后台工作进程 shared_preload_libraries pg_partman_bgw # 必填指定要运行维护任务的数据库名多个数据库用逗号分隔 pg_partman_bgw.dbname your_database_name # 可选调用维护过程的间隔秒数默认3600秒1小时 pg_partman_bgw.interval 3600 # 可选执行维护任务的角色默认为postgres pg_partman_bgw.role your_role修改此参数后必须重启PostgreSQL数据库服务。三、pg_partman的配置3.1、创建扩展建议为pg_partman创建一个专用的模式schema以便更好地组织对象。使用数据库超级用户或具有相应权限的用户连接到数据库并执行-- 创建专用模式 CREATE SCHEMA partman; -- 在该模式下创建扩展 CREATE EXTENSION pg_partman SCHEMA partman;3.2、设置权限pg_partman不要求必须由超级用户运行但建议创建一个专用角色来管理分区。-- 创建管理角色 CREATE ROLE partman_user WITH LOGIN; -- 授予对partman模式的权限 GRANT ALL ON SCHEMA partman TO partman_user; GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman_user; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman_user; GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman_user; -- 授予对将要创建分区的目标模式的权限 GRANT ALL ON SCHEMA your_target_schema TO partman_user; -- 允许创建临时表用于数据迁移 GRANT TEMPORARY ON DATABASE your_database_name TO partman_user;用超级管理员授权GRANT ALL ON SCHEMA partman TO postgres; GRANT ALL ON ALL TABLES IN SCHEMA partman TO postgres;四、pg_partman的使用本章主要是创建和管理分区这是使用pg_partman的核心步骤包括创建父表、调用create_parent函数初始化分区集以及理解配置表。4.1、创建分区父表首先你需要创建一个声明式分区的父表。pg_partman目前只支持RANGE分区类型。-- 示例创建一个按日期分区的日志记录表 CREATE TABLE public.logs ( id serial, log_time timestamptz NOT NULL DEFAULT now(), message text ) PARTITION BY RANGE (log_time); -- 在父表上创建索引它会自动传递给子分区 CREATE INDEX idx_log_time ON public.logs(log_time);关于主键或唯一约束在声明式分区中如果父表有主键或唯一约束必须包含分区键。这对于时间分区通常不实用因为它会限制每个分区只能有一个时间值。pg_partman通过模板表来解决这个问题将主键、唯一约束、默认值、非空约束等定义在模板表上这些属性会在创建新分区时自动应用。4.2、使用create_parent函数初始化创建好父表后调用create_parent函数让pg_partman接管分区管理。这个函数会创建初始的分区并在partman.part_config表中记录配置。SELECT partman.create_parent( p_parent_table : public.logs, -- 父表名需带schema p_control : log_time, -- 分区控制列 p_type : native, -- 分区类型固定为native 或 range p_interval : 1 day, -- 分区间隔支持1 day, 1 week, 1 month, 1 year,等 p_premake : 10, -- 预创建未来分区的数量 p_template_table : partman.template_public_logs, --分区模板表可省略 p_start_partition : 2026-03-19 00:00:00::text -- 起始时间 ); 或者 SELECT partman.create_parent( p_parent_table public.logs, -- 父表名 (必须带模式) p_control log_time, -- 分区列 (如 log_time, id) p_type native, -- 分区类型固定为native 或 range p_interval 1 day, -- 分区间隔 (如 1 day, 10) p_premake 3 -- 提前创建的未来分区数 (默认3) );关键参数说明p_parent_table已创建好的分区父表。p_control分区列通常是时间类型或整数类型。p_type分区类型值native是 pg_partman 3.x 到 4.x 以及部分早期5.x版本值range是 pg_partman 5.0 及更高版本 推荐p_interval分区间隔。对于时间列可以是daily,hourly等但官方文档和示例更推荐使用如1 day,1 month的写法。对于整数列可以是10000等数字。p_premake提前创建多少个未来分区这对于保证新数据插入时有对应的分区非常重要。p_template_table分区表模板。p_start_partition分区表起始时间。手动创建分区代码示例CREATE TABLE public.logs_p2026_03_22 PARTITION OF public.logs FOR VALUES FROM (2026-03-22) TO (2026-03-23);批量插入数据验证-- 一次插入多条记录 INSERT INTO logs (log_time, message) VALUES (2026-03-16 10:30:0008, 3.16 用户登录成功 - IP: 192.168.1.100), (2026-03-16 10:31:0008, 3.16 查询订单列表), (2026-03-16 10:32:0008, 3.16 查看商品详情 - 商品ID: 1001), (2026-03-16 10:33:0008, 3.16 添加购物车 - 商品ID: 1001, 数量: 2), (2026-03-16 10:34:0008, 3.16 创建订单 - 订单号: ORD202601150001), (2026-03-16 10:35:0008, 3.16 支付成功 - 金额: 299.00);4.3、理解part_config配置表每次成功调用create_parent后都会在partman.part_config表中生成一条配置记录。你可以通过查询和更新这个表来调整分区行为。SELECT parent_table, control, partition_interval, premake, retention FROM partman.part_config;常用的可配置选项包括retention数据保留策略。例如设置为30 days表示保留30天内的数据超过的分区将在维护任务运行时被清理DROP或DETACH。retention_keep_table当保留策略生效时是直接DROP表false还是仅从分区集中DETACHtrue后者会将表转换为普通表数据得以保留。infinite_time_partitions是否允许为未来的时间无限创建分区true或false 。4.4、自动化维护自动化是pg_partman的核心优势。这可以通过后台工作进程BGW或外部调度器如pg_cron实现。1、使用后台工作进程 (BGW)如果你在安装步骤中正确配置了shared_preload_libraries和pg_partman_bgw.dbname后台工作进程会自动定期调用run_maintenance()函数。这个函数会做两件事 为所有配置了automatic_maintenance on的分区集创建新的未来分区根据p_premake设置。根据retention设置清理DROP或DETACH过期的旧分区。-- 首先启用 pg_cron 扩展 CREATE EXTENSION pg_cron; -- 使用 cron.schedule 调度维护任务例如每天执行一次 SELECT cron.schedule( pg-partman-maintenance, -- 任务名称 daily, -- 调度计划也可以使用cron表达式 0 0 * * * $$CALL partman.run_maintenance_proc()$$ -- 要执行的维护过程 ); -- 注意旧版使用 run_maintenance() 函数新版建议使用 run_maintenance_proc() 存储过程 [citation:7][citation:8]特别注意pg_partman的维护逻辑是它会去检查最新分区里的数据找出最大的时间戳然后基于这个时间来创建下一个分区。如果你的最新分区是空的那么它查询到的最大时间戳就是NULL。代码在处理这个NULL值时可能会“迷路”导致它认为“没有下一步可做”从而直接退出不创建任何新分区。4.5、常用操作和监控掌握一些常用函数可以更灵活地管理分区。①.常用函数以下是一些pg_partman提供的实用函数手动运行维护SELECT partman.run_maintenance(public.logs); -- 或使用存储过程 CALL partman.run_maintenance_proc();迁移已有数据到分区如果创建分区前表中已有数据需要使用partition_data_time()或partition_data_id()函数将数据迁移到正确的子分区。查看分区信息-- 列出所有子分区 SELECT partman.show_partitions(public.logs); -- 显示特定值的分区名 SELECT partman.show_partition_name(public.logs, 2026-03-18::date);②.验证分区可以通过PostgreSQL的元命令或查询来验证分区是否创建成功。-- 查看表结构及分区列表 \d public.logs -- 查看数据在各个分区的分布 SELECT tableoid::regclass AS partition_name, count(*) FROM public.logs GROUP BY tableoid::regclass ORDER BY partition_name;4.6、数据保留与清理以时间分区为例这是分区管理中最常见的需求自动删除旧数据以释放空间。pg_partman通过part_config表中的retention相关字段来实现。①.设置保留策略例如希望只保留最近2天的数据。UPDATE partman.part_config SET retention 2 days, -- 保留2天 retention_keep_table false -- true: 仅从分区集分离, false: 直接删除表 WHERE parent_table public.logs;②.执行维护下一次运行run_maintenance_proc()时任何分区边界完全在(CURRENT_DATE - 2 days::interval)之前的分区都将根据retention_keep_table的设置被DROP或DETACH。DROP操作会立即将磁盘空间返还给操作系统这是清理旧数据的最高效方式。4.7、常见注意事项默认分区pg_partman的管理与默认分区DEFAULT不兼容。如果父表有默认分区run_maintenance函数会跳过处理因为它无法安全地移动或管理数据。如果数据不小心落入了默认分区需要先将其迁移出去。分区键与唯一约束如前所述在父表上直接定义包含分区键的唯一约束可能不现实。请使用模板表来为每个子分区单独应用主键、唯一约束等。锁create_parent函数在创建初始分区时会对父表加ACCESS EXCLUSIVE锁应在维护窗口执行。迁移现有分区集如果已有手动创建的分区表希望纳入pg_partman管理可以通过更新part_config表或使用辅助函数来实现但过程需要谨慎。