logicwind/postgres-partman-cron

PostgreSQL集成pg_partman与pg_cron镜像文档

镜像概述

本镜像基于官方PostgreSQL镜像构建，预集成了pg_partman和pg_cron两个扩展。pg_partman是PostgreSQL的表分区管理工具，支持自动创建和维护时间或数值分区；pg_cron是PostgreSQL的定时任务调度扩展，允许在数据库内部直接调度SQL任务。本镜像旨在为用户提供开箱即用的分区管理和定时任务能力，减少手动配置工作。

核心功能与特性

pg_partman特性

• 支持时间分区（按年、月、日、小时等）和数值分区
• 自动创建新分区、删除旧分区
• 支持分区表的批量维护操作
• 兼容PostgreSQL标准分区功能

pg_cron特性

• 类似Linux cron的语法，支持定时执行SQL命令
• 任务存储在数据库中，支持动态管理
• 支持按分钟、小时、日、月、周等周期调度
• 可指定任务执行用户和目标数据库

使用场景

• 需管理大量时序数据（如日志、监控指标）的数据库环境
• 需要定期执行数据清理、统计分析、报表生成的业务系统
• 需自动化管理分区表生命周期的场景
• 希望在数据库层直接实现定时任务，减少外部调度依赖的应用

使用方法与配置说明

基础使用（docker run）

bash
docker run -d \
  --name postgres-partman-cron \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=mydb \
  -e CRON_DATABASE_NAME=mydb \  # 指定pg_cron任务存储的数据库
  -p 5432:5432 \
  [镜像名称]:[标签]

Docker Compose配置

yaml
version: '3'
services:
  postgres:
    image: [镜像名称]:[标签]
    container_name: postgres-partman-cron
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: mydb
      CRON_DATABASE_NAME: mydb  # 可选，默认使用POSTGRES_DB
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
volumes:
  postgres_data:

环境变量说明

扩展启用与验证

容器启动后，可通过以下步骤验证扩展：

1. 连接数据库：

bash
psql -h localhost -U myuser -d mydb

2. 验证pg_partman：

sql
SELECT * FROM pg_extension WHERE extname = 'pg_partman';

3. 验证pg_cron：

sql
SELECT * FROM pg_extension WHERE extname = 'pg_cron';

基本使用示例

创建分区表（pg_partman）

sql
-- 创建父表
CREATE TABLE measurements (
    id SERIAL,
    log_time TIMESTAMPTZ NOT NULL,
    value NUMERIC NOT NULL
);

-- 使用pg_partman创建时间分区（按天）
SELECT partman.create_parent(
    p_parent_table => 'public.measurements',
    p_control => 'log_time',
    p_type => 'time',
    p_interval => '1 day',
    p_premake => 3  -- 预创建3个未来分区
);

创建定时任务（pg_cron）

sql
-- 每天凌晨2点清理30天前的分区数据
SELECT cron.schedule(
    'clean_old_partitions',
    '0 2 * * *',
    $SELECT partman.drop_old_partitions('public.measurements', p_retention := '30 days')$
);

-- 查看所有定时任务
SELECT * FROM cron.job;

注意事项

• 首次使用pg_partman需确保数据库用户具有足够权限（建议使用超级用户）
• pg_cron任务存储在指定数据库的cron模式下，需确保该数据库存在
• 分区表维护和定时任务执行会消耗数据库资源，建议根据实际负载调整调度频率
• 生产环境建议挂载数据卷以持久化存储数据