Monday, April 7, 2008

Database backup script

The script to take a backup of postgres database

Step i)
Write a shell script to dump the database.
Create a .sh file using vi editor inside the /bin as follows...
vi /bin/

1) #!/bin/bash
2) BACKUP_DIR="/home/MyApps/regular_backup"
3) PGHOST=""
4) PGUSER="postgres"
5) /usr/bin/pg_dumpall -h $PGHOST -U $PGUSER | gzip > $BACKUP_DIR/backup.gz

The first line #!/bin/bash is just a declaration that this is a BASH script.

2) BACKUP_DIR="/home/MyApps/regular_backup"
This line tells the target folder where our .gz file to be stored

3) PGHOST=""
4) PGUSER="postgres"
Host IP where our postgres database is running and user name. If some other database have to add password .

5) /usr/bin/pg_dump -h $PGHOST -U $PGUSER | gzip > $BACKUP_DIR/backup.gz
This key statement is dump the database using command[/usr/bin/pg_dumpall] and user name and password[-h $PGHOST -U $PGUSER] and zip it up using gzip and target file backup.gz

after saved this file should the file permission to 755
chmod 755 /bin/

Step ii) Add this script to crontab [Crontab (CRON TABle)is a program that manipulates the CRON daemon] and configure time to run script.

1) Edit crontab file in the directory /usr/bin/crontab file using crontab -e -e for edit/add entry in the crontab file.

2) Add this line in crontab file
10 0 * * * /bin/ >/dev/null 2>&1

This crontab run the script /bin/ on the time frame.

10 0 * * * is 10 minute 0 hours of every day every month end every year run the script which we have created in step 1[/bin/]

Your script is ready and will run and backup the database everyday morning 00:10


Post a Comment