从 postgresql 数据库定期运行查询（ala crontab）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4434600/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
run queries periodically (ala crontab) from a postgresql database
提问by skyeagle
I am trying to replicate (a mininimal) crontab functionality in a postgresql database table with crontab-like semantics as follows below (indices not shown, for simiplicity):
我正在尝试使用类似 crontab 的语义在 postgresql 数据库表中复制（最小的）crontab 功能，如下所示（为了简单起见，未显示索引）：
create table schedule (
id int not null primary key,
command text
);
create table schedule_detail(
schedule_id int not null references schedule(id),
sd_minute smallint not null,
sd_hour smallint not null,
sd_day smallint not null,
sd_month smallint not null,
sd_year smallint not null,
sd_dayofweek smallint not null
);
I have only got as far as the schema design. I would like some ideas on how I can implement this. In particular, how to implement:
我只了解架构设计。我想要一些关于如何实现这一点的想法。具体来说，如何实现：
- The 'periodic polling' of the schedule table
- How to determine which schedule to be run
- 调度表的“定期轮询”
- 如何确定要运行的计划
Does postgresql have any native (inbuilt) functionality that I can build upon to achieve this behaviour?
postgresql 是否具有我可以构建以实现此行为的任何本机（内置）功能？
回答by Frank Heikens
pgAgentwill do the job. A complete setup can be found at Postgresonline.com.
pgAgent将完成这项工作。可以在Postgresonline.com上找到完整的设置。
回答by mdev
I have been using psql + crontab for it. Like this :
我一直在使用 psql + crontab。像这样 ：
This crontab entry will delete content older than 60 days on rolling basis.
此 crontab 条目将滚动删除超过 60 天的内容。
0 16 * * * PGPASSWORD=mypassword psql -Umyuser -dmyschema -c "delete from mytable where created_at < now() - interval '60 days'"
