Event Scheduling on Postgres.APP on Mac OS

Are you struggling to create scheduled event triggers in Postgres on your Mac OS? Unlike MySQL, Postgres doesn't come with this feature readily available. However, don't worry! You can add extensions to your Postgres database to do this for you. In this guide, we'll be using pg_cron, a simple cron-based job scheduler for PostgreSQL that runs inside the database as an extension.

In fact, If you're using Amazon RDS, you're in luck. Starting with PostgreSQL version 12.5 and higher, their RDS instances come natively supported with pg_cron to schedule event triggers. Check their announcement here. For activating pg_cron on an AWS PSQL, follow this tutorial.

Terminal Postgres

To use pg_cron with your terminal Postgres, you can install it by building it from the source as follows:

1. Clone the pg_cron repo with the following command:

git clone https://github.com/citusdata/pg_cron.git

2. Navigate to the cloned directory and ensure that pg_config is in your path.

cd pg_cron
export PATH=/usr/pgsql-15/bin:$PATH

3. Run the following commands to install pg_cron:

make
sudo PATH=$PATH make install

4. To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to shared_preload_libraries. Add the following line to your postgresql.conf file:

shared_preload_libraries = 'pg_cron'

5. If necessary, add the following line to specify the database in which the pg_cron background worker should run:

cron.database_name = 'your_database_name'

Postgres.APP

If you're using Postgres.APP to manage your local Postgres Database cluster, you can install pg_cron as an extension to your cluster as follows:

1. Stop your database server.

2. Clone the pg_cron repo somewhere. For example: (I am placing it with the rest of the server packages)

git clone https://github.com/citusdata/pg_cron.git /Users/{username}/Library/Application Support/Postgres/var-{version}/pg_cron

3. Copy the following files from the pg_cron source code to the Postgres.APP extension library in the following path:

# files
pg_cron.control
pg_cron--1.0--1.1.sql
pg_cron--1.1--1.2.sql
pg_cron--1.2--1.3.sql 
pg_cron--1.3--1.4.sql 
pg_cron--1.4--1.4-1.sql
pg_cron--1.4-1--1.5.sql
pg_cron--1.0.sql

# Extensions path
/Applications/Postgres.app/Contents/Versions/{version}/share/postgresql/extension

4. Copy the following file to your Postgres.APP library:

pg_cron.so

# Library Path
/Applications/Postgres.app/Contents/Versions/{version}/lib/postgresql

5. Update your Postgres.APP config file to start pg_cron. Find the file in this path:

/Users/{username}/Library/Application Support/Postgres/var-{version}/postgresql.conf

Uncomment the shared_preload_libraries line and add pg_cron to it:

shared_preload_libraries = 'pg_cron'

6. If necessary, add the following line to specify the database in which the pg_cron background worker should run:

cron.database_name = 'your_database_name'

In conclusion, adding cron job scheduling to your PostgreSQL database can greatly simplify your workflow and automate routine tasks. By installing pg_cron, you can easily schedule jobs directly within your database without the need for external tools. Once you have followed the steps outlined in this article, you can start your database and use pg_cron to schedule jobs to run at specific times or intervals.

Don't forget to run the command CREATE EXTENSION pg_cron in your Postgres database terminal to ensure that the extension is installed correctly, and use \dx to check the status of your extensions.

For examples on how to create cron jobs, be sure to check out the pg_cron GitHub page.