pg_cron | Run periodic jobs in PostgreSQL | Cron Utils library

 by   citusdata C Version: v1.5.2 License: PostgreSQL

kandi X-RAY | pg_cron Summary

kandi X-RAY | pg_cron Summary

pg_cron is a C library typically used in Utilities, Cron Utils, PostgresSQL applications. pg_cron has no bugs, it has no vulnerabilities, it has a Permissive License and it has medium support. You can download it from GitHub.

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:. 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.
Support
    Quality
      Security
        License
          Reuse

            kandi-support Support

              pg_cron has a medium active ecosystem.
              It has 2050 star(s) with 157 fork(s). There are 70 watchers for this library.
              There were 2 major release(s) in the last 12 months.
              There are 80 open issues and 117 have been closed. On average issues are closed in 324 days. There are 1 open pull requests and 0 closed requests.
              It has a neutral sentiment in the developer community.
              The latest version of pg_cron is v1.5.2

            kandi-Quality Quality

              pg_cron has 0 bugs and 0 code smells.

            kandi-Security Security

              pg_cron has no vulnerabilities reported, and its dependent libraries have no vulnerabilities reported.
              pg_cron code analysis shows 0 unresolved vulnerabilities.
              There are 0 security hotspots that need review.

            kandi-License License

              pg_cron is licensed under the PostgreSQL License. This license is Permissive.
              Permissive licenses have the least restrictions, and you can use them in most projects.

            kandi-Reuse Reuse

              pg_cron releases are available to install and integrate.
              Installation instructions, examples and code snippets are available.

            Top functions reviewed by kandi - BETA

            kandi's functional review helps you automatically verify the functionalities of the libraries and avoid rework.
            Currently covering the most popular Java, JavaScript and Python libraries. See a Sample of pg_cron
            Get all kandi verified functions for this library.

            pg_cron Key Features

            No Key Features are available at this moment for pg_cron.

            pg_cron Examples and Code Snippets

            No Code Snippets are available at this moment for pg_cron.

            Community Discussions

            QUESTION

            Installation of pg_cron on Azure Flexible PostgeSQL
            Asked 2022-Feb-25 at 21:31

            I am trying to install pg-cron extension for Azure PostgreSQL Flexible server. According to documentation found here: https://docs.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-extensions#postgres-13-extensions pg_cron is available extension, but when I am trying to install it:

            ...

            ANSWER

            Answered 2021-Dec-16 at 14:12

            Seems that the pg_cron extension is already enabled, by default, in the default 'postgres' database. The reason why I was not seeing this is because I am not using the default 'postgres' database. I have created my own DB which I was connected to. This actually does not resolve my problem, because I can't execute jobs from pg_cron across databases...

            Source https://stackoverflow.com/questions/70363101

            QUESTION

            Running query against all schemas in Postgres
            Asked 2021-Dec-09 at 21:02
            Context

            We do schema-based multi-tenancy in our Postgres DB. Each and every schema is associated to a different tenant and will have the exact same structure except for one schema named public.

            To obtain the list of all the relevant schemas, we can use:

            ...

            ANSWER

            Answered 2021-Dec-09 at 21:02

            You can define a Postgres procedure that uses dynamic commands, e.g.:

            Source https://stackoverflow.com/questions/70221689

            QUESTION

            Postgres Tuning max_worker_processes
            Asked 2021-Dec-09 at 15:21

            I'm looking at turning for a very small postgres database (2 cores ver - 12.7) . Everything I have read so far says that the max_worker_processes should just be set to the number of cores allocated to postgres.

            My question is, is there any harm to setting this above the number of cores?

            Ultimately we're trying to use pg_cron with backend processes and for whatever reason pg_cron fails to launch a backend process if the max_worker_processes is set to less than 3.

            We're considering updating max_worker_processes but we can't find any documentation or information that helps us know this won't cause other problems.

            ...

            ANSWER

            Answered 2021-Sep-01 at 16:13

            max_worker_processes is the cluster-wide limit for the number of custom background workers and parallel workers.

            Since pg_cron uses background workers, it will fail if you set the limit too low.

            If you want to allow pg_cron to start enough workers, but you don't want to have too many parallel worker processes (to save on CPU resources), increase max_worker_processes but keep max_parallel_workers low.

            The ideal settings will depend on your requirements and your workload.

            Source https://stackoverflow.com/questions/69017140

            QUESTION

            Postgres insert function by aggregating data from multiple tables
            Asked 2021-Dec-05 at 21:07

            I have got two tables, 'page_visits' and 'comments', which store new webpage visits and new comments, respectively.

            PAGE_VISITS id page_id created_at 1 1111 2021-12-02T04:55:26.779Z 2 1442 2021-12-02T02:25:32.219Z 3 1111 2021-12-02T04:55:26.214Z COMMENTS id page_id ... created_at 1 1024 ... 2021-12-02T04:55:26.779Z 2 1111 ... 2021-12-02T02:25:32.219Z 3 3849 ... 2021-12-02T04:55:26.214Z

            I want to aggregate the data from both the tables in the past 1 hour to use for analytics, such that it looks like the table below.

            PAGE_DATA page_id visit_count comment_count created_at 1024 14 3 2021-12-02T04:55:26.779Z 1111 11 8 2021-12-02T02:25:32.219Z 3849 1 0 2021-12-02T04:55:26.214Z 2412 0 1 2021-12-02T04:55:26.779Z ...

            ANSWER

            Answered 2021-Dec-05 at 21:07

            Consider full join on two aggregates

            Source https://stackoverflow.com/questions/70231389

            QUESTION

            How do I chain a VACUUM off of a purge routine running with pg_cron?
            Asked 2021-Dec-02 at 13:36

            Postgres 13.4

            I've got some pg_cron jobs set up to periodically delete older records out of log-like files. What I'd like to do is to run VACUUM ANALYZE after performing a purge. Unfortunately, I can't work out how to do this in a stored function. Am I missing a trick? Is a stored procedure more appropriate?

            As an example, here's one of my purge routines

            ...

            ANSWER

            Answered 2021-Dec-01 at 04:52

            VACUUM is "top level" command. It cannot be executed from PL/pgSQL ever or from any other PL.

            Source https://stackoverflow.com/questions/70170477

            QUESTION

            Calling an insert *function* from a CTE in a SELECT query in Postgres 13.4
            Asked 2021-Nov-10 at 06:38

            I'm writing up utility code to run through pg_cron, and sometimes want the routines to insert some results into a custom table at dba.event_log. I've got a basic log table as a starting point:

            ...

            ANSWER

            Answered 2021-Nov-10 at 06:38

            Your function expects parameters of type citext but you are passing values of type text. You need to cast the parameters:

            Source https://stackoverflow.com/questions/69906957

            QUESTION

            Postgres 13 pg_cron error on RDS that's hard to track down
            Asked 2021-Nov-04 at 06:04
            pg_cron jobs all fail with "role of....doesn't provide permission to schedule a job"

            I'm working on getting pg_partman and pg_cron set up on RDS, but when my pg_cron jobs run, they return this error:

            ...

            ANSWER

            Answered 2021-Nov-04 at 06:04

            At least in my case, the answer is to run the jobs as the same user as the pg_cron background thread. I've posted more details to the end of the original question.

            Source https://stackoverflow.com/questions/69822551

            QUESTION

            Cannot catch Exception from Postgres plpgsql Procedure and Exception handling best practices?
            Asked 2021-Oct-09 at 09:57

            I have a plpgsql Procedure where I am simply trying to handle any possible exceptions, since I will be running these Procedures on pg_cron (automated) and I do not want anything to fail. The basic skeleton of the procedure looks like this:

            ...

            ANSWER

            Answered 2021-Oct-09 at 09:57

            You should really start indenting your code. This is not just about being pretty, but it would immediately show you the problem with your code.

            Your code, properly indented, looks like this:

            Source https://stackoverflow.com/questions/69503799

            QUESTION

            How to install pg_cron on Big Sur Apple M1?
            Asked 2021-Sep-13 at 06:05
            • I am trying to install pg_cron on my local machine Big Sur M1 for development https://github.com/citusdata/pg_cron
            • I cannot find any instructions on how to install this on M1
            • sudo yum install -y pg_cron_12 does not work
            • sudo apt-get -y install postgresql-12-cron doesnt work either
            • My PostgreSQL version is 13.4, how do I install pg_cron
            ...

            ANSWER

            Answered 2021-Sep-13 at 06:05

            You'll probably have to install from source. The documentation describes that:

            Source https://stackoverflow.com/questions/69157780

            QUESTION

            Schedule REINDEX table CONCURRENTLY job
            Asked 2021-Aug-27 at 09:59

            Aurora Postgress 12.6 Purpose: schedule to rebuild all indexes. what I did is create a function that calls all tables names and reindex concurrently and put the function in pg_cron but it gives me the error "SQL Error [25001]: ERROR: REINDEX CONCURRENTLY cannot be executed from a function". How can I archive the purpose?

            Thanks

            ...

            ANSWER

            Answered 2021-Aug-27 at 09:59

            Don't do it. There is usually never a need to rebuild indexes.

            You can test the indexes regularly using pgstatindex from the pgstattuple extension if you are worried.

            Source https://stackoverflow.com/questions/68949390

            Community Discussions, Code Snippets contain sources that include Stack Exchange Network

            Vulnerabilities

            No vulnerabilities reported

            Install 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. After restarting PostgreSQL, you can create the pg_cron functions and metadata tables using CREATE EXTENSION pg_cron. 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.

            Support

            For any new features, suggestions and bugs create an issue on GitHub. If you have any questions check and ask questions on community page Stack Overflow .
            Find more information at:

            Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from over 650 million Knowledge Items

            Find more libraries
            CLONE
          • HTTPS

            https://github.com/citusdata/pg_cron.git

          • CLI

            gh repo clone citusdata/pg_cron

          • sshUrl

            git@github.com:citusdata/pg_cron.git

          • Stay Updated

            Subscribe to our newsletter for trending solutions and developer bootcamps

            Agree to Sign up and Terms & Conditions

            Share this Page

            share link

            Explore Related Topics

            Consider Popular Cron Utils Libraries

            cron

            by robfig

            node-schedule

            by node-schedule

            agenda

            by agenda

            node-cron

            by kelektiv

            cron-expression

            by mtdowling

            Try Top Libraries by citusdata

            citus

            by citusdataC

            cstore_fdw

            by citusdataC

            pg_shard

            by citusdataC

            postgresql-hll

            by citusdataC

            pg_auto_failover

            by citusdataC