Automated Backup on Linux using cron jobs (Postgresql)

What Will I Learn?

This post explains how to set up a crontab job that automatically Backup a database (PostgreSQL) in time slots specific.

Requirements

• Operative system based Linux
• PostgreSQL
• cron

Difficulty

• Basic

Tutorial Contents

• What is Cron
• PostgreSQL
• Step by Step

What is Cron

Cron is the name of the program that allows Linux / Unix users to automatically execute commands or scripts at a specific time or date. It is normally used for administrative task commands, in this case we will use it for a simple backup task.

PostgreSQL

It is a very popular database engine in the web environment, especially on the open source side, official website https://www.postgresql.org/.

Step by Step

Create file with nano backup.sh

1. Add interpreter for the script
   #!bin/bash

2. Define the password in the variable used by Postgresql
   export PGPASSWORD="user1"

3. Variable for the File Name with current system date
   FILENAME="/DB/Backup_$(date +%Y%m%d%H%M%S).backup"

4. Command pg_dump to create the backup file.
   pg_dump -U user --format custom --verbose --file $FILENAME DB_NAME
   4.1 Parameters:

   • -U => Username
   • --fromat => File Format
   • --verbose => Show log
   • --file => File name to export
   • DB_NAME => Database to backup

5. delete content of PGPASSWORD
   unset PGPASSWORD

Result

#!bin/bash
export PGPASSWORD="user1" 
FILENAME="/DB/Backup_$(date +%Y%m%d%H%M%S).backup"

pg_dump -U user1 --format custom --blobs --verbose --file $FILENAME DB_NAME

unset PGPASSWORD

Add file on cron
Execute in console: crontab -e
This will allow you to edit or define cron

# m h  dom mon dow   command
0 22 * * * sh /backup.sh > /backup.log  

Cron Definition

• m=> minute
• h=> hour
• Sun=> day of the week
• mon=> month
• dow=> day of the month
• command => command to execute

To show your cron must execute the command
crontab -l

Finally, with this configuration you will have a daily backup every day at 22 hours.

Proof of Work Done

https://gist.github.com/Raul-mz/c25ba589371acde1619ee568e9ba14fb