https://github.com/citusdata/pg_cron Skip to content Sign up * Why GitHub? + Features + Mobile + Actions + Codespaces + Packages + Security + Code review + Issues + Integrations + GitHub Sponsors + Customer stories * Team * Enterprise * Explore + Explore GitHub + Learn and contribute + Topics + Collections + Trending + Learning Lab + Open source guides + Connect with others + The ReadME Project + Events + Community forum + GitHub Education + GitHub Stars program * Marketplace * Pricing + Plans + Compare plans + Contact Sales + Education [ ] * # In this repository All GitHub | Jump to | * No suggested jump to results * # In this repository All GitHub | Jump to | * # In this organization All GitHub | Jump to | * # In this repository All GitHub | Jump to | Sign in Sign up {{ message }} citusdata / pg_cron Public * Notifications * Fork 117 * Star 1.5k * Run periodic jobs in PostgreSQL PostgreSQL License 1.5k stars 117 forks Star Notifications * Code * Issues 57 * Pull requests 0 * Actions * Projects 0 * Wiki * Security * Insights More * Code * Issues * Pull requests * Actions * Projects * Wiki * Security * Insights main Switch branches/tags [ ] Branches Tags Could not load branches Nothing to show {{ refName }} default View all branches Could not load tags Nothing to show {{ refName }} default View all tags 7 branches 13 tags Code Latest commit @marcocitus marcocitus Update README.md ... 85eaf77 Jan 4, 2022 Update README.md 85eaf77 Git stats * 174 commits Files Permalink Failed to load latest commit information. Type Name Latest commit message Commit time expected Rename schedule with additional arguments to schedule_in_database Sep 16, 2021 include Add cron.enable_superuser_jobs option to disallow superuser jobs Sep 14, 2021 sql Rename schedule with additional arguments to schedule_in_database Sep 16, 2021 src fixed a cron.max_running_jobs initialization bug Dec 6, 2021 .gitignore feat: add regress options to run install Sep 29, 2021 CHANGELOG.md Add v1.4.1 to CHANGELOG Sep 25, 2021 LICENSE Add license file Sep 6, 2016 META.json Change to pg_cron version 1.0 Nov 18, 2016 Makefile Remove EXTVERSION from Makefile Nov 19, 2021 README.md Update README.md Jan 4, 2022 github-banner.png Add Citus Data banner Sep 7, 2016 pg_cron--1.0--1.1.sql o omit the exception handler in pg_cron 1.1 upgrade file Apr 10, 2018 pg_cron--1.1--1.2.sql Redo pg_extension_config_dump calls which were lost during build Aug 28, 2019 pg_cron--1.2--1.3.sql Make jobname unique per user Sep 23, 2020 pg_cron--1.3--1.4.sql Rename schedule with additional arguments to schedule_in_database Sep 16, 2021 pg_cron--1.4--1.4-1.sql Grant select to sequences to simplify pg_dump as non-superuser Nov 19, 2021 pg_cron.conf feat: add regress options to run install Sep 29, 2021 pg_cron.control Grant select to sequences to simplify pg_dump as non-superuser Nov 19, 2021 pg_cron.sql o To be able to switch on and off cron.job(s) we introduced a new Mar 21, 2018 View code What is pg_cron? Installing pg_cron Setting up pg_cron Example use cases Managed services README.md Citus Banner Slack Status What is pg_cron? pg_cron is a simple cron-based job scheduler for PostgreSQL (10 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: By default, pg_cron uses libpq to open a new connection to the local database, which needs to be allowed by pg_hba.conf. It may be necessary to enable trust authentication for connections coming from localhost in for the user running the cron job, or you can add the password to a .pgpass file, which libpq will use when opening a connection. Alternatively, pg_cron can be configured to use background workers. In that case, the number of concurrent jobs is limited by the max_worker_processes setting, so you may need to raise that. # Schedule jobs via background workers instead of localhost connections cron.use_background_workers = on # Increase the number of available background workers from the default of 8 max_worker_processes = 20 You can also use a unix domain socket directory as the hostname and enable trust authentication for local connections in pg_hba.conf, which is normally safe: # Connect via a unix domain socket cron.host = '/tmp' 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: * Auto-partitioning using pg_partman * Computing rollups in an analytical dashboard * Deleting old data, vacuum * Feeding cats * Routinely invoking a function * Postgres as a cron server 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 [?] About Run periodic jobs in PostgreSQL Topics cron scheduler postgresql periodic-jobs Resources Readme License PostgreSQL License Stars 1.5k stars Watchers 63 watching Forks 117 forks Releases 12 v1.4.1 Latest Sep 25, 2021 + 11 releases Packages 0 No packages published Contributors 23 * @marcocitus * @marcoslot * @df7cb * @bdrouvotAWS * @dverite * @robert-pang * @timgates42 * @serprex * @sebastianwebber * @lornajane * @CyberDem0n + 12 contributors Languages * C 99.3% * Makefile 0.7% * (c) 2022 GitHub, Inc. * Terms * Privacy * Security * Status * Docs * Contact GitHub * Pricing * API * Training * Blog * About You can't perform that action at this time. You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.