Repository Public
1#!/bin/bash2# =============================================================================3# SP BOUCHER | INFRASTRUCTURE & AUTOMATION4# ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING5# =============================================================================6#7# Project : Sheet8# File : scripts/backup.sh9# Module : scripts10# Repository : https://git.spboucher.ai/sheet11# Project Website : https://sheet.spboucher.ai12# Created : 2026-11-1713# Last Modified : 2026-11-1714#15# Author : Simon-Pierre Boucher16# Position : Professor of Finance17# Institution : Université du Québec en Outaouais (UQO)18# Organization : SP Boucher19# Website : https://spboucher.ai20# Git : https://git.spboucher.ai21#22# Preferred Contact: contact@spboucher.ai23# Technical Contact: dev@spboucher.ai24# Security/Admin : admin@spboucher.ai25#26# Copyright (c) 202627# Simon-Pierre Boucher28# All rights reserved where applicable. This file is governed by the29# applicable project license, copyright notices and relevant law.30# Existing third-party rights and notices remain unaffected.31#32# License : None specified (no license file in this repository)33#34# Do not embed credentials, private keys, access tokens,35# certificates or confidential personal information.36#37# =============================================================================38# END OF SP BOUCHER SOURCE HEADER39# =============================================================================4041# Sheet backup. Run daily (PM2 cron). Two parts, both encrypted with AES-256 (openssl, PBKDF2):42# 1. database — pg_dump (custom format) → $BACKUP_DIR/db/sheet-YYYYmmdd-HHMMSS.dump.enc43# 2. storage — tar of every stored file → $BACKUP_DIR/storage/sheet-YYYYmmdd-HHMMSS.tar.enc44# Archives older than BACKUP_KEEP_DAYS (default 30) are removed. Each new archive is decrypted and read back before45# the run is declared successful. Restore: scripts/restore.sh — see docs/BACKUP_RESTORE.md46set -euo pipefail47cd "$(dirname "$0")/.."48if [ -f .env ]; then49 while IFS='=' read -r k v; do50 case "$k" in DATABASE_URL|BACKUP_DIR|BACKUP_PASSPHRASE_FILE|STORAGE_ROOT|BACKUP_KEEP_DAYS)51 v="${v%\"}"; v="${v#\"}"; [ -n "${!k:-}" ] || export "$k=$v" ;;52 esac53 done < .env54fi55: "${DATABASE_URL:?DATABASE_URL missing}"; : "${BACKUP_DIR:?BACKUP_DIR missing}"; : "${BACKUP_PASSPHRASE_FILE:?BACKUP_PASSPHRASE_FILE missing}"; : "${STORAGE_ROOT:?STORAGE_ROOT missing}"56export PATH="/opt/homebrew/opt/postgresql@17/bin:/opt/homebrew/bin:$PATH"57[ -s "$BACKUP_PASSPHRASE_FILE" ] || { echo "passphrase file missing or empty" >&2; exit 1; }58umask 07759mkdir -p "$BACKUP_DIR/db" "$BACKUP_DIR/storage"; chmod 700 "$BACKUP_DIR"60stamp="$(date +%Y%m%d-%H%M%S)"61enc() { openssl enc -aes-256-cbc -pbkdf2 -iter 200000 -salt -pass "file:$BACKUP_PASSPHRASE_FILE" -out "$1"; }62dec() { openssl enc -d -aes-256-cbc -pbkdf2 -iter 200000 -pass "file:$BACKUP_PASSPHRASE_FILE" -in "$1"; }6364db="$BACKUP_DIR/db/sheet-$stamp.dump.enc"65pg_dump --format=custom --no-owner --no-privileges "$DATABASE_URL" | enc "$db.tmp"; mv "$db.tmp" "$db"66# pg_restore stops reading once it has the table of contents; the rest of the stream is drained so that the67# decryption can finish (and report a bad passphrase or a truncated file) instead of dying on a closed pipe.68dec "$db" | { pg_restore --list >/dev/null; rc=$?; cat >/dev/null; exit $rc; }6970st="$BACKUP_DIR/storage/sheet-$stamp.tar.enc"71mkdir -p "$STORAGE_ROOT"72tar -C "$STORAGE_ROOT" --exclude='*.part' --exclude='.probe' -cf - . | enc "$st.tmp"; mv "$st.tmp" "$st"73files=$(dec "$st" | tar -tf - | grep -vc '/$' || true)7475# CBC gives secrecy, not integrity: each archive gets an HMAC-SHA256 (same passphrase) that restore.sh checks first.76for f in "$db" "$st"; do openssl dgst -sha256 -hmac "$(cat "$BACKUP_PASSPHRASE_FILE")" -r "$f" | cut -d' ' -f1 > "$f.hmac"; done77/usr/bin/find "$BACKUP_DIR/db" "$BACKUP_DIR/storage" -name 'sheet-*.hmac' -mtime +"${BACKUP_KEEP_DAYS:-30}" -delete78/usr/bin/find "$BACKUP_DIR/db" "$BACKUP_DIR/storage" -name 'sheet-*.enc' -mtime +"${BACKUP_KEEP_DAYS:-30}" -delete79printf '{"at":"%s","database":"%s","storage":"%s","files":%s}\n' "$(date -u +%Y-%m-%dT%H:%M:%SZ)" "$(basename "$db")" "$(basename "$st")" "$files" > "$BACKUP_DIR/last-backup.json.tmp"80mv "$BACKUP_DIR/last-backup.json.tmp" "$BACKUP_DIR/last-backup.json"81echo "backup ok: $(basename "$db") ($(du -h "$db" | cut -f1)), $(basename "$st") ($files files)"82