pg_cron - PostgreSQL 定时任务扩展
pg_cron 是一个 PostgreSQL 插件，它提供了一个内置的作业调度器，可以让你在 PostgreSQL 内部定时执行 SQL 查询和其他任务。pg_cron 插件使得 PostgreSQL 能够像 Linux 系统中的 cron 一样定期执行作业。它支持定时运行 SQL 查询，并在数据库内调度任务，非常适用于自动化数据库维护、清理任务和数据导入等操作。
一、安装与启用
1. 准备环境
在安装 pg_cron 插件之前，请确保 PostgreSQL 已经正确安装并运行在你的系统中，并且你具有安装扩展所需的权限。此外，确保你系统中安装了 make、gcc 和 PostgreSQL 开发工具包（例如 postgresql-devel 或 postgresql-server-dev-<version>）。
2. 安装 PostgreSQL 开发工具包（如果未安装）
在基于 Debian 或 Ubuntu 的系统上：
sudo apt-get update
sudo apt-get install postgresql-server-dev-all build-essential
在基于 RedHat 或 CentOS 的系统上：
sudo yum install postgresql-devel gcc make
3. 下载 pg_cron 插件源码
pg_cron 插件的源码托管在 GitHub 上，可以通过以下步骤下载源代码：
-
访问 GitHub 上
pg_cron插件的仓库页面 -
克隆或下载源代码：
- 如果你使用 Git，可以通过以下命令克隆源代码：
git clone https://github.com/citusdata/pg_cron.git- 如果你不使用 Git，你可以直接从 GitHub 页面下载源代码的
.zip文件并解压。
-
进入
pg_cron目录：cd pg_cron
4. 编译和安装插件
pg_cron 插件的安装和编译过程类似于 PostgreSQL 的其他扩展。你需要先进行编译，然后将插件安装到 PostgreSQL 的扩展目录中。
编译插件
在 pg_cron 目录下，执行以下命令进行编译：
make
该命令将编译 pg_cron 插件的源代码。如果一切正常，编译过程会生成所需的二进制文件。
安装插件
编译完成后，通过以下命令将插件安装到 PostgreSQL 的扩展目录中：
sudo make install
安装过程会将插件的共享库复制到 PostgreSQL 配置的扩展目录中。
5. 配置 PostgreSQL 启用 pg_cron
安装完成后，你需要配置 PostgreSQL 使其加载 pg_cron 插件。
修改 postgresql.conf 配置文件
找到 PostgreSQL 的配置文件 postgresql.conf，并将 shared_preload_libraries 参数设置为 pg_cron，以便在 PostgreSQL 启动时自动加载 pg_cron 插件。
conf
复制代码
shared_preload_libraries = 'pg_cron'
如果此项配置已存在，确保在其值中加入 pg_cron。如果没有此项配置，添加它并保存文件。
重启 PostgreSQL 服务
修改配置文件后，重新启动 PostgreSQL 服务使配置生效：
在基于 systemd 的 Linux 系统上：
sudo systemctl restart postgresql
或者使用 pg_ctl 命令：
pg_ctl restart -D /path/to/your/data/directory
创建 pg_cron 扩展
在 PostgreSQL 中启用 pg_cron 插件之前，你需要创建扩展。连接到数据库，并运行以下 SQL 命令：
CREATE EXTENSION pg_cron;
这将会在当前数据库中安装 pg_cron 插件。
6. 验证安装
安装完成后，可以通过以下 SQL 命令验证 pg_cron 插件是否已成功安装：
sql
复制代码
SELECT * FROM pg_extension WHERE extname = 'pg_cron';
如果插件已安装并启用，你应该能够看到类似以下的输出：
plaintext复制代码 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------------+----------+--------------+----------------+------------+-----------+--------------
pg_cron | 16384 | 2200 | f | 1.3 | |
此时，pg_cron 插件已经成功安装并启用。
二、Cron 表达式
pg_cron 使用类似于传统 Unix cron 系统的表达式来指定任务的执行时间。Cron 表达式由五个字段组成，分别表示分钟、小时、日、月和星期，每个字段可以使用以下值：
*：表示每一分钟、每一小时、每一天、每一月或每一星期。,：用于分隔多个值。例如，1,3,5表示 1 分钟、3 分钟和 5 分钟。-：表示范围。例如，1-5表示 1 到 5 分钟。/：用于指定步长。例如，*/5表示每 5 分钟。
一些常见的 cron 表达式示例：
* * * * *：每分钟执行。0 0 * * *：每天凌晨 0 点执行。0 2 1 * *：每月 1 号凌晨 2 点执行。0 0 1 1 *：每年 1 月 1 日凌晨 0 点执行。
三、创建定时任务
使用 cron.schedule 函数来创建定时任务。以下是函数的语法：
SELECT cron.schedule(cron_expression, sql_command);
其中：
cron_expression：是前面提到的 cron 表达式，用于指定任务执行的时间。sql_command：是要在指定时间执行的 SQL 语句。
以下是一些创建任务的示例：
- 每 5 分钟执行一次更新操作：
SELECT cron.schedule('*/5 * * * *', 'UPDATE my_table SET status = ''processed'' WHERE status = ''pending''');
- 每天晚上 10 点备份数据：
SELECT cron.schedule('0 22 * * *', 'COPY my_table TO ''/backup/my_table_$(date +%Y-%m-%d).csv''');
- 每周一早上 8 点清理临时表：
SELECT cron.schedule('0 8 * * 1', 'DROP TABLE IF EXISTS temp_table;');
四、查看任务列表
通过查询 cron.job 视图可以获取已创建任务的详细信息，包括任务 ID、执行时间表达式、状态等。
SELECT * FROM cron.job;
五、控制任务
-
暂停任务
使用
cron.pause函数暂停指定的任务。
SELECT cron.pause(job_id);
-
恢复任务：
使用
cron.resume函数恢复暂停的任务。
SELECT cron.resume(job_id);
-
删除任务
使用
cron.unschedule函数删除不再需要的任务。
SELECT cron.unschedule(job_id);
六、性能与注意事项
- 执行的 SQL 语句应经过充分优化，尤其是对于频繁执行或涉及大量数据操作的任务，以避免对数据库性能产生不利影响。
- 对于可能消耗大量资源的任务，如大型数据备份或复杂的计算操作，应谨慎安排执行时间，避开数据库的业务高峰期。
- 定期检查任务的执行日志和结果，确保任务按预期运行。如果出现错误或异常，及时进行排查和修复。
