Introduction

Automating or scheduling background tasks is crucial for maintaining backups, data clearance , data integrity and many more. Postgresql famous for it's flexibility and performance provides an extension pg_cron to schedule SQL statements at specific intervals, which is simliar to background cron jobs. Let's explore running background tasks using postgreSQL. We will cover the basics of creating and managing cron jobs in PostgreSQL.

About pg_cron

pg_cron is an psql extension allowing you to schedule SQL queries to run a regular interval. Tasks like backups, data cleanup, and data integrity checks can be automated using pg_cron.

Prerequisites

• PostgreSQL installed and running
• Basic knowledge of SQL
• Familiarity with cron jobs concepts

Installing pg_cron

To install pg_cron in debian, ubuntu with

sudo apt-get -y install postgresql-16-cron

For docker with postgresql official alpine image follow the following steps:

 # Install all required tools to install pg_cron by building it from the source
 # Open your containter in interactive mode
docker exec -it postgresInstance sh

 # Inside the container
apk add --no-cache git build-base postgresql-dev libc-dev clang llvm15-dev llvm15-libs musl-dev

 # clone the pg_cron git repo
git clone https://github.com/citusdata/pg_cron.git

Ensure that the pg_cron extension is available in your PostgreSQL container.

SELECT * FROM pg_available_extensions WHERE name = 'pg_cron';
-- This should output a table with the pg_cron extension details

Configure pg_cron

pg_cron requires background worker proceses. Add the following lines in your postgresql.conf file to enable pg_cron

shared_preload_libraries = 'pg_cron'

cron.database_name = 'postgres' # optionally specify the db in which pg_cron works

cron.timezone = 'GMT' # Optional: You can also specify timezone

Now restart your postgresql Database

sudo systemctl restart PostgreSQL

docker restart <container_id> # For docker

Playing Around

-- Make sure to set your current database in postgresql.conf

CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Create a simple table
CREATE TABLE event (
id SERIAL PRIMARY KEY,
time TIMESTAMP DEFAULT now(),
);

-- Runs every minute
SELECT cron.schedule('test_job', '* * * * *', 'INSERT INTO event DEFAULT VALUES;');

-- Verify the job has been created;
SELECT * FROM cron.job;

-- Remove the background job by simply:
SELECT cron.unschedule('test_job');

Demo

Conclusion

In this article, we have learned how to schedule cron jobs in PostgreSQL. We covered the basics of creating and managing cron jobs in PostgreSQL. We also discussed the benefits of using cron jobs in PostgreSQL.

References

• https://github.com/citusdata/pg_cron

• https://wiki.postgresql.org/wiki/Apt#PostgreSQL_packages_for_ Debian_and_Ubuntu

• https://forums.unraid.net/topic/149636- installing-extensions -on-postgresql-docker-container/