pg_cron is a simple cron-based job scheduler for PostgreSQL that runs inside the database as an extension.

Overview

Citus Banner

Slack Status

What is pg_cron?

pg_cron is a simple cron-based job scheduler for PostgreSQL (9.5 or higher) that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database:

-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
 schedule
----------
       42

-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');
 schedule
----------
       43

-- Change to vacuum at 3:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
 schedule
----------
       43

-- Stop scheduling jobs
SELECT cron.unschedule('nightly-vacuum' );
 unschedule 
------------
 t
(1 row)

SELECT cron.unschedule(42);
 unschedule
------------
          t

pg_cron can run multiple jobs in parallel, but it runs at most one instance of a job at a time. If a second run is supposed to start before the first one finishes, then the second run is queued and started as soon as the first run completes.

The schedule uses the standard cron syntax, in which * means "run every time period", and a specific number means "but only at this time":

 ┌───────────── min (0 - 59)
 │ ┌────────────── hour (0 - 23)
 │ │ ┌─────────────── day of month (1 - 31)
 │ │ │ ┌──────────────── month (1 - 12)
 │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
 │ │ │ │ │                  Saturday, or use names; 7 is also Sunday)
 │ │ │ │ │
 │ │ │ │ │
 * * * * *

An easy way to create a cron schedule is: crontab.guru.

The code in pg_cron that handles parsing and scheduling comes directly from the cron source code by Paul Vixie, hence the same options are supported. Be aware that pg_cron always uses GMT!

Installing pg_cron

Install on Red Hat, CentOS, Fedora, Amazon Linux with PostgreSQL 12 using PGDG:

# Install the pg_cron extension
sudo yum install -y pg_cron_12

Install on Debian, Ubuntu with PostgreSQL 12 using apt.postgresql.org:

# Install the pg_cron extension
sudo apt-get -y install postgresql-12-cron

You can also install pg_cron by building it from source:

git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
# Ensure pg_config is in your path, e.g.
export PATH=/usr/pgsql-12/bin:$PATH
make && sudo PATH=$PATH make install

Setting up pg_cron

To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to shared_preload_libraries in postgresql.conf. Note that pg_cron does not run any jobs as a long a server is in hot standby mode, but it automatically starts when the server is promoted.

By default, the pg_cron background worker expects its metadata tables to be created in the "postgres" database. However, you can configure this by setting the cron.database_name configuration parameter in postgresql.conf.

# add to postgresql.conf:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'

After restarting PostgreSQL, you can create the pg_cron functions and metadata tables using CREATE EXTENSION pg_cron.

-- run as superuser:
CREATE EXTENSION pg_cron;

-- optionally, grant usage to regular users:
GRANT USAGE ON SCHEMA cron TO marco;

Important: Internally, pg_cron uses libpq to open a new connection to the local database. It may be necessary to enable trust authentication for connections coming from localhost in pg_hba.conf for the user running the cron job. Alternatively, you can add the password to a .pgpass file, which libpq will use when opening a connection.

For security, jobs are executed in the database in which the cron.schedule function is called with the same permissions as the current user. In addition, users are only able to see their own jobs in the cron.job table.

Example use cases

Articles showing possible ways of using pg_cron:

Managed services

The following table keeps track of which of the major managed Postgres services support pg_cron.

Service Supported
Aiven ✔️
Alibaba Cloud ✔️
Amazon RDS ✔️
Azure ✔️
Citus Cloud ✔️
Crunchy Bridge ✔️
DigitalOcean ✔️
Google Cloud
Heroku
ScaleGrid ✔️
Scaleway ✔️
Supabase ✔️
Issues
  • Log cron job results in a table

    Log cron job results in a table

    Currently, pg_cron does not give much feedback on the outcome of cron jobs other than what is available from the PostgreSQL log. Add logic to update the cron.result table as tasks progress. Optionally, add a cron job to clean up the table.

    opened by marcocitus 14
  • How to install pg_cron in ubuntu?

    How to install pg_cron in ubuntu?

    Any alternative other than building from source?

    opened by donatello 11
  • Install pg_cron on AWS RDS?

    Install pg_cron on AWS RDS?

    Is it possible and is there a recipe to install pg_cron on RDS Postgres?

    opened by pmehtaaustin 10
  • Memory leak

    Memory leak

    After a week running pg_cron we noticed an increase in memory usage, like we had in the past #26 :

      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3588 postgres  20   0   16.1g  14.9g 265240  15.0g  89704 S   0.0 24.0   4:57.82 postgres: change me: pg_cron launcher
    

    But looking at the repository, the branch fix_memory_leak is not there anymore.

    opened by DanielCristianPinogy 9
  • Can't dump pg_cron schema

    Can't dump pg_cron schema

    PostgreSQL 10 from PGDG, Ubuntu 18.10:

    Check PG config:

    [email protected]_probackup:~$ grep cron /etc/postgresql/10/main/conf.d/local.conf
    shared_preload_libraries = 'pg_cron'
    cron.database_name = 'postgres'
    
    

    Show jobs:

    [email protected]_probackup:~$ psql -U postgres -c '\x' -c 'select * from cron.job'
    Expanded display is on.
    -[ RECORD 1 ]----------------------------------------------
    jobid    | 2
    schedule | */1 * * * *
    command  | insert into testdata (testdata) values ('cron');
    nodename | localhost
    nodeport | 5432
    database | testdb
    username | postgres
    active   | t
    
    

    And now dump DB postgres

    [email protected]_probackup:~$ pg_dump -U postgres -f postgres.sql
    
    

    Result in attach. What is wrong? postgres.sql.gz

    opened by ykurenkov 9
  • pg_cron never ends job of

    pg_cron never ends job of "VACUUM VERBOSE"

    When I schedule a job of "VACUUM VERBOSE" or "VACUUM (VERBOSE, ANALYZE)" pg_cron executes job but never completes it. Specifically in "cron.job_run_details" the "end_time" column remains null.

    Screen Shot 2021-09-07 at 4 13 57 PM

    Ended up having to manually cancel vacuum jobs that were "VERBOSE". I think the issue might be the output of the job is not suitable for return_message... which somehow causes the job to not exit properly... really just speculation though. Replicated this behavior in multiple databases.

    Screen Shot 2021-09-07 at 4 23 50 PM
    opened by ekeric13 8
  • Not able to make pg_cron work

    Not able to make pg_cron work

    This seems to be a very useful project and appreciate Citusdata opensourcing it. I have tried this in both 9.6 and 10 ( single node default installation ) and did exactly as the instructions have said, but after several hours I cannot seem to make it work in both cases.

    Environment: Plain vanilla installation of PG 9.6 and PG 10 on Ubuntu

    1. I create a testcron table with id int , insert_time timestampz default now()
    2. I create a cron job using the syntax with SELECT cron.schedule('1 * * * *', $$insert into testcron values(1)$$); or SELECT cron.schedule('1 * * * *', $$delete from testcron where id = 1$$);
    3. It creates the job but doesn't do anything after 1 minute.
    opened by ranjanprj 8
  • Pg_cron crontab log

    Pg_cron crontab log

    We're trying to configure periodic jobs in PostgreSQL. To do this, we installed on Linux machine, with Postgres 9.6 running, the pg_cron project.

    System information:

    OS: Linux pg 4.4.0-72-generic #93-Ubuntu SMP PG: Postgres 9.6.3 installed from repo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main'

    Following the instructions in the pg_cron repository, we set in postgresql.conf the configuration below:

    shared_preload_libraries = 'pg_cron'
    cron.database_name = 'our db_name'

    Then, on db_name, we created the EXTENSION pg_cron

    CREATE EXTENSION pg_cron; and we scheduled our first Postgres job:

    SELECT cron.schedule('45 12 * * *', $$CREATE TABLE schema.testCron AS Select 'Test Cron' as Cron$$); So, jobid 1 was created and listed in table cron.job.

    We expected that at 12:45 the command of the scheduled job would have been launched. But nothing happened.

    The testCron table was not created and we had no trace in any logs.

    We also defined LOG_FILE in /usr/src/pg_cron/include/pathnames.h to enable logging.

    But, after re-compiling the project and restarting the Postgres service, we did not track log for pg_cron.

    How can we enable logs for pg_cron to check scheduling result?

    Thanks in advance!

    opened by martinamuscariello 8
  • background worker huge memory allocation

    background worker huge memory allocation

    We're seeing this behavior which is more perceptible when using many jobs scheduled on pg_cron.

    This is the situation after one week with 400 jobs:

      PID USER      PR  NI    VIRT    RES    SHR   USED S  %CPU %MEM     TIME+ COMMAND
     1786 postgres  20   0 9766.3m 4.181g   3228 4.181g S   0.0 22.0   0:38.14 postgres: bgworker: pg_cron_scheduler
    

    As shown the shared memory is aroung 3.2MB and the process memory is 4.1GB.

    The version where the issue was detected is PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit, but also occurs on PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit.

    Bellow there are the SQL commands creating jobs and the top measurements showing the memory increase:

    postgres=# select now(); insert into cron.job (schedule, command, nodename, nodeport, database, username) values ('* * * * *', 'select 1', 'localhost', '5432', 'postgres', 'postgres');
                  now              
    -------------------------------
     2017-06-01 10:58:28.591112-03
    (1 row)
    INSERT 0 1
    postgres=# select now(); insert into cron.job (schedule, command, nodename, nodeport, database, username) select schedule, command, nodename, nodeport, database, username from cron.job;
                 now              
    ------------------------------
     2017-06-01 10:58:48.90418-03
    (1 row)
    INSERT 0 1
    postgres=# select now(); insert into cron.job (schedule, command, nodename, nodeport, database, username) select schedule, command, nodename, nodeport, database, username from cron.job;
                  now              
    -------------------------------
     2017-06-01 10:59:43.526128-03
    (1 row)
    INSERT 0 2
    postgres=# select now(); insert into cron.job (schedule, command, nodename, nodeport, database, username) select schedule, command, nodename, nodeport, database, username from cron.job;
                  now              
    -------------------------------
     2017-06-01 11:00:04.406233-03
    (1 row)
    INSERT 0 4
    postgres=# select now(); insert into cron.job (schedule, command, nodename, nodeport, database, username) select schedule, command, nodename, nodeport, database, username from cron.job;
                  now              
    -------------------------------
     2017-06-01 11:00:20.862736-03
    (1 row)
    INSERT 0 8
    postgres=# select now(); insert into cron.job (schedule, command, nodename, nodeport, database, username) select schedule, command, nodename, nodeport, database, username from cron.job;
                  now              
    -------------------------------
     2017-06-01 11:00:49.438208-03
    (1 row)
    INSERT 0 16
    postgres=# select now(); insert into cron.job (schedule, command, nodename, nodeport, database, username) select schedule, command, nodename, nodeport, database, username from cron.job;
                  now              
    -------------------------------
     2017-06-01 11:01:27.310265-03
    (1 row)
    INSERT 0 32
    postgres=# select now(); insert into cron.job (schedule, command, nodename, nodeport, database, username) select schedule, command, nodename, nodeport, database, username from cron.job;
                  now              
    -------------------------------
     2017-06-01 11:01:56.518833-03
    (1 row)
    INSERT 0 64
    postgres=# select now(); insert into cron.job (schedule, command, nodename, nodeport, database, username) select schedule, command, nodename, nodeport, database, username from cron.job;
                  now              
    -------------------------------
     2017-06-01 11:02:23.070821-03
    (1 row)
    INSERT 0 128
    postgres=# select now(); insert into cron.job (schedule, command, nodename, nodeport, database, username) select schedule, command, nodename, nodeport, database, username from cron.job;
                  now              
    -------------------------------
     2017-06-01 11:02:51.662401-03
    (1 row)
    INSERT 0 256
    postgres=# select version();
                                             version                                          
    ------------------------------------------------------------------------------------------
     PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
    (1 row)
    [email protected]:~ $ top -b -d 30 -p 3988 | egrep "(load|pg_cron|USED)"
    top - 10:57:44 up 37 min,  6 users,  load average: 0.25, 0.20, 0.13
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  232452   8700   6624   8700      0 S   0.0  0.0   0:00.00 postgres: bgworker: pg_cron_scheduler
    top - 10:58:14 up 38 min,  6 users,  load average: 0.29, 0.22, 0.13
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  232452   8700   6624   8700      0 S   0.0  0.0   0:00.00 postgres: bgworker: pg_cron_scheduler
    top - 10:58:44 up 38 min,  6 users,  load average: 0.25, 0.21, 0.13
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  232452   8700   6624   8700      0 S   0.0  0.0   0:00.00 postgres: bgworker: pg_cron_scheduler
    top - 10:59:14 up 39 min,  6 users,  load average: 0.22, 0.21, 0.13
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  239028  11208   8896  11208      0 S   0.0  0.0   0:00.00 postgres: bgworker: pg_cron_scheduler
    top - 10:59:44 up 39 min,  6 users,  load average: 0.13, 0.18, 0.13
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  239028  11208   8896  11208      0 S   0.0  0.0   0:00.00 postgres: bgworker: pg_cron_scheduler
    top - 11:00:14 up 40 min,  6 users,  load average: 0.08, 0.17, 0.12
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  239028  11272   8896  11272      0 S   0.0  0.0   0:00.01 postgres: bgworker: pg_cron_scheduler
    top - 11:00:44 up 40 min,  6 users,  load average: 0.05, 0.15, 0.11
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  239028  11272   8896  11272      0 S   0.0  0.0   0:00.01 postgres: bgworker: pg_cron_scheduler
    top - 11:01:14 up 41 min,  6 users,  load average: 0.03, 0.13, 0.11
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  240120  11596   8896  11596      0 S   0.0  0.0   0:00.02 postgres: bgworker: pg_cron_scheduler
    top - 11:01:44 up 41 min,  6 users,  load average: 0.02, 0.12, 0.10
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  240120  11596   8896  11596      0 S   0.0  0.0   0:00.02 postgres: bgworker: pg_cron_scheduler
    top - 11:01:45 up 41 min,  6 users,  load average: 0.02, 0.12, 0.10
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  240120  11596   8896  11596      0 S   0.0  0.0   0:00.02 postgres: bgworker: pg_cron_scheduler
    top - 11:02:15 up 42 min,  6 users,  load average: 0.01, 0.11, 0.10
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  243640  12836   8928  12836      0 S   0.1  0.0   0:00.05 postgres: bgworker: pg_cron_scheduler
    top - 11:02:45 up 42 min,  6 users,  load average: 0.00, 0.09, 0.09
      PID USER      PR  NI    VIRT    RES    SHR   USED   SWAP S  %CPU %MEM     TIME+ COMMAND
     3988 pinogy    20   0  243640  13100   8936  13100      0 S   0.0  0.0   0:00.06 postgres: bgworker: pg_cron_scheduler
    top - 11:03:15 up 43 min,  6 users,  load average: 0.00, 0.08, 0.09
    
    opened by DanielCristianPinogy 8
  • getting error in running below command after cloning this repo and setting path

    getting error in running below command after cloning this repo and setting path

    Machine : ubuntu:16.04 postgreSQL : 9.5 ~/pg_cron-main$ make && sudo PATH=$PATH make install

    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x40000 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/opt/PostgreSQL/10/include -I. -I./ -I/opt/PostgreSQL/10/include/postgresql/server -I/opt/PostgreSQL/10/include/postgresql/internal -I/opt/local/Current/include -D_GNU_SOURCE -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -c -o src/job_metadata.o src/job_metadata.c src/job_metadata.c: In function ‘GetRoleOidIfCanLogin’: src/job_metadata.c:380:14: error: ‘FormData_pg_authid {aka struct FormData_pg_authid}’ has no member named ‘oid’ return rform->oid; ^ src/job_metadata.c:381:1: error: control reaches end of non-void function [-Werror=return-type] } ^ src/job_metadata.c: At top level: cc1: error: unrecognized command line option ‘-Wno-implicit-fallthrough’ [-Werror] cc1: all warnings being treated as errors : recipe for target 'src/job_metadata.o' failed make: *** [src/job_metadata.o] Error 1

    bug 
    opened by marshmello275 7
  • GCP supports pg_cron now

    GCP supports pg_cron now

    Hopefully I read this news correctly:

    https://cloud.google.com/sql/docs/postgres/release-notes#November_19_2021

    opened by toxaq 0
  • Update Google Cloud SQL support for pg_cron

    Update Google Cloud SQL support for pg_cron

    The support for the pg_cron extension has been added to Google Cloud SQL (release notes).

    Also update the link for Google Cloud SQL for PostgreSQL.

    opened by robert-pang 0
  • Persistent error in docker build:

    Persistent error in docker build: "pg_cron can only be loaded via shared_preload_libraries"

    The issue

    I've tried quite a few different iterations of my docker build and I consistently come against this error when I run CREATE EXTENSION pg_cron;

    mydb=# CREATE EXTENSION pg_cron;
    ERROR:  pg_cron can only be loaded via shared_preload_libraries
    HINT:  Add pg_cron to the shared_preload_libraries configuration variable in postgresql.conf.
    

    My config is persisted in a mounted volume and indeed has the two recommended lines in it:

    shared_preload_libraries = 'pg_cron'
    cron.database_name = 'mydb'
    

    If I try running the same command in the postgres db, I get a different error:

    postgres=# CREATE EXTENSION pg_cron;
    ERROR:  can only create extension in database evenkeeltimeseries
    DETAIL:  Jobs must be scheduled from the database configured in cron.database_name, since the pg_cron background worker reads job descriptions from this database.
    HINT:  Add cron.database_name = 'postgres' in postgresql.conf to use the current database.
    CONTEXT:  PL/pgSQL function inline_code_block line 4 at RAISE
    

    I get the same error in mydb whether I have restarted (pg_ctl -D "$PGDATA" -m fast -w stop) the postgres db or not. It all feels a bit circular. Any ideas?

    Musings

    My planned debug steps (until I find one that works)

    • Build with an Ubuntu base image (I notice that this is the one timescale's docker-ha image uses)
    • Build without timescaldb
    • Any ideas put forward

    Build (Dockerfile)

    FROM postgres:13-buster
    
    RUN apt-get update && apt-get install -y wget curl
    
    RUN curl -s https://packagecloud.io/install/repositories/timescale/timescaledb/script.deb.sh |  bash
    RUN apt-get update || exit 1;
    RUN apt-get install -y timescaledb-2-postgresql-13 -y postgresql-13-postgis-3 postgresql-13-cron || exit 1;
    RUN curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null;
    
    COPY entrypoint/ /docker-entrypoint-initdb.d/
    

    Edit/addendum

    Before this I was struggling with similar issue as these (first, second) issues, so admittedly, a bit of a noob with pg_cron.

    opened by snspinn 2
  • Error updating from version 1.3 to version 1.4

    Error updating from version 1.3 to version 1.4

    When upgrading pg_cron from version 1.3 to version 1.4 we are seeing the following error

    postgres=# ALTER EXTENSION pg_cron UPDATE;
    ERROR:  function "schedule" already exists with same argument types
    

    System info:

    postgres=# SELECT version();
                                                                 version                                                             
    ---------------------------------------------------------------------------------------------------------------------------------
     PostgreSQL 13.5 (Ubuntu 13.5-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
    (1 row)
    
    postgres=# select * from pg_available_extensions where name='pg_cron';
      name   | default_version | installed_version |           comment            
    ---------+-----------------+-------------------+------------------------------
     pg_cron | 1.4             | 1.3               | Job scheduler for PostgreSQL
    (1 row)
    postgres=# \dx pg_cron
                   List of installed extensions
      Name   | Version | Schema |         Description          
    ---------+---------+--------+------------------------------
     pg_cron | 1.3     | public | Job scheduler for PostgreSQL
    (1 row)
    
    postgres=# \dx+ pg_cron
         Objects in extension "pg_cron"
               Object description           
    ----------------------------------------
     function cron.job_cache_invalidate()
     function cron.schedule(name,text,text)
     function cron.schedule(text,text)
     function cron.unschedule(bigint)
     function cron.unschedule(name)
     schema cron
     sequence cron.jobid_seq
     sequence cron.runid_seq
     table cron.job
     table cron.job_run_details
    (10 rows)
    
    opened by kien-truong 0
  • Crash with job_name argument on schedule function

    Crash with job_name argument on schedule function

    I encounter some crash with recent updates (Postgresql 13 citus 10). This function return fatal error 7 and crash the connection (minimal reproducible exemple):

    SELECT cron.schedule('4dbb95f6-bb8c-4a29-99ab-8ae5dcf56ca9', '45 09 17 11 *','');

    Depending on the string argument, the input works but with jobname truncated or crash.

    Alter function argument IN job_name from Name to text type seem to resolve the problem

    opened by sguinebert 0
  • Issue: could not access file

    Issue: could not access file "pg_cron": No such file or directory

    Hi,

    I've used pg_cron for a while building docker images with postgres, but started to face this

    could not access file "pg_cron": No such file or directory

    issue since last month.

    I've been using the same pg_cron version 1.3.1 and same docker file, after built the image and let it run, the container would fail with the error message. Was there anything changed to pg_cron version 1.3.1?

    opened by GloriaGreatGreat 0
  • Unable to set task on alternate database

    Unable to set task on alternate database

    Trying to schedule a task on another database and I'm getting an error saying that the schedule is incorrect. When I remove the database parameter, then the schedule works fine.

    select cron.schedule('*/10 * * * *', 'VACUUM', 'other_database_name') fails with error "ERROR: invalid schedule: VACUUM"

    select cron.schedule('*/10 * * * *', 'VACUUM') works without issue.

    Any ideas?

    Thanks

    opened by scottwalkerwmda 2
  • Log-Messages and Cron-Job status as running forever

    Log-Messages and Cron-Job status as running forever

    Hello there,

    We have been using pg_cron to schedule and run a stored procedure at pre-defined intervals. The stored-procedure has a complex business logic and needs to log the steps along with dynamic-SQL and the data as it iterates through the records. Once the job is fired, we observed that after certain iterations (loop) in the same stored procedure, the logging stops and the Job status in job_run_details table shows as 'running' forever. (Note- we have been using the AWS RDS managed instance).

    The sample code is on the similar lines of the example shared below. Please suggest if we are missing something.

    Example - ` CREATE OR REPLACE PROCEDURE public.logtest() LANGUAGE plpgsql AS $procedure$

    declare 	
    	iCounter integer;
    BEGIN
    	for iCounter in 1..200 loop
    		raise notice 'ATTEMPT-%,123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899200123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899300123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899400123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899500',iCounter;		
    	end loop;
    exception
    	WHEN OTHERS THEN
    		RAISE NOTICE 'Exception occurred in logtest. Parameters  SQLSTATE=% ,SQLERRM=% ',SQLSTATE, SQLERRM  ;	
    
    END;
    

    $procedure$ ; `

    opened by bangyogesh 0
  • community package dependance issues

    community package dependance issues

    Hello,

    on SUSE 15 platform:

    package https://download.postgresql.org/pub/repos/zypp/13/suse/sles-15.2-x86_64/pg_cron_13-1.3.0-1.sles15.x86_64.rpm

    doesn't install because of a missing dependency Problem: nothing provides openssl-libs needed by pg_cron_13-1.3.0-1.sles15.x86_64

    package openssl-libs seems to exist only for rhel distributions.

    Please, do you have any idea or repositories where you package (RPM) your products (here pg_cron) ?

    thank you

    opened by Tiago-DBA 0
  • Custom

    Custom "return message"

    Is it possible to set a custom return message in a function or a procedure that would then show up in the job_run_details table?

    opened by mtvan 0
Releases(v1.4.1)
  • v1.4.1(Sep 25, 2021)

  • v1.4.0(Sep 16, 2021)

    • Adds a cron.alter_job function to change job properties, by @bdrouvotAWS
    • Adds a cron.schedule_in_database function to schedule in a custom database, by @bdrouvotAWS
    • Adds a cron.log_min_messages setting to control log_min_messages in pg_cron launcher, by @bdrouvotAWS
    • Adds a cron.enable_superuser_jobs setting to disallow superuser jobs, by @marcocitus
    • Fixes a bug that could cause jobs to hang when using cron.use_background_workers, by @bdrouvotAWS
    • Fixes a small memory allocation bug, by @mrdrivingduck
    • PostgreSQL 14 is supported (no changes were needed)
    Source code(tar.gz)
    Source code(zip)
  • v1.3.1(Mar 29, 2021)

  • v1.3.0(Oct 6, 2020)

    • Background worker support by Bertrand Drouvot
    • Audit table support by Bertrand Drouvot
    • PostgreSQL 13 support by Alexander Kukushkin
    • Schedule jobs by name
    • Fixes a bug that could cause cron.schedule to crash with long schedules
    • Fixes a bug that could cause cron.schedule to get into an infinite loop
    • Fixes a bug that caused overlapping runs not to start
    Source code(tar.gz)
    Source code(zip)
  • v1.2.0(Aug 30, 2019)

  • v1.1.4(Apr 9, 2019)

  • v1.1.3(Nov 16, 2018)

    • Fixes a bug that causes pg_cron to run during pg_upgrade
    • Fixes a bug that causes pg_cron to show up incorrectly in pg_stat_activity in PG11
    Source code(tar.gz)
    Source code(zip)
  • v1.1.2(Jul 27, 2018)

    • PostgreSQL 11 support by dverite
    • Fix a clang build error by kxjhlele
    • Add new 'active' column on cron.job table to enable or disable job(s) by morphax2k
    • Added a regression test, simply run 'make installcheck' by morphax2k
    • Set relevant application_name in pg_stat_activity

    ALTER EXTENSION pg_cron UPDATE is required to finish upgrade.

    Source code(tar.gz)
    Source code(zip)
  • v1.0.2(Oct 6, 2017)

  • v1.0.1(Jun 30, 2017)

    • Fixes a memory leak that occurs when a connection fails immediately
    • Fixes a memory leak due to switching memory context when loading metadata
    • Fixes a segmentation fault that can occur when using an error message after PQclear
    Source code(tar.gz)
    Source code(zip)
Owner
Citus Data
Scalable PostgreSQL
Citus Data
Sqrt OS is a simulation of an OS scheduler and memory manager using different scheduling algorithms including Highest Priority First (non-preemptive), Shortest Remaining Time Next, and Round Robin.

A CPU scheduler determines an order for the execution of its scheduled processes; it decides which process will run according to a certain data structure that keeps track of the processes in the system and their status. A process, upon creation, has one of the three states: Running, Ready, Blocked (doing I/O, using other resources than CPU or waiting on unavailable resource).

Abdallah Hemdan 17 Nov 6, 2021
Simple and lightweight pathname parser for C. This module helps to parse dirname, basename, filename and file extension .

Path Module For C File name and extension parsing functionality are removed because it's difficult to distinguish between a hidden dir (ex: .git) and

Prajwal Chapagain 2 Sep 16, 2021
A interpreter that runs the script which is programmed in the language of FF0 script (or you can call it as Warfarin)

ff0-script A interpreter that runs the script which is programmed in the language of FF0 script (or you can call it as Warfarin) You can do it, unders

null 25 Nov 23, 2021
Extension to Keyboard.h to allow non-US keyboards and Unicode characters

KeyboardUTF8 This Arduino library adds a mapping layer on top of the Keyboard library (for Leonardo/Micro/Due) to allow non-US keyboards and 'typing'

John Wasser 1 Oct 19, 2021
Simple .INI file parser in C, good for embedded systems

inih (INI Not Invented Here) inih (INI Not Invented Here) is a simple .INI file parser written in C. It's only a couple of pages of code, and it was d

Ben Hoyt 1.6k Dec 1, 2021
Libelf is a simple library to read ELF files

libelf Libelf is a simple library which provides functions to read ELF files. Headers #include <stdint.h> #include <elf.h> Structures typedef struct

David du Colombier 31 Nov 18, 2021
ByteCopy , or BCP, intends to copy files accurately (down to the bytes) in a simple , safe and efficient manner.

ByteCopy v3.6 About ByteCopy , or BCP, intends to copy files accurately (down to the bytes) in a simple , safe and efficient manner. It's functionalit

A.P. Jo. 14 Nov 13, 2021
Simple Virtual Machine with its own Bytecode and Assembly language.

BM Simple Virtual Machine with its own Bytecode and Assembly language. Build We are using nobuild build system which requires a bootstrapping step wit

Tsoding 48 Nov 30, 2021
Documenting the development of a simple first module.

Your First Module This guide will look at writing a complete module, with many common features in a reduced form. This includes the module initialisat

Open Multiplayer 16 Jun 3, 2021
A simple and easy-to-use library to enjoy videogames programming

hb-raylib v3.5 Harbour bindings for raylib 3.5, a simple and easy to use library to learn videogames programming raylib v3.5. The project has an educa

MarcosLMG 11 Nov 8, 2021
Dead simple C logging library contained in a single header (.h) file

Seethe Logging so simple, you only need to include a single header file. seethe supports 6 different log levels (DEBUG, INFO, NOTICE, WARNING, ERROR,

Jason Nguyen 18 Nov 22, 2021
A simple application that generates animated BTTV emotes from static images

emoteJAM WARNING! The application is in active development and can't do anything yet. A simple application that generates animated BTTV emotes from st

Tsoding 7 Apr 27, 2021
mathia : a simple computer algebra system in C++.

mathiu.cpp mathiu : a simple computer algebra system in C++ The library originated as a demo library showing the usage of the C++ pattern matching lib

Bowen Fu 54 Oct 30, 2021
Haxe bindings for raylib, a simple and easy-to-use library to learn videogame programming

Haxe bindings for raylib, a simple and easy-to-use library to learn videogame programming, Currently works only for windows but feel free the expand t

FSasquatch 23 Nov 22, 2021
Simple password/cookies/history/bookmarks stealer/dumper for chrome all version (includes 80+)

Simple password/cookies/history/bookmarks stealer/dumper for chrome all version (includes 80+), microsoft edge browser,includes all chromium based browsers, and all gecko based browser (firefox etc.).

null 22 Nov 24, 2021
This is a simple C++ implementation of plant-like structures defined with bracketed OLsystems.

Tree Hundred This is a simple C++ implementation of plant-like structures defined with bracketed OLsystems, as described in the book The Algorithmic B

null 21 Oct 20, 2021
libnpy is a simple C++ library for reading and writing of numpy's .npy files.

C++ library for reading and writing of numpy's .npy files

Leon Merten Lohse 99 Nov 16, 2021
Simple yet fancy CPU architecture fetching tool

Simple yet fancy CPU architecture fetching tool

null 1.4k Dec 4, 2021
A simple program to suspend or hibernate your computer

A simple program to suspend or hibernate your computer. It supports hooks before and after suspending.

Jakub Jirutka 6 Sep 26, 2021