esh-pfi-infrastructure/configs/restic/esh-vm-db/README.md

# restic / esh-vm-db

**Two-database host** at the ESH site (PostgreSQL 15 + MongoDB). Covered
at the VM-image layer by PBS-ANA via esh-pve (or whichever ESH
hypervisor owns this VM — confirm on next inventory pass). This restic
profile adds DB-level granularity via pre-backup dumps.

## What's backed up

| Path | Purpose |
|---|---|
| `/etc` | Host config — systemd, ssh, chrony, apt, pg_hba.conf, mongod.conf |
| `/root` | Root's ad-hoc scripts, shell history, ssh keys |
| `/home` | User homes (lkraven + any DB-admin locals) |
| `/var/lib/restic/stage` | **pg_dumpall.sql.gz** + **mongodump/** produced by pre-backup.sh |

## What's **not** backed up (by design)

- **`/var/lib/postgresql`** — raw PGDATA. Live-capture risk;
  `pg_dumpall` in pre-backup covers it consistently.
- **`/var/lib/mongodb`** — raw mongo dbPath. Same reasoning;
  `mongodump` covers it.
- NFS mount `/mnt/backup` (from esh-nas — not ours to mirror).

## Pre-backup hook

`pre-backup.sh` runs as root before restic. It:

1. Checks `pg_isready` on :5432 — if OK, runs `pg_dumpall` piped
   through gzip to `$STAGE/pg_dumpall.sql.gz`
2. Checks mongo ping via `mongosh` — if OK, runs `mongodump` into
   `$STAGE/mongodump/`

PostgreSQL uses the local Unix socket and peer authentication as postgres,
not TCP localhost. Dumps are staged before replacement. If either DB dump
fails, the hook returns nonzero and aborts the backup, preserving that DB's
previous dump. The two databases are not a single transactional snapshot.
`RESTIC_STAGE_DIR` supports isolated regression tests.

## Repair verified 2026-09-12

Weekly check failed September 6 on a repository connection timeout after boot.
Nightly backup returned success despite PostgreSQL TCP authentication failures,
reusing a dump last modified April 23. Fixed socket authentication, propagated
both DB failures, and added network-online ordering plus bounded retries to
both services (5-minute delay, maximum 3 starts per hour).

Deploy with `playbooks/esh-vm-db-restic-repair.yaml`. Service drop-ins survive
regeneration of resticprofile's main units. Previous hook and PG dump retained
under `/var/lib/restic/repair-20260912/` (root-only).

Fresh snapshot `bc5eeaff` at 07:01 PDT contains today's 3,460,215-byte compressed
PG dump. Retrieved FROM repository, decompressed successfully, and verified its
cluster-dump completion marker. This is not a full database restore test.
Mongo dump also completed. Weekly check rerun at 07:02 passed: 99 snapshots,
configured 10% data sample (19 packs), no errors. Both jobs Result=success,
no failed systemd units, timers active, PostgreSQL/MongoDB remain active.
Inactive/dead between scheduled runs is normal for these finite jobs.

Tests: `python3 configs/restic/esh-vm-db/test_pre_backup.py` — three passing
regressions for successful peer-auth dump and preservation/failure propagation
for each database. No DB authentication policy or service restarts changed.

## Deploy (one-time)

### 1. Create rest-server-ana htpasswd entry

**Do NOT use `sudo` for .htpasswd writes on ana-docker.** The file is
NFS-mounted from ana-nas and owned by uid 1000 (the rest-server user,
which equals lkraven). Sudo-root on the client gets squashed to
nobody on the NFS server and can't read/write the file. lkraven
writes it natively, using the docker group for the bcrypt helper.

```bash
# Pick password in password manager first
HTPW='<new-pw-saved-to-pw-manager>'

ssh -t ana-docker "docker run --rm httpd:2.4-alpine htpasswd -nbB esh-vm-db '$HTPW' | \
                   tee /tmp/htline.txt > /dev/null && \
                   sed -i '/^esh-vm-db:/d' /mnt/backup/restic/repo/ana/.htpasswd && \
                   cat /tmp/htline.txt >> /mnt/backup/restic/repo/ana/.htpasswd && \
                   rm /tmp/htline.txt && \
                   grep ^esh-vm-db: /mnt/backup/restic/repo/ana/.htpasswd && \
                   docker restart rest-server"
unset HTPW
```

### 2. Install secrets on esh-vm-db

```bash
ssh -t esh-vm-db 'sudo install -d -o root -g root -m 0700 /etc/restic /var/lib/restic /var/lib/restic/stage'

# restic.env — URL-encode the password if it has special chars
ssh -t esh-vm-db "sudo bash -c '
  read -sp \"htpasswd pw for rest-server-ana: \" HTPW; echo
  cat > /etc/restic/restic.env <<EOF
RESTIC_REPOSITORY=rest:http://esh-vm-db:\$HTPW@10.250.50.70:8000/esh-vm-db/
EOF
  chmod 600 /etc/restic/restic.env
'"

# Repo passphrase (prints once — save to password manager)
ssh -t esh-vm-db 'sudo bash -c "
  openssl rand -base64 48 | tr -d \"\\n\" > /etc/restic/password
  chmod 600 /etc/restic/password
  echo === SAVE THIS TO PASSWORD MANAGER NOW ===
  cat /etc/restic/password
  echo
"'
```

### 3. Initialize the repo

```bash
ssh -t esh-vm-db 'sudo bash -c "
  set -a; . /etc/restic/restic.env; set +a
  RESTIC_PASSWORD_FILE=/etc/restic/password restic init
"'
```

### 4. Install prerequisites (restic, resticprofile, mongosh client)

```bash
ssh -t esh-vm-db 'which restic || sudo apt-get install -y restic; \
                  which mongosh || echo "NOTE: mongosh not found; pre-backup mongo ping will fail safely — install via MongoDB APT repo if needed"; \
                  curl -sfL https://raw.githubusercontent.com/creativeprojects/resticprofile/master/install.sh | sudo sh -s -- -b /usr/local/bin; \
                  /usr/local/bin/resticprofile --version'
```

### 5. Deploy profile + hook

```bash
scp configs/restic/esh-vm-db/profiles.yaml esh-vm-db:/tmp/
scp configs/restic/esh-vm-db/pre-backup.sh esh-vm-db:/tmp/

ssh -t esh-vm-db 'sudo install -o root -g root -m 0644 /tmp/profiles.yaml /etc/restic/profiles.yaml && \
                  sudo install -o root -g root -m 0755 /tmp/pre-backup.sh /etc/restic/pre-backup.sh && \
                  rm /tmp/profiles.yaml /tmp/pre-backup.sh'
```

### 6. Schedule + verify

```bash
ssh -t esh-vm-db 'sudo resticprofile --config /etc/restic/profiles.yaml schedule --all && \
                  systemctl list-timers "resticprofile*" --no-pager'

# First manual run
ssh -t esh-vm-db 'sudo resticprofile --config /etc/restic/profiles.yaml backup --verbose'
```

Expect first run to land ~50-200 MB (mostly the mongodump directory + pg_dumpall).
Cross-check from Backrest UI on ana-docker.

## Restore

### Full host config

```bash
ssh -t esh-vm-db 'sudo resticprofile --config /etc/restic/profiles.yaml restore latest --target /tmp/restore --path /etc'
```

### Just the PG dump

```bash
ssh -t esh-vm-db 'sudo resticprofile --config /etc/restic/profiles.yaml restore latest --target /tmp/restore --path /var/lib/restic/stage/pg_dumpall.sql.gz'
# Then: gunzip + psql < pg_dumpall.sql
```

### Just a mongo DB

```bash
ssh -t esh-vm-db 'sudo resticprofile --config /etc/restic/profiles.yaml restore latest --target /tmp/restore --path /var/lib/restic/stage/mongodump'
# Then: mongorestore /tmp/restore/var/lib/restic/stage/mongodump/
```

## Gotchas

- **mongosh must be installed** or the mongo pre-backup step silently
  skips (logged as WARN). Install from the MongoDB APT repo if not
  already present — the stock Debian `mongodb-clients` package is
  out of date and doesn't include `mongosh`.
- **Mongo authentication** — if mongod ever gets auth enabled (it's
  currently open to 0.0.0.0 with no auth, which is its own concern),
  `mongodump` will need `--username/--password` flags. Reference in
  pre-backup.sh when that change happens.
- **pg_hba.conf** — `pg_dumpall` requires local postgres superuser
  access. Currently works via `sudo -u postgres` + peer auth on the
  local socket. If `pg_hba.conf` ever changes peer → md5 for local,
  the hook needs a `~postgres/.pgpass` entry.