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

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

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.

StackExchange Database Administrators Q#43937, answer score: 48