如何在postgres中安装使用pg_cron插件

pg_cron

使用pg的pg_cron插件做定时任务时，

• 用 SQL 创建、管理、查询定时任务，非常直观。
• pg_cron 运行在 PostgreSQL 的后台进程（worker）中，不会依赖外部服务
• 带有失败重试的机制

因为pg_cron跑在postgresql服务器本身，如果任务过重可能会占用数据库资源，小心设计。

应用场景

• 定时清理历史数据（比如保留 90 天以内的数据）
• 定时统计报表（INSERT/UPDATE到报表表）
• 复杂作业流的触发（比如每天凌晨批量处理某些数据）

创建流程

1. 创建 pg_cron 扩展

   CREATE EXTENSION pg_cron;
   

2. 创建一个定时任务

   示例一：假设我要每天凌晨2点自动删除7天前的日志：

   SELECT cron.schedule(
     '0 2 * * *',
     $$DELETE FROM app_logs WHERE created_at < NOW() - INTERVAL '7 days';$$
   );
   

   每天 02:00 运行，自动清理 app_logs 表中过期的数据。

   示例二：创建一个三分钟执行一次的定时任务

   SELECT cron.schedule(
     '*/3 * * * *',
     $$REFRESH MATERIALIZED VIEW filter_options;$$
   );
   

   • '*/3 * * * *' 代表 每3分钟执行一次。
   • $$...$$ 包住 SQL，防止引号冲突。
   • cron.schedule() 返回一个 job_id，可以用来管理这个定时任务。

3. 查询定时任务

   SELECT * FROM cron.job;
   

4. 删除任务

   SELECT cron.unschedule(job_id);
   

pg_cron工作流程图

Dockerfile

镜像制作Dockerfile，制作时需从github上下载 timescaledb以及pg_cron的源代码

FROM postgres:14-bullseye

RUN apt-get update && \
    apt-get install -y \
        build-essential \
        cmake \
        postgresql-server-dev-14 \
        git \
        curl \
        ca-certificates && \
    rm -rf /var/lib/apt/lists/*

COPY timescaledb-2.18.2 /tmp/timescaledb
RUN apt-get update && \
    apt-get install -y gnupg curl ca-certificates && \
    curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
RUN apt-get update && \
    apt-get install -y libkrb5-dev

# 安装 TimescaleDB
RUN cd /tmp/timescaledb && \
    ./bootstrap && \
    cd build && \
    make && make install && \
    cd / && rm -rf /tmp/timescaledb

# 安装 pg_cron
COPY pg_cron /tmp/pg_cron
RUN cd /tmp/pg_cron && \
    make && make install && \
    cd / && rm -rf /tmp/pg_cron

# 配置 shared_preload_libraries
RUN echo "shared_preload_libraries = 'timescaledb,pg_cron'" >> /var/lib/postgresql/data/postgresql.conf