Getting my server maintenance script right

Question

I have two routine postgresql maintenance tasks that require deletion of aged rows from certain tables of my web application.

Both take ~1 hr to complete, respectively.

Currently, each of these maintenance tasks is represented by a script, that is called at a designated time of day via crontab. I try to schedule both scripts with at least a ~2hr gap between them, since I don't want there to be any overlap between the two tasks.

My question is - can I just merge the two scripts into one, like so:

#!/bin/sh

dbname="dbname"
username="myuser"
psql -d $dbname -U $username << EOF
# task 1
delete from links_publicreply WHERE "submitted_on" < now() - interval '14 days' AND id NOT IN (SELECT latest_reply_id FROM links_link WHERE la$
# task 2
begin;
DELETE FROM links_vote WHERE link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted$
DELETE FROM links_photoobjectsubscription WHERE which_link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_pu$
DELETE FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour';
commit;
EOF

Notice how the deletion tasks are called one after another. This way, I can be certain task 2 only kicks off once task 1 is complete. I want to confirm my understanding is correct, or whether there are any caveats I need to keep in mind. Please advise.

score 3 · Accepted Answer · edited Apr 13 '17 at 12:14

Maybe your crontab file now looks like:

# My cronjobs:
* 2 * * * /scripts/task1.sh
* 4 * * * /scripts/task2.sh

You have multiple options instead of hoping that scheduling two cron jobs far enough apart is sufficient that they won't overlap when one runs a bit longer than expected:

1. Simply schedule a single cron job that executes multiple scripts sequentially:

# My cronjobs:
* 2 * * * /scripts/nightly-tasks.sh

and a simple script (which can be greatly improved with some more error handling and such) will already preserve the sequence and prevent the second script from getting started when the first one has not (yet) been completed successfully:

#!/bin/bash
# /scripts/nightly-tasks.sh
/scripts/task1.sh && /scripts/task2.sh

2. Merge the scripts into a single script as you've done is perfectly valid but depending on the complexity of those existing scripts not always the best solution.

In your example it should work although you might want to consider moving the begin statement to make it a single transaction though.

3. Use a lock file to prevent one cron job from starting before another has finished with flock as described in this Q&A

Getting my server maintenance script right

1 Answers1