3 Simple scheduler for running stored functions against databases in a
4 PostgreSQL cluster, with support for selective execution based on the
5 replication role of nodes in Slony-I clusters.
7 The script is designed to be run once a minute from cron or similar, and
8 as such has a maximum resolution of 1 minute. It provides very simple
9 scheduling functionality to execute individual stored functions at
10 specified intervals and offsets (E.g. The most complex schedule would
11 be of the form "once every 5 hours at 20 minutes past the hour").
13 If you require more complex schedules or multi-step tasks try
14 <a href="http://www.postgresql.org/ftp/pgadmin3/release/pgagent/" target="_blank">pgAgent</a>
19 $ pg_sched.pl -h <db host> -p <db port> -d <db name> -U <db user> -n <schema> -t <table> -cl <slony clustername> -m <master sets> -l <lock file>
20 -h Hostname of database containing schedule table
22 -p Listening port of database containing schedule table
24 -d Name of database containing schedule table
26 -U User to connect to databse with, also used as default user for all tasks
28 -n Name of schema containing schedule table
30 -t Name of schedule table
32 -cl Name of slony cluster. 'auto' to autodetect, 'off' to disable
34 -m Comma separated list of slony sets on the master. 'all' for all
36 -l Lockfile used to prevent concurrent execution of tasks.
42 Create a PostgreSQL user for schedule to run as, to run tasks under
43 different roles create the user as a superuser:
46 mydb=> CREATE USER pg_sched WITH PASSWORD 'my_password' SUPERUSER ;
49 Create a pgpass file for the OS user that will run the script from cron:
52 $ echo *:*:*:pg_sched:my_password >> /home/myuser/.pgpass
53 chmod 600 /home/myuser/.pgpass
56 Create the schedule table in your database of choice:
59 $ psql -U pg_sched -d mydb -f pg_sched.sql
62 Add an entry to run from cron something like:
65 * * * * * myuser /home/myuser/pg_sched.pl -d mydb -U pg_sched >> /var/log/pg_sched.log 2>&1
68 Insert a row into the table to create a task, e.g:
71 mydb=> INSERT INTO pg_sched (usename, datname, pronamespace, proname, proargs, proargtypes, enabled, frequency, frequency_offset)
72 VALUES ('test_user', 'mydb', 'pg_catalog', 'date_part', '{"hour", "2015-03-25 13:50:59"}', '{text, timestamp}', 'A', '1 hour', '1 minute');