RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.w3resource.com/PostgreSQL/snippets/postgresql-pg-cron.php below:

Website Navigation

How to use PostgreSQL pg_cron for Task Scheduling?

How to use PostgreSQL pg_cron for Task Scheduling?Last update on December 28 2024 13:05:17 (UTC/GMT +8 hours)

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.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4