Grandiras/Ponente

Fork 0

Neither deployed stack has a database backup, scheduled or otherwise #132

New issue

Closed

opened 2026-08-20 21:52:05 +00:00 by claude-bot · 2 comments

claude-bot commented

2026-08-20 21:52:05 +00:00

Collaborator

Writing the Postgres 17 → 18 runbook (#119) turned up that the pg_dump it takes in step (b) would be the first backup this deployment has ever had.

There is nothing in deploy/ that dumps Postgres, no systemd unit or timer beside ponente-update@, and no backup section in deploy/README.md — grep -rn 'backup\|pg_dump' over the repository hits only .claude/skills/release/SKILL.md, and that is about backing up deploy/.env. The same is true of the PDS: pds-data holds the SQLite repositories and the blobstore for every member account, and nothing copies it anywhere.

So the loss of the server's disk today loses the whole board and every account on the PDS. The encryption at rest does not help here — it protects the rows from a reader, not from an absence.

Worth deciding, roughly in this order:

what a backup of this deployment is (a pg_dump of ponente plus a tar of pds-data, both stacks or production only);
where it goes, given the constraint that nothing can reach in to the host (see deploy/autoupdate/update.sh for why) — so it has to be the host pushing outward, on its own timer, next to ponente-update@;
how a restore is proven, since a backup nobody has restored is a guess. deploy/postgres-upgrade.md now documents a verified restore path for the Postgres half, which is a start.

Not urgent in the sense that nothing is broken, but it is the kind of thing that is only ever noticed at the worst possible moment.

Filed by Claude while working on #119 (run).

Writing the Postgres 17 → 18 runbook (#119) turned up that the `pg_dump` it takes in step (b) would be the first backup this deployment has ever had. There is nothing in `deploy/` that dumps Postgres, no systemd unit or timer beside `ponente-update@`, and no backup section in `deploy/README.md` — `grep -rn 'backup\|pg_dump'` over the repository hits only `.claude/skills/release/SKILL.md`, and that is about backing up `deploy/.env`. The same is true of the PDS: `pds-data` holds the SQLite repositories and the blobstore for every member account, and nothing copies it anywhere. So the loss of the server's disk today loses the whole board and every account on the PDS. The encryption at rest does not help here — it protects the rows from a reader, not from an absence. Worth deciding, roughly in this order: - what a backup of this deployment is (a `pg_dump` of `ponente` plus a tar of `pds-data`, both stacks or production only); - where it goes, given the constraint that nothing can reach *in* to the host (see `deploy/autoupdate/update.sh` for why) — so it has to be the host pushing outward, on its own timer, next to `ponente-update@`; - how a restore is proven, since a backup nobody has restored is a guess. `deploy/postgres-upgrade.md` now documents a verified restore path for the Postgres half, which is a start. Not urgent in the sense that nothing is broken, but it is the kind of thing that is only ever noticed at the worst possible moment. --- _Filed by Claude while working on #119 ([run](https://git.grandiras.net/Grandiras/claude-bot/actions/runs/326))._

claude-bot referenced this issue

2026-08-20 21:52:06 +00:00

Plan the Postgres 17 → 18 upgrade (it is a pg_upgrade, not a tag edit) #119

claude-bot was assigned by Grandiras

2026-08-20 21:58:21 +00:00

claude-bot commented

2026-08-21 00:57:13 +00:00

Author

Collaborator

🤖 On it — follow along in the action run; the reply will link the full session transcript.

📜 Live transcript — updating every ~5 minutes while the task runs.

🤖 On it — follow along in [the action run](https://git.grandiras.net/Grandiras/claude-bot/actions/runs/345); the reply will link the full session transcript.  📜 [Live transcript](https://git.grandiras.net/Grandiras/claude-bot/src/branch/claude-logs/transcripts/Grandiras/Ponente/issue-132/run-345.md) — updating every ~5 minutes while the task runs.

claude-bot referenced this issue from a commit

2026-08-21 01:44:40 +00:00

Back up production nightly, and prove the backup restores

claude-bot referenced this issue from a commit

2026-08-21 01:44:59 +00:00

Back up production nightly, and prove the backup restores

claude-bot referenced this issue from a pull request that will close it,

2026-08-21 01:45:37 +00:00

Claude: changes for issue #132 #134

claude-bot referenced this issue

2026-08-21 01:45:38 +00:00

deploy.sh's header still says production copies nothing to the server #135

claude-bot commented

2026-08-21 01:45:38 +00:00

Author

Collaborator

Pushed claude/issue-132.

What I decided, in the order the issue asked

What a backup is. One encrypted archive per run, holding five things: the pg_dump, the PDS's SQLite databases, its blobstore, the Data Protection keys — and the stack's env file. That last one is the decision worth arguing with. A dump of this database is enc:v1: ciphertext and ENCRYPTION_KEY is not in it; a backup without the env file restores to nothing on the day the machine holding it died. Carrying it is what makes a rebuild need exactly two things (the archive and a passphrase kept off the server), and it is why the archive is encrypted rather than merely compressed.

The PDS half is deliberately not a tar. That PDS runs SQLite in WAL mode with almost everything in the -wal, and I measured what a racing copy gets on 0.4.5027: a straight copy of a live account.sqlite reports 0 objects in sqlite_master where SQLite's own online backup of the same file reports 52 and integrity_check says ok. So every *.sqlite goes through sqlite3 .backup and is integrity-checked; the databases are captured before the blobstore, because a blob is uploaded before the record referencing it.

Where it goes. Outward, on the host's own timer beside ponente-update@, for the same measured reason the updater polls. BACKUP_REMOTE is an rclone destination and rclone runs as a container, so the provider is a config file (deploy/rclone.conf, gitignored, synced by deploy.sh) and never a code change. No remote → the backup still runs, stays on the host, and says so on stderr every time.

How a restore is proven. Every run, before the archive counts: the dump is restored into a scratch Postgres of the same image and queried, every SQLite copy is integrity-checked, and the finished file is decrypted back and compared by sha256. A run that cannot verify writes no archive at all — an unverified file is worse than a missing one. restore.sh adds the drill (verify, changing nothing) and the disaster (--stack).

Production only; dev leaves it off, its board being throwaway by design.

Verified

dotnet build and dotnet test green (380 tests, DocumentationTests included); shellcheck -S warning clean on all four scripts. Then rehearsed end to end in this container against a stand-in stack — a live ghcr.io/bluesky-social/pds:latest, a postgres:17-alpine carrying this repository's real schema as applied by the app itself, and MinIO as the offsite remote:

full run: dump → snapshot of 4 live SQLite databases → blobstore tar → scratch-restore verification (1 members, 4 cards, 4 comments, 13 encrypted lines) → encrypt → decrypt-and-compare → push → size read back → prune to BACKUP_KEEP;
--stack restore against a deliberately damaged stack (3 cards deleted, Comments dropped, blobstore and actor stores removed, DP keys deleted) — all of it came back and the PDS answered /xrpc/_health on the restored volume;
the disaster order: local archives pruned away, --fetch pulled one back off the remote, --stack restored a wiped stack from it;
failure paths: wrong passphrase, tampered archive (caught by the manifest checksum), BACKUP_ENABLED=false, missing passphrase, no remote.

The rehearsal earned its keep — it found a race I had written in: the official Postgres image accepts socket connections while it is still creating POSTGRES_DB, so pg_isready says yes and the restore then dies on database "ponente" does not exist. It waits on select 1 now, and that is written down where the next person will hit it.

Not rehearsed, and named as such in the runbook: rootless podman rather than docker, a real provider rather than MinIO, the units under an actual systemctl --user, a blobstore of real size, and the bare-metal rebuild.

Docs

deploy/backups.md is the runbook (what it protects against and what it doesn't, turning it on, the drill, restoring, rebuilding from nothing, the decisions and why, and what was rehearsed). deploy/README.md, docs/deployment.md, deploy/postgres-upgrade.md, both .env templates and the CLAUDE.md router are updated to match. No Changelog.Unreleased entry — no member would notice this.

⏱ 48m 9s · 74 turns · tokens: 190.8k in / 92.3k out (+9282.7k cache-read)
📜 Full session transcript
→ Opened PR: #134
→ Filed follow-up issue #135: deploy.sh's header still says production copies nothing to the server

Pushed **`claude/issue-132`**. ## What I decided, in the order the issue asked **What a backup is.** One encrypted archive per run, holding five things: the `pg_dump`, the PDS's SQLite databases, its blobstore, the Data Protection keys — and **the stack's env file**. That last one is the decision worth arguing with. A dump of this database is `enc:v1:` ciphertext and `ENCRYPTION_KEY` is not in it; a backup without the env file restores to nothing on the day the machine holding it died. Carrying it is what makes a rebuild need exactly two things (the archive and a passphrase kept off the server), and it is why the archive is encrypted rather than merely compressed. The PDS half is deliberately not a `tar`. That PDS runs SQLite in WAL mode with almost everything *in* the `-wal`, and I measured what a racing copy gets on 0.4.5027: a straight copy of a live `account.sqlite` reports **0** objects in `sqlite_master` where SQLite's own online backup of the same file reports **52** and `integrity_check` says `ok`. So every `*.sqlite` goes through `sqlite3 .backup` and is integrity-checked; the databases are captured *before* the blobstore, because a blob is uploaded before the record referencing it. **Where it goes.** Outward, on the host's own timer beside `ponente-update@`, for the same measured reason the updater polls. `BACKUP_REMOTE` is an rclone destination and rclone runs as a container, so the provider is a config file (`deploy/rclone.conf`, gitignored, synced by `deploy.sh`) and never a code change. No remote → the backup still runs, stays on the host, and says so on stderr every time. **How a restore is proven.** Every run, before the archive counts: the dump is restored into a scratch Postgres of the same image and queried, every SQLite copy is integrity-checked, and the finished file is decrypted back and compared by sha256. **A run that cannot verify writes no archive at all** — an unverified file is worse than a missing one. `restore.sh` adds the drill (verify, changing nothing) and the disaster (`--stack`). Production only; dev leaves it off, its board being throwaway by design. ## Verified `dotnet build` and `dotnet test` green (380 tests, `DocumentationTests` included); `shellcheck -S warning` clean on all four scripts. Then rehearsed end to end in this container against a stand-in stack — a live `ghcr.io/bluesky-social/pds:latest`, a `postgres:17-alpine` carrying this repository's real schema as applied by the app itself, and MinIO as the offsite remote: - full run: dump → snapshot of 4 live SQLite databases → blobstore tar → scratch-restore verification (`1 members, 4 cards, 4 comments, 13 encrypted lines`) → encrypt → decrypt-and-compare → push → size read back → prune to `BACKUP_KEEP`; - `--stack` restore against a **deliberately damaged** stack (3 cards deleted, `Comments` dropped, blobstore and actor stores removed, DP keys deleted) — all of it came back and the PDS answered `/xrpc/_health` on the restored volume; - the disaster order: local archives pruned away, `--fetch` pulled one back off the remote, `--stack` restored a wiped stack from it; - failure paths: wrong passphrase, tampered archive (caught by the manifest checksum), `BACKUP_ENABLED=false`, missing passphrase, no remote. The rehearsal earned its keep — it found a race I had written in: the official Postgres image accepts socket connections while it is still creating `POSTGRES_DB`, so `pg_isready` says yes and the restore then dies on `database "ponente" does not exist`. It waits on `select 1` now, and that is written down where the next person will hit it. Not rehearsed, and named as such in the runbook: rootless podman rather than docker, a real provider rather than MinIO, the units under an actual `systemctl --user`, a blobstore of real size, and the bare-metal rebuild. ## Docs `deploy/backups.md` is the runbook (what it protects against and what it doesn't, turning it on, the drill, restoring, rebuilding from nothing, the decisions and why, and what was rehearsed). `deploy/README.md`, `docs/deployment.md`, `deploy/postgres-upgrade.md`, both `.env` templates and the `CLAUDE.md` router are updated to match. No `Changelog.Unreleased` entry — no member would notice this. --- ⏱ 48m 9s · 74 turns · tokens: 190.8k in / 92.3k out (+9282.7k cache-read) 📜 [Full session transcript](https://git.grandiras.net/Grandiras/claude-bot/src/branch/claude-logs/transcripts/Grandiras/Ponente/issue-132/run-345.md) → Opened PR: https://git.grandiras.net/Grandiras/Ponente/pulls/134 → Filed follow-up issue [#135](https://git.grandiras.net/Grandiras/Ponente/issues/135): deploy.sh's header still says production copies nothing to the server