Get a Job, a Cron Job...
Author: John on December 21, 2015
UPDATE: I found an error when I went back to confirm my cron job was properly backing up the postgres database. The entry in the cron file was missing the escape character (\) before the (%) characters in the formatting of date and time.
In a previous post, "Backup Remote DB & Restore It To Your Dev Env", I covered how to backup a production Postgres database, on a VPS, and restore it to a development environment on my laptop. In that post I made mention of finding a better way to automate the scheduled backup of that database. So today we will look at how to automate our backup by using the cool tool - CRON.
Before we hop into cron, let’s try a test. I have just logged on to my VPS and as such I am in my home directory, then I enter the following command:
pg_dump -Fc flex-production > backup_flex-production_`date +%Y%m%d_%H%M%S`
We should confirm that a backup file was created, and see what size of files we are dealing with. Try this one out:
➜ ~ ls -lh
total 464K
-rw-rw-r-- 1 jfhogarty jfhogarty 91K Dec 15 07:23 backup_flex-production_20151215_072346
-rw-rw-r-- 1 jfhogarty jfhogarty 2.9K Nov 6 20:34 backup_zshrc
drwxrwxr-x 2 jfhogarty jfhogarty 4.0K May 17 2015 cron
drwxrwxr-x 5 jfhogarty jfhogarty 4.0K Dec 3 14:46 dotfilez
drwxrwxr-x 6 jfhogarty jfhogarty 4.0K Dec 1 08:27 tmp
➜ ~
Not too bad. Our new backup file is only around 91K. We should keep this in mind though, especially if we are going to be doing very frequent backups.
Speaking of backup frequency, when should we backup our database? For the purpose of backing up the OGNT.io database, I’ve settled on once a week. Surely I don’t want to have to remember to log in every week and perform the pg_dump step as noted above. This is where cron comes in. We create/update a cron file which the system will process and execute commands automagically. No need for us to be logged in.
The syntax used in cron can be a little tricky so we will use a neat website to help us out. Check out the cron generator here: click me. For this example, I am choosing the radio buttons next to the select column for Minutes, Hours, and Weekday. For those three, I respectfully chose: 5, 4am, and Sun. In the field Command To Execute, I entered:
/usr/bin/pg_dump flex-production > backup_flex-production_`date +%Y%m%d_%H%M%S`
Let’s take a minute to break that command string down. First, we have the full path and command for pg_dump. If you are not sure where pg_dump is on your system, try the following:
➜ ~ whereis pg_dump
pg_dump: /usr/bin/pg_dump /usr/bin/X11/pg_dump /usr/share/man/man1/pg_dump.1.gz
➜ ~
After the pg_dump command we specify which database we want to backup: flex-production. Then we have a greater than sign which is directing the output of the pg_dump command
TIP
We can use the -Fx option with pg_dump. Using a c (custom) will yield a smaller file. Or you can use a t (tar).
➜ ~ ls -lh
total 964K
-rw-rw-r-- 1 jfhogarty jfhogarty 91K Dec 15 07:23 backup_flex-production_20151215_072346 (Used -Fc)<
-rw-rw-r-- 1 jfhogarty jfhogarty 234K Dec 15 08:03 backup_flex-production_20151215_080319 (No option passed)
-rw-rw-r-- 1 jfhogarty jfhogarty 262K Dec 15 08:20 backup_flex-production_20151215_082039 (Used -Ft)
➜ ~
You can use pg_dump —help to see all of the available options.
I like how small the file is when we used the -Fc so I’m going to update the Command To Execute field at the crontab-generator website.
/usr/bin/pg_dump -Fc flex-production > backup_flex-production_`date +%Y%m%d_%H%M%S`
Now click on the button Generate Crontab Line.
You should get a response similar to:
5 4 * * 0 /usr/bin/pg_dump -Fc flex-production > backup_flex-production_`date +%Y%m%d_%H%M%S` >/dev/null 2>&1
Note: I choose the above format for the date, but there are other options. See the below examples:
#Here are some examples of other date/time formats:
➜ ~ CURR_DATE=`date +%Y%m%d_%H%M%S`
➜ ~ echo $CURR_DATE
20151215_071400
➜ ~
➜ ~ CURR_DATE=`date +%m/%d/%Y+%H:%M:%S`
➜ ~ echo $CURR_DATE
12/14/2015+21:28:02
➜ ~ CURR_DATE=`date +%m/%d/%Y-%H:%M:%S`
➜ ~
➜ ~ echo $CURR_DATE
12/14/2015-21:28:39
➜ ~ CURR_DATE=`date +%Y/%m/%d-%H:%M:%S`
➜ ~ echo $CURR_DATE
2015/12/14-21:29:00
➜ ~ CURR_DATE=`date +%Y_%m_%d-%H:%M:%S`
➜ ~ echo $CURR_DATE
2015_12_15-07:11:41
➜ ~ CURR_DATE=`date +%Y-%m-%d-%H:%M:%S`
➜ ~ echo $CURR_DATE
2015-12-15-07:11:58
➜ ~ CURR_DATE=`date +%Y.%m.%d_%H.%M.%S`
➜ ~ echo $CURR_DATE
2015.12.15_07.12.31
With the prep work completed, now we just need to edit our cron job. You can use the following command to see what, if any, jobs are currently running:
crontab -l
For our exercise, I am going to assume that we are creating a user cron job. Change to or confirm that you are in your home directory:
➜ ~ cd
➜ ~ pwd
/home/jfhogarty
➜ ~ mkdir cron
➜ cd cron
➜ cron vim db_backup.cron
Now we can take the output from the crontab-generator.org site and paste it into our new cron file:
# Backup the OGNT.io Flex Blogr production database
5 4 * * 0 /usr/bin/pg_dump -Fc flex-production > backup_flex-production_`date +\%Y\%m\%d_\%H\%M\%S` >/dev/null 2>&1
Save the file and exit vim (:wq).
Our last step is to tell cron to read in the new settings so it can run our backup according to the defined schedule.
Tip confirm that cron is running
➜ ~ ps aux | grep crond
jfhogar+ 13109 0.0 0.0 11740 944 pts/0 S+ 11:05 0:00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn crond
➜ ~
A quick review of relevant cron commands:
crontab -l #list current crontab
crontab -r #remove (delete) current crontab
crontab /path/to/file_name.cron #read in new crontab, replacing existing
If you don’t have a current crontab running, assuming the file we created earlier, we can do:
crontab /home/jfhogarty/cron/db_backup.cron
Time to confirm that cron accepted our new tasks:
➜ ~ crontab -l
# Cron tasks to support Connexator Application - connexator.com
*/15 * * * * echo "`date` Calling rake task to Generate ConneXions" >> /home/jfhogarty/cron/gen_connexions.log
*/15 * * * * /bin/bash -l -c 'cd /var/www/connexator.com/connexator && RAILS_ENV=production bundle exec rake gen:connexions >> /dev/null'
➜ ~ crontab /home/jfhogarty/cron/db_backup.cron
➜ ~ crontab -l
# Backup the OGNT.io Flex Blogr production database
5 4 * * 0 /usr/bin/pg_dump -Fc flex-production > backup_flex-production_`date +%Y%m%d_%H%M%S` >/dev/null 2>&1
➜ ~
Notice that I did a crontab -l before executing the new crontab /path/to/file_name.cron command. You can see that I had two tasks running. After running the new crontab command, and then verifying via crontab -l, my previous tasks are no longer configured. Please keep this in mind if you have previous cron jobs scheduled. In my case this is ok as I didn’t need those tasks running. If I did, instead of creating a second cron file, I would have all of my settings in one file.
Learn Something New Everyday
Last Edited by: John on January 05, 2016