vps/runbooks/pgdb-health.md

Runbook: pgdb health (TimescaleDB + pgweb + pg-backup)

Purpose

Read-only health check of the pgdb TimescaleDB compose stack (PG18 + pgweb GUI + nightly custom-format backups). Confirms the stack is serving Home Assistant (hass/scribe) and that backups are current and restorable.

Scope

• Applicable: pgdb (192.168.55.15), /opt/database compose stack.
• Read-only: never mutates containers, databases, backups, or secrets.
• Not applicable: restoring data (use pgdb-restore), upgrading images (use pgdb-update), HA-side changes (see hosts/hass.windy.lan.md).

Ownership

• Owner: personal ops (Windy)
• Last reviewed: 2026-08-29
• Related systems: pgdb (/opt/database, TimescaleDB 18.6 / TS 2.29.2), HA 192.168.55.11 (hass/scribe clients)

Access

SSH to pgdb (key auth works from the WSL agent shell as of 2026-08-29):

ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15

From the agent sandbox, always use -F /dev/null (system ssh config is unreadable there) and prefer IPv4. The compose project lives at /opt/database — prefix every docker compose call with cd /opt/database. Never print .env values or bookmarks (they contain DB passwords); compare or use them only inside commands that output non-secret signals (status codes, counts, names).

Safety

Non-negotiable rules

• Read-only diagnosis only; never "fix while checking".
• Never print passwords or secrets — redact/consume them inside commands.
• If live state conflicts with an expected value, STOP and record evidence; do not invent parameters or bypass a failed check.
• Restore/update work belongs to the change runbooks, not this one.

Stop conditions

• Any container Exited, Restarting, or not healthy where expected.
• Expected database/table/hypertable missing or a key query errors.
• Write-activity sample does not increase (scribe states_raw static).
• Latest daily backup older than today, not custom format, or pg_restore -l fails.
• Disk usage near full on /srv/pgdata or /.
• New ERROR/FATAL lines in the timescaledb log or backup failures in the pg-backup log.

Pass criteria

• docker compose ps -a: timescaledb + pg-backup Up (healthy), pgweb Up; ports bound to 192.168.55.15:5432 and :8081.
• PG 18.x; databases hass, scribe, postgres present; HA (192.168.55.11) connected as hass to both hass and scribe.
• hass.states and scribe.states_raw row counts grow between two samples (scribe writes continuously).
• TimescaleDB extension 2.29.x; scribe hypertables states_raw + events (1-dim, time); compression configured (segmentby/orderby rows in timescaledb_information.compression_settings); entities table exists.
• pgweb: no credentials → HTTP 401; with credentials → HTTP 200; /api/bookmarks → ["hass","scribe"].
• daily/*-latest.dump symlinks point to today's dumps; file -L reports PostgreSQL custom database dump.
• /srv/pgdata (/dev/sdb1, 32G) and / not near full; fstab mounts /srv/pgdata by UUID=c9e12e79-1f66-404c-ab7f-b8809be81d86 with defaults,noatime.
• timescaledb log: no new ERROR/FATAL; pg-backup log: recent successful backup.

Checks

1. Containers

ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose ps -a --format "table {{.Name}}\t{{.Status}}\t{{.Ports}}"'

Expected

• timescaledb Up (healthy), pg-backup Up (healthy), pgweb Up.
• Ports: 192.168.55.15:5432->5432/tcp (timescaledb), 192.168.55.15:8081->8081/tcp (pgweb).

Stop if any container is Exited/Restarting/unhealthy, or a port binding changed.

2. PG core and HA clients

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 version();" | head -1'
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 from pg_database where datistemplate=false order by 1;"'
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, usename, client_addr from pg_stat_activity where client_addr is not null group by 1,2,3 order by 1;"'

Expected

• PostgreSQL 18.x (verified: 18.6).
• Databases: hass, postgres, scribe.
• HA sessions: hass|hass|192.168.55.11 and scribe|hass|192.168.55.11 (the HAOS recorder/scribe clients from 192.168.55.11).

Stop if a database is missing, the version is not 18.x, or HA has no live sessions (scribe connectivity is part of the HA pipeline).

3. Write activity

ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database
A=$(docker compose exec -T timescaledb psql -U postgres -d scribe -Atc "select count(*) from states_raw;")
sleep 30
B=$(docker compose exec -T timescaledb psql -U postgres -d scribe -Atc "select count(*) from states_raw;")
echo "states_raw $A -> $B"'

Also sample hass.states once (recorder table, bulk-writes on HA restart): docker compose exec -T timescaledb psql -U postgres -d hass -Atc "select count(*) from states;".

Expected — states_raw increases between samples (verified: 2665 → 2693 in 30 s). states count is sane (thousands).

Stop if states_raw is static across samples while HA is up — writes have stalled.

4. TimescaleDB (extension, hypertables, compression)

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 extversion from pg_extension where extname='"'"'timescaledb'"'"';"'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose exec -T timescaledb psql -U postgres -d scribe -Atc "select hypertable_name, num_dimensions from timescaledb_information.hypertables order by 1;"'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose exec -T timescaledb psql -U postgres -d scribe -Atc "select hypertable_name, attname, segmentby_column_index, orderby_column_index from timescaledb_information.compression_settings order by 1,3,4;"'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose exec -T timescaledb psql -U postgres -d scribe -Atc "select to_regclass('"'"'public.entities'"'"');"'

Expected

• Extension version 2.29.x (verified: 2.29.2).
• Scribe hypertables: states_raw and events, both 1 dimension.
• Compression configured for states_raw (segmentby metadata_id idx 1, orderby time idx 1) and events (segmentby event_type, orderby time). Note: TimescaleDB 2.29.x has no compression_enabled column in this view — row presence is the enabled signal.
• entities resolves (scribe registry table).

Stop if the extension version differs from the pinned 2.29.x line, a hypertable is missing, compression rows vanish, or entities is absent (scribe schema broke — see Known issues in hosts/pgdb.md).

5. pgweb GUI

ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database
curl -s -o /dev/null -w "no-auth:%{http_code}\n" --max-time 8 http://192.168.55.15:8081/
U=$(grep -E "^PGWEB_AUTH_USER=" .env | cut -d= -f2-); P=$(grep -E "^PGWEB_AUTH_PASS=" .env | cut -d= -f2-)
curl -s -o /dev/null -w "auth:%{http_code}\n" --max-time 8 -u "$U:$P" http://192.168.55.15:8081/
echo -n "bookmarks:"; curl -s --max-time 8 -u "$U:$P" http://192.168.55.15:8081/api/bookmarks; echo'

Use 192.168.55.15:8081 (pgweb binds the VM IP only — loopback is not bound). Credentials are read from .env on the host and never printed.

Expected — no-auth:401, auth:200, bookmarks:["hass","scribe"].

Stop if pgweb is unreachable, unauthenticated access is not 401, or bookmarks diverge from ["hass","scribe"].

6. Backups

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 'file -L /opt/database/backups/daily/hass-latest.dump'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose exec -T pg-backup pg_restore -l /backups/daily/hass-latest.dump | head -4'

Expected

• daily/*-latest.dump symlinks point to today's *-YYYYMMDD.dump (nightly 02:00 local Asia/Shanghai; a fresh container also fires BACKUP_ON_START).
• file -L reports PostgreSQL custom database dump (pg_restore format; verified v1.16-0).
• pg_restore -l from the pg-backup container lists the archive TOC without error (timescaledb does not mount /backups).

Stop if the latest dump is not from today, is not custom format, or pg_restore -l fails. Stray non-daily/ dumps at the /opt/database/backups/ root are pre-compose leftovers — ignore for health, flag for cleanup.

7. Disk and mount

ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'df -h /srv/pgdata /'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'grep -E "srv/pgdata" /etc/fstab'

Expected

• /srv/pgdata = /dev/sdb1 32G (verified: 88M used / 30G avail) and / with comfortable headroom.
• fstab: UUID=c9e12e79-1f66-404c-ab7f-b8809be81d86 /srv/pgdata ext4 defaults,noatime 0 2.

Stop if either filesystem is near full (define threshold before acting) or the fstab entry is missing/changed.

8. Logs

ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose logs --since 24h timescaledb 2>&1 | grep -E "ERROR|FATAL" | tail -10'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose logs --since 24h pg-backup 2>&1 | tail -5'

Expected

• timescaledb: no new ERROR/FATAL. Known benign history: an old relation "hass.states" does not exist from a wrong-schema probe and compression_enabled column errors from an outdated query — neither recurs with the commands above.
• pg-backup: recent successful run (SQL backup created successfully for each database, no restore/cleanup errors).

Stop if repeated ERROR/FATAL appear or a backup run failed.