How to use PostgreSQL pg_cron for Task Scheduling?

PostgreSQL pg_cron: Simplifying Scheduled Tasks

pg_cron is a PostgreSQL extension that enables scheduling and execution of SQL-based tasks within the database. It is useful for automating repetitive jobs like backups, data synchronization, or regular maintenance.

Discription:

The pg_cron extension allows PostgreSQL users to run cron-like scheduled tasks directly within the database. It integrates the cron job scheduler with SQL, eliminating the need for external tools like cron or task schedulers.

pg_cron jobs are managed using SQL commands and stored in a system catalog table. The scheduler relies on the database's background worker processes to execute the tasks.

Prerequisites:

• PostgreSQL version 10 or higher.
• The pg_cron extension must be installed on the server.
• Access to a cron.database_name role for executing jobs.

Installation and Setup

Step 1: Install pg_cron

On a Linux-based system, use the following command:

sudo apt install postgresql-pg-cron

Step 2: Enable the Extension

Load the pg_cron extension into a specific database.

CREATE EXTENSION pg_cron;

Step 3: Configure postgresql.conf

Update the PostgreSQL configuration to include shared_preload_libraries.

shared_preload_libraries = 'pg_cron'

• Restart PostgreSQL to apply changes.

Syntax:

SELECT cron.schedule(
    'job_name',       -- Unique identifier for the job
    'cron_timing',    -- Standard cron expression
    'SQL_command'     -- SQL to be executed
);

Examples:

1. Scheduling a Daily Backup

Code:

-- Schedule a backup job to run every day at 2 AM
SELECT cron.schedule(
    'daily_backup',
    '0 2 * * *',
    $$ BACKUP DATABASE my_database TO '/backup/daily' $$
);

Explanation:

• daily_backup: Unique name for the job.
• 0 2 * * *: Cron expression for 2 AM every day.
• BACKUP DATABASE: SQL command executed at the scheduled time.

2. Refreshing a Materialized View

Code:

-- Refresh a materialized view every 6 hours
SELECT cron.schedule(
    'refresh_view',
    '0 */6 * * *',
    $$ REFRESH MATERIALIZED VIEW my_view $$
);

Explanation:

• The cron expression (0 */6 * * *) specifies every 6 hours.
• The SQL command refreshes the materialized view at the specified interval.

3. Automated Data Cleanup

Code:

-- Schedule a job to clean up old records daily
SELECT cron.schedule(
    'cleanup_old_data',
    '30 3 * * *',
    $$ DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days' $$
);

Explanation:

• The task runs at 3:30 AM daily (30 3 * * *).
• Deletes logs older than 30 days to maintain database efficiency.

4. Viewing Scheduled Jobs

Retrieve all scheduled tasks using:

Code:

SELECT * FROM cron.job;

Job Management Commands

Disabling a Job:

Code:

SELECT cron.unschedule(job_id);

Updating a Job:

Code:

SELECT cron.update(job_id, 'new_cron_timing', 'new_SQL_command');

Dropping a Job:

Code:

SELECT cron.unschedule(job_id);

Advantages of using pg_cron

• Centralized scheduling of database tasks.
• Reduces reliance on external tools.
• Directly integrates with PostgreSQL’s SQL ecosystem.
• Allows monitoring of scheduled tasks using SQL queries.

All PostgreSQL Questions, Answers, and Code Snippets Collection.