database-backups

Database Backups

Implement comprehensive, automated database backup strategies with tested recovery procedures.

When to Use

• You are deploying a new database and need a backup plan from day one.
• You need to automate nightly or hourly backups for PostgreSQL, MySQL, or MongoDB.
• You want to ship backups to S3-compatible object storage with retention policies.
• You are building or verifying disaster recovery runbooks.

Prerequisites

• Database client tools installed (pg_dump, mysqldump, mongodump).
• AWS CLI or restic for remote storage.
• cron or systemd timers for scheduling.
• An S3 bucket (or S3-compatible endpoint) for offsite backups.

Backup Types

Type	Description	Frequency	Use Case
Full	Complete database copy	Weekly	Baseline for restores
Incremental	Changes since last backup	Daily	Reduce storage and time
Transaction log / WAL	Continuous log shipping	Continuous	Point-in-time recovery (PITR)
Snapshot	Storage-level snapshot (EBS, ZFS)	Daily	Fast full restores

PostgreSQL Backups

Logical Backup with pg_dump

#!/bin/bash
# pg_backup.sh — PostgreSQL logical backup
set -euo pipefail

DB_NAME="mydb"
DB_USER="backup_user"
DB_HOST="localhost"
BACKUP_DIR="/backups/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
FILENAME="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"

mkdir -p "$BACKUP_DIR"

# Custom compressed format (recommended for selective restore)
pg_dump -h "$DB_HOST" -U "$DB_USER" -Fc -Z6 "$DB_NAME" > "$FILENAME"

echo "[$(date)] PostgreSQL backup complete: $FILENAME ($(du -h "$FILENAME" | cut -f1))"

Physical Backup with pg_basebackup

#!/bin/bash
# pg_basebackup.sh — PostgreSQL physical backup for PITR
set -euo pipefail

BACKUP_DIR="/backups/postgres/base_$(date +%Y%m%d)"
mkdir -p "$BACKUP_DIR"

pg_basebackup \
  -h localhost \
  -U replicator \
  -D "$BACKUP_DIR" \
  --wal-method=stream \
  --checkpoint=fast \
  --progress \
  --verbose

echo "[$(date)] Base backup complete: $BACKUP_DIR"