max / makenotwork

3.3 KB · 82 lines History Blame Raw

1	#!/bin/bash
2	# Sync database backups to offsite host (astra) via Tailscale.
3	# Called by backup-db.sh after each successful backup.
4	#
5	# Usage: sync-backup-offsite.sh <db_name> <backup_file>
6	# db_name — database name (used for subdir and prune glob)
7	# backup_file — absolute path to the timestamped .sql.gz produced by this run
8	#
9	# On astra, backups land in /opt/backups/mnw/<db_name>/, with a per-DB
10	# latest.sql.gz hard link maintained for downstream pullers (sando).
11	#
12	# Setup on astra (one-time):
13	# mkdir -p /opt/backups/mnw
14	# chown max:max /opt/backups/mnw
15	#
16	# Setup on Hetzner: tailnet ACL grants tag:prod -> max@tag:testing SSH (no
17	# pubkey wrangling — Tailscale SSH bypasses authorized_keys via tailnet cert).
18
19	set -euo pipefail
20
21	DB_NAME="${1:?usage: sync-backup-offsite.sh <db_name> <backup_file>}"
22	BACKUP_FILE="${2:?usage: sync-backup-offsite.sh <db_name> <backup_file>}"
23
24	OFFSITE_HOST="100.106.221.39" # astra (Tailscale IP)
25	OFFSITE_USER="max"
26	OFFSITE_DIR="/opt/backups/mnw/${DB_NAME}"
27	OFFSITE_RETENTION_DAYS=30
28	WAM_URL="${WAM_URL:-http://127.0.0.1:7890}"
29
30	wam_alert() {
31	local title="$1"
32	local body="${2:-}"
33	curl -sf -X POST "$WAM_URL/tickets" \
34	-H "Content-Type: application/json" \
35	-d "{\"title\": \"$title\", \"body\": \"$body\", \"priority\": \"high\", \"source\": \"backup-offsite\"}" \
36	>/dev/null 2>&1 \|\| true
37	}
38
39	if [ ! -f "$BACKUP_FILE" ]; then
40	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): backup file missing: $BACKUP_FILE"
41	exit 0
42	fi
43
44	BASENAME=$(basename "$BACKUP_FILE")
45	SSH_OPTS="-o ConnectTimeout=10 -o StrictHostKeyChecking=accept-new"
46
47	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): Syncing ${BASENAME} to ${OFFSITE_HOST}:${OFFSITE_DIR}"
48
49	# Ensure the per-DB offsite dir exists.
50	ssh ${SSH_OPTS} "${OFFSITE_USER}@${OFFSITE_HOST}" "mkdir -p '${OFFSITE_DIR}'"
51
52	if rsync -e "ssh ${SSH_OPTS}" --timeout=120 \
53	"$BACKUP_FILE" \
54	"${OFFSITE_USER}@${OFFSITE_HOST}:${OFFSITE_DIR}/"; then
55	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): Transfer complete"
56	else
57	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): Transfer FAILED (astra unreachable or SSH error)"
58	wam_alert "Offsite backup sync failed (${DB_NAME})" "rsync to ${OFFSITE_HOST}:${OFFSITE_DIR} failed for ${BASENAME}. Check Tailscale connectivity and SSH auth."
59	exit 0
60	fi
61
62	# Refresh the per-DB latest.sql.gz hard link on astra (atomic temp-then-rename).
63	ssh ${SSH_OPTS} "${OFFSITE_USER}@${OFFSITE_HOST}" "
64	set -e
65	cd '${OFFSITE_DIR}'
66	ln -f '${BASENAME}' latest.sql.gz.new
67	mv -Tf latest.sql.gz.new latest.sql.gz
68	" \|\| echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): WARNING — failed to refresh latest.sql.gz"
69
70	# Prune offsite backups older than retention.
71	DELETED=$(ssh ${SSH_OPTS} "${OFFSITE_USER}@${OFFSITE_HOST}" \
72	"find ${OFFSITE_DIR} -name '${DB_NAME}-*.sql.gz' -mtime +${OFFSITE_RETENTION_DAYS} -delete -print 2>/dev/null \| wc -l" \
73	2>/dev/null \|\| echo "0")
74	if [ "$DELETED" -gt 0 ]; then
75	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): Pruned ${DELETED} backup(s) older than ${OFFSITE_RETENTION_DAYS} days"
76	fi
77
78	TOTAL=$(ssh ${SSH_OPTS} "${OFFSITE_USER}@${OFFSITE_HOST}" \
79	"ls ${OFFSITE_DIR}/${DB_NAME}-*.sql.gz 2>/dev/null \| wc -l" \
80	2>/dev/null \|\| echo "?")
81	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): Total backups on astra: ${TOTAL}"
82