Creating a cron job to automate daily backup at shared directory at backup server

1.       Create a directory for SQL backup within mounted directory

su - postgres

cd /mnt/db-backup/
mkdir sqlbackup

2.       Create a shell script with following commands and save it as ‘daily_backup.sh’
within data directory (or anywhere where postgres user has appropriate permissions)

vi /var/lib/PostgreSQL/9.1/data/daily_backup.sh

Add following command in the file (To take backup and include Current Date and Time in the dump file name)

/var/lib/PostgreSQL/9.1/bin/pg_dump -U jnk_ee_dba  empexchangedb > /mnt/db-backup/sqlbackup/$(date +"%Y-%m-%d_%H%M")_empexchangedb_jnk_ee_dba_16.sql

Save and exit the file

3.       Set authentication method to trust (without password) for backup user of database

vi /var/lib/PostgreSQL/9.1/data/pg_hba.conf

Add following line in the list authentication file

# TYPE   DATABASE                USER              ADDRESS     METHOD
local    sampledb                dbuser                         trust

Save and exit the file

4.       Reload the Postgres configuration

/var/lib/PostgreSQL/9.1/bin/pg_ctl –D /var/lib/PostgreSQL/9.1/data reload

5.    Make a cron job entry for time of backup as follows 

crontab –e

Add following line

35 5 * * * /var/lib/PostgreSQL/9.1/data/daily_backup.sh

Save and exit the file

Daily Backup Script will be executed at the specified time and backup will be done at shared directory of backup server.