41

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

30

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.

  • Please take a look at the version policy, upgrading Postgresql is not an option. – Stephan Jun 6 '13 at 9:53
  • 2
    @Alex You're going to have to upgrade at some point, and it's only going to get harder. What 8.3 point release, by the way? How many significant bug fixes are you missing? Or are you at least on 8.3.23? That said, as I explained the feature you want doesn't exist even in the coming 9.3 release, though some of the groundwork to permit its addition has been added. – Craig Ringer Jun 6 '13 at 9:55
  • I'll have a talk with my boss :) – Stephan Jun 6 '13 at 9:58
  • 1
    @Alex Good idea :-) . At minimum update to 8.3.23 urgently, then start working on upgrade plans to a newer release. It won't solve this question, but it's a very good idea to save future pain. The number of customers I support who're having problems they would never have had if they just stayed current is amazing. We don't release new versions just for kicks ;-) . Read the release notes for each .0 version for guidance on things you might need to deal with, and read the manual on upgrading. Your only likely pain points are standard_conforming_strings and bytea_output. – Craig Ringer Jun 6 '13 at 10:01
  • Craig, what do you think about pg_cron? – mehmet Mar 26 '18 at 22:43
20

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.

-1

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
  • 1
    'it's not really a good idea to abuse the database like that' Why do you think it's abuse? The different mainstream RDBMSes tend to have similar approaches, and I don't think it's so very terrible. Also, if you don't have access to the OS, you are out of lack with crontab. – dezso Jan 10 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.