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 頂部，環境變數可覆寫 — 本表請以變數名為準，勿將數值寫死於其他文件。

備份落地：Compose 的 DB_BACKUP_PATH，預設為 host 的 investment-agent/backups/{daily,weekly}/（已 gitignore）。本機 override 可覆寫這個 mount，確認位置時以合併後的 Compose 設定及容器 inspect 為準。
每輪備份後以 pg_restore --list 驗證 archive TOC 可讀。
daily 的排除清單（DAILY_EXCLUDE_TABLES）是排除式設計：新增的表預設會被備份，漏登記時 fail-safe（多備而非漏備）。
注意：daily dump 用 --exclude-table-data，schema 仍完整，只略過大表資料。

容器排程：啟用 / 確認

此模式適用於容器能正確回報備份檔案系統容量的環境。macOS 外接 SSD 若遇到以下容量誤報，改用後述主機排程；同一目的地只啟用一種排程。

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/

macOS 外接 SSD：主機排程

Docker Desktop 分享 /Volumes 時，容器內的 df 可能回報 Mac 內部磁碟的容量。例如 2026-09-14 Dev 實測：SSD 約有 296 GiB 可用，但容器 /backups 回報約 28.6 GiB；重新建立唯讀 SSD bind 的隔離容器也一樣。因此不能以降低 BACKUP_CAPACITY_HEADROOM_PERCENT 或固定可用容量數字解決。

backend/scripts/db_backup_host.py 在 macOS 執行既有 db_backup.sh，讓容量檢查使用原生檔案系統數值。PostgreSQL 客戶端仍來自指定的來源容器， dump 透過 stdout 直接寫入 SSD，不使用 Mac 內部磁碟暫存大型檔案。 daily 排除清單、weekly 全庫、110% 容量門檻、TOC、SHA-256、原子更名及成功後才執行的保留規則均沿用既有腳本。

設定時先取得 SSD VolumeUUID 與資料庫 cluster identity：

diskutil info '/Volumes/Extreme SSD'
docker exec cortex-postgres psql -U cortex -d cortex_investment -Atqc \
  'SELECT system_identifier FROM pg_control_system();'

先以 --preflight-only 驗證，再執行備份。以下 UUID／identity 必須替換為目標環境實際查得的值；--script 應指向該環境使用的整合版本腳本。

python3 backend/scripts/db_backup_host.py \
  --backup-dir '/Volumes/Extreme SSD/cortex-dev-backups' \
  --volume-uuid '<SSD VolumeUUID>' \
  --container cortex-postgres --database cortex_investment --user cortex \
  --system-identifier '<Dev cluster identity>' \
  --script "$PWD/backend/scripts/db_backup.sh" \
  --disabled-sidecar cortex-db-backup \
  --docker-bin "$(command -v docker)" \
  --preflight-only

正式執行移除 --preflight-only；手動單次驗證可加 --attempts 1，補做全庫時加 --full（會先 daily 再 full）。主機 adapter 要求 SSD 已掛載、 UUID 與來源 DB identity 正確，並在實際備份前確認舊 sidecar 已停止或不存在。每次 dump 開始前會再次核對來源 identity，client 開啟備份前也會比對本輪驗證過的目的地 device／inode，避免長時間執行時沿用已被替換的來源或目錄。主機鎖保護同目的地的並行執行；容量不足或備份失敗會保留既有 restore points。

本機切換時，在已忽略的 docker-compose.override.yml 中，僅對 db-backup 增加 profiles: [container-backup] 與 restart: "no"，保留現有 volumes。這讓一般 docker compose up 不會再啟動舊排程。確認 sidecar 沒有正在執行備份後，僅停用這個服務：

docker update --restart=no cortex-db-backup
docker stop cortex-db-backup

使用使用者層級 LaunchAgent 執行上方 adapter 命令（移除 preflight 旗標）， StartCalendarInterval 設定 Hour=3, Minute=30。launchd 依 macOS 系統時區觸發，部署時須確認系統時區為 Asia/Taipei；同時設定程序的 TZ=Asia/Taipei。保留 stderr/stdout log，驗證 launchctl print gui/$(id -u)/<label> 的載入狀態、最近 exit code 與實際新增備份。使用者必須已登入、SSD 掛載且 Docker 可用；睡眠喚醒後的實際執行時間可能晚於 03:30。首次從 launchd 執行時，macOS 可能要求 Python 存取卸除式磁碟；需由使用者在系統視窗允許。互動終端的 preflight 通過並不代表背景程序已有相同權限，所以切換驗收必須包含一次真正的 launchctl kickstart 與完成的備份。

切回容器排程前，先卸載主機 LaunchAgent、確認沒有備份正在執行，再恢復原本的 backup override／restart policy 並啟動 sidecar。切換只涉及備份排程，不需要重新建立 PostgreSQL 或修改 PGDATA mount。

主機模式可直接透過來源容器驗證 SSD archive TOC，無須重新啟動 sidecar：

docker exec -i cortex-postgres pg_restore --list < '/Volumes/Extreme SSD/cortex-dev-backups/weekly/<archive>.dump'

TOC 與 checksum 驗證不等於完整還原測試；清除最後一份其他位置的備份前，仍應用新的 full dump 在隔離 PostgreSQL 完成還原與資料核對。

還原程序

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

cortex-postgres 與 cortex-db-backup 用同一個 image （pgvector/pgvector:pg15），兩邊都有 pg_restore。備份檔已掛載在 cortex-db-backup:/backups，與備份相關的操作建議直接用該容器，可省去 docker cp。若已切換為 macOS 主機排程，sidecar 應保持停止；以下使用 sidecar 的命令需改用備份檔案作 stdin，或在隔離還原容器中唯讀掛載實際 SSD 目錄。

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-realtime-quote-scheduler

# 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）：三條管線的回補參數**語意不同**，
#    不可互相套用（分歧本身追蹤於 issue #1607）
#
#    a. analysis —— 唯一有日曆窗口下界的：
#    docker exec cortex-api bash -c "cd /app/scripts && python daily_analysis_snapshot.py --backfill-days <N>"
#    <N> 算的是**日曆天**不是交易日：實際重算 [目標日 − N 天, 目標日] 內有 K 棒的
#    交易日，窗口之前的日期一律不補，所以缺口若跨到週末或連假，N 要開得比缺的交易
#    日數大，否則舊的那幾天不會被補到（issue #1537）
#
#    b. chip —— **沒有**下界，別套用上面的算法：
#    docker exec cortex-api bash -c "cd /app/scripts && python daily_chip_snapshot.py --backfill <N>"
#    旗標名是 --backfill，取的是目標日（含）以前最近 N+1 個**有法人資料**的交易日
#    （ORDER BY i.trade_date DESC LIMIT N+1，無日曆起點），所以缺口再久 N 也一定
#    補滿 N+1 天，但反過來也可能一路往前撈到你要修的區間之外
#
#    c. financial —— **沒有任何 backfill 參數**：
#    daily_financial_snapshot.py 只有 --date，一次算一天（省略 = 今天），多天缺口
#    必須自己逐日迴圈 --date

# 5. 重啟服務並驗證
docker start cortex-api cortex-scheduler cortex-realtime-quote-scheduler
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

D. 技術快照落在退化錨點（`anchor_fallback_date` 非 null）

成因：還原順序把 taiwan.stock_ohlc 載入在 taiwan.stocks 之前（A 的單表還原與 B 的全庫還原都可能造成），taiwan.stocks 內沒有任何 active 代碼持有 K 棒，單檔技術快照因此取不到全市場錨點，退回以該檔自己的最後報價日為錨（即 issue #1537 之前的行為），並把該錨點日期填進 stats.anchor_fallback_date（正常執行恆為 null）。

會落入此狀態的只有兩條路徑（都是帶單檔且 include_inactive=True 的執行）： scheduler 的除權息調整重播 _replay_adjustment_ticker_history（一偵測到此欄非 null 就中止並指回本節），以及 CLI 同時帶 --ticker 與 --include-inactive。 POST /api/daily-analysis/run 不傳 include_inactive，在此狀態下會先以「No stock_ohlc trading date available」中止（回 500），不可能走到這裡（見 docs/api-reference.md 的 POST /api/daily-analysis/run 一節）。

已經發生的事：SES 新鮮度閘門在此狀態必然關閉（issue #1308），不會有過期內容寫進即時層；但該次寫下的 daily_technical_snapshot 已經 commit，且是在半還原的資料庫下算出來的。改成明確指定一個比該檔最後 K 棒新的日期也沒用：clamp 的比較對象就是已退回的錨點，同樣被 clamp 到那個舊日期。

先確定實際寫入了哪個區間：上界一律是 stats.target_date，下界看該次怎麼呼叫：

該次的呼叫形狀	實際寫入區間的下界
帶 `start_date`（scheduler 重播、CLI `--start-date`）	該 `start_date` 本身（scheduler 重播用的是該次 delta 的 `earliest_changed_date`，此值不在 `stats` 內）
只帶 `backfill_days`（CLI `--backfill-days`）	`stats.target_date` − `stats.backfill_days` 日曆天；該窗口內最多 `stats.backfill_days + 1` 個有 K 棒的交易日
兩者皆無	只有 `stats.target_date` 當天

scheduler 重播的退回錨點正好等於該檔自己的終端日，所以它解析出的區間與請求的區間一致（範圍看起來正確，只有 anchor_fallback_date 報出這批列是在什麼狀態下算出來的）。CLI 省略 --date、或指定了比該檔最後 K 棒新的 --date 時則會被 clamp 到退回的舊錨點：

1537 在 dev 實測到的形狀就是錨點退回 2026-04-13、10 日窗口 → 實際寫入

[2026-04-03, 2026-04-13] 內的 5 個交易日，與原本要求的 8/9 月窗口不相交，因此「修好之後用正確日期重跑一次」不會覆蓋到那批舊列。

處置順序是三步：

先修好 taiwan.stocks（補齊 active 名冊／完成還原）並確認全市場錨點恢復；在那之前改成明確指定日期無效（同樣會被 clamp）。
先把上表推得的舊窗口重算或從備份還原（還原見 A），再碰原本要求的區間。重算方式：以 --date = 該次的 stats.target_date、--backfill-days = 該次的 stats.backfill_days 重跑該檔（該次是帶 start_date 的形狀就改用 --start-date = 該值）——修好之後這個日期已落在全市場錨點之前，會被逐字採用（不再 clamp），命中的 (stock_code, trading_date, interval) 與退化那次相同，因而原地覆寫；backfill_days 非空或目標日不是最新交易日時 SES 新鮮度閘門一樣關閉（issue #1308），不會把舊內容推進即時層。

docker exec cortex-api bash -c "cd /app/scripts && python daily_analysis_snapshot.py --ticker <TICKER> --date <stats.target_date> --backfill-days <stats.backfill_days>"

最後才跑原本要求的區間。兩個窗口若有重疊，重疊的日期由這一步一併覆寫，第 2 步只需補其餘日期；上述 clamp 形狀兩者不相交，整個舊窗口都要補。scheduler 重播路徑的中止發生在 run_backfill_ses_history 之前，該檔的 SES 歷史尚未重播，要連同重跑。

第 2 步是覆寫不是刪除：退化那次若替原本沒有快照的日期新建了列，重算只讓那些列的值變正確，列本身仍會留著；要回到該次執行前的列集合只能從備份還原（A）。

已知限制

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