Configuing Automatic PostgreSQL Database Backup Using Cron

28 Jul 2015 First, use crontab,

$ crontab -e

add the following lines at the end of the file

 ####backup toppers sb everyday##########  
   
 # m h dom mon dow             command   
   
   0 0  *    *   *   cd /home/user/db_backup && ./backup > /dev/null 2>&1

where,

 # m h dom mon dow  command

is the format.

m - Minute (0-59)
h - Hour (0-23)
dom - Day of Month (1-31)
mon - Month (1-12)
dow - Day of week (0-6, starting with Sunday)

and the command is the shell command that you want the cron to execute at the specified time.

Here in the above example I've asked cron to execute the command

 cd /home/user/db_backup && ./backup > /dev/null 2>&1

at every dom, mon and dow. That is what for the '*' is used. So, as everyday starts, cron will run the command I've specified.

In the above command in cron, I've asked cron to execute a shell script named backup. I've done this in order to declare some variables or settings needed for backing up the database without complicating the crontab. It will also be easy to maintain different shell scripts for backing up different databases with respective configurations.

In backup file,

$ nano /home/user/db_backup/backup

 #! /bin/bash  
 FILE="/home/user/db_backup/backup_$(date +%Y%m%d_%H%M%S).pgsql"  
 pg_dump --no-password database_name > $FILE

I've created a file name and stored it into FILE variable, then dumped the database into a new file with name FILE. If you are using openscg or something, and if that specific environment is not added in the current environment, then add the following line next to shebang,

 source /opt/postgres/9.1/pg91-openscg.env # for openscg

But, the above setup will end with an error message,

pg_dump: [archiver (db)] connection to database 'database_name' failed: fe_sendauth: no password supplied

as there should be a password supplied in order to authenticate a user to back up the database. As we are automating the backup we can't (I'm not sure here) enter the password interactively every time. So, we've to supply the password in some implicit way. Postgresql gives us an option in the form of a .pgpass file.

.pgpass file should be created in home directory and it should contain the following details,

$ nano ~/.pgpass

 hostname:port:database:username:password

now restart the postgresql and check if the backup works without a password by executing the backup file.

$ chmod +x /home/user/db_backup/backup # making 'backup' executable
$ /home/user/db_backup/backup

This will backup a database in the db_backup directory or any other directory you've specified in backup script file with current date and time.