vps/runbooks/pgdb-update.md

Runbook: pgdb update (image / compose upgrade)

Purpose

Upgrade the pgdb compose stack (timescaledb + pgweb + pg-backup) to newer images with gated commands and verification. Data lives on the /srv/pgdata volume and survives container recreation.

Scope

• Applicable: pgdb (192.168.55.15), /opt/database/docker-compose.yml (source: compose/pgdb/compose.yml in this repo).
• Not applicable: config/secret changes, .env rotation, DB migration, or pgweb bookmark changes.

Ownership

• Owner: personal ops (Windy)
• Last reviewed: 2026-08-29
• Related systems: pgdb (/opt/database), HA 192.168.55.11, pgdb-health

Approval gates

Action	Risk	Explicit approval
docker compose pull + up -d (recreates changed containers; timescaledb recreate = brief write interruption for HA)	Medium	Yes — user confirmation required

Safety Rules

• Never delete an existing configuration as the first recovery action.
• Prefer read-only diagnosis before mutation.
• After every mutation, verify the expected state.
• If actual state conflicts with this runbook, STOP.
• Do not invent missing parameters (e.g. new env vars required by a major image bump).
• Do not bypass failed tests.
• Destructive actions require explicit approval.

Before

1. Record a health baseline: run pgdb-health. Note current versions (select version() → PG 18.x, TimescaleDB 2.29.x) and image digests:

   ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker inspect --format "{{.Name}} {{.Config.Image}} {{.Image}}" timescaledb pgweb pg-backup'
   

2. Confirm parity between the repo source and the host compose file:

   diff compose/pgdb/compose.yml <(ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cat /opt/database/docker-compose.yml')
   

3. Check disk: df -h /srv/pgdata / (new images need pull space on /).
4. Confirm the intended change with the user (approval gate above).

Update

ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose pull'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose config -q && echo CONFIG_OK'
ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose up -d'

• compose config -q fails fast on invalid compose syntax — STOP there, do not up.
• up -d recreates only containers whose image/config changed. Recreating pg-backup triggers an immediate BACKUP_ON_START dump (expected, keep it).
• HA (192.168.55.11) recorder reconnects automatically after a brief write interruption; do not restart HA unless it fails to recover.

After

1. Re-run pgdb-health. Confirm:
   • Containers Up (healthy); ports still 192.168.55.15:5432 / :8081.
   • select version() still PG 18.x; TimescaleDB extension version still the pinned 2.29.x line (image latest-pg18 should not jump major/minor unexpectedly — if it does, STOP and review before accepting).
   • Write activity resumes (states_raw grows); HA sessions from 192.168.55.11 present.
   • A fresh backup exists (today) and passes pg_restore -l.
2. Update Verified on hosts/pgdb.md with date, new versions, and outcome.

Rollback

• Trigger: health check fails after the update, or the new image misbehaves.
• Action (data-safe — /srv/pgdata volume is untouched):
  1. Pin the previous image tags in /opt/database/docker-compose.yml (from the docker inspect baseline above) and sync the repo source if changed:

     ssh -F /dev/null -o BatchMode=yes windy@192.168.55.15 'cd /opt/database && docker compose up -d --force-recreate'
     

  2. Last resort: the pre-compose launcher /opt/database/run (legacy docker run command) with the same /srv/pgdata mount and the old anonymous volume as fallback — see hosts/pgdb.md.
• Verify: pgdb-health passes and versions match the pre-update baseline.