How to Schedule Jobs in PostgreSQL

Galaxy Glossary

How do I schedule recurring SQL jobs directly in PostgreSQL?

Schedule recurring SQL commands inside PostgreSQL with extensions like pg_cron, eliminating external cron scripts.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is the quickest way to schedule jobs inside PostgreSQL?

Install the pg_cron extension, load it in the desired database, then use the cron.schedule function to register a job that runs on a crontab-style timetable.

How do I install and enable pg_cron?

Add pg_cron to shared_preload_libraries in postgresql.conf, restart the server, then run CREATE EXTENSION pg_cron; in the database that will own the jobs.

What does the cron.schedule syntax look like?

You call SELECT cron.schedule(job_name, schedule, command); where schedule is a five-field crontab string and command is the SQL you want executed.

How do I list, alter, or drop scheduled jobs?

Query cron.job to view jobs.Modify timing or SQL with cron.alter_job(job_id, schedule, command). Remove jobs with cron.unschedule(job_id).

Can I target a specific database or role?

Yes. Inside postgresql.conf set cron.database_name and cron.job_owner defaults.Alternatively, provide run_as and database arguments in cron.schedule.

Best practice: run housekeeping off-hours

Batch tasks such as refreshing materialized views or archiving old orders at low-traffic times (e.g., 02:00) to avoid user-facing latency spikes.

Best practice: include job naming conventions

Name jobs with a clear prefix like daily_ or hourly_ so they sort naturally and self-document in cron.job.

How do I schedule a daily sales summary refresh?

Use cron.schedule('daily_sales_summary', '5 1 * * *', $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary$$);.The job runs every day at 01:05.

How do I run a job every 15 minutes?

Set schedule to '*/15 * * * *'. Example: cron.schedule('quarter_hour_inventory', '*/15 * * * *', $$CALL update_product_stock()$$);.

How to debug failed jobs?

Check cron.job_run_details for exit_code and stderr. Wrap SQL in DO blocks with explicit RAISE NOTICE statements to surface intermediate values during failures.

Why How to Schedule Jobs in PostgreSQL is important

How to Schedule Jobs in PostgreSQL Example Usage


-- Run nightly cleanup of abandoned carts at 03:00
SELECT cron.schedule(
    'nightly_abandoned_cart_cleanup',
    '0 3 * * *',
    $$DELETE FROM Orders
      WHERE total_amount = 0
        AND order_date < NOW() - INTERVAL '24 hours';$$
);

How to Schedule Jobs in PostgreSQL Syntax


SELECT cron.schedule(
    job_name  text,      -- unique identifier
    schedule  text,      -- five-field crontab string
    command   text,      -- SQL to execute
    database  text DEFAULT current_database(),
    run_as    text DEFAULT current_user
) RETURNS bigint;

-- List jobs
SELECT * FROM cron.job;

-- Alter job timing or SQL
SELECT cron.alter_job(job_id => 42, schedule => '0 3 * * *', command => $$REFRESH MATERIALIZED VIEW daily_sales$$);

-- Remove job
SELECT cron.unschedule(42);

-- Example crontab strings
'0 2 * * *'     -- daily at 02:00
'*/10 * * * *'  -- every 10 minutes

Common Mistakes

Running heavy jobs during peak hours – this slows user queries. Fix by setting schedules to off-hours and limiting concurrency with cron.max_running_jobs.
Forgetting to reload shared_preload_libraries after adding pg_cron – jobs will not execute. Always restart PostgreSQL or run pg_ctl reload after config change.

Frequently Asked Questions (FAQs)

Can I use pg_cron on managed services like AWS RDS?

pg_cron is available on Amazon RDS for PostgreSQL 13+ and Aurora PostgreSQL. Enable it in the DB parameter group, then create the extension in each database.

Does pg_cron require superuser privileges?

Installing the extension needs superuser rights or rds_superuser on RDS. Once installed, non-superusers can schedule jobs if allowed by the cron.job_owner setting.