database-backups – Data & Science skill for AI agents

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.

Confirm source/restore targets, RPO/RTO, retention and rollback. These scripts are partial: write dumps to private temporary files and promote only after successful exit and integrity checks; upload only completed manifests, not every old/partial file recursively. Verify remote checksums and a restore before deleting local copies. Lifecycle replacement affects the whole configured bucket and must preserve other policies/noncurrent versions. Restore-test row counts are application-specific smoke checks, not full recovery proof. Use protected client configuration/secret mechanisms rather than passwords in process arguments; Mongo URI credentials need proper encoding.

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
umask 077

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
umask 077

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"

PostgreSQL Restore

# Restore from custom-format dump
pg_restore -h localhost -U myapp -d mydb --clean --if-exists /backups/postgres/mydb_20250115_020000.dump

# Restore a single table
pg_restore -h localhost -U myapp -d mydb -t orders /backups/postgres/mydb_20250115_020000.dump

# Restore from plain SQL
psql -h localhost -U myapp -d mydb < /backups/postgres/mydb_20250115.sql

MySQL Backups

Logical Backup with mysqldump

#!/bin/bash
# mysql_backup.sh — MySQL logical backup
set -euo pipefail
umask 077

DB_NAME="mydb"
DB_USER="backup_user"
MYSQL_CNF="/etc/backup/mysql.cnf" # root/backup-owned mode 0600, credentials from approved secret storage
BACKUP_DIR="/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
FILENAME="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"

mkdir -p "$BACKUP_DIR"

mysqldump --defaults-extra-file="$MYSQL_CNF" -u "$DB_USER" \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  "$DB_NAME" | gzip > "$FILENAME"

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

Physical Backup with Percona XtraBackup

#!/bin/bash
# xtrabackup.sh — MySQL physical backup
set -euo pipefail
umask 077

BACKUP_DIR="/backups/mysql/full_$(date +%Y%m%d)"

xtrabackup --backup \
  --defaults-extra-file=/etc/backup/mysql.cnf \
  --user=backup_user \
  --target-dir="$BACKUP_DIR"

xtrabackup --prepare --target-dir="$BACKUP_DIR"

echo "[$(date)] XtraBackup complete: $BACKUP_DIR"

MySQL Restore

# Restore from compressed mysqldump
gunzip < /backups/mysql/mydb_20250115_020000.sql.gz | mysql -u root -p mydb

# Restore from XtraBackup
sudo systemctl stop mysql
ls /var/lib/mysql/*  # verify this is the correct data dir first
# Preserve/rename the old data directory under an approved recovery runbook;
# do not recursively delete it to make a restore fit.
xtrabackup --copy-back --target-dir=/backups/mysql/full_20250115
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

MongoDB Backups

Logical Backup with mongodump

#!/bin/bash
# mongo_backup.sh — MongoDB backup
set -euo pipefail
umask 077

MONGO_URI="mongodb://backup_user:${MONGO_BACKUP_PASSWORD}@localhost:27017"
BACKUP_DIR="/backups/mongodb"
DATE=$(date +%Y%m%d_%H%M%S)
TARGET="${BACKUP_DIR}/${DATE}"

mkdir -p "$BACKUP_DIR"

# Full backup with compression
mongodump --uri="$MONGO_URI" --gzip --out="$TARGET"

echo "[$(date)] MongoDB backup complete: $TARGET"

MongoDB Restore

# Restore all databases
mongorestore --config=/etc/backup/mongo-restore.yml \
  --gzip --drop /backups/mongodb/20250115_020000/

# Restore a single database
mongorestore --config=/etc/backup/mongo-restore.yml \
  --gzip --drop --db mydb /backups/mongodb/20250115_020000/mydb/

# Restore a single collection
mongorestore --config=/etc/backup/mongo-restore.yml \
  --gzip --drop --db mydb --collection users \
  /backups/mongodb/20250115_020000/mydb/users.bson.gz

Upload to S3

#!/bin/bash
# s3_upload.sh — Upload backups to S3
set -euo pipefail
umask 077

S3_BUCKET="s3://my-backups"
BACKUP_DIR="/backups"
DATE=$(date +%Y%m%d)

# Upload PostgreSQL backup
aws s3 cp "${BACKUP_DIR}/postgres/" "${S3_BUCKET}/postgres/${DATE}/" \
  --recursive --storage-class STANDARD_IA \
  --sse AES256

# Upload MySQL backup
aws s3 cp "${BACKUP_DIR}/mysql/" "${S3_BUCKET}/mysql/${DATE}/" \
  --recursive --storage-class STANDARD_IA \
  --sse AES256

# Upload MongoDB backup
aws s3 cp "${BACKUP_DIR}/mongodb/" "${S3_BUCKET}/mongodb/${DATE}/" \
  --recursive --storage-class STANDARD_IA \
  --sse AES256

echo "[$(date)] S3 upload complete for ${DATE}"

S3 Lifecycle Policy for Retention

{
  "Rules": [
    {
      "ID": "BackupRetention",
      "Status": "Enabled",
      "Filter": { "Prefix": "" },
      "Transitions": [
        { "Days": 30, "StorageClass": "GLACIER" }
      ],
      "Expiration": { "Days": 365 }
    }
  ]
}

aws s3api put-bucket-lifecycle-configuration \
  --bucket my-backups \
  --lifecycle-configuration file://lifecycle.json

Restic Backup (Encrypted, Deduplicated)

# Initialize a restic repository on S3
export AWS_ACCESS_KEY_ID="AKIA..."
export AWS_SECRET_ACCESS_KEY="..."
export RESTIC_PASSWORD="..." # set a strong restic password via your vault
export RESTIC_REPOSITORY="s3:s3.amazonaws.com/my-backups-restic"

restic init

# Backup the local backup directory
restic backup /backups/postgres /backups/mysql /backups/mongodb

# List snapshots
restic snapshots

# Prune old snapshots — keep 7 daily, 4 weekly, 6 monthly
restic forget --keep-daily 7 --keep-weekly 4 --keep-monthly 6 --prune

# Restore a snapshot
restic restore latest --target /restore/

Cron Schedules

# /etc/cron.d/db-backups

# PostgreSQL: nightly at 02:00
0 2 * * * backup /opt/scripts/pg_backup.sh >> /var/log/backup-pg.log 2>&1

# MySQL: nightly at 02:30
30 2 * * * backup /opt/scripts/mysql_backup.sh >> /var/log/backup-mysql.log 2>&1

# MongoDB: nightly at 03:00
0 3 * * * backup /opt/scripts/mongo_backup.sh >> /var/log/backup-mongo.log 2>&1

# Upload to S3: daily at 04:00
0 4 * * * backup /opt/scripts/s3_upload.sh >> /var/log/backup-s3.log 2>&1

# Local cleanup: keep 7 days of local backups
0 5 * * * backup find /backups -type f -mtime +7 -delete >> /var/log/backup-cleanup.log 2>&1

# Restic prune: weekly on Sunday at 06:00
0 6 * * 0 backup /opt/scripts/restic_prune.sh >> /var/log/backup-restic.log 2>&1

Automated Recovery Testing

#!/bin/bash
# verify_backup.sh — weekly restore test
set -euo pipefail
umask 077

BACKUP_FILE=$(ls -t /backups/postgres/mydb_*.dump | head -1)

echo "[$(date)] Starting backup verification with $BACKUP_FILE"

# Spin up a temporary PostgreSQL container
docker run -d --name pg-restore-test \
  -e POSTGRES_USER=testuser \
  -e POSTGRES_PASSWORD=testpass \
  -e POSTGRES_DB=testdb \
  postgres:16-alpine

# Wait for container to be ready
trap 'docker rm -f pg-restore-test >/dev/null 2>&1 || true' EXIT
ready=false
for attempt in {1..30}; do
  if docker exec pg-restore-test pg_isready -U testuser; then ready=true; break; fi
  sleep 2
done
[[ "$ready" == true ]] || { echo "Restore container did not become ready" >&2; exit 1; }

# Copy backup 

… (full content is in the download package)