benastsec/postgres-pg-cron

PostgreSQL with pg-cron 镜像文档

概述

本镜像基于官方PostgreSQL数据库镜像构建，预安装并配置了pg-cron扩展。pg-cron是PostgreSQL的轻量级定时任务扩展，允许用户直接在数据库中定义、调度和管理定时执行的SQL任务，无需依赖外部调度工具（如crontab、Airflow），适用于需要数据库级定时操作的场景。

核心功能与特性

• 预安装pg-cron扩展：无需手动编译或安装，镜像启动后即可使用pg-cron功能
• 标准cron语法支持：采用与系统crontab兼容的时间表达式（如* * * * *）定义任务周期
• 数据库内任务管理：通过SQL函数直接创建、修改、删除定时任务，支持任务执行日志记录
• 灵活的任务类型：支持定时执行SQL语句、存储过程或自定义函数
• 权限控制：遵循PostgreSQL权限模型，仅超级用户或授权用户可管理任务
• 与PostgreSQL原生兼容：保留官方PostgreSQL镜像的所有功能和配置选项

适用场景

• 数据定期维护：定时清理过期数据、归档历史记录、重建索引
• 自动化报表生成：按日/周/月生成业务统计报表并存储到结果表
• 周期性数据同步：定时同步不同数据库表或跨库数据
• 定时触发业务逻辑：执行基于时间的业务规则（如每月账单生成）
• 数据库监控与告警：定时检查数据库状态并触发告警（如磁盘空间不足提醒）

使用方法

快速启动（Docker Run）

通过以下命令快速启动包含pg-cron的PostgreSQL容器：

bash
docker run -d \
  --name postgres-pg-cron \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v postgres-data:/var/lib/postgresql/data \
  docker.xuanyuan.run/your-image-name:tag

注意：需将your-image-name:tag替换为实际镜像名称和标签

Docker Compose 配置

创建docker-compose.yml文件进行更灵活的配置：

yaml
version: '3.8'
services:
  postgres-pg-cron:
    image: docker.xuanyuan.run/your-image-name:tag
    container_name: postgres-pg-cron
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: mydb
      # 可选：设置时区（影响cron任务时间计算）
      TZ: Asia/Shanghai
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data
      # 可选：挂载自定义postgresql.conf（如需调整pg-cron配置）
      # - ./postgresql.conf:/etc/postgresql/postgresql.conf
    restart: unless-stopped

volumes:
  postgres-data:

环境变量

支持官方PostgreSQL镜像的所有环境变量，核心变量说明：

配置pg-cron

pg-cron默认已通过shared_preload_libraries启用，无需额外配置。如需自定义pg-cron参数（如任务日志级别），可通过挂载postgresql.conf文件调整：

ini
# postgresql.conf 中pg-cron相关配置（可选）
shared_preload_libraries = 'pg_cron'  # 已默认配置，无需修改
cron.log_statement = 'on'             # 记录任务执行日志（可选，默认'off'）
cron.max_running_jobs = 10            # 最大并发任务数（默认5）

创建定时任务示例

连接数据库后，通过cron.schedule函数创建定时任务，语法如下：

sql
-- 语法：cron.schedule(job_name, cron_expression, sql_statement)
-- 示例1：每分钟执行一次数据清理
SELECT cron.schedule(
  'clean-expired-data',  -- 任务名称
  '* * * * *',           -- 每分钟执行（cron表达式）
  'DELETE FROM logs WHERE created_at < NOW() - INTERVAL ''7 days'''  -- 执行的SQL
);

-- 示例2：每天凌晨3点生成日报表
SELECT cron.schedule(
  'daily-report',
  '0 3 * * *',  -- 每天3:00执行
  'CALL generate_daily_report()'  -- 执行存储过程
);

管理定时任务

• 查看所有任务：

  sql
  SELECT * FROM cron.job;
  

• 修改任务周期：

  sql
  SELECT cron.alter_job('clean-expired-data', schedule => '0 */6 * * *');  -- 每6小时执行
  

• 删除任务：

  sql
  SELECT cron.unschedule('clean-expired-data');
  

持久化数据

通过挂载卷（如示例中的-v postgres-data:/var/lib/postgresql/data）确保数据库数据和任务配置在容器重启后不丢失。

注意事项

• pg-cron任务存储在cron模式下，容器重启后任务会自动恢复
• 仅超级用户（如POSTGRES_USER指定的用户）可创建/管理任务，普通用户需通过GRANT授权
• cron表达式时区默认与容器时区（TZ环境变量）一致，建议显式设置时区避免时间偏差
• 任务执行结果可通过cron.job_run_details表查看：

  sql
  SELECT * FROM cron.job_run_details WHERE jobid = (SELECT jobid FROM cron.job WHERE jobname = 'clean-expired-data') ORDER BY start_time DESC;