How to run recurring tasks on Postgresql without an external cron-like tool?

Question

I would like to call a stored procedure on a regular basis. On Oracle, I would create a job for this. I have found that Postgresql can mimic this well by using an external tool (cron etc) and PgAgent.

Do you know of an "internal" alternative which wouldn't involve the external tool ?

• I want to avoid security concerns with the password stored on the command line of the pgAgent.
• I want to avoid any additional system configuration for hiding the password (~/.pgpass).

Postgresql 8.3
Linux RedHat 64bit

Answer 1

Even if you were running the soon-to-be-released (at time of writing) PostgreSQL 10 or the current PostgreSQL 9.6 not an ancient release like 8.3, there's still no built-in task scheduler.

Something like PgAgent or external cron jobs is required, there is no convenient workaround.

The background workers feature introduced in 9.3 should hopefully permit a tool like PgAgent to be moved into the PostgreSQL core in a later release, but it hasn't been done yet. Even on 9.3 you still have to run cron or pgagent.

A few people are working on background worker based schedulers, and there are some patches coming that should provide facilities to help with that. But as of PostgreSQL 10 there's still no good quality, widely adopted scheduler, and most people use cron / ms task scheduler / etc.

Please take a look at the version policy, too; you're running an obsolete and unsupported release.

Answer 2

As of PostgreSQL 9.5, you can use the pg_cron extension, which is loaded as a shared library into PostgreSQL.

After setting it up, creating a job is pretty simple:

SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

This will run the delete command according to the specified cron schedule. You can also use @reboot to schedule a job when the server restarts, and pg_cron will automatically start running jobs if you promote a hot standby.

Instead of using .pgpass, you can provide localhost access for the cron user in pg_hba.conf.

Answer 3

You really, really don't want to do this. Postgres is not an operating system, it's a database server. Even if the database supports running scheduled tasks, it's not really a good idea to abuse the database like that.

If your concern is you don't want to setup password and stuffs, that's easy to resolve. Set up a local Unix socket connection using trust or ident authentication instead, run your cronjob as that user.

In its out of the box configuration, usually postgres sets up the system user postgres to run the db server, and this system user is usually already preconfigured so it can connect to the local server using trust authentication when connecting via local unix socket. You can run your cronjob as postgres system user, connect to the local socket, and then switch role if you don't want your stored procedure to run with superuser privilege.

In default setup, you can just do this:

$ sudo -u postgres crontab -e

In the editor, add to the crontab entry like so:

0    0    *     *    * bash /path/to/run_stored_procedure.sh

and in your /path/to/run_stored_procedure.sh file you simply just use psql to call your stores procedure

#!/usr/bin/env bash
psql my_db_name <<END
    SET ROLE limited_user;
    SELECT my_stored_proc();
    SELECT 1 FROM my_stored_proc();
END