Enabling PostgreSQL cron jobs on AWS RDS

A database cron job is a process for scheduling a procedure or command on your database to automate repetitive tasks. In our case, we needed to bulk delete a huge set of records, and cron jobs came in handy.

By default, cron jobs are disabled on PostgreSQL RDS instances, so to use them on our database we need to enable them on Amazon Web Services (AWS) RDS console.

Add pg_cron to your database on AWS

Firstly, let's configure on our AWS Console the pg_cron extension to the database we want to use cron jobs.

⚠️ If you find yourself struggling with the configuration or you find any typos in the article, please comment below! Me & all the DevOps team at Coletiv will be more than happy to help you or fix what's wrong.

Create a parameter group

We need add pg_cron extension, so the way to add extensions is to create a parameter group which is a copy of the default parameter group and add the desired extension.

Access your AWS RDS Console and enter on the Parameter Groups tab on the left panel.

Click on Create parameter group and fill in the details about it.

Edit parameter group parameters

Access your parameter group and search for the shared_preload_libraries parameter. You can use the search bar on top of the parameters to help you find it!

Add the value pg_cron to the shared_preload_libraries parameter. Apply the changes and in the end, it should look something like this.

Now, in the same parameter group, find the field cron.database_name and change it to the name of your database.

Save the changes. Now let's jump to our DB instances 🙂

Add parameter group to our DB instance

Access the DB Instance of the database you want to change, and click on the Configuration tab.

Scroll down a bit and you'll find that the Parameter Group for that instance is not the one we have created.

Normally, if you've never edited or created anything about parameter groups, yours should be default-postgres{POSTGRES_VERSION}. In my case, it's default-postgres12.

What we'll do is change the parameter group to the one we've just created. For that, click on Modify in the top right corner. Then, scroll down until you find where to change the DB parameter group.

Save the changes by pressing Continue at the end of the page. You'll have to confirm the fields you are changing on the Summary of modifications.

Please check if you want to apply the changes right away or schedule a maintenance window.

Applying them immediately will demand a database reboot so the changes take effect immediately (DANGER: apply changes immediately causes database downtime). After that, click on Modify DB Instance and you're good to go!

Reboot database instance

If you choose to apply the changes immediately go to your DB Instance, click on Actions, and Reboot. No magic tricks, it's really this simple ☺️!

Congratulations 🥳, you successfully installed pg_cron on your database instance! We've learned quite a bit about AWS configurations so far:

1. How to create parameter groups
2. How to edit parameter group fields
3. How to change parameter groups on our database instance on RDS

Now, I'll show you some commands you can do on your database with cron.

Working with cron on the database

Enable pg_cron extension

For you to test and work with cron jobs on your database, your first need to enable it. We enabled it on the AWS Console, but now we need to tell our database to go get the extension. For that, we should run the following command.

/* Enable pg_cron extension */ CREATE EXTENSION pg_cron;

Example job you can do with pg_cron

If you reached this far, you can finally create cron jobs to help you execute those repetitive tasks.

Imagine you have an events table, and that table is flooded with events every millisecond. That table will be populated with a lot of data pretty fast 💨. We want to delete all the events from the table every 5 minutes. We could do a cron job that would do something like that.

/* Delete all events every 5 minutes */ SELECT cron.schedule('5 * * * *', $$DELETE FROM events$$);

Useful commands

• To list all cron jobs → SELECT * FROM cron.job;
• To list the run details → SELECT jobid, username, status, return_message, start_time FROM cron.job_run_details;
• To stop/delete a job → SELECT cron.unschedule(job_id)

Hope this article helps you on your daily work and thank you for taking the time to follow me until here, and as always, if you have any questions please write them in the comments below 👇. I will be more than pleased to answer them all and help you with possible problems you have throughout the article!