PostgreSQL is a feature-rich relational database, but sometimes you need it to handle recurring tasks automatically, such as refreshing materialized views daily, inserting periodic statistics, or cleaning up old records.
Traditionally, this automation would require an external scheduler like Linux cron. However, the pg_cron extension brings this scheduling ability inside PostgreSQL itself. You can run SQL commands on a defined schedule, directly from the database, without writing extra shell scripts or configuring OS-level cron jobs.
1. What is pg_cron?
pg_cron is a lightweight, time-based job scheduler for PostgreSQL. It uses the familiar cron syntax (* * * * *) to execute SQL commands periodically.
Key Features:
- Schedule SQL commands directly inside PostgreSQL
- Supports standard cron syntax (minute, hour, day, month, weekday)
- Can run jobs in the same database or across different databases
- Jobs are persisted in PostgreSQL tables
- No external cron configuration required
2. Installing pg_cron
On Debian/Ubuntu:
sudo apt install postgresql-17-cron
On Red Hat/CentOS:
sudo yum install pg_cron_17
3. Enabling the Extension in PostgreSQL
Edit your postgresql.conf to load pg_cron:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres' # Database where jobs are stored
Restart PostgreSQL:
sudo systemctl restart postgresql
Create the extension:
CREATE EXTENSION pg_cron;
4. Creating a Demo Table and Scheduling Inserts
Let’s create a simple table and set up a cron job to insert a row every minute.
-- Create a demo table
CREATE TABLE demo (
id SERIAL PRIMARY KEY,
data TEXT,
created_at TIMESTAMP DEFAULT now()
);
-- Schedule job: Insert data every minute
SELECT cron.schedule(
'insert_demo_data', -- Job name
'* * * * *', -- Every minute
$$INSERT INTO demo(data) VALUES('demo data')$$
);
Check scheduled jobs:
SELECT * FROM cron.job;
Now you can see a result like this
postgres=# select * from cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+-----------+--------------------------------------------+-----------+----------+----------+----------+--------+------------------
2 | * * * * * | INSERT INTO demo(data) VALUES('demo data') | localhost | 5432 | postgres | postgres | t | insert_demo_data
(1 row)
You can view more details of the cron job by this way
SELECT *
FROM cron.job_run_details
ORDER BY end_time DESC;
You can see result like this
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+-------------------------------------------+-----------+-------------------+----------------------------------+----------------------------------
1 | 26 | 52814 | postgres | postgres | insert into demo(data)values('demo data') | succeeded | INSERT 0 1 | 2025-08-09 09:24:00.011889+05:30 | 2025-08-09 09:24:00.015723+05:30
1 | 25 | 52247 | postgres | postgres | insert into demo(data)values('demo data') | succeeded | INSERT 0 1 | 2025-08-09 09:23:00.01518+05:30 | 2025-08-09 09:23:00.020435+05:30
1 | 24 | 51713 | postgres | postgres | insert into demo(data)values('demo data') | succeeded | INSERT 0 1 | 2025-08-09 09:22:00.014858+05:30 | 2025-08-09 09:22:00.019943+05:30
Check that the value of the column named status.
If the status column value is succeeded, it means there is no issue.
If the status column value is failed, you need to edit the pg_hba.conf file of postgres, like this.
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
5. Understanding pg_cron Objects
Running:
\dx+ pg_cron
Will list functions, tables, sequences, and types provided by the extension in this format.
Objects in extension "pg_cron"
Object description
----------------------------------------------------------------------
function cron.alter_job(bigint,text,text,text,text,boolean)
function cron.job_cache_invalidate()
function cron.schedule_in_database(text,text,text,text,text,boolean)
function cron.schedule(text,text)
function cron.schedule(text,text,text)
function cron.unschedule(bigint)
function cron.unschedule(text)
schema cron
sequence cron.jobid_seq
sequence cron.runid_seq
table cron.job
table cron.job_run_details
type cron.job
type cron.job[]
type cron.job_run_details
type cron.job_run_details[]
(16 rows)
Here’s the important ones, along with usage examples.
5.1 cron.schedule(schedule, command)
Schedules a job in the current database.
-- Run every 5 minutes
SELECT cron.schedule('*/5 * * * *', 'VACUUM ANALYZE');5.2 cron.schedule(job_name, schedule, command)
Schedules a named job in the current database.
SELECT cron.schedule('vacuum_job', '*/10 * * * *', 'VACUUM VERBOSE');5.3 cron.schedule_in_database(job_name, schedule, command, database, username, active)
Gives explicit control over activation status.
SELECT cron.schedule_in_database('nightly_analyze', '0 1 * * *',
'analyze',
'postgres', 'postgres', true);5.4 cron.unschedule(job_id) / cron.unschedule(job_name)
Removes a scheduled job.
-- By job ID
SELECT cron.unschedule(1);
-- By job name
SELECT cron.unschedule('vacuum_job');5.5 cron.alter_job(job_id, schedule, command, database, username, active)
Modifies an existing job.
SELECT cron.alter_job(1, '*/10 * * * *', 'VACUUM FULL', NULL, NULL, true);
6. Important Tables & Sequences
- cron.job – Stores job definitions, including the job’s name, schedule, and command.
- cron.job_run_details – Stores execution logs, such as start time, end time, and status.
- cron.jobid_seq – Generates unique job IDs.
- cron.runid_seq – Generates unique IDs for each job run.
7. Example: Automating a Materialized View Refresh
-- Create a sales table
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
amount NUMERIC,
sale_date DATE
);
-- Insert sample data
INSERT INTO sales(amount, sale_date) VALUES (100, '2025-08-01');
INSERT INTO sales(amount, sale_date) VALUES (200, '2025-08-02');
-- Create a materialized view
CREATE MATERIALIZED VIEW sales_summary AS
SELECT sale_date, SUM(amount) AS total_amount
FROM sales
GROUP BY sale_date;
-- Schedule daily refresh at 1 AM
SELECT cron.schedule(
'refresh_sales_summary',
'* * * * *',
$$REFRESH MATERIALIZED VIEW sales_summary$$
);
Later, insert new data:
INSERT INTO sales(amount, sale_date) VALUES (150, '2025-08-03');
INSERT INTO sales(amount, sale_date) VALUES (300, '2025-08-04');
INSERT INTO sales(amount, sale_date) VALUES (250, '2025-08-05');
After the scheduled time, the materialized view will automatically include new totals.
8. Monitoring and Managing Jobs
View all jobs:
SELECT * FROM cron.job;
View job run history:
SELECT * FROM cron.job_run_details ORDER BY end_time DESC;
Stop a job:
SELECT cron.unschedule('refresh_sales_summary');Conclusion
The pg_cron extension turns PostgreSQL into a fully self-managing scheduler, removing the dependency on external cron jobs or shell scripts.
By storing both job definitions and execution history inside the database, it ensures:
- Centralized and simplified job management
- Transparent execution history and monitoring
- Secure, database-native automation
From refreshing materialized views on schedule to inserting recurring data to running maintenance tasks without leaving SQL—pg_cron delivers a clean, flexible, and reliable automation layer.
Its cron-style scheduling, ability to run jobs across databases, and detailed logging make it an essential tool for database administrators and developers who want to streamline and control operations directly within PostgreSQL.