How to run pg_cron job as per local time?

Question

Currently pg_cron works as per UTC/ GMT time only. There is no configuration option available to run job as per local time server time. How to run pg_cron job as per local time?

Answer 1

When you define cronjob with pg_cron you can set first the timezone

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

Answer 2

I had the same problem with pg_cron version 1.4.2. I tried different combinations, setting the time zone in the config file, reinstalling the extension and reloading the database. After upgrading to version 1.5.1 pg_cron_15-1.5.1-1.rhel7.x86_64, jobs run normally, according to the timezone set in the config file.

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