67

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

0

4 Answers 4

38

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.

0
48

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
  • As of today, the github page states: pg_cron is a simple cron-based job scheduler for PostgreSQL (10 or higher) that runs inside the database as an extension Looks like PostgreSQL 9.5 isn't supported anymore since going back in the history of releases I don't see any specific supported versions referrenced besides 10 as the lowest one. Aug 2, 2022 at 20:40
5

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
3
  • 23
    '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. Jan 10, 2019 at 11:44
  • 3
    Abuse depends what you're doing with it. There are many extensions which make use of crons on the db layer such as timescaledb. I also think a great use case is automatically refreshing materialised views. Why not have that responsibility in the db? I do get the sentiment though as it is easily abused.
    – Damien
    Jul 26, 2021 at 6:06
  • 1
    Absolutely not abuse to use pg_cron to run scheduled db operations tasks. Bad SQL queries are abusive because they are bad not cos they are running in a cron job. Running SELECT * FROM db every minute with a cron job IS abuse - regardless of how you run it - nothing to do with cron. More importantly, you are also making the assumption that the operator has terminal access such that they can use the local unix socket - THIS is not an option with Amazon RDS/Aurora.
    – Okezie
    Nov 25, 2022 at 10:00
2

TimescaleDB is an extension to Postgres that handles time-series data - it has actions/jobs - might be useful to some.

User-defined actions allow you to run functions and procedures implemented in a language of your choice on a schedule within TimescaleDB. This allows automatic periodic tasks that are not covered by existing policies and even enhancing existing policies with additional functionality.

https://docs.timescale.com/timescaledb/latest/how-to-guides/user-defined-actions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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