skip to Main Content

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?

2

Answers


  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');
    
    Login or Signup to reply.
  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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search