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> -w <lock file> -l <logfile>
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 -w Lockfile used to prevent concurrent execution of tasks.
39 DEFAULT = log to STDOUT instead
44 Create a PostgreSQL user for schedule to run as, to run tasks under
45 different roles create the user as a superuser:
48 mydb=> CREATE USER pg_sched WITH PASSWORD 'my_password' SUPERUSER ;
51 Create a pgpass file for the OS user that will run the script from cron:
54 $ echo *:*:*:pg_sched:my_password >> /home/myuser/.pgpass
55 chmod 600 /home/myuser/.pgpass
58 Create the schedule table in your database of choice:
61 $ psql -U pg_sched -d mydb -f pg_sched.sql
64 Add an entry to run from cron something like:
67 * * * * * myuser /home/myuser/pg_sched.pl -d mydb -U pg_sched -l /var/log/pg_sched.log > /dev/null 2>&1
70 Insert a row into the table to create a task, e.g:
73 mydb=> INSERT INTO pg_sched (usename, datname, pronamespace, proname, proargs, proargtypes, enabled, frequency, frequency_offset)
74 VALUES ('test_user', 'mydb', 'pg_catalog', 'date_part', '{"hour", "2015-03-25 13:50:59"}', '{text, timestamp}', 'A', '1 hour', '1 minute');