feat: database backup & restore automation

[ericfitz]

Need a way to backup the database periodically (and restore).

Need to engineer it in a way that the database is guaranteed to be in a consistent state, or at least that a full restore of the resultant backup, even to an empty database, will result in a consistent state.

[bad-antics]

Here's a production-ready database backup/restore implementation:

1. PostgreSQL Backup Script (backup.sh):

#!/bin/bash
set -euo pipefail

DB_NAME="${DB_NAME:-app_db}"
DB_USER="${DB_USER:-postgres}"
BACKUP_DIR="/backups"
RETENTION_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql.gz"

mkdir -p "$BACKUP_DIR"

echo "Starting backup of $DB_NAME..."
pg_dump -U "$DB_USER" -d "$DB_NAME" \
    --format=custom \
    --no-owner \
    --compress=9 \
    | gzip > "$BACKUP_FILE"

if gzip -t "$BACKUP_FILE" 2>/dev/null; then
    echo "Backup verified: $BACKUP_FILE"
else
    echo "Backup failed!"
    exit 1
fi

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

2. Restore Script (restore.sh):

#!/bin/bash
set -euo pipefail

BACKUP_FILE="$1"
DB_NAME="${DB_NAME:-app_db}"
DB_USER="${DB_USER:-postgres}"

dropdb -U "$DB_USER" --if-exists "$DB_NAME"
createdb -U "$DB_USER" "$DB_NAME"

gunzip -c "$BACKUP_FILE" | pg_restore -U "$DB_USER" -d "$DB_NAME" --no-owner

echo "Restore complete!"

3. Automated Cron:

0 2 * * * postgres /opt/scripts/backup.sh >> /var/log/backup.log 2>&1

4. Consistency Guarantees:

• pg_dump uses MVCC snapshot (point-in-time consistent)
• Custom format allows parallel restore
• Add WAL archiving for point-in-time recovery

Let me know if you need MySQL/MongoDB versions!