Configure Automatic DB Backups with email alert notifications

We all know the importance of highly available databases. The DB downtime can lead to unimaginable losses in revenue and reputation as well.

This guide will demonstrate the easiest way to configure database backups locally and sending alert notification after each run. Ideally we should always backup mission critical databases on to remote servers.

My setup:

OS: Ubuntu 20.04 LTS Server
DB: Postgres psql (PostgreSQL) 12.6 (Ubuntu 12.6–0ubuntu0.20.04.1)
Application: Running an open-source IPAM/DCIM application called NetBox.
DB Name: netbox

Backup Script

Following is my backup script which will take the netboxdatabase backup and save the sql dump file locally at /opt/netbox/dumps/ location.

PostgreSQL provides the pg_dump utility to back up databases.

#!/bin/bash
# You should eventually backup remotely
# Backup Folder name
backupfolder=/opt/netbox/dumps
# Notification email address
recipient_email=test_user@example.com
# MySQL user
user=postgres
sudo -u $user /usr/bin/pg_dump netbox > $backupfolder/netbox.$(date +%Y%m%d-%H%M%S).sql
if [[ $? -eq "0" ]]; then
echo -e  "NetBox Data backup is successful" | mail -s "NetBox db backup is successful" $recipient_email
else
echo "Failed to produce NetBox data backup" | mail -s "Please have a look. NetBox db backup task failed!" $recipient_email
fi

To backup all the databases you can use pg_dumpall command.

Configure Email Alerting

To send email notifications we need to configure Postfix on a null client.

A null client is a machine that can only send mail. It receives no mail from the network, and it does not deliver any mail locally. A null client typically uses POP, IMAP or NFS for mailbox access.

Steps to configure Alerting:

1. Install mailutils and postfixpackage.

apt-get install mailutils postfix

2. Configure /etc/postfix/main.cf

Configure following parameters in postfix main.cf file.

myhostname = labs-netbox.example.com
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
myorigin = example.com
mydestination =
relayhost = smtp.example.com
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = loopback-only
inet_protocols = all

3. Restart the postfix service

systemctl restart postfix

Below is the email alert notification sample.

Schedule the backup task to run daily at midnight

root@labs-netbox:/opt/netbox# crontab -l
@daily /opt/netbox/backup.sh

The above crontab entry make sure that the backup script runs every midnight.

Delete old backups

Along with the above I have scheduled a backup retention task as well. All the database backup files older than 30 days will be deleted.

root@labs-netbox:/opt/netbox# crontab -l
@daily /opt/netbox/backup.sh
0 0 * * 0 /usr/bin/find /opt/netbox/dumps/ -type f -mtime +30 -exec rm -rf {} +

That’s all!

Hope you like the tutorial. Stay tuned and don’t forget to provide your feedback in the response section.

Happy Learning!