How to Set Up Automated Database Backups on Linux (PostgreSQL and MySQL)

How to Set Up Automated Database Backups on Linux (PostgreSQL and MySQL) If your production database isn't being backed up automatically, you're one bad deploy away from losing everything. This is how to set up automated backups that actually work. PostgreSQL Automated Backup The Backup Script sudo nano /usr/local/bin/pg-backup.sh #!/bin/bash set -e BACKUP_DIR = "/var/backups/postgresql" DATE = $( date +%Y%m%d_%H%M%S ) KEEP_DAYS = 7 DB_NAME = " ${ 1 :- all } " # Pass db name or backs up all mkdir -p " $BACKUP_DIR " if [ " $DB_NAME " = "all" ] ; then # Backup all databases pg_dumpall -U postgres | gzip > " $BACKUP_DIR /all_ ${ DATE } .sql.gz" else # Backup specific database pg_dump -U postgres -Fc " $DB_NAME " > " $BACKUP_DIR / ${ DB_NAME } _ ${ DATE } .dump" fi # Remove backups older than KEEP_DAYS find " $BACKUP_DIR " -type f -mtime + $KEEP_DAYS -delete # Verify backup file exists and is not empty LATEST = $( ls -t " $BACKUP_DIR " | head -1 ) if [ ! -s " $BACKUP_DIR / $LATEST " ] ; th