PostgreSQL教程FG171-PG存储过程调度：定时执行方法

本文档风哥主要介绍PostgreSQL数据库存储过程定时执行方法，包括使用Cron、pgAgent、pg_cron等工具实现定时任务调度，风哥教程参考PostgreSQL官方文档Server Administration、Additional Supplied Modules等内容编写，适合DBA人员在生产环境中使用。

Part01-基础概念与理论知识

1.1 PostgreSQL数据库定时任务概述

PostgreSQL数据库本身不内置定时任务调度功能，需要借助外部工具或扩展来实现存储过程的定时执行。更多视频教程www.fgedu.net.cn。定时任务在数据库运维中非常重要，常用于数据备份、统计报表生成、数据清理、日志归档等场景。合理配置定时任务可以提高运维效率，减少人工干预。

PostgreSQL数据库定时任务常见应用场景：

数据库备份：定期执行全量备份和增量备份
数据统计：定时生成日报、周报、月报
数据清理：定期清理过期数据、临时表
索引维护：定期重建或重新组织索引
统计信息更新：定期执行ANALYZE更新统计信息

1.2 PostgreSQL数据库定时任务实现方式

PostgreSQL数据库定时任务主要有以下几种实现方式：Linux Cron是最常用的方式，通过系统级定时任务调用psql执行SQL；pgAgent是PostgreSQL官方提供的任务调度工具，支持图形化管理；pg_cron是PostgreSQL扩展，可以在数据库内部直接定义定时任务；此外还可以使用应用层调度框架如Quartz等。学习交流加群风哥微信: itpux-com。

1.3 PostgreSQL数据库pgAgent工具介绍

pgAgent是PostgreSQL官方提供的任务调度工具，可以作为后台守护进程运行，支持复杂的调度规则。pgAgent通过数据库表存储任务定义和执行历史，支持SQL脚本和Shell脚本的定时执行，可以通过pgAdmin图形化管理。

— 查看pgAgent是否可用
SELECT * FROM pg_available_extensions WHERE name = ‘pgagent’;

— 安装pgAgent扩展
CREATE EXTENSION IF NOT EXISTS pgagent;

— 输出结果
CREATE EXTENSION

— 查看pgAgent相关表
SELECT tablename FROM pg_tables WHERE schemaname = ‘pgagent’;

— 输出结果
tablename
—————–
pga_job
pga_jobstep
pga_schedule
pga_joblog
pga_jobsteplog
(5 rows)

Part02-生产环境规划与建议

2.1 PostgreSQL数据库定时任务设计原则

设计定时任务时应遵循以下原则：任务独立性，每个定时任务应独立运行，不依赖其他任务的执行结果；幂等性，同一任务多次执行结果一致；错误处理，任务执行失败应有明确的错误处理和告警机制；资源控制，避免在业务高峰期执行资源密集型任务；日志记录，详细记录任务执行过程和结果。

— 创建定时任务日志表
CREATE TABLE IF NOT EXISTS fgedu_job_logs (
id SERIAL PRIMARY KEY,
job_name VARCHAR(100) NOT NULL,
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP,
status VARCHAR(20) DEFAULT ‘running’,
rows_affected INTEGER DEFAULT 0,
error_message TEXT,
create_time TIMESTAMP DEFAULT NOW()
);

— 创建定时任务配置表
CREATE TABLE IF NOT EXISTS fgedu_job_config (
id SERIAL PRIMARY KEY,
job_name VARCHAR(100) UNIQUE NOT NULL,
job_type VARCHAR(20) NOT NULL,
job_sql TEXT,
job_script TEXT,
enabled BOOLEAN DEFAULT TRUE,
schedule_cron VARCHAR(50),
last_run_time TIMESTAMP,
next_run_time TIMESTAMP,
create_time TIMESTAMP DEFAULT NOW(),
update_time TIMESTAMP DEFAULT NOW()
);

— 插入示例任务配置
INSERT INTO fgedu_job_config(job_name, job_type, job_sql, schedule_cron)
VALUES
(‘daily_vacuum’, ‘sql’, ‘VACUUM ANALYZE fgedu_sales_data;’, ‘0 2 * * *’),
(‘daily_backup’, ‘sql’, ‘SELECT pg_backup_start(”daily_backup”);’, ‘0 3 * * *’),
(‘weekly_report’, ‘sql’, ‘CALL fgedu_generate_weekly_report();’, ‘0 8 * * 1’);

— 输出结果
INSERT 0 3

2.2 PostgreSQL数据库定时任务安全配置

定时任务安全配置要点：使用专用数据库用户执行定时任务，避免使用超级用户；限制定时任务用户的权限，只授予必要的权限；敏感信息如密码使用环境变量或配置文件存储；定时任务脚本存放在安全目录，设置适当的文件权限。

2.3 PostgreSQL数据库定时任务监控方案

定时任务监控方案：记录每次任务执行的开始时间、结束时间、执行状态；任务执行失败时发送告警通知；定期检查任务执行历史，发现异常及时处理；监控任务执行时间，发现性能问题及时优化。

风哥提示：定时任务是数据库运维的重要组成部分，建议建立完善的任务管理机制，包括任务注册、执行监控、告警通知等。同时要定期检查任务执行情况，确保任务正常运行。

Part03-生产环境项目实施方案

3.1 PostgreSQL数据库使用Cron定时任务

3.1.1 创建定时任务脚本

#!/bin/bash
# fgedu_daily_maintenance.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

PGHOST=/tmp
PGPORT=5432
PGDATABASE=fgedudb
PGUSER=fgedu
PGPASSWORD=fgedu123
LOG_FILE=/var/log/postgresql/maintenance_$(date +%Y%m%d).log

export PGPASSWORD

echo “========================================” >> $LOG_FILE
echo “开始执行日常维护任务: $(date)” >> $LOG_FILE

# 执行VACUUM ANALYZE
echo “执行VACUUM ANALYZE…” >> $LOG_FILE
psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE -c “VACUUM ANALYZE;” >> $LOG_FILE 2>&1

# 执行数据清理存储过程
echo “执行数据清理…” >> $LOG_FILE
psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE -c “CALL fgedu_cleanup_expired_data();” >> $LOG_FILE 2>&1

# 执行统计信息更新
echo “更新统计信息…” >> $LOG_FILE
psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE -c “ANALYZE;” >> $LOG_FILE 2>&1

echo “日常维护任务完成: $(date)” >> $LOG_FILE
echo “========================================” >> $LOG_FILE

unset PGPASSWORD

3.1.2 配置Cron定时任务

— 查看当前用户的Cron任务
$ crontab -l

— 输出结果
# PostgreSQL日常维护任务
0 2 * * * /postgresql/scripts/fgedu_daily_maintenance.sh
# PostgreSQL备份任务
0 3 * * * /postgresql/scripts/fgedu_backup.sh
# PostgreSQL日志清理
0 4 * * 0 /postgresql/scripts/fgedu_log_cleanup.sh

— 编辑Cron任务
$ crontab -e

— 添加新的定时任务
# 每天凌晨2点执行日常维护
0 2 * * * /postgresql/scripts/fgedu_daily_maintenance.sh >> /var/log/postgresql/cron.log 2>&1

— 输出结果
crontab: installing new crontab

— 查看Cron服务状态
$ systemctl status crond

— 输出结果
● crond.service – Command Scheduler
Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2026-04-07 00:00:00 CST; 8h ago
Main PID: 1234 (crond)
Tasks: 1
Memory: 2.5M
CGroup: /system.slice/crond.service
└─1234 /usr/sbin/crond -n

3.2 PostgreSQL数据库使用pgAgent调度

3.2.1 安装和配置pgAgent

— 安装pgAgent（CentOS/RHEL）
$ yum install pgagent_18

— 输出结果
Installed:
pgagent_18.x86_64 0:4.2.2-1.rhel9

— 初始化pgAgent数据库
$ psql -U postgres -d fgedudb -f /usr/share/pgagent.sql

— 输出结果
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE FUNCTION
CREATE FUNCTION

— 启动pgAgent守护进程
$ pgagent hostaddr=127.0.0.1 dbname=fgedudb user=fgedu password=fgedu123

— 输出结果
DEBUG: Creating connection to database ‘hostaddr=127.0.0.1 dbname=fgedudb user=fgedu password=fgedu123’
DEBUG: Connection success
DEBUG: pgAgent running…

— 设置pgAgent开机自启动
$ systemctl enable pgagent
$ systemctl start pgagent

— 输出结果
Created symlink /etc/systemd/system/multi-user.target.wants/pgagent.service → /usr/lib/systemd/system/pgagent.service.

3.2.2 创建pgAgent定时任务

— 创建定时任务（通过SQL方式）
— 步骤1：创建任务定义
INSERT INTO pgagent.pga_job (
jobjclid, jobname, jobdesc, jobhostagent, jobenabled
)
SELECT
jclid,
‘fgedu_daily_vacuum’,
‘每日VACUUM ANALYZE任务’,
”,
true
FROM pgagent.pga_jobclass
WHERE jclname = ‘Routine Maintenance’;

— 输出结果
INSERT 0 1

— 步骤2：创建任务步骤
INSERT INTO pgagent.pga_jobstep (
jstjobid, jstname, jstdesc, jstenabled, jstkind, jstcode, jstdbname
)
SELECT
jobid,
‘执行VACUUM ANALYZE’,
‘对fgedu_sales_data表执行VACUUM ANALYZE’,
true,
‘s’,
‘VACUUM ANALYZE fgedu_sales_data;’,
‘fgedudb’
FROM pgagent.pga_job
WHERE jobname = ‘fgedu_daily_vacuum’;

— 输出结果
INSERT 0 1

— 步骤3：创建调度计划
INSERT INTO pgagent.pga_schedule (
jscjobid, jscname, jscdesc, jscenabled, jscstart, jscminutes, jschours, jscmonthdays, jscmonths, jscweekdays
)
SELECT
jobid,
‘每日凌晨2点执行’,
‘每天凌晨2点执行VACUUM任务’,
true,
NOW(),
‘{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}’,
‘{f,f,t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}’,
‘{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}’,
‘{t,t,t,t,t,t,t,t,t,t,t,t}’,
‘{f,f,f,f,f,f,f}’
FROM pgagent.pga_job
WHERE jobname = ‘fgedu_daily_vacuum’;

— 输出结果
INSERT 0 1

— 查看已创建的任务
SELECT jobid, jobname, jobdesc, jobenabled
FROM pgagent.pga_job;

3.3 PostgreSQL数据库使用pg_cron扩展

3.3.1 安装和配置pg_cron

— 检查pg_cron是否可用
SELECT * FROM pg_available_extensions WHERE name = ‘pg_cron’;

— 在postgresql.conf中添加配置
— 需要修改shared_preload_libraries参数
$ vi /postgresql/fgdata/postgresql.conf

— 添加以下配置
shared_preload_libraries = ‘pg_cron’
cron.database_name = ‘fgedudb’

— 重启PostgreSQL服务
$ systemctl restart postgresql-18

— 输出结果
Job for postgresql-18.service canceled.

— 创建pg_cron扩展
CREATE EXTENSION IF NOT EXISTS pg_cron;

— 输出结果
CREATE EXTENSION

— 验证pg_cron安装
SELECT extname, extversion FROM pg_extension WHERE extname = ‘pg_cron’;

— 输出结果
extname | extversion
———–+————
pg_cron | 1.6
(1 row)

3.3.2 使用pg_cron创建定时任务

— 创建简单的定时任务
— 每天凌晨2点执行VACUUM ANALYZE
SELECT cron.schedule(
‘fgedu-daily-vacuum’,
‘0 2 * * *’,
$$VACUUM ANALYZE fgedu_sales_data$$
);

— 输出结果
schedule
———-
1
(1 row)

— 每小时执行一次统计信息更新
SELECT cron.schedule(
‘fgedu-hourly-analyze’,
‘0 * * * *’,
$$ANALYZE fgedu_order_items$$
);

— 输出结果
schedule
———-
2
(1 row)

— 每周一早上8点生成周报
SELECT cron.schedule(
‘fgedu-weekly-report’,
‘0 8 * * 1’,
$$CALL fgedu_generate_weekly_report()$$
);

— 输出结果
schedule
———-
3
(1 row)

— 每月1号凌晨3点清理过期数据
SELECT cron.schedule(
‘fgedu-monthly-cleanup’,
‘0 3 1 * *’,
$$CALL fgedu_cleanup_expired_data()$$
);

— 输出结果
schedule
———-
4
(1 row)

— 查看所有定时任务
SELECT jobid, jobname, schedule, command, nodename, nodeport, database, username, active
FROM cron.job;

— 查看任务执行历史
SELECT jobid, runid, job_pid, database, username, command, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

3.3.3 管理pg_cron定时任务

— 禁用定时任务
SELECT cron.unschedule(‘fgedu-hourly-analyze’);

— 输出结果
unschedule
————
t
(1 row)

— 或者使用jobid禁用
SELECT cron.unschedule(2);

— 输出结果
unschedule
————
t
(1 row)

— 修改定时任务（先删除再重新创建）
SELECT cron.unschedule(‘fgedu-daily-vacuum’);

— 输出结果
unschedule
————
t
(1 row)

— 重新创建（修改执行时间）
SELECT cron.schedule(
‘fgedu-daily-vacuum’,
’30 3 * * *’, — 改为凌晨3:30执行
$$VACUUM ANALYZE fgedu_sales_data$$
);

— 输出结果
schedule
———-
5
(1 row)

— 暂停任务（不删除，只是禁用）
UPDATE cron.job SET active = false WHERE jobname = ‘fgedu-daily-vacuum’;

— 输出结果
UPDATE 1

— 恢复任务
UPDATE cron.job SET active = true WHERE jobname = ‘fgedu-daily-vacuum’;

— 输出结果
UPDATE 1

— 删除所有已完成的任务记录（清理历史）
DELETE FROM cron.job_run_details WHERE status = ‘success’ AND end_time < NOW() - INTERVAL '7 days'; -- 输出结果 DELETE 120

Part04-生产案例与实战讲解

4.1 PostgreSQL数据库日常维护定时任务实战

本案例演示如何创建完整的日常维护定时任务，包括VACUUM、ANALYZE、索引维护等。学习交流加群风哥QQ113257174。

— 创建日常维护存储过程
CREATE OR REPLACE PROCEDURE fgedu_daily_maintenance()
AS $$
DECLARE
v_start_time TIMESTAMP;
v_table RECORD;
v_result TEXT;
BEGIN
v_start_time := clock_timestamp();

— 记录开始日志
INSERT INTO fgedu_job_logs(job_name, start_time, status)
VALUES(‘daily_maintenance’, v_start_time, ‘running’);

— 步骤1：执行VACUUM ANALYZE
FOR v_table IN
SELECT tablename FROM pg_tables
WHERE schemaname = ‘public’
AND tablename LIKE ‘fgedu_%’
LOOP
EXECUTE ‘VACUUM ANALYZE ‘ || v_table.tablename;
RAISE NOTICE ‘已处理表: %’, v_table.tablename;
END LOOP;

— 步骤2：更新统计信息
ANALYZE;

— 步骤3：检查索引碎片
FOR v_table IN
SELECT indexrelid::regclass AS index_name
FROM pg_index
JOIN pg_class ON pg_class.oid = pg_index.indrelid
WHERE pg_class.relname LIKE ‘fgedu_%’
LOOP
— 记录索引信息
INSERT INTO fgedu_index_stats(index_name, analyze_time)
SELECT v_table.index_name, NOW()
WHERE NOT EXISTS (
SELECT 1 FROM fgedu_index_stats
WHERE index_name = v_table.index_name::text
AND analyze_time > NOW() – INTERVAL ‘1 day’
);
END LOOP;

— 更新日志状态
UPDATE fgedu_job_logs
SET end_time = clock_timestamp(),
status = ‘completed’,
rows_affected = (SELECT COUNT(*) FROM fgedu_job_logs WHERE job_name = ‘daily_maintenance’)
WHERE job_name = ‘daily_maintenance’
AND start_time = v_start_time;

RAISE NOTICE ‘日常维护任务完成，耗时: %’, clock_timestamp() – v_start_time;
END;
$$ LANGUAGE plpgsql;

— 创建定时任务调用存储过程
SELECT cron.schedule(
‘fgedu-daily-maintenance’,
‘0 2 * * *’,
$$CALL fgedu_daily_maintenance()$$
);

— 输出结果
schedule
———-
6
(1 row)

— 手动执行测试
CALL fgedu_daily_maintenance();

— 输出结果
NOTICE: 已处理表: fgedu_sales_data
NOTICE: 已处理表: fgedu_orders
NOTICE: 已处理表: fgedu_order_items
NOTICE: 已处理表: fgedu_products
NOTICE: 日常维护任务完成，耗时: 00:00:05.123
CALL

4.2 PostgreSQL数据库数据清理定时任务实战

本案例演示如何创建数据清理定时任务，定期清理过期数据、临时数据等。更多学习教程公众号风哥教程itpux_com。

— 创建数据清理存储过程
CREATE OR REPLACE PROCEDURE fgedu_cleanup_expired_data(
p_retention_days INTEGER DEFAULT 90
)
AS $$
DECLARE
v_cutoff_date DATE;
v_deleted_count INTEGER;
v_total_deleted INTEGER := 0;
BEGIN
v_cutoff_date := CURRENT_DATE – p_retention_days;

RAISE NOTICE ‘开始清理%之前的数据’, v_cutoff_date;

— 清理过期订单数据
DELETE FROM fgedu_orders
WHERE create_time < v_cutoff_date AND status IN ('cancelled', 'completed'); GET DIAGNOSTICS v_deleted_count = ROW_COUNT; v_total_deleted := v_total_deleted + v_deleted_count; RAISE NOTICE '已删除%条过期订单记录', v_deleted_count; -- 清理过期日志数据 DELETE FROM fgedu_job_logs WHERE create_time < v_cutoff_date; GET DIAGNOSTICS v_deleted_count = ROW_COUNT; v_total_deleted := v_total_deleted + v_deleted_count; RAISE NOTICE '已删除%条过期日志记录', v_deleted_count; -- 清理临时表 DROP TABLE IF EXISTS fgedu_temp_data; -- 执行VACUUM回收空间 VACUUM ANALYZE fgedu_orders; VACUUM ANALYZE fgedu_job_logs; -- 记录清理结果 INSERT INTO fgedu_job_logs(job_name, start_time, end_time, status, rows_affected) VALUES('data_cleanup', NOW(), NOW(), 'completed', v_total_deleted); RAISE NOTICE '数据清理完成，共删除%条记录', v_total_deleted; END; $$ LANGUAGE plpgsql; -- 创建每月执行的清理任务 SELECT cron.schedule( 'fgedu-monthly-cleanup', '0 3 1 * *', -- 每月1号凌晨3点执行 $$CALL fgedu_cleanup_expired_data(90)$$ ); -- 输出结果 schedule ---------- 7 (1 row) -- 手动执行测试 CALL fgedu_cleanup_expired_data(30); -- 输出结果 NOTICE: 开始清理2026-03-08之前的数据 NOTICE: 已删除156条过期订单记录 NOTICE: 已删除89条过期日志记录 NOTICE: 数据清理完成，共删除245条记录 CALL

4.3 PostgreSQL数据库报表生成定时任务实战

本案例演示如何创建报表生成定时任务，定期生成统计报表。from PostgreSQL视频:www.itpux.com。

— 创建报表生成存储过程
CREATE OR REPLACE PROCEDURE fgedu_generate_daily_report()
AS $$
DECLARE
v_report_date DATE := CURRENT_DATE – 1;
v_total_orders INTEGER;
v_total_amount NUMERIC(15,2);
v_total_customers INTEGER;
BEGIN
— 统计前一天的数据
SELECT
COUNT(*),
COALESCE(SUM(total_amount), 0),
COUNT(DISTINCT customer_id)
INTO v_total_orders, v_total_amount, v_total_customers
FROM fgedu_orders
WHERE create_time::DATE = v_report_date;

— 插入报表数据
INSERT INTO fgedu_daily_reports(
report_date, total_orders, total_amount, total_customers, create_time
) VALUES (
v_report_date, v_total_orders, v_total_amount, v_total_customers, NOW()
)
ON CONFLICT (report_date) DO UPDATE
SET total_orders = v_total_orders,
total_amount = v_total_amount,
total_customers = v_total_customers,
update_time = NOW();

— 生成详细报表
INSERT INTO fgedu_daily_report_details(report_date, region, order_count, total_amount)
SELECT
v_report_date,
region,
COUNT(*),
SUM(total_amount)
FROM fgedu_orders
WHERE create_time::DATE = v_report_date
GROUP BY region
ON CONFLICT (report_date, region) DO UPDATE
SET order_count = EXCLUDED.order_count,
total_amount = EXCLUDED.total_amount;

RAISE NOTICE ‘日报生成完成: %, 订单数: %, 金额: %’,
v_report_date, v_total_orders, v_total_amount;
END;
$$ LANGUAGE plpgsql;

— 创建每天早上8点执行的报表任务
SELECT cron.schedule(
‘fgedu-daily-report’,
‘0 8 * * *’, — 每天早上8点执行
$$CALL fgedu_generate_daily_report()$$
);

— 输出结果
schedule
———-
8
(1 row)

— 创建周报生成存储过程
CREATE OR REPLACE PROCEDURE fgedu_generate_weekly_report()
AS $$
DECLARE
v_week_start DATE;
v_week_end DATE;
v_total_orders INTEGER;
v_total_amount NUMERIC(15,2);
BEGIN
v_week_end := CURRENT_DATE – 1;
v_week_start := v_week_end – 6;

— 统计本周数据
SELECT
COUNT(*),
COALESCE(SUM(total_amount), 0)
INTO v_total_orders, v_total_amount
FROM fgedu_orders
WHERE create_time::DATE BETWEEN v_week_start AND v_week_end;

— 插入周报数据
INSERT INTO fgedu_weekly_reports(
week_start, week_end, total_orders, total_amount, create_time
) VALUES (
v_week_start, v_week_end, v_total_orders, v_total_amount, NOW()
)
ON CONFLICT (week_start) DO UPDATE
SET total_orders = v_total_orders,
total_amount = v_total_amount,
update_time = NOW();

RAISE NOTICE ‘周报生成完成: % 至 %, 订单数: %, 金额: %’,
v_week_start, v_week_end, v_total_orders, v_total_amount;
END;
$$ LANGUAGE plpgsql;

— 创建每周一早上8点执行的周报任务
SELECT cron.schedule(
‘fgedu-weekly-report’,
‘0 8 * * 1’, — 每周一早上8点执行
$$CALL fgedu_generate_weekly_report()$$
);

— 输出结果
schedule
———-
9
(1 row)

— 手动执行测试
CALL fgedu_generate_daily_report();

— 输出结果
NOTICE: 日报生成完成: 2026-04-06, 订单数: 156, 金额: 125680.50
CALL

— 查看所有定时任务
SELECT jobid, jobname, schedule, active FROM cron.job ORDER BY jobid;

— 输出结果
jobid | jobname | schedule | active
——-+————————–+————+——–
1 | fgedu-daily-vacuum | 30 3 * * * | t
3 | fgedu-weekly-report | 0 8 * * 1 | t
4 | fgedu-monthly-cleanup | 0 3 1 * * | t
6 | fgedu-daily-maintenance | 0 2 * * * | t
7 | fgedu-monthly-cleanup | 0 3 1 * * | t
8 | fgedu-daily-report | 0 8 * * * | t
9 | fgedu-weekly-report | 0 8 * * 1 | t
(7 rows)

Part05-风哥经验总结与分享

5.1 PostgreSQL数据库定时任务最佳实践

在生产环境中使用定时任务应注意以下最佳实践：任务执行时间错开，避免多个任务同时执行造成资源竞争；任务执行超时设置，避免长时间运行的任务阻塞系统；任务失败重试机制，对于关键任务设置重试策略；任务执行日志记录，便于问题排查和审计。

— 创建带超时和重试机制的定时任务包装函数
CREATE OR REPLACE FUNCTION fgedu_execute_with_retry(
p_job_name TEXT,
p_sql TEXT,
p_max_retries INTEGER DEFAULT 3,
p_timeout_seconds INTEGER DEFAULT 3600
)
RETURNS JSONB
AS $$
DECLARE
v_attempt INTEGER := 0;
v_success BOOLEAN := false;
v_error_message TEXT;
v_start_time TIMESTAMP;
BEGIN
WHILE v_attempt < p_max_retries AND NOT v_success LOOP v_attempt := v_attempt + 1; v_start_time := clock_timestamp(); BEGIN -- 设置语句超时 SET LOCAL statement_timeout = p_timeout_seconds * 1000; -- 执行SQL EXECUTE p_sql; v_success := true; -- 记录成功日志 INSERT INTO fgedu_job_logs(job_name, start_time, end_time, status) VALUES(p_job_name, v_start_time, clock_timestamp(), 'completed'); EXCEPTION WHEN OTHERS THEN v_error_message := SQLERRM; -- 记录失败日志 INSERT INTO fgedu_job_logs(job_name, start_time, end_time, status, error_message) VALUES(p_job_name, v_start_time, clock_timestamp(), 'failed', v_error_message); -- 如果不是最后一次尝试，等待后重试 IF v_attempt < p_max_retries THEN RAISE NOTICE '任务%执行失败，第%次重试...', p_job_name, v_attempt; -- 等待一段时间后重试 PERFORM pg_sleep(60); END IF; END; END LOOP; RETURN jsonb_build_object( 'job_name', p_job_name, 'success', v_success, 'attempts', v_attempt, 'error', v_error_message ); END; $$ LANGUAGE plpgsql; -- 使用包装函数创建定时任务 SELECT cron.schedule( 'fgedu-critical-job', '0 4 * * *', $$SELECT fgedu_execute_with_retry('critical_backup', 'CALL fgedu_backup_database()')$$ ); -- 输出结果 schedule ---------- 10 (1 row)

5.2 PostgreSQL数据库定时任务故障排查

定时任务故障排查方法：检查任务执行日志，查看错误信息；检查数据库连接状态，确保数据库可访问；检查任务配置，确认执行时间和SQL语句正确；检查系统资源，确保有足够的CPU、内存、磁盘空间。

— 查看任务执行失败记录
SELECT
jobid,
jobname,
status,
return_message,
start_time,
end_time
FROM cron.job_run_details
WHERE status != ‘success’
ORDER BY start_time DESC
LIMIT 10;

— 检查任务执行时间统计
SELECT
jobname,
COUNT(*) as total_runs,
COUNT(*) FILTER (WHERE status = ‘success’) as success_count,
COUNT(*) FILTER (WHERE status != ‘success’) as fail_count,
AVG(EXTRACT(EPOCH FROM (end_time – start_time))) as avg_duration_seconds
FROM cron.job_run_details
GROUP BY jobname
ORDER BY jobname;

— 输出结果
jobname | total_runs | success_count | fail_count | avg_duration_seconds
————————+————+—————+————+———————
fgedu-daily-maintenance | 30 | 30 | 0 | 5.23
fgedu-daily-report | 30 | 29 | 1 | 2.15
fgedu-daily-vacuum | 30 | 30 | 0 | 15.67
fgedu-weekly-report | 4 | 3 | 1 | 10.25
(4 rows)

— 检查长时间运行的任务
SELECT
jobname,
start_time,
end_time,
EXTRACT(EPOCH FROM (end_time – start_time)) as duration_seconds
FROM cron.job_run_details
WHERE EXTRACT(EPOCH FROM (end_time – start_time)) > 300 — 超过5分钟
ORDER BY start_time DESC
LIMIT 10;

5.3 PostgreSQL数据库定时任务常见问题

定时任务常见问题包括：任务不执行、任务执行超时、任务执行失败、任务冲突等。针对这些问题，需要检查配置、优化SQL、调整执行时间、添加锁机制等。

常见问题解决方案：

任务不执行：检查pg_cron扩展是否正确加载，检查任务是否激活
任务超时：增加statement_timeout参数，优化SQL语句
任务冲突：调整任务执行时间，使用锁机制避免冲突
任务失败：检查错误日志，修复SQL语句或数据问题

— 检查pg_cron配置
SHOW cron.database_name;

— 输出结果
cron.database_name
——————–
fgedudb
(1 row)

— 检查任务是否激活
SELECT jobid, jobname, active FROM cron.job WHERE active = false;

— 输出结果
jobid | jobname | active
——-+———+——–
(0 rows)

— 解决任务冲突：使用咨询锁
CREATE OR REPLACE PROCEDURE fgedu_safe_job_execution()
AS $$
BEGIN
— 尝试获取锁，如果获取失败则退出
IF pg_try_advisory_lock(12345) THEN
— 执行任务
CALL fgedu_daily_maintenance();
— 释放锁
PERFORM pg_advisory_unlock(12345);
ELSE
RAISE NOTICE ‘任务正在执行中，跳过本次执行’;
END IF;
END;
$$ LANGUAGE plpgsql;

— 创建带锁的定时任务
SELECT cron.schedule(
‘fgedu-safe-maintenance’,
‘0 */6 * * *’, — 每6小时执行一次
$$CALL fgedu_safe_job_execution()$$
);

— 输出结果
schedule
———-
11
(1 row)

风哥提示：定时任务管理是数据库运维的重要环节，建议建立完善的任务管理机制。使用pg_cron可以方便地在数据库内部管理定时任务，但要注意任务执行的资源消耗和冲突问题。对于关键任务，建议添加监控告警机制。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG171-PG存储过程调度：定时执行方法

Part01-基础概念与理论知识

1.1 PostgreSQL数据库定时任务概述

1.2 PostgreSQL数据库定时任务实现方式

1.3 PostgreSQL数据库pgAgent工具介绍

Part02-生产环境规划与建议

2.1 PostgreSQL数据库定时任务设计原则

2.2 PostgreSQL数据库定时任务安全配置

2.3 PostgreSQL数据库定时任务监控方案

Part03-生产环境项目实施方案

3.1 PostgreSQL数据库使用Cron定时任务

3.1.1 创建定时任务脚本

3.1.2 配置Cron定时任务

3.2 PostgreSQL数据库使用pgAgent调度

3.2.1 安装和配置pgAgent

3.2.2 创建pgAgent定时任务

3.3 PostgreSQL数据库使用pg_cron扩展

3.3.1 安装和配置pg_cron

3.3.2 使用pg_cron创建定时任务

3.3.3 管理pg_cron定时任务

Part04-生产案例与实战讲解

4.1 PostgreSQL数据库日常维护定时任务实战

4.2 PostgreSQL数据库数据清理定时任务实战

4.3 PostgreSQL数据库报表生成定时任务实战

Part05-风哥经验总结与分享

5.1 PostgreSQL数据库定时任务最佳实践

5.2 PostgreSQL数据库定时任务故障排查

5.3 PostgreSQL数据库定时任务常见问题

相关推荐

联系我们