Runbook: PostgreSQL 備份與還原（cortex-postgres）

背景：2026-06-11 發生 9911-9915 真實個股遭測試 cleanup CASCADE 刪除的事故（issue #402）。當時無任何備份（archive_mode=off、無 dump），TDCC ownership 歷史週資料永久損失。本 runbook 對應的備份機制即為該事故的防再發措施。

備份機制（db-backup sidecar）

investment-agent/docker-compose.yml 的 db-backup service 以 scripts/db_backup.sh 常駐執行：

層級	時間	內容	保留
daily	每日 `$DB_BACKUP_TIME`（預設 03:30，Asia/Taipei）	`pg_dump -Fc`，排除大型衍生表的資料（snapshots / indicators / messages / latest_ses — 佔 DB ~93%，可由 pipeline 重算；排除清單 `$DAILY_EXCLUDE_TABLES`）	`$DAILY_KEEP` 份（預設 14）
weekly	每週 `$WEEKLY_DOW`（ISO，預設 7=週日）	`pg_dump -Fc` 全庫	`$WEEKLY_KEEP` 份（預設 2）

以上預設值定義於 scripts/db_backup.sh 頂部，環境變數可覆寫 — 本表請以變數名為準，勿將數值寫死於其他文件。

備份落地：host 的 investment-agent/backups/{daily,weekly}/（已 gitignore）。
每輪備份後以 pg_restore --list 驗證 archive TOC 可讀。
daily 的排除清單（DAILY_EXCLUDE_TABLES）是排除式設計：新增的表預設會被備份，漏登記時 fail-safe（多備而非漏備）。
注意：daily dump 用 --exclude-table-data，schema 仍完整，只略過大表資料。

啟用 / 確認

cd investment-agent
docker compose up -d db-backup
docker logs cortex-db-backup --tail 5     # 應看到 "sidecar started: daily at 03:30..."

手動立即備份

docker exec cortex-db-backup /bin/sh /db_backup.sh --once
ls -lh backups/daily/ backups/weekly/

還原程序

以下指令皆假設目前位於 investment-agent/（同「啟用 / 確認」一節的 cd）。

cortex-postgres 與 cortex-db-backup 用同一個 image （pgvector/pgvector:pg15），兩邊都有 pg_restore。備份檔已掛載在 cortex-db-backup:/backups，與備份相關的操作建議直接用該容器，可省去 docker cp。

A. 還原單一表（最常見：誤刪某些列）

# 1. 找最新含該表資料的 dump（source 表用 daily；衍生表用 weekly）
LATEST=$(ls -1t backups/daily/*.dump | head -1)

# 2. 還原到暫存 DB，避免直接覆蓋 prod
#    （DROP IF EXISTS 讓中斷過的還原可重跑；TEMPLATE template0 是刻意的 —
#      本機 template1 有 collation version mismatch，且 dump 內含
#      CREATE EXTENSION，不需依賴 template 預載 pgvector）
docker exec cortex-postgres psql -U cortex -d postgres \
  -c "DROP DATABASE IF EXISTS restore_scratch;"
docker exec cortex-postgres psql -U cortex -d postgres \
  -c "CREATE DATABASE restore_scratch TEMPLATE template0;"
docker cp "$LATEST" cortex-postgres:/tmp/restore.dump
docker exec cortex-postgres pg_restore -U cortex -d restore_scratch /tmp/restore.dump

# 3. 從暫存 DB 把需要的列搬回 prod — 先進暫存表再 ON CONFLICT 寫入，
#    部分資料已存在時不會整批失敗
#    例（還原特定 ticker 的 ownership_distribution）：
docker exec cortex-postgres psql -U cortex -d restore_scratch -c \
  "\copy (SELECT * FROM ownership_distribution WHERE ticker='9911.TW') TO '/tmp/rows.csv' CSV"
docker exec cortex-postgres psql -U cortex -d cortex_investment -c \
  "CREATE TABLE IF NOT EXISTS tmp_restore (LIKE ownership_distribution);"
docker exec cortex-postgres psql -U cortex -d cortex_investment -c \
  "\copy tmp_restore FROM '/tmp/rows.csv' CSV"
docker exec cortex-postgres psql -U cortex -d cortex_investment -c \
  "INSERT INTO ownership_distribution SELECT * FROM tmp_restore ON CONFLICT DO NOTHING; DROP TABLE tmp_restore;"

# 4. 清理
docker exec cortex-postgres psql -U cortex -d postgres -c "DROP DATABASE restore_scratch;"
docker exec cortex-postgres rm -f /tmp/restore.dump /tmp/rows.csv

B. 全庫還原（災難復原）

# 1. 停掉所有寫入方
docker stop cortex-api cortex-scheduler cortex-streamlit

# 2. 重建資料庫後還原 — --clean 只會清掉「dump 裡也有」的物件，
#    殘留的多餘表/欄位不會被移除；整庫重建才保證乾淨（PG 13+ WITH (FORCE)
#    會踢掉殘餘連線）
LATEST=$(ls -1t backups/weekly/*.dump | head -1)
docker cp "$LATEST" cortex-postgres:/tmp/restore.dump
docker exec cortex-postgres psql -U cortex -d postgres \
  -c "DROP DATABASE IF EXISTS cortex_investment WITH (FORCE);"
docker exec cortex-postgres psql -U cortex -d postgres \
  -c "CREATE DATABASE cortex_investment OWNER cortex TEMPLATE template0;"
docker exec cortex-postgres pg_restore -U cortex -d cortex_investment /tmp/restore.dump

# 3. weekly dump 之後的 daily 增量：再套最新 daily dump 的 source 表
#    （pg_restore --data-only -t <table> 逐表，或接受 gap 由 pipeline 重抓）

# 4. 重算 weekly 之後的衍生 snapshots（如有 gap）：
#    docker exec cortex-api bash -c "cd /app/scripts && python daily_analysis_snapshot.py --backfill-days <N>"
#    （chip / financial 同理）

# 5. 重啟服務並驗證
docker start cortex-api cortex-scheduler cortex-streamlit
curl -s http://localhost:8000/health

C. 驗證備份可用性（建議每月一次）

LATEST=$(ls -1t backups/daily/*.dump | head -1)
docker exec cortex-db-backup pg_restore --list /backups/daily/$(basename "$LATEST") | head -20

已知限制

非 PITR：本機制是邏輯 dump，恢復點 = 最近一次 dump（最多損失 24h 寫入）。若需要 point-in-time recovery，須另開啟 archive_mode=on + base backup （需要 postgres 重啟與額外磁碟規劃，列為未來工作）。
備份存在同一台主機：磁碟整顆損毀仍會全失。異地備份（rsync / 雲端）為後續強化項。