---

title: "059: Database Backup System"
type: ticket
status: ready
priority: critical
sprint: S001_base_and_mvp1
assignee: CC0
estimated_hours: 2
last_updated: "2026-03-22"
version: "2.0"

059: Database Backup System

Problem

Two Postgres databases on Hetzner with zero backup:

1. Bus DB (deploy-postgres-1) — events, sessions, tokens, OAuth
2. Paperclip DB (deploy-paperclip-db-1) — issues, agents, activity

Solution

Automated pg_dump → restic → Backblaze B2. Encrypted, deduplicated, incremental.

Why restic + Backblaze B2

• restic: single binary, encrypted at rest, deduplication, incremental, pruning policies
• Backblaze B2: object storage designed for backups, Shai already owns account, S3-compatible API
• NOT git: binary dumps bloat repos, GitHub has size limits, wrong tool for this

Implementation

Step 1: Install restic on Hetzner

apt-get install -y restic

Step 2: Create Backblaze B2 bucket

Shai provides:

• B2 Application Key ID
• B2 Application Key
• Bucket name: struxio-db-backups

Step 3: Initialize restic repo

export B2_ACCOUNT_ID="<from Shai>"
export B2_ACCOUNT_KEY="<from Shai>"
restic -r b2:struxio-db-backups init

Save the restic repo password in SOPS-encrypted file.

Step 4: Backup script at /opt/struxio/scripts/backup_databases.sh

#!/bin/bash
set -euo pipefail

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DUMP_DIR=/tmp/struxio-db-dumps
mkdir -p "$DUMP_DIR"

# Source credentials
source /opt/struxio/scripts/.backup_env

# Dump Bus DB
docker exec deploy-postgres-1 pg_dump -U bus -d bus --clean --if-exists | gzip > "$DUMP_DIR/bus_${TIMESTAMP}.sql.gz"

# Dump Paperclip DB
docker exec deploy-paperclip-db-1 pg_dump -U paperclip -d paperclip --clean --if-exists | gzip > "$DUMP_DIR/paperclip_${TIMESTAMP}.sql.gz"

# Backup to B2 via restic
restic -r b2:struxio-db-backups backup "$DUMP_DIR/" --tag "db-backup" --tag "$TIMESTAMP"

# Prune old backups: keep 7 daily, 4 weekly, 3 monthly
restic -r b2:struxio-db-backups forget --keep-daily 7 --keep-weekly 4 --keep-monthly 3 --prune

# Cleanup local dumps
rm -rf "$DUMP_DIR"

echo "[$(date)] Backup complete: $TIMESTAMP" >> /opt/struxio/logs/backup.log

Step 5: Credentials file (SOPS encrypted)

# /opt/struxio/scripts/.backup_env (plaintext, chmod 600)
export B2_ACCOUNT_ID="xxx"
export B2_ACCOUNT_KEY="xxx"
export RESTIC_PASSWORD="xxx"
export RESTIC_REPOSITORY="b2:struxio-db-backups"

Encrypt with SOPS, decrypt at runtime.

Step 6: Cron — twice daily

# /etc/cron.d/struxio-db-backup
0 6,18 * * * struxio /opt/struxio/scripts/backup_databases.sh >> /opt/struxio/logs/backup.log 2>&1

Step 7: Verify

• Run backup manually once
• Check B2 bucket has snapshot
• Run restic snapshots to list
• Test restore: restic restore latest --target /tmp/restore-test

Retention Policy

Period	Keep
Daily	7 days
Weekly	4 weeks
Monthly	3 months

Acceptance Criteria

• restic installed on Hetzner
• B2 bucket created and accessible
• restic repo initialized
• Backup script works (manual test)
• Both DBs backed up successfully
• Cron entry active
• Restore test passes
• Credentials stored securely (SOPS or chmod 600)

---

STRUXIO.ai // Confidential & Proprietary // © 2026