如何按照当地时间运行pg_cron作业

Question

当前pg_cron仅按UTC/GMT时间工作。没有可用于按照本地时间服务器时间运行作业的配置选项。如何按照当地时间运行pg_cron作业？

Answer 1

使用pg_cron定义cronjob时，可以首先设置时区

SELECT cron.schedule('manual vacuum', '0 22 * * *', 'SET LOCAL TIME ZONE 'Europe/Rome'; VACUUM FREEZE pgbench_accounts');

Answer 2

我在使用pg_cron 1.4.2版本时遇到了同样的问题。我尝试了不同的组合，在配置文件中设置时区，重新安装扩展名并重新加载数据库。升级到1.5.1 pg_cron_15-1.5.1-1.rhel7.x86_64版本后，作业将根据配置文件中设置的时区正常运行。

postgresql.conf
cron.timezone = 'Europe/Moscow'
[root@demo-db ~]# yum update pg_cron_15.x86_64
................
Running transaction
Updating   : pg_cron_15-1.5.1-1.rhel7.x86_64                                                                                                                                              1/2
Cleanup    : pg_cron_15-1.4.2-1.rhel7.x86_64                                                                                                                                              2/2
Verifying  : pg_cron_15-1.5.1-1.rhel7.x86_64                                                                                                                                              1/2
Verifying  : pg_cron_15-1.4.2-1.rhel7.x86_64                                                                                                                                              2/2
Updated:
pg_cron_15.x86_64 0:1.5.1-1.rhel7

-bash-4.2$ psql -d db_stroy -U postgres
psql (15.2)
Type "help" for help.
db_stroy=# DROP EXTENSION IF EXISTS pg_cron;
DROP EXTENSION
[root@demo-db ~]# systemctl stop  postgresql-15.service
[root@demo-db ~]# systemctl start  postgresql-15.service
[root@demo-db ~]# systemctl status  postgresql-15.service
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2023-03-24 07:53:13 MSK; 1s ago
Docs: https://www.postgresql.org/docs/15/static/
Process: 29027 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 29033 (postmaster)
CGroup: /system.slice/postgresql-15.service
├─29033 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/stroy/
├─29035 postgres: logger
├─29036 postgres: checkpointer
├─29037 postgres: background writer
├─29039 postgres: walwriter
├─29040 postgres: autovacuum launcher
├─29041 postgres: pg_cron launcher
└─29042 postgres: logical replication launcher

[root@demo-db ~]# su - postgres
Last login: Fri Mar 24 07:43:25 MSK 2023 on pts/2
-bash-4.2$ psql -d db_stroy -U postgres
psql (15.2)
Type "help" for help.
db_stroy=# CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE EXTENSION
db_stroy=# GRANT ALL ON TABLE cron.job TO str;
GRANT
db_stroy=# GRANT SELECT, USAGE, UPDATE ON SEQUENCE cron.jobid_seq TO str;
GRANT
db_stroy=# GRANT USAGE ON SCHEMA cron TO str;
GRANT
SELECT cron.schedule('test msk3', '58 07 * * *', 'select 1');
select * from cron.job_run_details  
jobid   runid   job_pid "database"  username    command status  return_message  start_time                    end_time
1          1    29099    db_str     str     select 1    succeeded   1 row   2023-03-24 07:58:00.007 +0300   2023-03-24 07:58:00.008 +0300