Simple Postgres & MySQL regular maintenance + backup script

backup

#!/usr/bin/env bash
# Usage: backup.sh type database username host gdrive-parent-folder-id gpg-recipient
# Requires gdrive: https://github.com/prasmussen/gdrive
set -e

timeslot=$(date '+%Y%m%d%H%M')
type=$1
database=$2
user=$3
host=$4
parent_id=$5
gpg_recipient=$6
backup_dir=~/backups/$database

filename=$backup_dir/$database-$timeslot.sql.gz

mkdir -p "$backup_dir"

case $type in
  postgres)
    /usr/bin/vacuumdb -z -h "$host" -U "$user" "$database" > /dev/null 2>&1
    /usr/bin/pg_dump -U "$user" "$database" -h "$host" | gzip -9 | gpg -r "$gpg_recipient" -e -o "$filename.gpg"
    ;;
  mysql)
    /usr/bin/mysql -h "$host" "$database" > /dev/null
    /usr/bin/mysqldump -h "$host" "$database" | gzip -9 | gpg -r "$gpg_recipient" -e -o "$filename.gpg"
    ;;
  *)
    echo 'Unsupported database type. postgres|mysql only'
    exit 1
    ;;
esac

cd "$backup_dir"

rm -rf "$backup_dir/latest"
ln -s "$database-$timeslot.sql.gz.gpg" "$backup_dir/latest"

# Upload the file to Google Drive
~/gdrive upload --no-progress --parent "$parent_id" "$filename.gpg" > /dev/null

# Delete older backups from local storage (they are still kept in Drive)
find . -type f -mtime +6 -exec rm {} \;

# Delete old backups from Google Drive
old_backup_date=$(date +%Y-%m-%d -d '1 year ago')
~/gdrive list --no-header --max 365 --order createdTime --query "'$parent_id' in parents and name contains '$database-' and mimeType = 'application/gpg-signature' and createdTime <= '$old_backup_date'" | awk '{print $1}' | xargs -r -L 1 ~/gdrive delete > /dev/null