Engineering

04 November, 2021

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. By default, cron jobs are disabled on PostgreSQL instances. Here is how you can enable them on Amazon Web Services (AWS) RDS console.

Nuno Marinho

Software Engineer

Enabling PostgreSQL cron jobs on AWS RDS - Coletiv Blog

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.

AWS Console configuration screenshot: parameters groups section

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

AWS Console configuration screenshot: create a parameters group

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!

AWS Console configuration screenshot: shared preload libraries

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

AWS Console configuration screenshot: add pg_cron to shared preload libraries

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

AWS Console configuration screenshot: cron database name

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.

AWS Console configuration screenshot: 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.

AWS Console configuration screenshot: modify parameters 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 ☺️!

AWS Console configuration screenshot: reboot

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!

Backend Development

PostgreSQL

AWS RDS

Join our newsletter

Be part of our community and stay up to date with the latest blog posts.

Subscribe

Join our newsletter

Be part of our community and stay up to date with the latest blog posts.

Subscribe

You might also like...

Go back to blogNext
How to support a list of uploads as input with Absinthe GraphQL

Engineering

26 July, 2022

How to support a list of uploads as input with Absinthe GraphQL

As you might guess, in our day-to-day, we write GraphQL queries and mutations for Phoenix applications using Absinthe to be able to create, read, update and delete records.

Nuno Marinho

Software Engineer

Flutter Navigator 2.0 Made Easy with Auto Router - Coletiv Blog

Engineering

04 January, 2022

Flutter Navigator 2.0 Made Easy with Auto Router

If you are a Flutter developer you might have heard about or even tried the “new” way of navigating with Navigator 2.0, which might be one of the most controversial APIs I have seen.

António Valente

Software Engineer

An intro to Svelte for ReactJS developers - Coletiv Blog

Engineering

21 October, 2021

An intro to Svelte for ReactJS developers

After playing around with Svelte and doing some projects, in this blog post Rui shares his experience with and how different it is from React.

Rui Sousa

Software Engineer

Go back to blogNext