CentOS 7上PostgreSQL分区扩展pg_partman安装配置指南

做PostgreSQL运维久了迟早要面对分区这件事。业务表动辄上亿行查询越来越慢清理数据越来越痛苦很多人的第一反应是手动建表、写触发器、再写定时任务结果维护成本比收益还高。pg_partman就是专门解决这个问题的扩展它可以在PostgreSQL里自动创建和管理分区表尤其擅长按时间分区每天、每周、每月都能自动往下滚新分区提前建好旧分区按策略清理DBA只需要定义好父表和参数剩下的交给它。这篇文章就围绕CentOS 7上安装配置pg_partman的完整过程展开包括版本匹配、编译安装、初始化配置、实战建表和排错思路适合正在用PostgreSQL做数据归档、日志存储、监控数据和任何时间序列数据的运维和开发同学参考。1. 装之前先搞清楚的事版本匹配与依赖陷阱1.1 PostgreSQL版本与pg_partman不是随便配的很多人在CentOS 7上安装pg_partman失败根本原因不是操作不对而是PostgreSQL版本太老。CentOS 7系统自带的PostgreSQL是9.2版本这个版本连原生声明式分区都不支持更别说跑pg_partman的很多新特性。pg_partman 4.x系列一般要求PostgreSQL 10以上5.x系列对PostgreSQL主版本要求更高。你如果拿着系统自带的9.2去编译大概率会报一堆看不懂的错误就算强行装上扩展也创建不了后台维护进程更是起不来。所以在安装之前第一件事就是确认当前PostgreSQL版本。用下面命令一眼就能看到psql --version或者直接在数据库里执行SELECT version();如果版本低于10我的建议是别挣扎了直接把PostgreSQL升级走官方源或者PGDG源安装新版。CentOS 7上配置PGDG源很简单yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm然后安装你需要的版本比如PostgreSQL 13yum install -y postgresql13-server postgresql13-devel注意一定要装postgresql13-devel后面编译pg_partman时需要用它的pg_config来定位头文件和扩展安装目录。如果你少装了这个开发包编译时会直接提示找不到pg_config这一步能挡住一半的人。1.2 依赖关系没有Perl模块寸步难行pg_partman虽然主体是C扩展和SQL脚本但它的后台维护逻辑大量依赖Perl脚本比如自动建分区、清理过期分区这些动作最终都是通过Perl调度的。CentOS 7上最常出现的错误是Cant locate DBD/Pg.pm in INC这个错误就是缺少Perl操作PostgreSQL的驱动模块。编译前先把这些依赖装齐yum install -y perl-DBI perl-DBD-Pg perl-Try-Tiny如果yum源里找不到可以用cpan安装cpan DBD::Pg我之前踩过这个坑扩展编译安装成功了CREATE EXTENSION pg_partman;也执行成功了但一调用维护函数就报Perl模块缺失折腾了半个多小时才发现是依赖没装全。所以建议你在编译之前就把Perl依赖装好省得后面排查。2. CentOS 7环境下的编译安装全过程2.1 下载源码与编译步骤确定好PostgreSQL版本和依赖之后就可以开始编译安装了。先去pg_partman的GitHub Releases页面下载对应版本的源码包。我以4.7.x版本为例cd /opt wget https://github.com/pgpartman/pg_partman/archive/refs/tags/v4.7.4.tar.gz tar -xzf v4.7.4.tar.gz cd pg_partman-4.7.4新版pg_partman编译很简单Makefile默认会去PATH里找pg_config。如果你装了多个PostgreSQL版本就必须显式指定。比如我装的是PostgreSQL 13make PG_CONFIG/usr/pgsql-13/bin/pg_config make install PG_CONFIG/usr/pgsql-13/bin/pg_config执行完make install后扩展文件会安装到PostgreSQL的share/extension目录动态库会装到lib目录。这一步基本不会报错如果报错九成是pg_config路径不对或者权限不足。值得说明的是老版本的pg_partman需要先执行configure生成Makefile4.x之后的版本已经不需要了直接make就行。网上很多教程还在让你./configure其实那是过时的步骤照做反而会卡住。2.2 安装后的验证方法装完不要急着配置先用命令确认文件是否落位pg_config --sharedir ls -l $(pg_config --sharedir)/extension/ | grep partman正常能看到partman.control和一堆partman--*.sql文件。然后切换到postgres用户连上数据库试创建扩展CREATE EXTENSION pg_partman;这里有两个容易翻车的细节。第一CREATE EXTENSION需要数据库用户有超级权限普通用户会报权限不足第二如果之前执行过CREATE EXTENSION失败PostgreSQL会在当前库里留下一个半残的schema重试之前最好先清理。查询扩展版本确认安装成功SELECT extversion FROM pg_extension WHERE extname pg_partman;能看到版本号说明扩展本身已经装好了。但此时后台维护进程还没起来因为还没有配置shared_preload_libraries这就是下一章的内容。3. PostgreSQL侧的初始化配置让维护任务真正跑起来3.1 修改postgresql.conf与后台进程加载pg_partman有两种维护方式一种是内置后台工作进程一种是靠外部cron定时调用维护函数。生产环境我推荐后台工作进程方式自动化程度高部署好了基本不用管。要启用后台进程需要修改postgresql.conf中的shared_preload_libraries。这里有个版本差异容易踩坑PostgreSQL 14及以后版本pg_partman的动态库名是pg_partman更早版本里叫pg_partman_bgw。以PostgreSQL 13为例shared_preload_libraries pg_partman_bgw如果你用的是PostgreSQL 14以上就写shared_preload_libraries pg_partman。拿不准的时候看安装目录里的.so文件叫什么名字ls -l $(pg_config --libdir)/ | grep partman修改完配置后最关键的一步来了必须完全重启PostgreSQL而不是reload。很多人用pg_ctl reload或者SELECT pg_reload_conf();想热加载结果后台进程永远起不来因为shared_preload_libraries只能在启动时加载。重启命令取决于你PostgreSQL的安装方式。我用的是PGDG RPM包所以systemctl restart postgresql-13重启后检查进程ps aux | grep partman正常会看到类似postgres: pg_partman_bgw的进程。如果看不到去PostgreSQL日志里翻最常见的报错是动态库路径不对或者shared_preload_libraries里写的名字和实际.so文件名不一致。3.2 配置项与控制开关后台进程起来后还需要决定它按什么节奏工作。postgresql.conf里几个核心参数如下参数默认值说明partman.auto_maintenanceon是否自动执行维护任务partman.interval3600后台进程唤醒间隔单位秒partman.analyzeon维护后是否自动执行ANALYZEpartman.verboseoff是否输出详细日志我个人习惯把partman.interval设为3600也就是每小时跑一次维护。如果业务表写入量特别大分区增长很快可以调到900甚至300但这会增加数据库开销。partman.analyze建议保持on因为新分区刚创建时统计信息是空的自动analyze能避免SQL走错执行计划。配置完成后手动跑一次维护函数验证链路是否通畅SELECT partman.run_maintenance();如果返回空结果或者正常结束说明后台链路没有问题。这一步很关键因为后续如果分区不自动生成可以先用这条SQL判断是函数问题还是后台进程问题。4. 用pg_partman创建第一张时间分区表4.1 父表与create_parent函数原理讲再多不如实际建一张表。假设我有一张设备上报数据表每天几百万行需要按天分区。先创建父表CREATE TABLE public.device_metrics ( logdate date NOT NULL, device_id int NOT NULL, payload text ) PARTITION BY RANGE (logdate);注意控制列logdate必须NOT NULL否则pg_partman会拒绝管理。然后调用create_parent函数SELECT partman.create_parent( p_parent_table public.device_metrics, p_control logdate, p_type native, p_interval 1 day, p_premake 4, p_start_partition 2024-06-01 );参数含义对照表参数示例值作用p_parent_tablepublic.device_metrics父表全名必须带schemap_controllogdate分区控制列date或timestamp类型p_typenativenative表示原生声明式分区partman表示旧式触发器分区p_interval1 day分区间隔支持1 week、1 month、1 yearp_premake4预创建未来多少个分区p_start_partition2024-06-01起始分区边界这里核心要理解p_type。native走的是PostgreSQL 10以后的原生声明式分区性能更好约束管理由内核负责partman是老式的触发器继承分区兼容老版本PG但现在我不建议新项目再用了。CentOS 7上如果装的是PostgreSQL 13直接上native。4.2 分区实际生成与数据路由验证执行完create_parent后立刻能看到一批分区已经生成SELECT inhrelid::regclass AS child_partition FROM pg_inherits WHERE inhparent public.device_metrics::regclass ORDER BY 1;分区命名规则是父表名加下划线加日期比如device_metrics_p2024_06_01。插入数据验证路由是否正确INSERT INTO public.device_metrics (logdate, device_id, payload) VALUES (2024-06-02, 101, test-data);然后看执行计划EXPLAIN SELECT * FROM public.device_metrics WHERE logdate 2024-06-02;如果分区剪枝生效计划里只会扫描device_metrics_p2024_06_02这一个分区而不是全表扫描。到这一步一张能被pg_partman自动管理的时间分区表就建好了。后续每天凌晨后台维护进程会自动创建新的日期分区你不需要再手动建表。5. 实际运维中才会遇到的坑完整排查链路5.1 新分区不自动生成的排查思路我见过太多人卡在这一步分区表建好了当天数据也在正常写入但第二天检查时发现未来的分区没有自动创建。我第一次遇到也是这个情况当时差点把整个配置推倒重来。后来总结出一条完整的排查链路按顺序走基本十分钟内能定位问题。第一步确认后台进程是否真的活着ps aux | grep partman如果进程不存在回看3.1节确认shared_preload_libraries写对了、PostgreSQL是否完全重启过。第二步手动执行维护函数排除函数本身的问题SELECT partman.run_maintenance(public.device_metrics);如果这条能正常生成未来的分区说明函数的执行链路没问题问题多半在后台进程或者自动开关如果它直接把新分区建出来了恭喜你问题就是后台进程没起来。第三步查part_config表看参数是否合理SELECT parent_table, control, interval, premake, last_partition FROM partman.part_config WHERE parent_table public.device_metrics;重点看premake和last_partition。如果last_partition已经建到很远但新的一天来临后没有继续建那可能是partman.interval设置太长或者partman.auto_maintenance被关掉了。还有一个容易被忽略的点如果维护进程已经建出了一批空分区但业务写入时间突然跳到更远的未来比如运维手动插了一条三个月后的数据分区路由会临时自动建分区这个临时分区不在预创建范围内后续维护可能会漏掉需要人工触发一次run_maintenance补齐。5.2 编译阶段最容易翻车的几个历史教训编译安装阶段有几个坑是CentOS 7上的“特产”值得单独拎出来说。第一个是SELinux。CentOS 7默认SELinux是enforcing模式PostgreSQL扩展安装到系统目录后SELinux策略可能阻止数据库进程读取这些新文件表现就是CREATE EXTENSION报权限错误但文件权限明明没问题。测试环境最省事的做法是临时关闭SELinux验证setenforce 0如果关掉后一切正常那就是SELinux的问题。生产环境不建议直接关而是用audit2allow生成针对性的策略模块或者把PostgreSQL的数据目录和扩展目录的上下文标签设成和原PostgreSQL文件一致restorecon -Rv /usr/pgsql-13/第二个坑是pg_config路径被多个版本的PostgreSQL搞乱。我遇到过一台机器上同时有系统自带的PostgreSQL 9.2和PGDG的PostgreSQL 13不指定PG_CONFIG直接make安装文件全部进了9.2的目录导致13的实例里创建扩展时找不到partman.control。解决办法就是编译时显式写清楚make PG_CONFIG/usr/pgsql-13/bin/pg_config make install PG_CONFIG/usr/pgsql-13/bin/pg_config第三个坑是创建扩展时schema冲突。pg_partman默认会创建partman这个schema如果之前手动建过同名schemaCREATE EXTENSION会失败。可以先检查SELECT * FROM pg_namespace WHERE nspname partman;有冲突就清理掉再重试或者创建扩展时指定WITH SCHEMA放到别处但我不推荐因为后台脚本默认找的是partmanschema改名容易埋雷。6. 进阶用法与日常维护建议6.1 手动管理分区和清理策略自动维护虽然省心但特殊场景下还是要手动干预。比较常用的是这三个函数。第一手动补充建分区。当磁盘突发占用或者业务促销导致写入量暴增预创建的分区马上要被写满时可以手动跑一次维护并调大premakeSELECT partman.run_maintenance(public.device_metrics);第二保留策略。很多日志类表只需要保留最近90天数据可以在part_config里设置retention策略UPDATE partman.part_config SET retention 90 days, retention_keep_table false WHERE parent_table public.device_metrics;这样后台维护进程会在每次运行时自动删除90天前的旧分区效率远高于DELETE。第三彻底清理某个分区。如果某天数据出了问题要快速删掉一个分区SELECT partman.drop_partition(public.device_metrics, 2024-01-01);这个操作是直接移除分区文件比DELETE FROM ... WHERE logdate ...快几个数量级但要注意操作不可逆生产环境先备份再执行。6.2 序列分区与自定义分区间隔时间分区是pg_partman最常见的用法但它也支持按序列和数字范围分区。对于没有时间列只有自增ID的业务表可以在表上加一个序列控制列然后调用create_parent时把类型设为partman间隔设为数值区间。这种场景适合流水号表、订单表等但要注意数字分区的边界维护比时间分区麻烦你需要自己预估增长速率否则分区数量可能失控。自定义分区间隔使用上也有讲究。比如p_interval 3 months表示按季度分区这没问题但如果你写p_interval 90 days虽然也能创建分区可分区边界和你预期的自然季度会错开日后再按月份查询时一条数据会被分到相邻分区里很容易造成查询计划判断混乱。我的建议是生产环境优先用native类型加标准时间间隔维护成本最低路由效率最高如果需要非常规间隔先在测试库上把边界验证清楚再上生产。再分享一个实践中的小习惯每次升级PostgreSQL小版本后我会顺手检查一次pg_partman版本因为PostgreSQL的API在少数小版本更新中也会调整晚一步升级扩展可能导致后台进程报错。检查方法很简单SELECT extversion FROM pg_extension WHERE extname pg_partman;然后和官方Release Notes对比有新版就在维护窗口内重新make install并重启数据库。这个动作我一般规划在PostgreSQL升级的同一天一次性完成省得隔三差五为了扩展单独重启一次。