Run Unix Shell Scripts With Postgres Trigger

[sainathr]

Hi,

I would like to run shell scripts with respect to the time mentioned in postgres data base tables. Postgres database should trigger the shell script for the time mentioned in the table. To make it more clear, i will add an entry like as below in postgres database table

file_id script_run_time
crd_extract.sh 13:00

Now as part of automation process postgres database should trigger the script to run.

FYI, I know that i can write a shell script and put it in cron table to run continously so the script queries the table every minute and compare the extracted time from the table with the system date and trigger the extraction script which will be the subshell. But this would not be my automation requirement. Iam looking for a solution of triggering the script by looking the time in Postgres table.

Request all the masters in the forum to reply ASAP.

Thanks in advance and with Regards,
Sainath

[michaelb]

Postgresql is not the best tool to run the OS jobs and unfortunately I cannot think of any really smooth solution.

Look at the Postgresql job scheduler pgAgent.

Another approach could be to create a trigger on insert, update and delete for your table.
The function invoked by this trigger will get all rows from the jobs table and compile a crontab file out of them. Then it would submit the job and you're done. From that point on cron will take care of running your scheduled tasks.

When this table gets updated the trigger fires and your crontab file is recompiled and resubmitted. If all records are deleted the function will know how to call crontab to remove the job.

Obviously for this function you'll pick a language which has a way of running shell commands.