尧图网页设计 
1. 先把pg_partman这玩意儿说清楚最近在CentOS 7上给一套业务库做数据生命周期治理最核心的一件事就是把几张上亿行的流水表切成按时间分区。刚开始我准备纯手工写触发器、按月建表、再定期删旧表搞到一半觉得太痛苦了。然后同事丢过来一个词pg_partman。简单说这是一个 PostgreSQL 分区管理扩展专门干“自动建子表、自动维护约束、按保留策略清理过期分区”这些脏活累活。这个工具适合谁用主要是三类人一是像我一样要管大表、又不想天天盯维护任务的DBA二是后端开发业务表数据量涨得快但又不想把分区分区逻辑写死在业务代码里三是数据运维需要做冷热数据分离、归档清理的场景。理解起来也不难你可以把pg_partman理解成一个“分区表管家”你告诉它“以后每周帮我建一个新分区保留最近三个月的数据”剩下的它定时自动执行。需要提前说明的是pg_partman不是独立服务它是PostgreSQL的一个扩展跑在数据库内部靠后台工作进程background worker或者外部定时任务调用维护函数。所以它本身不增加额外的运维复杂度只要装好、配置好它就安安静静在库里面干活。下文中我会把CentOS 7上的安装、建分区、自动化维护、排障经验都过一遍全程都是我在实际环境里验证过的操作。2. 安装前的环境准备CentOS 7侧的老规矩2.1 系统与数据库版本选型先说你必须在装之前想清楚的一件事pg_partman的版本和PostgreSQL版本必须匹配。我这边测试环境是CentOS 7.9内核3.10数据库用的是PostgreSQL 12pg_partman用的是4.7.x版本这套组合很稳。如果你还在用PG 9.6或者10建议用pg_partman 4.5.x左右的版本如果是PG 13以上尽量用最新release包。总之先查清楚你的数据库版本再选pg_partman版本顺序不要反过来。另外CentOS 7默认的PostgreSQL版本比较旧如果你是用yum直接装的多半是9.2这种老古董那就没必要折腾pg_partman了。我建议要么用PostgreSQL官方yum源装新版要么用源码编译。既然标题里提到CentOS 7说明很多人的环境是内网、离线的yum源和依赖包都得早做准备。2.2 编译工具链与PostgreSQL开发包pg_partman的安装方式有两种一种是用包管理器直接装比如PostgreSQL官方源里可能带了postgresql12-partman这样的包另一种是源码编译这也是最通用、最容易在离线环境复现的方式。源码编译的前提是PostgreSQL的开发头文件必须齐全也就是postgresql-devel或者postgresqlXX-devel这个包。在CentOS 7上我一般这样检查环境rpm -qa | grep postgresql pg_config --version which pg_config如果pg_config命令找不到说明开发包没装全。用官方yum源的话安装命令类似yum install -y postgresql12-devel postgresql12-server postgresql12-contrib确认pg_config可用之后编译工具链还差gcc、make这两个直接yum装yum install -y gcc make注意如果是内网离线环境建议在一台能联网的同版本机器上把依赖包通过yum downloadonly拉下来或者准备好rpm包。我吃过一次亏内网机器编译到一半才发现缺perl模块整个人都裂开了。2.3 磁盘空间与分区挂载检查这个坑属于“不撞一次不会记住”的那种。pg_partman是按时间自动创建子分区的每次run_maintenance()跑完都可能新增好几张子表每张子表如果数据量很大一段时间的磁盘增长会非常快。我在一台测试机上只建了3张按月分区表premake设为4结果一个季度下来数据目录多了将近200GB直接把根分区撑满了。所以装之前务必检查一下数据目录的挂载和剩余空间df -h /var/lib/pgsql du -sh /var/lib/pgsql/*如果你根目录空间不大建议提前把PostgreSQL数据目录迁移到大分区或者做逻辑卷扩容。热词里提到的“根目录扩容”就是这种典型场景别再等告警了才去扩。2.4 角色与权限规划安装扩展本身通常用超级用户postgres操作但业务运行时不建议让业务账号持有超级权限。pg_partman的官方文档里给了专门的授权方案核心是先建一个专门账号CREATE ROLE partman_user WITH LOGIN; 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;跑维护任务的定时任务用这个专用账号来连库就行。我自己的习惯是建表、初始化用postgres定时任务用partman_user业务账号只读写业务表权限给最小集。这样即使某天定时任务配置出了问题影响面也可控。3. pg_partman安装实操源码编译与配置3.1 下载源码与解压源码获取直接从GitHub拉release包就行不需要git clone整个仓库。比如4.7.1版本cd /usr/local/src wget https://github.com/pgpartman/pg_partman/archive/refs/tags/v4.7.1.tar.gz tar -zxvf v4.7.1.tar.gz cd pg_partman-4.7.1如果你的环境完全离线那就找一台能联网的机器下好tar包再想办法拷进去。GitHub有时候连起来很慢也可以先用国内镜像加速下载这个看个人习惯。3.2 configure、make、make install 全流程pg_partman的编译流程和普通PostgreSQL扩展一样核心是让pg_config指向正确的版本。这里有个最容易踩的坑如果你系统里装了多个PostgreSQL版本pg_config可能指向旧版编译出来的扩展就没法加载。我的做法是在configure前显式指定路径export PATH/usr/pgsql-12/bin:$PATH export PGPORT5432 ./configure make make installmake install会把扩展文件放到PostgreSQL的lib和share/extension目录下。装完之后你可以用pg_config --pkglibdir确认一下路径对不对也可以直接去扩展目录里看有没有pg_partman.controlls -l $(pg_config --sharedir)/extension/pg_partman* ls -l $(pg_config --pkglibdir)/pg_partman*看到这两个文件都在说明扩展文件已经就位。编译过程中最常见的报错是“pg_confignot found”或者版本不匹配遇到之后优先检查PATH。经验之谈编译前一定先把PGPORT和PGDATA环境变量理清楚。我见过有人configure的时候一切正常make也通过但装完库后CREATE EXTENSION时报版本不对最后定位到是系统里残留了旧版pg_config特别浪费时间。3.3 在数据库中创建扩展文件装好了下一步就是登进数据库创建扩展。这里有一个建议给pg_partman建一个独立的schema不要丢在public里方便后续权限管理。CREATE SCHEMA partman; CREATE EXTENSION pg_partman SCHEMA partman;创建完之后可以通过两个方式验证是否正常SELECT extname, extversion FROM pg_extension WHERE extname pg_partman; \dx pg_partman如果你建扩展时报错找不到pg_partman.control那说明make install没有成功或者安装到了别的实例目录。另外注意如果数据库是旧版本升级上来的扩展安装可能需要额外处理依赖我会在第5部分展开讲。3.4 开启后台工作进程pg_partman有两种跑维护任务的方式一种靠外部定时任务调用run_maintenance()函数另一种是靠它自带的background worker进程在数据库内部定时自动跑。用后台进程的方式更省心但需要修改postgresql.conf并重启实例。我推荐直接用后台进程配置很简单shared_preload_libraries pg_partman_bgw然后在postgresql.conf里再加上几个参数pg_partman_bgw.interval 3600 pg_partman_bgw.role postgres pg_partman_bgw.dbname yourdbinterval默认是3600秒也就是每小时检查一次。如果你希望更频繁可以调到1800但一般业务一小时跑一次足够了。改完之后必须重启PostgreSQL进程systemctl restart postgresql-12重要shared_preload_libraries里同时要加载其他扩展的话用逗号分隔类比一下就像Linux系统的开机自启服务列表加载顺序和写法都得注意。pg_partman_bgw的排序放在最后一般更稳妥。重启之后查看日志确认后台进程是否真的起来了tail -f /var/log/postgresql/*.log如果能看到类似pg_partman_bgw: pg_partman background worker started之类的日志说明一切正常。如果没见到大概率是参数名写错了或者角色名不对优先排查这两点。3.5 验证后台进程是否工作光看日志还不行我一般还会直接往配置表里插一条测试数据。比如查询partman.part_config表SELECT * FROM partman.part_config;如果查询正常没有权限报错说明扩展已经可用。接下来进入核心环节建第一张由pg_partman管理的时间分区表。4. 核心配置从创建第一张分区表开始4.1 先设计分区策略别急着敲命令很多人拿到pg_partman就先建父表然后一股脑地调create_parent结果后面发现分区粒度不对又得推倒重来。我的建议是先回答三个问题数据按哪个字段分区单分区数据量多大才合适保留多少分区够用先说字段选择。时间分区一般选一个不会为NULL、且写入后基本不变的时间字段比如订单创建时间created_at。如果表里有id这种自增字段也可以按序列区间分区但绝大多数业务场景还是按时间最直观。单分区数据量这个事没有一个绝对标准。我一般这样粗算一张子表的数据量控制在千万级以内单表文件大小在10GB左右比较合适。如果每天写入量是50GB那就按小时分区或者按天分区如果每天只有几百MB按周或者按月分区就行。分区粒度太细会导致子表数量膨胀维护时要遍历的分区太多DELETE和VACUUM都有压力粒度太粗又会让大分区失去拆分意义。再一个容易被忽略的点时区。你的应用服务器、数据库服务器、业务JavaScript前端如果处于不同时区时间字段的写入值可能有偏差分区边界对不上偶发数据漏进不该进的分区。最好在项目初始化时就统一约定表结构里存统一的时间戳如UTC展示层再转换时区。4.2 创建父表与索引模板分区表的结构设计比较讲究。父表本身在PG里是一个“空壳”所有数据都实际存在子分区里所以索引如果在父表上建子表会自动继承相同的索引结构。这块建议先建父表再统一建索引不要在每张子表上单独手动加索引那样维护量会爆炸。CREATE TABLE public.audit_log ( id bigserial, occurred_at timestamptz NOT NULL, event_type text, detail jsonb, PRIMARY KEY (id, occurred_at) ) PARTITION BY RANGE (occurred_at);注意如果你的数据库版本是12可以直接用原生分区语法如果是PG 10以前的老版本只能走传统继承分区PARTITION BY RANGE语法不支持。pg_partman两种模式都兼容原生分区模式下它会用PG自己的分区机制来创建子表继承模式下它会自己维护继承关系。我用的是PG 12所以上面直接写了原生分区语法。索引模板上有一个重点主键或唯一索引需要包含分区字段。因为PostgreSQL原生分区要求分区键必须包含在主键中否则无法保证全局唯一性。这也是很多新手第一次跑create_parent报错的原因。所以上面我把主键设计成(id, occurred_at)这不是随手写的。4.3 调用create_parent初始化分区父表建好之后调用create_parent函数这一步是pg_partman真正开始接管这张表的标志。常用参数如下SELECT partman.create_parent( p_parent_table : public.audit_log, p_control : occurred_at, p_interval : 1 day, p_premake : 4, p_start_partition : 2025-01-01 00:00:0000 );p_parent_table父表名注意要带schema。p_control分区字段就是你要按哪个字段切分。p_interval分区间隔支持1 day、1 week、1 month等。p_premake提前创建的子分区数量。设成4意味着从现在起后面4个周期分区会提前准备好。p_start_partition起始分区时间给一个你业务数据开始的时间。从工程角度讲p_premake不建议设太大4到6比较常见。设太大会提前创建很多空子分区不仅浪费空间还会让partman.part_config表的扫描变慢后续每次维护都要多处理很多空表。4.4 约束、索引与默认分区第一次执行create_parent之后你可能会发现表里多了好几个子分区比如audit_log_p2025_01_01、audit_log_p2025_01_02之类。pg_partman的命名格式是基于时间的可以自己查一下SELECT child_table, partition_interval, retention FROM partman.part_config;子分区的约束是pg_partman自动加的它会根据字段值判断这条数据属于哪个分区。这里我要强调一个容易踩的坑不要自己手动去DROP或ALTER这些约束否则run_maintenance()在后续运行时会判断这个分区不合法然后尝试重建报出一堆奇怪的错误。如果你真想删除某个分区应该用drop_partition或者直接DROP TABLE而不是去动约束。还有默认分区的问题。原生分区模式下PG 11以后支持DEFAULT分区。如果你的业务偶尔会写入超出所有子分区范围的数据比如premake没覆盖到的时间点却又没有默认分区PostgreSQL会直接报错。我的建议是加上一个默认分区兜底CREATE TABLE public.audit_log_default PARTITION OF public.audit_log DEFAULT;这样即使维护任务偶尔没跑数据也能写进来不至于线上报错。但默认分区相当于一个“杂物间”里面的数据不会自动清理时间久了容易堆积。所以我会在巡检时专门看一眼默认分区是否在不断增长如果持续增长说明维护任务可能出问题了。4.5 常见误区别在线上大表上直接建分区如果这张表已经是线上跑了好几个月的大表里面已经有海量数据你想直接PARTITION BY RANGE重建它原生分区语法要求建表时指定分区键已存在的普通表是不能直接转换的。pg_partman官方倒是提供了一个方式在create_parent时通过p_initialize : init参数初始化把已有数据搬到分区里去。但我给你的建议是大表迁移一定先在测试环境演练估算一下要迁移多少数据量预计多久窗口完事同时准备好备库或回滚手段。不要天真地以为在美滋滋跑一句SQL就能搞定几亿行的数据迁移轻则锁表重则把库拖垮。我踩过最疼的一次坑迁移一张3亿行的大表实际的初始化把整个库的负载打到了90%以上业务侧告警满天飞。5. 自动化维护与数据生命周期管理5.1 run_maintenance内部到底在干什么pg_partman的维护核心就是run_maintenance()函数。这个函数被后台进程或者定时任务周期性调用每次运行它都会做这几件事检查每张被管理的父表根据当前时间和premake值计算需要提前创建的子分区调用底层的建表逻辑创建缺失的子分区根据retention设置清理过期子分区。所以实际上维护任务做得勤不勤决定了“分区表有没有窗口期没有可用分区”。只要premake够大即使维护任务晚跑一两个小时新数据也能落到已经建好的下几个分区里不会报错。这也是为什么说premake参数是保险丝。SELECT partman.run_maintenance(p_analyze : true);p_analyze : true表示维护完顺手对新分区做ANALYZE更新统计信息。第一次跑维护或者刚完成数据迁移后建议手动执行一次确认没有报错再交给后台进程。5.2 保留策略retention设置与自动清理很多用pg_partman的人其实不是为了自动建分区而是为了自动删分区。一张大表如果手动DELETE数据会产生大量死元组且不能释放空间VACUUM全表还会一直锁表。但按分区DROP TABLE则几乎瞬间完成且空间立刻回收。这就是pg_partman清理方案的核心优势。在partman.part_config表里有几个关键字段retention保留多少周期的数据。retention_keep_table若为true则保留表比如归档表只是不做自动DROP。retention_keep_index是否保留索引。设置保留策略有两种方式。一种是在建表时直接指定但更常见的是维护时动态修改UPDATE partman.part_config SET retention 90 days, retention_keep_table false, retention_keep_index false WHERE parent_table public.audit_log;这里再强调一遍retention的单位需要和partition_interval匹配。如果你分区间隔是1 dayretention 90 days就是保留90个分区如果分区间隔是1 week那90 days在源码里会被换算成多少个周容易产生歧义。所以最稳的写法是把retention设成和分区间隔一致的格式比如12 weeks或者3 months。5.3 定时任务选型后台进程还是cronpg_partman官方推荐方式是使用后台进程但我自己实际用下来在不同的环境里各有优劣。如果库数量少、表数量也少后台进程是最省事的因为不需要额外部署crontab也不需要担心过期任务没清理。但有个问题后台进程默认每小时跑一次如果你的业务表很多比如几十张父表一次维护可能要扫很久那么维护周期内新增的分区请求可能不会被及时处理。这时候可以调低pg_partman_bgw.interval参数或者改用外部crontab更灵活地控制执行频率。如果分时段执行更贴合你的业务比如凌晨2点集中跑一次那cron更合适0 2 * * * psql -U postgres -d yourdb -c SELECT partman.run_maintenance(p_analyze : true);不过用cron要注意一点脚本要设置好PGPASSWORD或者用.pgpass文件否则密码交互会让你执行失败。另外cron环境变量很少postgresql的bin目录要写全路径我在生产上干脆直接写/usr/pgsql-12/bin/psql。我个人的最终选择是线上库数量多用后台进程统一管关键的几张核心大表另外加了cron做凌晨的集中数据归档与分区整理。两条腿走路比单一机制稳得多。5.4 批量迁移历史数据partition_data_proc的使用如果你不是从零开始建分区表而是想把一张普通大表的历史数据按时间搬到新的分区结构里去用create_parent初始化是一个选择但更细粒度的控制方式是partition_data_proc函数。这个函数可以分批把指定范围内的数据从主表移动到子分区。SELECT partman.partition_data_proc( p_parent_table : public.audit_log, p_interval : 1 day, p_batch : 10000, p_delay : 0.5 );p_batch是每批迁移行数p_delay是批与批之间的休眠秒数用来给主库缓解负载。实际生产中我建议p_batch取5000以内p_delay至少0.2秒不要贪快。迁移过程中不断有新的业务数据写入时就需要配合锁表和业务停写窗口否则会出现数据漏迁。跑完批量迁移之后原来的父表里可能还有残留数据用下面的方式检查SELECT count(*) FROM ONLY public.audit_log;如果还有残留继续跑partition_data_proc直到结果为0。最后别忘了CLUSTER或VACUUM FULL释放空间。5.5 停止维护与回滚stop_maintenance和undo_partition有开始就得有回退方案。如果你要下线一张分区表或者需要把数据重新从分区结构合并回普通表pg_partman提供了undo_partition函数。它的用法是从最旧的分区开始把数据搬回父表然后删除子分区。SELECT partman.stop_maintenance(p_parent_table : public.audit_log); SELECT partman.undo_partition(p_parent_table : public.audit_log, p_batch : 10000);stop_maintenance只是把该表的自动维护停掉配置还在。undo_partition执行真正的数据回迁。要注意undo_partition是一次性把全部分区数据合并回去所以执行前一定要评估总数据量准备好足够的磁盘空间和停机窗口。我在一台测试库上做过一次大约200GB的回迁跑了将近6个小时中间不敢停停了就得重新来。这种操作基本属于“非必要不执行”平时把配置想清楚比事后回滚重要得多。6. 常见问题与排查技巧实录6.1 非超级用户想要维护分区时的授权问题run_maintenance()函数默认需要超级用户权限但实际生产环境不可能给定时任务用postgres超管。pg_partman从4.x开始提供了给普通角色授权的方式。除了我在2.4节提到的基础授权外你还需要把维护函数执行权限给出去GRANT USAGE ON SCHEMA partman TO partman_user; GRANT EXECUTE ON FUNCTION partman.run_maintenance(text, boolean, boolean, boolean) TO partman_user; GRANT EXECUTE ON FUNCTION partman.create_parent(text, text, text, text, int, text, boolean) TO partman_user;同时还需要让partman_user对父表和子分区所在的schema有建表权限。不然后台进程生成了建表语句却执行不了日志里全是权限错误。6.2 分区维护不生效排查思路遇到“分区没有按预期创建”或者“旧数据没被清理”我有一套固定的排查路径。先看配置表SELECT * FROM partman.part_config WHERE parent_table public.audit_log;确认partition_interval、premake、retention是否合理。如果配置没问题手动跑一次维护函数SELECT partman.run_maintenance(p_analyze : false);然后立刻查分区列表和日志。90%的情况都可以在这里定位到问题。如果手动跑也报错那就看报错信息。最常见的是时间字段类型不对比如p_control字段是timestamp without time zone而分区建表语句里用了timestamptz两个类型比较时出现转换错误。解决办法是建表时统一用timestamptz极少情况下需要改字段类型。另一个常见问题后台进程没有真正启动。很多人改完shared_preload_libraries重启数据库后发现日志里根本没有bgw启动记录。这时请检查pg_partman_bgw.dbname参数是否写对它必须匹配你连的数据库名不能是逗号分隔的多库名。官方文档里说支持逗号分隔多库但实际配置中我建议一库一配出了问题好排查。6.3 与已有触发器或外键的冲突分区表有外键时问题比较头疼。PostgreSQL原生分区表在PG 12之前不允许在父表上定义外键PG 12之后有所放宽但分区键仍然不能带上外键关联的字段。也就是说如果audit_log里有一个外键指向users你把它作为分区键直接就会报错。我的建议是分区表尽量不建外键由应用层保证数据完整性。触发器方面pg_partman自己要求分区表不能有UNLOGGED之类的特殊属性冲突。如果你在父表上建了BEFORE INSERT触发器时间字段如果是由触发器在写入时填充的那p_control字段必须在触发器填充完之后才做分区路由。也就是说插入时分区判定可能看不到实际上要写入的分区键导致数据进错分区。遇到这种情况要么重写触发器逻辑要么确保分区键由应用传值别依赖触发器改。6.4 分区清理后空间没释放怎么办自动删了子分区表空间文件理论上应该释放。但如果你用了DROP TABLE磁盘空间会立即回收如果走的是TRUNCATE或者普通DELETE则需要VACUUM FULL才能把空间还给操作系统。pg_partman默认的子分区删除方式是DROP TABLE所以正常情况不会出现空间不释放的问题。我踩过的坑是手动清理过子分区用的是TRUNCATE而不是DROP结果PostgreSQL数据目录里的文件大小没变。后来才反应过来TRUNCATE只是清空数据不释放文件。建议所有清理操作都优先用drop_partition函数或者直接DROP TABLE不要自己写DELETE或TRUNCATE。6.5 pg_partman版本升级注意事项升级pg_partman之前需要看官方文档里的升级路径。最稳的顺序是备份partmanschema下的所有配置数据。停止后台进程可以先注释掉shared_preload_libraries并重启实例。用新版本的源码重新make install。在数据库中执行ALTER EXTENSION pg_partman UPDATE TO 4.7.1;。恢复配置参数重启实例确认bgw起来了。很多人升级直接替换二进制文件却不更新扩展这一步执行到一半就会报版本不匹配。另外升级完别忘了重新授权因为新版本可能会新增函数和表权限不会自动继承。6.6 我从实际使用里提炼的几个经验第一模板表机制值得用起来。pg_partman支持把一张模板表上的列完整复刻到新子分区包括字段注释、约束、默认值。建好模板表后在create_parent时传入p_template_table参数即可。这样以后字段调整只改模板表和父表不需要去逐个改几十张子表省了很多事。第二日志别开太全。pg_partman的日志字段里有run_maintenance的详细输出但这个函数在debug模式下会打印很长生产上如果开了DEBUG会刷爆日志文件。我一般把log_min_messages设为warning只在排障时临时调到debug。第三premake和retention要配合业务巡检周期。有些业务有季度大促数据量是平时的几倍如果premake只设了4大促前忘了测到了周末发现分区表没有下一周的分区写入直接失败就是事故了。我现在养成的习惯是每季度初检查所有父表的premake值估算未来三个月的写入量提前手动调大premake或者手动补建分区别等系统报错才救火。第四备份恢复时一定要把partman schema一起备份。有用pg_dump备份时很多人只备份业务库的表结果新建的实例里没有partman扩展恢复的业务分区表就成了孤儿。我现在的备份脚本里明确要求pg_dump附带schema partman和相关函数恢复时先CREATE EXTENSION再恢复业务表。最后分享一个我一直在用的小技巧新建父表时顺手检查一下partman.part_config里的automatic_maintenance字段是否为true。这个字段虽然不常修改但一旦它被误改为false后台进程就会自动跳过这张表表现就是“配置明明存在却一直不建新分区”。每次巡检先看这个字段能从根上排除很多伪故障。这几次在CentOS 7上从零配置pg_partman的项目花的时间最多的其实不是安装而是理解“自动化”这件事里隐藏的各种边界。建表语句谁都能写真正值钱的是你对业务写入节奏、分区粒度、保留周期的判断以及遇到问题时能顺着配置表和日志快速定位的能力。把这个工具用熟了再看那些动辄几百GB的大表心里就有底了。 CentOS 7上PostgreSQL分区管理神器pg_partman安装配置实践
1. 先把pg_partman这玩意儿说清楚最近在CentOS 7上给一套业务库做数据生命周期治理最核心的一件事就是把几张上亿行的流水表切成按时间分区。刚开始我准备纯手工写触发器、按月建表、再定期删旧表搞到一半觉得太痛苦了。然后同事丢过来一个词pg_partman。简单说这是一个 PostgreSQL 分区管理扩展专门干“自动建子表、自动维护约束、按保留策略清理过期分区”这些脏活累活。这个工具适合谁用主要是三类人一是像我一样要管大表、又不想天天盯维护任务的DBA二是后端开发业务表数据量涨得快但又不想把分区分区逻辑写死在业务代码里三是数据运维需要做冷热数据分离、归档清理的场景。理解起来也不难你可以把pg_partman理解成一个“分区表管家”你告诉它“以后每周帮我建一个新分区保留最近三个月的数据”剩下的它定时自动执行。需要提前说明的是pg_partman不是独立服务它是PostgreSQL的一个扩展跑在数据库内部靠后台工作进程background worker或者外部定时任务调用维护函数。所以它本身不增加额外的运维复杂度只要装好、配置好它就安安静静在库里面干活。下文中我会把CentOS 7上的安装、建分区、自动化维护、排障经验都过一遍全程都是我在实际环境里验证过的操作。2. 安装前的环境准备CentOS 7侧的老规矩2.1 系统与数据库版本选型先说你必须在装之前想清楚的一件事pg_partman的版本和PostgreSQL版本必须匹配。我这边测试环境是CentOS 7.9内核3.10数据库用的是PostgreSQL 12pg_partman用的是4.7.x版本这套组合很稳。如果你还在用PG 9.6或者10建议用pg_partman 4.5.x左右的版本如果是PG 13以上尽量用最新release包。总之先查清楚你的数据库版本再选pg_partman版本顺序不要反过来。另外CentOS 7默认的PostgreSQL版本比较旧如果你是用yum直接装的多半是9.2这种老古董那就没必要折腾pg_partman了。我建议要么用PostgreSQL官方yum源装新版要么用源码编译。既然标题里提到CentOS 7说明很多人的环境是内网、离线的yum源和依赖包都得早做准备。2.2 编译工具链与PostgreSQL开发包pg_partman的安装方式有两种一种是用包管理器直接装比如PostgreSQL官方源里可能带了postgresql12-partman这样的包另一种是源码编译这也是最通用、最容易在离线环境复现的方式。源码编译的前提是PostgreSQL的开发头文件必须齐全也就是postgresql-devel或者postgresqlXX-devel这个包。在CentOS 7上我一般这样检查环境rpm -qa | grep postgresql pg_config --version which pg_config如果pg_config命令找不到说明开发包没装全。用官方yum源的话安装命令类似yum install -y postgresql12-devel postgresql12-server postgresql12-contrib确认pg_config可用之后编译工具链还差gcc、make这两个直接yum装yum install -y gcc make注意如果是内网离线环境建议在一台能联网的同版本机器上把依赖包通过yum downloadonly拉下来或者准备好rpm包。我吃过一次亏内网机器编译到一半才发现缺perl模块整个人都裂开了。2.3 磁盘空间与分区挂载检查这个坑属于“不撞一次不会记住”的那种。pg_partman是按时间自动创建子分区的每次run_maintenance()跑完都可能新增好几张子表每张子表如果数据量很大一段时间的磁盘增长会非常快。我在一台测试机上只建了3张按月分区表premake设为4结果一个季度下来数据目录多了将近200GB直接把根分区撑满了。所以装之前务必检查一下数据目录的挂载和剩余空间df -h /var/lib/pgsql du -sh /var/lib/pgsql/*如果你根目录空间不大建议提前把PostgreSQL数据目录迁移到大分区或者做逻辑卷扩容。热词里提到的“根目录扩容”就是这种典型场景别再等告警了才去扩。2.4 角色与权限规划安装扩展本身通常用超级用户postgres操作但业务运行时不建议让业务账号持有超级权限。pg_partman的官方文档里给了专门的授权方案核心是先建一个专门账号CREATE ROLE partman_user WITH LOGIN; 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;跑维护任务的定时任务用这个专用账号来连库就行。我自己的习惯是建表、初始化用postgres定时任务用partman_user业务账号只读写业务表权限给最小集。这样即使某天定时任务配置出了问题影响面也可控。3. pg_partman安装实操源码编译与配置3.1 下载源码与解压源码获取直接从GitHub拉release包就行不需要git clone整个仓库。比如4.7.1版本cd /usr/local/src wget https://github.com/pgpartman/pg_partman/archive/refs/tags/v4.7.1.tar.gz tar -zxvf v4.7.1.tar.gz cd pg_partman-4.7.1如果你的环境完全离线那就找一台能联网的机器下好tar包再想办法拷进去。GitHub有时候连起来很慢也可以先用国内镜像加速下载这个看个人习惯。3.2 configure、make、make install 全流程pg_partman的编译流程和普通PostgreSQL扩展一样核心是让pg_config指向正确的版本。这里有个最容易踩的坑如果你系统里装了多个PostgreSQL版本pg_config可能指向旧版编译出来的扩展就没法加载。我的做法是在configure前显式指定路径export PATH/usr/pgsql-12/bin:$PATH export PGPORT5432 ./configure make make installmake install会把扩展文件放到PostgreSQL的lib和share/extension目录下。装完之后你可以用pg_config --pkglibdir确认一下路径对不对也可以直接去扩展目录里看有没有pg_partman.controlls -l $(pg_config --sharedir)/extension/pg_partman* ls -l $(pg_config --pkglibdir)/pg_partman*看到这两个文件都在说明扩展文件已经就位。编译过程中最常见的报错是“pg_confignot found”或者版本不匹配遇到之后优先检查PATH。经验之谈编译前一定先把PGPORT和PGDATA环境变量理清楚。我见过有人configure的时候一切正常make也通过但装完库后CREATE EXTENSION时报版本不对最后定位到是系统里残留了旧版pg_config特别浪费时间。3.3 在数据库中创建扩展文件装好了下一步就是登进数据库创建扩展。这里有一个建议给pg_partman建一个独立的schema不要丢在public里方便后续权限管理。CREATE SCHEMA partman; CREATE EXTENSION pg_partman SCHEMA partman;创建完之后可以通过两个方式验证是否正常SELECT extname, extversion FROM pg_extension WHERE extname pg_partman; \dx pg_partman如果你建扩展时报错找不到pg_partman.control那说明make install没有成功或者安装到了别的实例目录。另外注意如果数据库是旧版本升级上来的扩展安装可能需要额外处理依赖我会在第5部分展开讲。3.4 开启后台工作进程pg_partman有两种跑维护任务的方式一种靠外部定时任务调用run_maintenance()函数另一种是靠它自带的background worker进程在数据库内部定时自动跑。用后台进程的方式更省心但需要修改postgresql.conf并重启实例。我推荐直接用后台进程配置很简单shared_preload_libraries pg_partman_bgw然后在postgresql.conf里再加上几个参数pg_partman_bgw.interval 3600 pg_partman_bgw.role postgres pg_partman_bgw.dbname yourdbinterval默认是3600秒也就是每小时检查一次。如果你希望更频繁可以调到1800但一般业务一小时跑一次足够了。改完之后必须重启PostgreSQL进程systemctl restart postgresql-12重要shared_preload_libraries里同时要加载其他扩展的话用逗号分隔类比一下就像Linux系统的开机自启服务列表加载顺序和写法都得注意。pg_partman_bgw的排序放在最后一般更稳妥。重启之后查看日志确认后台进程是否真的起来了tail -f /var/log/postgresql/*.log如果能看到类似pg_partman_bgw: pg_partman background worker started之类的日志说明一切正常。如果没见到大概率是参数名写错了或者角色名不对优先排查这两点。3.5 验证后台进程是否工作光看日志还不行我一般还会直接往配置表里插一条测试数据。比如查询partman.part_config表SELECT * FROM partman.part_config;如果查询正常没有权限报错说明扩展已经可用。接下来进入核心环节建第一张由pg_partman管理的时间分区表。4. 核心配置从创建第一张分区表开始4.1 先设计分区策略别急着敲命令很多人拿到pg_partman就先建父表然后一股脑地调create_parent结果后面发现分区粒度不对又得推倒重来。我的建议是先回答三个问题数据按哪个字段分区单分区数据量多大才合适保留多少分区够用先说字段选择。时间分区一般选一个不会为NULL、且写入后基本不变的时间字段比如订单创建时间created_at。如果表里有id这种自增字段也可以按序列区间分区但绝大多数业务场景还是按时间最直观。单分区数据量这个事没有一个绝对标准。我一般这样粗算一张子表的数据量控制在千万级以内单表文件大小在10GB左右比较合适。如果每天写入量是50GB那就按小时分区或者按天分区如果每天只有几百MB按周或者按月分区就行。分区粒度太细会导致子表数量膨胀维护时要遍历的分区太多DELETE和VACUUM都有压力粒度太粗又会让大分区失去拆分意义。再一个容易被忽略的点时区。你的应用服务器、数据库服务器、业务JavaScript前端如果处于不同时区时间字段的写入值可能有偏差分区边界对不上偶发数据漏进不该进的分区。最好在项目初始化时就统一约定表结构里存统一的时间戳如UTC展示层再转换时区。4.2 创建父表与索引模板分区表的结构设计比较讲究。父表本身在PG里是一个“空壳”所有数据都实际存在子分区里所以索引如果在父表上建子表会自动继承相同的索引结构。这块建议先建父表再统一建索引不要在每张子表上单独手动加索引那样维护量会爆炸。CREATE TABLE public.audit_log ( id bigserial, occurred_at timestamptz NOT NULL, event_type text, detail jsonb, PRIMARY KEY (id, occurred_at) ) PARTITION BY RANGE (occurred_at);注意如果你的数据库版本是12可以直接用原生分区语法如果是PG 10以前的老版本只能走传统继承分区PARTITION BY RANGE语法不支持。pg_partman两种模式都兼容原生分区模式下它会用PG自己的分区机制来创建子表继承模式下它会自己维护继承关系。我用的是PG 12所以上面直接写了原生分区语法。索引模板上有一个重点主键或唯一索引需要包含分区字段。因为PostgreSQL原生分区要求分区键必须包含在主键中否则无法保证全局唯一性。这也是很多新手第一次跑create_parent报错的原因。所以上面我把主键设计成(id, occurred_at)这不是随手写的。4.3 调用create_parent初始化分区父表建好之后调用create_parent函数这一步是pg_partman真正开始接管这张表的标志。常用参数如下SELECT partman.create_parent( p_parent_table : public.audit_log, p_control : occurred_at, p_interval : 1 day, p_premake : 4, p_start_partition : 2025-01-01 00:00:0000 );p_parent_table父表名注意要带schema。p_control分区字段就是你要按哪个字段切分。p_interval分区间隔支持1 day、1 week、1 month等。p_premake提前创建的子分区数量。设成4意味着从现在起后面4个周期分区会提前准备好。p_start_partition起始分区时间给一个你业务数据开始的时间。从工程角度讲p_premake不建议设太大4到6比较常见。设太大会提前创建很多空子分区不仅浪费空间还会让partman.part_config表的扫描变慢后续每次维护都要多处理很多空表。4.4 约束、索引与默认分区第一次执行create_parent之后你可能会发现表里多了好几个子分区比如audit_log_p2025_01_01、audit_log_p2025_01_02之类。pg_partman的命名格式是基于时间的可以自己查一下SELECT child_table, partition_interval, retention FROM partman.part_config;子分区的约束是pg_partman自动加的它会根据字段值判断这条数据属于哪个分区。这里我要强调一个容易踩的坑不要自己手动去DROP或ALTER这些约束否则run_maintenance()在后续运行时会判断这个分区不合法然后尝试重建报出一堆奇怪的错误。如果你真想删除某个分区应该用drop_partition或者直接DROP TABLE而不是去动约束。还有默认分区的问题。原生分区模式下PG 11以后支持DEFAULT分区。如果你的业务偶尔会写入超出所有子分区范围的数据比如premake没覆盖到的时间点却又没有默认分区PostgreSQL会直接报错。我的建议是加上一个默认分区兜底CREATE TABLE public.audit_log_default PARTITION OF public.audit_log DEFAULT;这样即使维护任务偶尔没跑数据也能写进来不至于线上报错。但默认分区相当于一个“杂物间”里面的数据不会自动清理时间久了容易堆积。所以我会在巡检时专门看一眼默认分区是否在不断增长如果持续增长说明维护任务可能出问题了。4.5 常见误区别在线上大表上直接建分区如果这张表已经是线上跑了好几个月的大表里面已经有海量数据你想直接PARTITION BY RANGE重建它原生分区语法要求建表时指定分区键已存在的普通表是不能直接转换的。pg_partman官方倒是提供了一个方式在create_parent时通过p_initialize : init参数初始化把已有数据搬到分区里去。但我给你的建议是大表迁移一定先在测试环境演练估算一下要迁移多少数据量预计多久窗口完事同时准备好备库或回滚手段。不要天真地以为在美滋滋跑一句SQL就能搞定几亿行的数据迁移轻则锁表重则把库拖垮。我踩过最疼的一次坑迁移一张3亿行的大表实际的初始化把整个库的负载打到了90%以上业务侧告警满天飞。5. 自动化维护与数据生命周期管理5.1 run_maintenance内部到底在干什么pg_partman的维护核心就是run_maintenance()函数。这个函数被后台进程或者定时任务周期性调用每次运行它都会做这几件事检查每张被管理的父表根据当前时间和premake值计算需要提前创建的子分区调用底层的建表逻辑创建缺失的子分区根据retention设置清理过期子分区。所以实际上维护任务做得勤不勤决定了“分区表有没有窗口期没有可用分区”。只要premake够大即使维护任务晚跑一两个小时新数据也能落到已经建好的下几个分区里不会报错。这也是为什么说premake参数是保险丝。SELECT partman.run_maintenance(p_analyze : true);p_analyze : true表示维护完顺手对新分区做ANALYZE更新统计信息。第一次跑维护或者刚完成数据迁移后建议手动执行一次确认没有报错再交给后台进程。5.2 保留策略retention设置与自动清理很多用pg_partman的人其实不是为了自动建分区而是为了自动删分区。一张大表如果手动DELETE数据会产生大量死元组且不能释放空间VACUUM全表还会一直锁表。但按分区DROP TABLE则几乎瞬间完成且空间立刻回收。这就是pg_partman清理方案的核心优势。在partman.part_config表里有几个关键字段retention保留多少周期的数据。retention_keep_table若为true则保留表比如归档表只是不做自动DROP。retention_keep_index是否保留索引。设置保留策略有两种方式。一种是在建表时直接指定但更常见的是维护时动态修改UPDATE partman.part_config SET retention 90 days, retention_keep_table false, retention_keep_index false WHERE parent_table public.audit_log;这里再强调一遍retention的单位需要和partition_interval匹配。如果你分区间隔是1 dayretention 90 days就是保留90个分区如果分区间隔是1 week那90 days在源码里会被换算成多少个周容易产生歧义。所以最稳的写法是把retention设成和分区间隔一致的格式比如12 weeks或者3 months。5.3 定时任务选型后台进程还是cronpg_partman官方推荐方式是使用后台进程但我自己实际用下来在不同的环境里各有优劣。如果库数量少、表数量也少后台进程是最省事的因为不需要额外部署crontab也不需要担心过期任务没清理。但有个问题后台进程默认每小时跑一次如果你的业务表很多比如几十张父表一次维护可能要扫很久那么维护周期内新增的分区请求可能不会被及时处理。这时候可以调低pg_partman_bgw.interval参数或者改用外部crontab更灵活地控制执行频率。如果分时段执行更贴合你的业务比如凌晨2点集中跑一次那cron更合适0 2 * * * psql -U postgres -d yourdb -c SELECT partman.run_maintenance(p_analyze : true);不过用cron要注意一点脚本要设置好PGPASSWORD或者用.pgpass文件否则密码交互会让你执行失败。另外cron环境变量很少postgresql的bin目录要写全路径我在生产上干脆直接写/usr/pgsql-12/bin/psql。我个人的最终选择是线上库数量多用后台进程统一管关键的几张核心大表另外加了cron做凌晨的集中数据归档与分区整理。两条腿走路比单一机制稳得多。5.4 批量迁移历史数据partition_data_proc的使用如果你不是从零开始建分区表而是想把一张普通大表的历史数据按时间搬到新的分区结构里去用create_parent初始化是一个选择但更细粒度的控制方式是partition_data_proc函数。这个函数可以分批把指定范围内的数据从主表移动到子分区。SELECT partman.partition_data_proc( p_parent_table : public.audit_log, p_interval : 1 day, p_batch : 10000, p_delay : 0.5 );p_batch是每批迁移行数p_delay是批与批之间的休眠秒数用来给主库缓解负载。实际生产中我建议p_batch取5000以内p_delay至少0.2秒不要贪快。迁移过程中不断有新的业务数据写入时就需要配合锁表和业务停写窗口否则会出现数据漏迁。跑完批量迁移之后原来的父表里可能还有残留数据用下面的方式检查SELECT count(*) FROM ONLY public.audit_log;如果还有残留继续跑partition_data_proc直到结果为0。最后别忘了CLUSTER或VACUUM FULL释放空间。5.5 停止维护与回滚stop_maintenance和undo_partition有开始就得有回退方案。如果你要下线一张分区表或者需要把数据重新从分区结构合并回普通表pg_partman提供了undo_partition函数。它的用法是从最旧的分区开始把数据搬回父表然后删除子分区。SELECT partman.stop_maintenance(p_parent_table : public.audit_log); SELECT partman.undo_partition(p_parent_table : public.audit_log, p_batch : 10000);stop_maintenance只是把该表的自动维护停掉配置还在。undo_partition执行真正的数据回迁。要注意undo_partition是一次性把全部分区数据合并回去所以执行前一定要评估总数据量准备好足够的磁盘空间和停机窗口。我在一台测试库上做过一次大约200GB的回迁跑了将近6个小时中间不敢停停了就得重新来。这种操作基本属于“非必要不执行”平时把配置想清楚比事后回滚重要得多。6. 常见问题与排查技巧实录6.1 非超级用户想要维护分区时的授权问题run_maintenance()函数默认需要超级用户权限但实际生产环境不可能给定时任务用postgres超管。pg_partman从4.x开始提供了给普通角色授权的方式。除了我在2.4节提到的基础授权外你还需要把维护函数执行权限给出去GRANT USAGE ON SCHEMA partman TO partman_user; GRANT EXECUTE ON FUNCTION partman.run_maintenance(text, boolean, boolean, boolean) TO partman_user; GRANT EXECUTE ON FUNCTION partman.create_parent(text, text, text, text, int, text, boolean) TO partman_user;同时还需要让partman_user对父表和子分区所在的schema有建表权限。不然后台进程生成了建表语句却执行不了日志里全是权限错误。6.2 分区维护不生效排查思路遇到“分区没有按预期创建”或者“旧数据没被清理”我有一套固定的排查路径。先看配置表SELECT * FROM partman.part_config WHERE parent_table public.audit_log;确认partition_interval、premake、retention是否合理。如果配置没问题手动跑一次维护函数SELECT partman.run_maintenance(p_analyze : false);然后立刻查分区列表和日志。90%的情况都可以在这里定位到问题。如果手动跑也报错那就看报错信息。最常见的是时间字段类型不对比如p_control字段是timestamp without time zone而分区建表语句里用了timestamptz两个类型比较时出现转换错误。解决办法是建表时统一用timestamptz极少情况下需要改字段类型。另一个常见问题后台进程没有真正启动。很多人改完shared_preload_libraries重启数据库后发现日志里根本没有bgw启动记录。这时请检查pg_partman_bgw.dbname参数是否写对它必须匹配你连的数据库名不能是逗号分隔的多库名。官方文档里说支持逗号分隔多库但实际配置中我建议一库一配出了问题好排查。6.3 与已有触发器或外键的冲突分区表有外键时问题比较头疼。PostgreSQL原生分区表在PG 12之前不允许在父表上定义外键PG 12之后有所放宽但分区键仍然不能带上外键关联的字段。也就是说如果audit_log里有一个外键指向users你把它作为分区键直接就会报错。我的建议是分区表尽量不建外键由应用层保证数据完整性。触发器方面pg_partman自己要求分区表不能有UNLOGGED之类的特殊属性冲突。如果你在父表上建了BEFORE INSERT触发器时间字段如果是由触发器在写入时填充的那p_control字段必须在触发器填充完之后才做分区路由。也就是说插入时分区判定可能看不到实际上要写入的分区键导致数据进错分区。遇到这种情况要么重写触发器逻辑要么确保分区键由应用传值别依赖触发器改。6.4 分区清理后空间没释放怎么办自动删了子分区表空间文件理论上应该释放。但如果你用了DROP TABLE磁盘空间会立即回收如果走的是TRUNCATE或者普通DELETE则需要VACUUM FULL才能把空间还给操作系统。pg_partman默认的子分区删除方式是DROP TABLE所以正常情况不会出现空间不释放的问题。我踩过的坑是手动清理过子分区用的是TRUNCATE而不是DROP结果PostgreSQL数据目录里的文件大小没变。后来才反应过来TRUNCATE只是清空数据不释放文件。建议所有清理操作都优先用drop_partition函数或者直接DROP TABLE不要自己写DELETE或TRUNCATE。6.5 pg_partman版本升级注意事项升级pg_partman之前需要看官方文档里的升级路径。最稳的顺序是备份partmanschema下的所有配置数据。停止后台进程可以先注释掉shared_preload_libraries并重启实例。用新版本的源码重新make install。在数据库中执行ALTER EXTENSION pg_partman UPDATE TO 4.7.1;。恢复配置参数重启实例确认bgw起来了。很多人升级直接替换二进制文件却不更新扩展这一步执行到一半就会报版本不匹配。另外升级完别忘了重新授权因为新版本可能会新增函数和表权限不会自动继承。6.6 我从实际使用里提炼的几个经验第一模板表机制值得用起来。pg_partman支持把一张模板表上的列完整复刻到新子分区包括字段注释、约束、默认值。建好模板表后在create_parent时传入p_template_table参数即可。这样以后字段调整只改模板表和父表不需要去逐个改几十张子表省了很多事。第二日志别开太全。pg_partman的日志字段里有run_maintenance的详细输出但这个函数在debug模式下会打印很长生产上如果开了DEBUG会刷爆日志文件。我一般把log_min_messages设为warning只在排障时临时调到debug。第三premake和retention要配合业务巡检周期。有些业务有季度大促数据量是平时的几倍如果premake只设了4大促前忘了测到了周末发现分区表没有下一周的分区写入直接失败就是事故了。我现在养成的习惯是每季度初检查所有父表的premake值估算未来三个月的写入量提前手动调大premake或者手动补建分区别等系统报错才救火。第四备份恢复时一定要把partman schema一起备份。有用pg_dump备份时很多人只备份业务库的表结果新建的实例里没有partman扩展恢复的业务分区表就成了孤儿。我现在的备份脚本里明确要求pg_dump附带schema partman和相关函数恢复时先CREATE EXTENSION再恢复业务表。最后分享一个我一直在用的小技巧新建父表时顺手检查一下partman.part_config里的automatic_maintenance字段是否为true。这个字段虽然不常修改但一旦它被误改为false后台进程就会自动跳过这张表表现就是“配置明明存在却一直不建新分区”。每次巡检先看这个字段能从根上排除很多伪故障。这几次在CentOS 7上从零配置pg_partman的项目花的时间最多的其实不是安装而是理解“自动化”这件事里隐藏的各种边界。建表语句谁都能写真正值钱的是你对业务写入节奏、分区粒度、保留周期的判断以及遇到问题时能顺着配置表和日志快速定位的能力。把这个工具用熟了再看那些动辄几百GB的大表心里就有底了。