filiptrivan/spiderly/backups

# infrastructure/cloudflare-r2.tf
resource "cloudflare_r2_bucket" "db_backups" {
  account_id = var.cloudflare_account_id
  name       = "<your-app>-db-backups"
  location   = "EEUR"
 
  lifecycle {
    prevent_destroy = true
  }
}

#!/usr/bin/env bash
set -euo pipefail
 
CONTAINER_NAME="<your-app>-postgres-1"
DB_NAME="<your-db>"
DB_USER="postgres"
CLOUDFLARE_ACCOUNT_ID="<account-id>"
S3_BUCKET="s3://<your-app>-db-backups"
R2_ENDPOINT="https://${CLOUDFLARE_ACCOUNT_ID}.r2.cloudflarestorage.com"
RETENTION_DAYS=7
LOCAL_BACKUP_DIR="/var/backups/postgresql"
LOG_FILE="/var/log/<your-app>_pg_backup.log"
 
TIMESTAMP=$(date +%Y-%m-%d_%H%M%S)
BACKUP_FILE="<your-app>_${TIMESTAMP}.sql.gz"
LOCAL_PATH="${LOCAL_BACKUP_DIR}/${BACKUP_FILE}"
 
log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"; }
 
trap 'rc=$?; [[ $rc -ne 0 ]] && rm -f "$LOCAL_PATH" && log "Aborted, removed partial $LOCAL_PATH"; exit $rc' ERR INT TERM
 
mkdir -p "$LOCAL_BACKUP_DIR"
 
if ! docker exec "$CONTAINER_NAME" pg_dump -U "$DB_USER" "$DB_NAME" | gzip > "$LOCAL_PATH"; then
    log "ERROR: pg_dump failed"; exit 1
fi
log "Backup created: ${LOCAL_PATH} ($(du -h "$LOCAL_PATH" | cut -f1))"
 
if ! aws s3 --endpoint-url "$R2_ENDPOINT" cp "$LOCAL_PATH" "${S3_BUCKET}/${BACKUP_FILE}"; then
    log "ERROR: S3 upload failed"; exit 1
fi
log "Uploaded to ${S3_BUCKET}/${BACKUP_FILE}"
 
find "$LOCAL_BACKUP_DIR" -name "<your-app>_*.sql.gz" -mtime +"$RETENTION_DAYS" -delete
 
CUTOFF_ISO=$(date -u -d "-${RETENTION_DAYS} days" +%Y-%m-%dT%H:%M:%SZ)
BUCKET_NAME="${S3_BUCKET#s3://}"
OLD_KEYS=$(aws s3api --endpoint-url "$R2_ENDPOINT" list-objects-v2 \
    --bucket "$BUCKET_NAME" \
    --query "Contents[?LastModified<'${CUTOFF_ISO}'].Key" \
    --output text 2>/dev/null || echo "")
if [[ -n "$OLD_KEYS" && "$OLD_KEYS" != "None" ]]; then
    for KEY in $OLD_KEYS; do
        aws s3 --endpoint-url "$R2_ENDPOINT" rm "${S3_BUCKET}/${KEY}" \
            && log "Deleted remote: ${KEY}" \
            || log "WARN: failed to delete remote ${KEY}"
    done
fi

[default]
aws_access_key_id = <R2 access key>
aws_secret_access_key = <R2 secret>

0 2 * * * root /usr/bin/flock -n /var/lock/<your-app>-pg-backup.lock /usr/local/bin/pg_backup_s3.sh >> /var/log/<your-app>_pg_backup.log 2>&1

#!/usr/bin/env bash
set -euo pipefail
SSH_ALIAS="<your-app>"
CONTAINER="<your-app>-postgres-1"
DB="<your-db>"
REMOTE_DIR="/var/backups/postgresql"
MIN_SAFETY_BYTES=1024
 
trap 'echo "Interrupted — DB may be inconsistent. Latest safety snapshot is in $SSH_ALIAS:$REMOTE_DIR."' INT TERM
 
ssh -o ConnectTimeout=5 "$SSH_ALIAS" "docker exec $CONTAINER pg_isready -U postgres -d $DB" >/dev/null \
    || { echo "Postgres not ready"; exit 1; }
 
mapfile -t BACKUPS < <(ssh "$SSH_ALIAS" "ls -1t $REMOTE_DIR/<your-app>_*.sql.gz 2>/dev/null | xargs -n1 basename")
[[ ${#BACKUPS[@]} -gt 0 ]] || { echo "No backups found"; exit 1; }
for i in "${!BACKUPS[@]}"; do printf "  %2d) %s\n" "$((i+1))" "${BACKUPS[$i]}"; done
read -rp "Pick: " PICK
[[ "$PICK" =~ ^[0-9]+$ ]] && (( PICK >= 1 && PICK <= ${#BACKUPS[@]} )) || { echo "Invalid"; exit 1; }
CHOSEN="${BACKUPS[$((PICK-1))]}"
 
read -rp "Type 'OVERWRITE $DB' to confirm: " CONFIRM
[[ "$CONFIRM" == "OVERWRITE $DB" ]] || { echo "aborted"; exit 1; }
 
SAFETY="<your-app>_pre_restore_$(date +%Y-%m-%d_%H%M%S).sql.gz"
ssh "$SSH_ALIAS" "set -euo pipefail; docker exec $CONTAINER pg_dump -U postgres $DB | gzip > $REMOTE_DIR/$SAFETY"
SIZE=$(ssh "$SSH_ALIAS" "stat -c%s $REMOTE_DIR/$SAFETY")
(( SIZE >= MIN_SAFETY_BYTES )) || { echo "Safety snapshot suspiciously small ($SIZE B) — aborting"; exit 1; }
 
ssh "$SSH_ALIAS" "
    set -euo pipefail
    docker exec $CONTAINER psql -U postgres -d postgres -c \"DROP DATABASE IF EXISTS \\\"$DB\\\" WITH (FORCE);\"
    docker exec $CONTAINER psql -U postgres -d postgres -c \"CREATE DATABASE \\\"$DB\\\";\"
    gunzip -c $REMOTE_DIR/$CHOSEN | docker exec -i $CONTAINER psql -U postgres -d $DB
"
echo "Restored. Safety snapshot at $SSH_ALIAS:$REMOTE_DIR/$SAFETY."