# pg_cron: a cron scheduler that lives inside PostgreSQL

> pg_cron runs periodic SQL jobs from a background worker inside the database, using Vixie cron syntax plus a seconds interval and a last-day-of-month marker. It suits teams that already trust Postgres with their data and want scheduling in the same transaction log.

**citusdata/pg_cron** — Run periodic jobs in PostgreSQL

- Repository: https://github.com/citusdata/pg_cron
- Stars: 3,899 · Forks: 262
- Language: C
- License: PostgreSQL
- Published: 2026-09-23 · Updated: 2026-09-23 · Language: en
- Canonical page: https://hysenlabs.com/projects/citusdata-pg-cron

## The gap pg_cron fills between crontab and the database

A Unix crontab entry that runs psql has three problems. It needs a shell on the database host, it stores credentials somewhere outside the database, and it has no idea whether the previous run finished. pg_cron moves the schedule into a table. The README describes it as "a simple cron-based job scheduler for PostgreSQL (10 or higher) that runs inside the database as an extension", and the mechanism follows from that: a background worker reads rows from cron.job and executes them.

The audience is narrow but common. If you already run nightly VACUUM, weekly deletes of old event rows, or a stored procedure that refreshes a materialized view, and your only reason for a separate scheduler is that Postgres has no built-in one, pg_cron removes that reason. It is not a general workflow engine. There are no dependencies between jobs, no retry policy, and no alerting beyond the tables it exposes.

## How the background worker and cron.job table fit together

The extension creates a background worker that tracks jobs in the cron.job table. The README shows the table definition, and its columns explain the model: schedule, command, nodename, nodeport, database and username all have defaults, so a job created with only a schedule and a command runs on localhost, on the current port, in the current database, as the current user.

To execute a job, the extension either establishes a Postgres connection or spawns a database worker, depending on configuration. That distinction matters: a job that runs in a separate connection sees the database as a client would, while a worker runs inside the server process. The README does not spell out which configuration selects which path, so treat that as something to confirm in your own setup rather than assume.

Parallelism has a clear rule. pg_cron can run multiple jobs in parallel, but only one instance of each specific job at a time. If a second instance is triggered before the first finishes, it is queued and starts as soon as the first one completes. That prevents overlap for a single job, and it also means a job that hangs will block its own future runs indefinitely. There is no documented timeout.

The cron parsing code comes directly from Paul Vixie's cron source, so the five-field syntax is the familiar one. Two extensions go beyond it: `$` in the day-of-month field means the last day of the month, and a value like `[1-59] seconds` schedules by interval. Seconds cannot be combined with the other time units.

## Installing pg_cron and scheduling a first job

The README has an Installing pg_cron section but the excerpt here does not include its steps, so the examples below cover only what the README does document: creating a job and managing it. Because pg_cron is a C extension, the shared library has to be present in the PostgreSQL installation before the extension can be created, and the background worker has to be preloaded, which is why shared_preload_libraries is the setting to check first.

A job is one function call. This example deletes rows older than a week every Saturday at 3:30am, and it returns the cron id:

```sql
-- 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'$$
);
-- returns cron id
```

Named jobs are easier to reason about later, since you can remove them by name instead of by id. This one runs VACUUM daily at 10:00am:

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

Intervals below a minute use the seconds form, which cannot be mixed with the other fields:

```sql
-- run SELECT 1 every 30 seconds
SELECT cron.schedule(
       'run_every_30_seconds', 
       '30 seconds', 
       'SELECT 1'
);
-- returns cron id
```

To stop a job, cron.unschedule takes either a name or a numeric id and returns true when the job was removed:

```sql
-- delete job by name
SELECT cron.unschedule('nightly-vacuum');
-- returns true if job was removed
```

Changing a schedule in place uses cron.alter_job, which returns void. Passing NULL for a parameter leaves that field alone, so this changes only the schedule of job 42:

```sql
-- change job's schedule
SELECT cron.alter_job(42, '0 10 * * *');
-- returns void
```

One access rule is worth knowing before you hand out credentials. An RLS policy ensures that jobs can only be seen and modified by the user that created them, unless the user is a superuser or has the bypassrls attribute. A team that shares one application role will therefore see each other's jobs; a team that gives each service its own role will not.

## Running a job in another database and what that costs

cron.schedule_in_database exists for the case where the scheduling database is not the database the work belongs to. Its signature takes job_name, schedule, command, database, an optional username defaulting to NULL, and an active flag defaulting to true.

```sql
-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule_in_database(
       'delete_old_data', 
       '30 3 * * 6', 
       $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$,
       'some_other_database'
);
-- returns cron id
```

The cost is that the job now spans two databases. The cron.job row lives in the scheduling database, but the command runs against the named one, and the username parameter decides whose privileges apply. If that user is dropped or its password changes, the job fails at run time rather than at creation time. The README does not document a validation step that would catch this when you create the job, so the failure surfaces in the job run history instead.

## Where pg_cron is the wrong tool

The most obvious limitation is structural: the scheduler is a background worker inside PostgreSQL. If the server is down, nothing runs, and there is no catch-up mechanism described in the README for runs missed during downtime. A job scheduled for every five minutes during a two-hour maintenance window does not fire twenty-four times afterwards. For work that must happen on wall-clock time regardless of database availability, an external scheduler is the correct choice.

Overlap handling is the second constraint. Queuing a second instance behind a running one is sensible for most maintenance work, but it is a poor fit for jobs that must not run late. A long-running job quietly delays every subsequent run of itself with no documented cap.

Portability is the third. The repository contains a Makefile.win, which indicates Windows build support exists in some form, but the README excerpt does not describe a Windows installation procedure, and the extension still needs a PostgreSQL server built to load it. Anyone searching for how to install pg_cron on Windows should expect to build from source against a matching PostgreSQL installation rather than follow a documented path.

Finally, pg_cron schedules SQL. If the command is a shell script, you are either wrapping it in a database function or reaching for something outside the database.

## pg_cron against an external scheduler such as cron or systemd timers

The real alternative is the operating system's own scheduler. A crontab entry calling psql, or a systemd timer, keeps the schedule outside the database. The difference in approach is where state lives. With an external scheduler, the schedule is a file on the host, version control is your own problem, and every host that can reach the database can have its own copy. With pg_cron, the schedule is a row in cron.job, readable and editable with SQL, and it moves with a logical dump of that database.

That cuts both ways. A dump that includes cron.job carries your schedules to the new cluster, which is convenient, but it also means a restore can resurrect jobs that were deliberately disabled. The active column on cron.job, which cron.alter_job can set, is the mechanism for turning a job off without deleting it.

The external scheduler also has capabilities pg_cron does not claim: running before the database starts, sending mail on failure, and chaining tasks with dependencies. If you need any of those, the trade is not close. If you need none of them, keeping the schedule next to the data removes a moving part.

## Upgrades, licence and the maintenance question

The repository carries a chain of migration scripts from pg_cron--1.0--1.1.sql through pg_cron--1.5--1.6.sql, which is how PostgreSQL extensions handle version upgrades: the next script in the chain is applied when you update the extension. That means upgrading is a SQL operation, not a rebuild, as long as the installed shared library matches the target version. A mismatch between the library on disk and the extension version recorded in the catalog is the failure mode to watch for, and the README excerpt does not describe a check for it.

The last push to the repository was on 2026-09-08, the same day as the v1.6.8 release. The previous release, v1.6.7, is dated 2025-09-04, so the gap between them is roughly a year. That is a slow cadence, and it is worth knowing before you plan around new features.

The licence is the PostgreSQL licence, the same permissive terms as PostgreSQL itself. It imposes no copyleft obligation on the surrounding application, and redistribution requires keeping the licence text. This is a description of the licence identifier in the repository, not legal advice; if the licence matters to your organisation's review process, read the LICENSE file.

## Conclusion

Adopt pg_cron when the work you schedule is SQL that belongs in the same database as your data, and when you can install a shared library into PostgreSQL's extension directory. Skip it when the job needs to run before the database is up, when you have no way to add a background worker to the server, or when the schedule must survive a rebuild of the cluster. Before you commit, confirm that the extension package for your distribution matches your PostgreSQL major version, that the value you put in shared_preload_libraries is exactly pg_cron, and that the database named in cron.database_name exists and is not the one you intend to drop.

## FAQ

### What is pg_cron?

It is a cron-based job scheduler for PostgreSQL 10 or higher that runs inside the database as an extension. A background worker tracks jobs in the cron.job table and executes them on the schedule you give.

### How do I install pg_cron in PostgreSQL?

The README has an Installing pg_cron section, but the steps are not reproduced here. Once the shared library is in place and the worker is preloaded, the extension is enabled in the database that should hold the schedules.

### How do I set up pg_cron?

Create the extension, then add jobs with cron.schedule, passing a cron expression and the SQL command. Jobs run as the user that created them, and an RLS policy keeps them visible only to that user unless the user is a superuser or has bypassrls.

### How do I use pg_cron in PostgreSQL?

Call cron.schedule with a schedule and a command to create a job, cron.alter_job to change its schedule, command, database, username or active flag, and cron.unschedule with a name or id to remove it. Jobs in another database go through cron.schedule_in_database.

### How do I install pg_cron on Windows?

The repository includes a Makefile.win, so Windows build support exists in some form, but the README excerpt does not document a Windows installation procedure. Expect to build against a matching PostgreSQL installation rather than follow a written guide.

### How do I add the pg_cron extension?

The extension is added to a database, and the README recommends managing jobs through the cron functions rather than writing to the cron.job table directly, even though direct access is possible with the required permissions.

## Sources

- [citusdata/pg_cron on GitHub](https://github.com/citusdata/pg_cron)
- [Issues](https://github.com/citusdata/pg_cron/issues)
- [License: PostgreSQL](https://github.com/citusdata/pg_cron/blob/main/LICENSE)
- [README](https://github.com/citusdata/pg_cron/blob/main/README.md)
- [Releases](https://github.com/citusdata/pg_cron/releases)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/citusdata-pg-cron