Backup PostgreSQL database and send backup to email on Linux

Please note that the system I am using is Fedora Core 4 but it should work on any Linux system with the proper mail service activated.

Install MUTT

To be able to attach files to e-mails and then send them you need a mail client.
I chose MUTT because it is very simple to install and use.
Mutt is a small but very powerful text-based mail client for Unix operating system. http://www.mutt.org/
If you are using YUM you could very easily install MUTT by typing:

yum install mutt.i386

Or you could download the MUTT package from the website and manually install.
After installation no other configuration is necessary, MUTT just runs from the box.

Backup and e-mail bash script

The next step is creating the bash script that will backup all the databases, create the archive, append the date of the backup and e-mail it to a specified address.
To create a script login to your server and type the following commands:

vi /bin/backup-script.sh

Note that this will only work if you have the VI editor installed, if you do not have VI you can use any editor you prefer, what you need to do is create a .sh file and type your script.

To start of here is the actual script:

#!/bin/bash
BACKUP_DIR="/home/pg-bkp"
PGHOST="/tmp"
PGUSER="postgres"
time=`date '+%d'-'%m'`
/usr/bin/pg_dumpall -h $PGHOST -U $PGUSER | gzip > $BACKUP_DIR/backup-$time.gz
sleep 10
mutt -s "PostgreSQL Backup ($time)" -n -F /dev/null -a /home/pg-bkp/backup-$time.gz This e-mail address is being protected from spam bots, you need JavaScript enabled to view it < /dev/null

Let us assume you are using VI as your default editor, after you used vi /bin/backup-script.sh you need to copy the script and paste it in the editor. To paste in VI you could right click with your mouse or use Shift+Insert.
After you pasted the script in your backup-script.sh file, you need to save and quit. To save and quit in VI you need to press the ESC (Escape) key, and then type:

:wq!

and Enter. That’s it, you now have a working SHELL script that if executed will create a back-up of your PostgreSQL database and send it to your e-mail.

Now let’s just have a quick look at the script so we can understand it better.

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

The second, third and forth lines

BACKUP_DIR="/home/pg-bkp"
PGHOST="/tmp"
PGUSER="postgres"

are used for declaring variables the script needs to know like where to add the backups, the PostgreSQL host and username.

In the next line time=`date '+%d'-'%m'` we are creating a new variable called time witch gets the current date and converts it to the day-month format.

In the next line of code /usr/bin/pg_dumpall -h $PGHOST -U $PGUSER | gzip > $BACKUP_DIR/backup-$time.gz we issue the command to backup all databases pg_dumpall and create an archive of the files gzip > and append the current date to that archive name.

The next line sleep 10 is just a safety measure that makes the script wait 10 seconds until it passes to the next line of code. Say for example you have a number of databases filled with lots of data, the backup and archive process could take some time.

In the last line mutt -s "PostgreSQL Backup ($time)" -n -F /dev/null -a /home/pg-bkp/backup-$time.gz This e-mail address is being protected from spam bots, you need JavaScript enabled to view it < /dev/null we are using mutt to send an e-mail containing the subject “PostgreSQL Backup date” -s "PostgreSQL Backup ($time)" and attach the archive to the e-mail -a /home/pg-bkp/backup-$time.gz .

Now that we have our script ready we need to give it EXECUTE permissions so it create the PostgreSQL backup archive, append the date and send an e-mail using MUTT.
To give the script execute permissions use one of the following commands:

chmod +x/bin/backup-script.sh
chmod 755 /bin/backup-script.sh

Run the script every day at midnight using Crontab

Now that we have our script we need to make everything even more automated and just sit back and relax as our PostgreSQL backup script sends us an e-mail containing the backup archive everyday at midnight.
To achieve this we just create a simple Cron job. To add a Crontab job you need enter crontab by using crontab –e which is short for Edit Crontab so you can add a new task. You could also use our crontab tutorial for a better understanding of cron jobs. After you issued the command just paste the following code inside, replacing the bath to the file with your own path.

0 0 * * * /bin/backup-script.sh > /dev/null 2>&1

And then just save and quit using :wq!

If you have any more questions or misunderstandings please feel free to comment.

Subscribe now via RSS feed and get all the new tutorials