Schedule Auto Backup Database With Crontab

Backing up database is one of the most important part in real world applications. Imagine your system is running over a year and unfortunately you lost the database. It might be a nightmare!!!

This article focuses on Postgresql and VPS. I assumed you already setup postgresql and VPS.

1. SSH to VPS

ssh youruser@yourip

2. Make a tmp directory to store database backup

sudo mkdir tmp

3. Create backup script in tmp directory

cd tmp/
sudo nano pg_backup_script.sh


# in your pg_backup_script.sh

#!/bin/bash
# This script will backup the postgresql database
# and store it in a specified directory

# Constants
BACKUP_DIRECTORY="/home/cb_dev/tmp"

# Date stamp (formated YYYYMMDD)
CURRENT_DATE=$(date "+%Y%m%d")
DB_NAME=$DATABASE\_$CURRENT_DATE
# Database named (command line argument) use pg_dump for targed backup

pg_dump -U dbUser dbName -h 127.0.0.1 | gzip - > $BACKUP_DIRECTORY/$DB_NAME.dump.gz

4. Make sure your script work

cd tmp/
bash pg_backup_script.sh

// you can check in your tmp folder and you will see your backup file.

5. Setup Crontab for scheduling backup

sudo apt install cron

sudo systemctl enable cron

click here for more details.

6. Edit Crontab

crontab -e

It will ask you to choose your favourite editor

# Edit this file to introduce tasks to be run by cron.
#
# Each task to run has to be defined through a single line
# indicating with different fields when the task will be run
# and what command to run for the task
#
# To define the time you can provide concrete values for
# minute (m), hour (h), day of month (dom), month (mon),
# and day of week (dow) or use '*' in these fields (for 'any').
#
# Notice that tasks will be started based on the cron's system
# daemon's notion of time and timezones.
#
# Output of the crontab jobs (including errors) is sent through
# email to the user the crontab file belongs to (unless redirected).
#
# For example, you can run a backup of all your user accounts
# at 5 a.m every week with:
# 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/
#
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command
0 0 * * * cd /home/YOURUSERNAME/tmp && bash pg_backup_script.sh args

0 0 * * * means it will backup your database every day at midnight.

I hope this will help.