1

Unable to run at proper timezone. I want to run two things

  1. bash scripts
  2. Stored procedure

For both case I found it runs at the wrong time. Instead of 1 AM and 3 AM pg_cron runs at a different time. Why could that be? At PostgreSQL timezone +6 pg_cron log also show that it runs at +6 timezone but actually it runs at 9PM (21:00). How actually pg_cron get that time?

SELECT cron.schedule('00 15 * * * ','/main/backup/archive.sh');

SELECT cron.schedule('00 15 * * *', $$ call archiver( 10, 'tbl_main','tbl_archiver')$$);

Note: Though I found syntax error for script running but it is not main issue for me.

mustaccio
  • 28,207
  • 24
  • 60
  • 76
Sheikh Wasiu Al Hasib
  • 273
  • 1
  • 2
  • 14

1 Answers1

1

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
.... cut ........
    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
Dmitry Demin
  • 178
  • 1
  • 1
  • 7