Website Database Backup Setup

Setting up database backup for website

Database backup is insurance against data loss from hardware failure, accidental deletion, ransomware attack. Strategy 3-2-1: three copies, two different media, one offsite.

PostgreSQL: automated backup script

#!/bin/bash
DB_NAME="myapp"
BACKUP_DIR="/var/backups/postgresql"
S3_BUCKET="s3://myapp-backups/postgresql"
TIMESTAMP=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql.gz"

pg_dump -U "$DB_NAME" "$DB_NAME" | gzip -9 > "$BACKUP_FILE"

aws s3 cp "$BACKUP_FILE" "${S3_BUCKET}/${DB_NAME}_${TIMESTAMP}.sql.gz" \
  --storage-class STANDARD_IA

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +7 -delete

MySQL/MariaDB backup

mysqldump --single-transaction --routines --triggers \
  myapp | gzip -9 > "/var/backups/mysql/myapp_$(date +%Y-%m-%d_%H-%M-%S).sql.gz"

Laravel Spatie Backup

Package spatie/laravel-backup automates database and file backup:

// config/backup.php
return [
    'backup' => [
        'name' => 'myapp',
        'source' => [
            'databases' => ['mysql'],
            'files' => [
                'include' => [storage_path('app')],
            ],
        ],
        'destination' => [
            'disks' => ['s3'],
        ],
    ],
    'cleanup' => [
        'keep_all_backups_for_days' => 7,
        'keep_daily_backups_for_days' => 30,
        'keep_weekly_backups_for_weeks' => 8,
        'keep_monthly_backups_for_months' => 4,
    ],
];

php artisan backup:run
php artisan backup:clean

Backup verification

Test that backup can be restored:

LATEST=$(ls -t /var/backups/postgresql/*.sql.gz | head -1)

gunzip -c "$LATEST" | psql -U postgres -d myapp_test

USERS=$(psql -U postgres -d myapp_test -t -c "SELECT COUNT(*) FROM users;")
if [ "$USERS" -gt 0 ]; then
    echo "Backup verification OK"
    curl -fsS https://hc-ping.com/your-uuid > /dev/null
fi

psql -U postgres -c "DROP DATABASE myapp_test;"

Implementation Timeline

Automated backup of PostgreSQL/MySQL to S3 with rotation: 1–2 days. With restore verification and Slack notifications: 2–3 days.