Database Backup

# Full logical backup, custom format (pg_dump compresses -Fc output by default; do not gzip it again)
pg_dump -Fc --no-owner --no-acl -h $DB_HOST -U $DB_USER $DB_NAME | \
  aws s3 cp - s3://backups/pg/$(date +%Y%m%d_%H%M%S).dump

# WAL archiving for point-in-time recovery (large databases)
# postgresql.conf:
#   archive_mode = on
#   archive_command = 'aws s3 cp %p s3://backups/pg-wal/%f'

# Base backup for PITR: in tar format, pg_basebackup writes one .tar.gz per
# tablespace (base.tar.gz, 16384.tar.gz, ...) and the WAL as pg_wal.tar.gz
pg_basebackup -D /backups/base -Ft -z -P -h $DB_HOST -U replication

# One-time: store the credentials in a login path so the password is not in the process list
mysql_config_editor set --login-path=backup --host=$DB_HOST --user=$DB_USER --password

# Full logical backup
mysqldump --login-path=backup --single-transaction --routines --triggers --all-databases \
  | gzip > backup_$(date +%Y%m%d).sql.gz

# Binary log for point-in-time recovery (needs a user with REPLICATION SLAVE privilege)
mysqlbinlog --read-from-remote-server --host=$DB_HOST --raw binlog.000001

# Full backup with oplog for PITR (--oplog needs a replica set or sharded cluster)
mongodump --uri="$MONGO_URI" --oplog --gzip --archive | \
  aws s3 cp - s3://backups/mongo/$(date +%Y%m%d_%H%M%S).archive.gz

# Restore, replaying the oplog to the backup point
mongorestore --uri="$MONGO_URI" --gzip --archive=backup.archive.gz --oplogReplay

# RDB snapshot pulled over the replication protocol; the password comes from the environment
REDISCLI_AUTH="$REDIS_PASSWORD" redis-cli -h $REDIS_HOST --rdb /backups/redis/dump-$(date +%Y%m%d_%H%M%S).rdb

# Download latest backup
# Restore to temporary database
# Run integrity checks (row counts, checksum of critical tables)
# Compare against production counts
# Alert if variance > 0.1%
# Tear down temporary database

Set up automated PostgreSQL backups for my production database (250GB). I need daily full backups stored in S3, WAL archiving for point-in-time recovery, and automated verification that backups are restorable.

#!/bin/bash
# scripts/backup-postgres.sh
set -euo pipefail

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
S3_BUCKET="s3://myapp-backups/postgres"
DB_HOST="${DB_HOST:?required}"
DB_NAME="${DB_NAME:?required}"

echo "[$(date)] Starting backup of ${DB_NAME}..."

# Create base backup with checksums
pg_basebackup \
  -h "$DB_HOST" -U replication \
  -D /tmp/backup_${TIMESTAMP} \
  -Ft -z -P --checkpoint=fast --wal-method=stream

# Upload the base backup (STANDARD_IA suits a copy that is rarely read)
aws s3 cp /tmp/backup_${TIMESTAMP}/base.tar.gz \
  "${S3_BUCKET}/base/${TIMESTAMP}/base.tar.gz" \
  --storage-class STANDARD_IA

# Upload every tar file: one per tablespace plus pg_wal.tar.gz
aws s3 sync /tmp/backup_${TIMESTAMP}/ \
  "${S3_BUCKET}/base/${TIMESTAMP}/" --exclude "*" --include "*.tar.gz"

# Cleanup local
rm -rf /tmp/backup_${TIMESTAMP}

# Record backup metadata
echo "{\"timestamp\":\"${TIMESTAMP}\",\"type\":\"full\",\"size\":\"$(aws s3 ls ${S3_BUCKET}/base/${TIMESTAMP}/ --summarize | tail -1)\"}" >> /var/log/backup-history.jsonl

echo "[$(date)] Backup complete: ${TIMESTAMP}"

Create a disaster recovery runbook for our PostgreSQL database. Include steps for full restore, point-in-time recovery to a specific timestamp, and failover to a read replica.