Scheduled Jobs & Work Queues With Postgresql

October 8, 2019

With the help of an extension or two, it's easy to use Postgresql for scheduled work.

For example, using the extensions pg_cron and pgsql-http, you can issue a post request to a web service at regular intervals:

cron.schedule accepts two arguments, (1) the cron schedule expression and (2) the dollar-quoted sql script to be executed.

While it's inadvisable to be interacting with web services in postgresql triggers, using pgsql-http in a Postgresql cron job is a perfect use-case.

Note that the above code snippet only works after installing pgsql-http and pg_cron:

For pg_cron, you'll also need to edit postgres.conf by uncommenting shared_preload_libraries and setting it to 'pg_cron' and adding a line that specifies the database that should be used by pg_cron (e.g., cron.database_name = 'postgres').

pg_cron uses the familiar crontab syntax. You can view cron jobs like so:

select * from cron.job;

Also don't forget that you can debug the output of cron jobs at /var/lib/pgsql/10/data/log (assuming that you've installed postgresql 10).