max / makenotwork

3.4 KB · 86 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	WAM_TOKEN="${WAM_TOKEN:-}"
30
31	wam_alert() {
32	local title="$1"
33	local body="${2:-}"
34	local auth=()
35	[ -n "$WAM_TOKEN" ] && auth=(-H "Authorization: Bearer $WAM_TOKEN")
36	curl -sf -X POST "$WAM_URL/tickets" \
37	-H "Content-Type: application/json" \
38	"${auth[@]}" \
39	-d "{\"title\": \"$title\", \"body\": \"$body\", \"priority\": \"high\", \"source\": \"backup-offsite\"}" \
40	>/dev/null 2>&1 \|\| true
41	}
42
43	if [ ! -f "$BACKUP_FILE" ]; then
44	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): backup file missing: $BACKUP_FILE"
45	exit 0
46	fi
47
48	BASENAME=$(basename "$BACKUP_FILE")
49	SSH_OPTS="-o ConnectTimeout=10 -o StrictHostKeyChecking=accept-new"
50
51	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): Syncing ${BASENAME} to ${OFFSITE_HOST}:${OFFSITE_DIR}"
52
53	# Ensure the per-DB offsite dir exists.
54	ssh ${SSH_OPTS} "${OFFSITE_USER}@${OFFSITE_HOST}" "mkdir -p '${OFFSITE_DIR}'"
55
56	if rsync -e "ssh ${SSH_OPTS}" --timeout=120 \
57	"$BACKUP_FILE" \
58	"${OFFSITE_USER}@${OFFSITE_HOST}:${OFFSITE_DIR}/"; then
59	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): Transfer complete"
60	else
61	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): Transfer FAILED (astra unreachable or SSH error)"
62	wam_alert "Offsite backup sync failed (${DB_NAME})" "rsync to ${OFFSITE_HOST}:${OFFSITE_DIR} failed for ${BASENAME}. Check Tailscale connectivity and SSH auth."
63	exit 0
64	fi
65
66	# Refresh the per-DB latest.sql.gz hard link on astra (atomic temp-then-rename).
67	ssh ${SSH_OPTS} "${OFFSITE_USER}@${OFFSITE_HOST}" "
68	set -e
69	cd '${OFFSITE_DIR}'
70	ln -f '${BASENAME}' latest.sql.gz.new
71	mv -Tf latest.sql.gz.new latest.sql.gz
72	" \|\| echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): WARNING, failed to refresh latest.sql.gz"
73
74	# Prune offsite backups older than retention.
75	DELETED=$(ssh ${SSH_OPTS} "${OFFSITE_USER}@${OFFSITE_HOST}" \
76	"find ${OFFSITE_DIR} -name '${DB_NAME}-*.sql.gz' -mtime +${OFFSITE_RETENTION_DAYS} -delete -print 2>/dev/null \| wc -l" \
77	2>/dev/null \|\| echo "0")
78	if [ "$DELETED" -gt 0 ]; then
79	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): Pruned ${DELETED} backup(s) older than ${OFFSITE_RETENTION_DAYS} days"
80	fi
81
82	TOTAL=$(ssh ${SSH_OPTS} "${OFFSITE_USER}@${OFFSITE_HOST}" \
83	"ls ${OFFSITE_DIR}/${DB_NAME}-*.sql.gz 2>/dev/null \| wc -l" \
84	2>/dev/null \|\| echo "?")
85	echo "[$(date -Iseconds)] OFFSITE(${DB_NAME}): Total backups on astra: ${TOTAL}"
86