Script 13 · Backup Automation

13. Auto Backup and Upload to GDrive (Fixed & Secure)

Creates a cron-driven PostgreSQL backup job that uploads compressed custom-format dumps to Google Drive using rclone.

Category: Backup Automation Risk: Medium Lines: calculating Language: Bash / Linux
Back to index

What this script does

  • Automate database backups every 8 hours.
  • Remove local backup after successful upload.
  • Keep operational logs in /var/log/backup_erupe.log.

Prerequisites

  • PostgreSQL client tools
  • Working rclone gdrive remote
  • Correct DB host/port/user/password
  • Cron service

Execution flow

  1. Writes /usr/local/bin/auto_backup_erupe.sh
  2. Sets root-only permission
  3. Installs deduplicated cron entry
  4. Runs pg_dump then rclone copy

Validation checklist

  • crontab -l
  • tail /var/log/backup_erupe.log
  • rclone ls gdrive:Erupe_DB_Backup

Operational cautions

  • DB password is stored in the backup script.
  • Local file is deleted after upload; failed uploads need monitoring.
  • Use a restricted DB user where possible.

Original script notes

ℹ️ Script Info: Creates a scheduled cron job that automatically dumps your PostgreSQL database and uploads it securely to Google Drive via rclone every 8 hours, ensuring no data loss.

Script source 
cat << 'EOF' > /usr/local/bin/auto_backup_erupe.sh
#!/bin/bash

# --- CONFIGURATION ---
DB_NAME="erupe"
DB_USER="postgres"
DB_PASS="Qwerty333"
DB_HOST="127.0.0.1"
DB_PORT="5444"
BACKUP_DIR="/home/erupebackup"
# Fix: Using TZ=Asia/Jakarta so the filename matches WIB even if the server is UTC
# Fix: Format Y-m-d_H-M-S for easy sorting and safe from illegal characters (:)
TIMESTAMP=$(TZ=Asia/Jakarta date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE="rain_database_${TIMESTAMP}.sql"
RCLONE_DEST="gdrive:Erupe_DB_Backup"

# 1. Create folder if missing
mkdir -p "$BACKUP_DIR"

# 2. Database Backup Process
export PGPASSWORD="$DB_PASS"

# Using full path for pg_dump to be safe in cron environment
if /usr/lib/postgresql/*/bin/pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -Fc -d "$DB_NAME" -f "$BACKUP_DIR/$BACKUP_FILE"; then
    echo "[SUCCESS] Database backup created: $BACKUP_FILE"

    # 3. Upload to Google Drive via Rclone
    # Using full path for rclone (adjust if manually installed, usually /usr/bin/rclone)
    if rclone copy "$BACKUP_DIR/$BACKUP_FILE" "$RCLONE_DEST"; then
        echo "[SUCCESS] Upload to Google Drive successful."
        
        # 4. Remove local file (Cleanup)
        rm "$BACKUP_DIR/$BACKUP_FILE"
        echo "[INFO] Local backup file deleted to save space."
    else
        echo "[ERROR] Failed to upload to Google Drive."
    fi

else
    echo "[ERROR] Failed to perform database backup."
fi

# Security Cleanup
unset PGPASSWORD
EOF

# 1. Set permissions so only root can read (due to DB password) and execute
chmod 700 /usr/local/bin/auto_backup_erupe.sh

# 2. Setup Cronjob (Anti-Duplication)
# Check if cron already exists. If not, add it.
CRON_CMD="0 */8 * * * /usr/local/bin/auto_backup_erupe.sh >> /var/log/backup_erupe.log 2>&1"
(crontab -l 2>/dev/null | grep -v "auto_backup_erupe.sh"; echo "$CRON_CMD") | crontab -

echo "[SUCCESS] Backup Setup Complete! The script will run every 8 hours."
Previous Next
Done