windy/vps
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
main
vps/runbooks/pgdb-restore.md
T
windyboy 2d95f87897 docs(runbooks): pgdb ops runbooks — health / restore / update + facts refresh (W1N-228) 
- runbooks/pgdb-health.md: read-only health check (8 diagnostics) — containers,
  PG core + HA clients, write activity, TimescaleDB hypertables/compression,
  pgweb auth/bookmarks, daily custom-format backups, disk/fstab, logs
- runbooks/pgdb-restore.md: procedure-type restore (pg_restore -Fc, temp-DB swap,
  approval gates, rollback) — precondition command verified live
- runbooks/pgdb-update.md: gated command reference (pull -> config -q -> up -> verify;
  rollback = /opt/database/run + old volumes)
- index + validate-repo.sh classification updated; hosts/pgdb.md refreshed
  (SSH key auth works, scribe events hypertable, runbook cross-refs)
2026-08-29 14:51:20 +08:00

182 lines
8.5 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# Runbook: pgdb database restore (pg_restore, custom format)
## Purpose
Restore one pgdb database (`hass`, `scribe`, or `postgres`) from a pg-backup custom-format dump via `pg_restore`. The dump → restore path was exercised on 2026-08-29 (hass restore test, `states` 10014 rows); this runbook makes it repeatable and safe.
## Scope
- Applicable: single-database restore on [pgdb](../hosts/pgdb.md) (`192.168.55.15`) from `/opt/database/backups/{daily,weekly,monthly}/*.dump`.
- Not applicable: full-stack/VM recovery, HAOS-side changes, secret rotation, or restoring to a different host (adjust `-h` and auth first).
## Ownership
- Owner: personal ops (Windy)
- Last reviewed: 2026-08-29
- Related systems: pgdb (`/opt/database`), HA `192.168.55.11` (hass/scribe clients), [pgdb-health](pgdb-health.md)
## Preconditions
- Health baseline recorded ([pgdb-health](pgdb-health.md)): stack healthy, current backup is from today and passes `pg_restore -l`.
- A valid dump exists for the target database (see Step 1).
- Explicit user approval obtained for the chosen restore mode (see `## Approval gates`) and recorded in the Linear `vps` project.
- Disk headroom on `/srv/pgdata` for a second copy of the database (temp-DB mode).
## Inputs
| Input | Source | Required | Validation |
|---|---:|---|---|
| Target database (`hass`/`scribe`/`postgres`) | [hosts/pgdb.md](../hosts/pgdb.md) | yes | exists in `pg_database` |
| Dump path (inside pg-backup, `/backups/...`) | `ls /opt/database/backups/daily/` | yes | `pg_restore -l` lists TOC |
| Restore mode (temp-DB swap recommended / in-place `--clean`) | operator + user | yes | approval record |
| Downtime window | user | yes (for in-place) | user-confirmed |
## Safety
### Non-negotiable rules
- Never delete the existing configuration or the last known-good dump as the first recovery action.
- Prefer read-only diagnosis before any mutation; the restore itself is the only mutation.
- Never print passwords — use `PGPASSWORD="$POSTGRES_PASSWORD"` inside the pg-backup container.
- If actual state conflicts with this runbook (e.g. dump corrupt, DB missing), `STOP`.
- Do not invent missing parameters; do not bypass failed checks or approval.
### Stop conditions
- Dump fails `pg_restore -l` (corrupt/truncated) — do not restore from it.
- Target DB contains data newer than the dump and the operator did not accept the data-loss window.
- Disk space insufficient for the temp copy.
- Approval missing or stale (recorded in Linear).
- Post-restore verification fails — do not declare success.
### Approval gates
| Action | Risk | Explicit approval | Recorded at |
|---|---|---|---|
| Create temp DB + `pg_restore` into it (no data loss) | Low | No (executor can run after plan review) | Linear vps issue |
| Drop/rename the live target DB (temp-DB swap) | High | Yes — user confirmation | Linear vps issue |
| In-place restore with `--clean --if-exists` | High | Yes — user confirmation | Linear vps issue |
## Procedure
### Step 1 — Diagnose
**Action**
```bash
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'ls -l --time-style=long-iso /opt/database/backups/daily/'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose exec -T pg-backup sh -c "PGPASSWORD=\"\$POSTGRES_PASSWORD\" pg_restore -h timescaledb -U postgres -l /backups/daily/<db>-latest.dump" | head -6'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose exec -T timescaledb psql -U postgres -Atc "select datname, pg_size_pretty(pg_database_size(datname)) from pg_database where datistemplate=false order by 1;"'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'df -h /srv/pgdata'
```
**Expected**
- Dump exists for today and is custom format (`file -L` → `PostgreSQL custom database dump`).
- `pg_restore -l` lists the archive TOC (dbname matches the target) without error.
- Target DB exists; disk has headroom for a second copy.
**Decision**
- Dump valid + DB present → Step 2.
- Dump invalid or target missing → `STOP`; record evidence, do not restore.
### Step 2 — Approve
**Action** — Present the plan: target DB, dump (path + creation time), restore mode (temp-DB swap recommended), estimated write downtime (none for temp mode; brief for in-place), and rollback plan. Obtain explicit user approval and record it in the Linear `vps` project issue for this work.
**Expected** — User approval recorded with the chosen mode.
**Decision**
- Approved → Step 3.
- Not approved / mode changed → `STOP` and report; re-plan if needed.
### Step 3 — Restore into a temp database (recommended, no data loss)
**Action**
```bash
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database
docker compose exec -T timescaledb psql -U postgres -c "CREATE DATABASE <db>_restore OWNER <owner>;"
docker compose exec -T pg-backup sh -c "PGPASSWORD=\"\$POSTGRES_PASSWORD\" pg_restore -h timescaledb -U postgres -d <db>_restore /backups/daily/<db>-latest.dump"
docker compose exec -T timescaledb psql -U postgres -Atc "select count(*) from pg_database where datname='"'"'<db>_restore'"'"';"'
```
`<owner>` is the database owner from [hosts/pgdb.md](../hosts/pgdb.md) (`hass` → `hass`, `scribe`/`postgres` → `postgres`). Run inside the pg-backup container so the password stays server-side; `-h timescaledb` routes over the compose network.
**Expected** — `CREATE DATABASE` succeeds; `pg_restore` exits 0 with no error lines; the temp DB exists.
**Decision**
- Restore OK → Step 4.
- Restore errors → diagnose (dump integrity, permissions), fix, or `STOP`; the live DB is untouched in this mode.
**Verification**
```bash
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose exec -T timescaledb psql -U postgres -d <db>_restore -Atc "select count(*) from <key_table>;"'
```
Compare against the pre-restore baseline (e.g. `states` ≈ dump's row count). Row counts match → proceed to swap; mismatch → `STOP` and inspect.
### Step 4 — Swap temp DB into place (approval-gated)
**Action** — After user approval: drop the live DB and rename the temp (or `pg_dump` the live DB as a safety copy first).
```bash
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database
docker compose exec -T timescaledb psql -U postgres -c "DROP DATABASE <db>;"
docker compose exec -T timescaledb psql -U postgres -c "ALTER DATABASE <db>_restore RENAME TO <db>;"'
```
**Expected** — Rename succeeds; `pg_database` shows `<db>` with the restored size.
**Decision**
- Swap OK → Step 5.
- Swap fails → `STOP`; see Rollback.
**Verification**
- `select count(*)` on key tables matches the dump.
- HA sessions re-establish: `pg_stat_activity` shows `hass|hass|192.168.55.11` on `<db>` (scribe writes resume).
- pgweb bookmarks show restored data (read-only browse).
**Rollback**
- Trigger: verification fails or swap is wrong.
- Action: restore again from the dump taken before the operation; if the live DB was dropped, recreate `<db>` and restore the pre-change dump (`--clean` not needed on an empty DB).
- Verify: row counts + HA sessions match pre-change baseline; record in Linear.
### In-place variant (only with explicit approval)
```bash
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose exec -T pg-backup sh -c "PGPASSWORD=\"\$POSTGRES_PASSWORD\" pg_restore -h timescaledb -U postgres --clean --if-exists -d <db> /backups/daily/<db>-latest.dump"'
```
Destructive to current data — requires the High-risk approval gate and a fresh dump of the current state taken immediately before. Follow Steps 4–5 verification afterwards.
## Final Verification
Success requires all of:
- Target DB row counts match the dump (`states`, `states_raw`, `events` etc. sane).
- HA (`192.168.55.11`) reconnects and writes resume (write-activity sample grows).
- [pgdb-health](pgdb-health.md) passes (containers, backups still current, logs clean).
- Outcome recorded in [hosts/pgdb.md](../hosts/pgdb.md) and the Linear `vps` project.
## Failure Handling
1. Stop further changes; do not guess.
2. Collect: dump path + `pg_restore`/`psql` output, DB sizes, log tail.
3. Record completed steps, actual vs expected, and whether rollback ran.
4. Escalate to the user with evidence; continue only with explicit direction.
## References
- [hosts/pgdb.md](../hosts/pgdb.md) — roles, owners, backup layout, verification history
- [pgdb-health](pgdb-health.md) — pre/post health baseline
- [pgdb-update](pgdb-update.md) — image/compose upgrades
- Linear `vps`: W1N-226 (first check), W1N-227 (compose-化 + restore test), W1N-228 (this runbook)
Reference in New Issue View Git Blame Copy Permalink