PostgreSQL 17 → 18 升級 + 60 個 sqlx migration 壓成單一 baseline

2026-07-02,趁著同一個停機窗口,一次做完兩件拖了一陣子的事:資料庫從 postgres:17-alpine 升到 postgres:18-alpine,以及把後端累積近兩年的 60 個 sqlx migration 壓成單一 baseline。實際停機約 2 分鐘,API 中斷約 15 分鐘(等 CI build),資料零遺失。

為什麼要做

PG 18:其實沒有急迫性——PG 17 官方支援到 2029 年 11 月。但 PG 18 有幾個想用的東西:原生 uuidv7()、非同步 I/O(io_uring),以及 pg_upgrade 開始保留統計資訊,之後大版本升級成本更低。

Migration squash:專案從 2024 年 10 月累積了 60 個 migration(120 個檔案),裡面甚至有「建了 stocks 表、後來又整張砍掉」這種死路徑。每次新機器 bootstrap 都要完整重播一遍歷史,而 schema 的「現在長什麼樣」散落在 60 個檔案裡,沒有單一可讀的事實來源。

為什麼一起做:這是關鍵決策。兩件事各自都需要停機——PG 大版本升級要 dump/restore,migration squash 要動生產 DB 的 _sqlx_migrations 表。合併在同一個維護窗口,成本互相攤薄。

兩個必須先知道的坑

坑一:PG 大版本不能直接換 image tag

PostgreSQL 的資料目錄格式跨大版本不相容。直接把 compose 的 image 從 17 改成 18,容器會直接報 database files are incompatible with server 起不來。一定要走 pg_upgrade 或 dump/restore——個人站資料量小,dump/restore 最穩。

坑二:PG 18 官方 image 改了 volume 佈局

postgres:18 的 Docker image 把 volume 掛載點從 /var/lib/postgresql/data 改成 /var/lib/postgresql,實際資料目錄由 image 決定為 <mount>/18/docker。這是為了讓未來大版本升級可以用 image 內建的 pg_upgrade 流程。compose 要跟著改:

  database:
    image: postgres:18-alpine
    volumes:
      - /srv/kawa/dbdata:/var/lib/postgresql   # 不再是 …/data
    # 移除原本的 PGDATA 覆寫,交給 image 預設

如果沿用舊掛載路徑,資料會寫進匿名 volume,重啟就消失。

Migration squash 的原理

sqlx 在啟動時(sqlx::migrate!)會比對兩邊:

內嵌在 binary 裡的 migration 清單(編譯時從 migrations/ 讀入)
DB 裡 _sqlx_migrations 表的套用紀錄(version + SHA-384 checksum)

squash 之後 binary 只認得 baseline 一筆,但生產 DB 裡躺著 60 筆舊紀錄,直接部署會報 migration was previously applied but is missing。所以必須手動把 _sqlx_migrations 清掉、插入 baseline 那一筆,checksum 就是 baseline 檔案的 SHA-384,可以事先在本地算好。

事前準備(本地)

產生 baseline

起一個臨時 PG 18 容器,建兩個空庫:

kawa_old:跑完整 60 個 migration(順帶驗證了現有 migration 在 PG 18 上全數乾淨套過)
kawa_new:之後跑 baseline,做對照組

從 kawa_old dump 出兩份東西合成 baseline:

# schema(排除 _sqlx_migrations)
pg_dump --schema-only --no-owner --no-privileges -T _sqlx_migrations

# 種子資料(migration 裡 INSERT 過的四張表)
pg_dump --data-only --column-inserts -t roles -t permissions -t role_permissions -t app_settings

有幾個整理細節:

PG 18 的 pg_dump 會輸出 \restrict / \unrestrict 這種 psql 專用指令,sqlx 跑不了,要清掉,連同 SET 開頭的 session 設定一起
種子資料用 --column-inserts 而不是預設的 COPY——sqlx migration 不支援 COPY FROM stdin
種子以明確 id 插入(保留歷史刪除留下的 id 空洞),最後補 setval 對齊序列,不然下一筆 INSERT 會撞主鍵

驗證等價性

這步不能省。kawa_new 套 baseline 後,兩庫各自 pg_dump 出 schema 和種子資料做 diff——必須完全一致。再驗 down → up 循環正常、序列 nextval 正確接在 max+1、cargo clippy -D warnings + cargo test 全過。

最後記下 baseline 的 checksum:

sha384sum migrations/20260702000000_baseline.up.sql

sqlx 的 checksum 就是檔案內容的 SHA-384,生產修表會用到。也因此baseline 一旦上了生產就不能再改內容,sqlx 啟動時驗 checksum 不符會直接拒起。

生產切換(停機窗口)

策略:先 commit 不 push,VPS 上手動做完 DB 切換,最後 push 讓 CI 接手。

# 1. 備份(站台不中斷)
docker exec database pg_dump -U kawa -d kawa > ~/kawa-pg17-dump.sql

# 2. 停站、換資料目錄(停機開始)
cd ~/kawa-deploy && docker compose down
docker run --rm -v /srv/kawa:/d alpine mv /d/dbdata /d/dbdata-pg17   # 舊資料改名保留當回滾保險
# 本機:rsync 新版 compose 上去

# 3. 起 PG 18、灌回
docker compose up -d database    # 等 healthy,首次啟動初始化全新 PG 18 空庫
docker exec -i database psql -U kawa -d kawa -v ON_ERROR_STOP=1 < ~/kawa-pg17-dump.sql

# 4. 修 _sqlx_migrations:60 筆 → baseline 一筆
docker exec database psql -U kawa -d kawa -c "
  BEGIN;
  TRUNCATE _sqlx_migrations;
  INSERT INTO _sqlx_migrations (version, description, installed_on, success, checksum, execution_time)
  VALUES (20260702000000, 'baseline', now(), true, decode('<事先算好的 SHA-384>','hex'), 0);
  COMMIT;
"

# 5. 起全站、push
docker compose up -d    # 舊 backend image 對不上 baseline,啟動失敗直接退出——預期行為
git push                # CI build 新 image 部署後 backend 恢復

restore 的輸出可以順便對帳:36 萬筆行情資料、11 萬筆收盤價都 COPY 進來,所有 setval / index / trigger 正常。

一個預期外的插曲:CI 時序 race

push 之後 deploy.yml(只 rsync 編排設定,不 build)比 backend.yml(要 build Rust image)先跑到 deploy,此時 Docker Hub 上的 :latest 還是舊後端——舊 binary 對不上 baseline 紀錄,啟動即退,nginx 找不到 upstream backend 跟著死,workflow 在 docker compose exec nginx nginx -t 那步吃了 SIGKILL(exit 137)紅燈收場。

不用處理:等 backend.yml build 完部署,backend 起來、nginx 跟著活,回去把失敗的 run 按 Re-run failed jobs 補綠燈即可。事後看這是切換方案先天的時間差,單次維護窗口可接受。

結果

	之前	之後
PostgreSQL	17	18.4
migration 檔案	120 個(60 組)	2 個(1 組 baseline)
新機 bootstrap	重播 60 步歷史	套一步 baseline
schema 事實來源	散落 60 個檔案	單一可讀檔案

停機約 2 分鐘(compose down 到資料灌回完成),API 中斷約 15 分鐘(等 CI build 新 image),前台頁面在窗口內大部分時間可用。

事後清單

舊資料目錄 dbdata-pg17 和 dump 檔留幾天,確認排程 job(每日抓行情、發票對獎)都跑過一輪再刪
本地開發 DB 砍掉重建,cargo run 自動套 baseline
歷史 migration 沒有消失——都在 git 紀錄裡,只是不再參與 runtime

心得

兩件要停機的事,想辦法排進同一個窗口,成本攤薄後每件事都變便宜
等價性驗證(兩庫 diff)是整件事的信心來源,有了它才敢動生產的 _sqlx_migrations
checksum 事先算好寫進 runbook,窗口內就是照抄貼上,不用臨場思考
每一步都要有回滾路徑:改名保留的舊資料目錄 + dump 檔 + Docker Hub 上的 :<sha> 舊 image tag
CI 的多條 workflow 對同一台機器部署時,path-based 觸發 + 序列化不等於順序保證,跨 workflow 有依賴的變更要預想時間差

PostgreSQL 17 → 18 升級 + 60 個 sqlx migration 壓成單一 baseline

為什麼要做

兩個必須先知道的坑

坑一:PG 大版本不能直接換 image tag

坑二:PG 18 官方 image 改了 volume 佈局

  database:
    image: postgres:18-alpine
    volumes:
      - /srv/kawa/dbdata:/var/lib/postgresql   # 不再是 …/data
    # 移除原本的 PGDATA 覆寫,交給 image 預設

如果沿用舊掛載路徑,資料會寫進匿名 volume,重啟就消失。

Migration squash 的原理

sqlx 在啟動時(sqlx::migrate!)會比對兩邊:

內嵌在 binary 裡的 migration 清單(編譯時從 migrations/ 讀入)
DB 裡 _sqlx_migrations 表的套用紀錄(version + SHA-384 checksum)

事前準備(本地)

產生 baseline

起一個臨時 PG 18 容器,建兩個空庫:

kawa_old:跑完整 60 個 migration(順帶驗證了現有 migration 在 PG 18 上全數乾淨套過)
kawa_new:之後跑 baseline,做對照組

從 kawa_old dump 出兩份東西合成 baseline:

# schema(排除 _sqlx_migrations)
pg_dump --schema-only --no-owner --no-privileges -T _sqlx_migrations

# 種子資料(migration 裡 INSERT 過的四張表)
pg_dump --data-only --column-inserts -t roles -t permissions -t role_permissions -t app_settings

有幾個整理細節:

PG 18 的 pg_dump 會輸出 \restrict / \unrestrict 這種 psql 專用指令,sqlx 跑不了,要清掉,連同 SET 開頭的 session 設定一起
種子資料用 --column-inserts 而不是預設的 COPY——sqlx migration 不支援 COPY FROM stdin
種子以明確 id 插入(保留歷史刪除留下的 id 空洞),最後補 setval 對齊序列,不然下一筆 INSERT 會撞主鍵

驗證等價性

最後記下 baseline 的 checksum:

sha384sum migrations/20260702000000_baseline.up.sql

sqlx 的 checksum 就是檔案內容的 SHA-384,生產修表會用到。也因此baseline 一旦上了生產就不能再改內容,sqlx 啟動時驗 checksum 不符會直接拒起。

生產切換(停機窗口)

策略:先 commit 不 push,VPS 上手動做完 DB 切換,最後 push 讓 CI 接手。

# 1. 備份(站台不中斷)
docker exec database pg_dump -U kawa -d kawa > ~/kawa-pg17-dump.sql

# 2. 停站、換資料目錄(停機開始)
cd ~/kawa-deploy && docker compose down
docker run --rm -v /srv/kawa:/d alpine mv /d/dbdata /d/dbdata-pg17   # 舊資料改名保留當回滾保險
# 本機:rsync 新版 compose 上去

# 3. 起 PG 18、灌回
docker compose up -d database    # 等 healthy,首次啟動初始化全新 PG 18 空庫
docker exec -i database psql -U kawa -d kawa -v ON_ERROR_STOP=1 < ~/kawa-pg17-dump.sql

# 4. 修 _sqlx_migrations:60 筆 → baseline 一筆
docker exec database psql -U kawa -d kawa -c "
  BEGIN;
  TRUNCATE _sqlx_migrations;
  INSERT INTO _sqlx_migrations (version, description, installed_on, success, checksum, execution_time)
  VALUES (20260702000000, 'baseline', now(), true, decode('<事先算好的 SHA-384>','hex'), 0);
  COMMIT;
"

# 5. 起全站、push
docker compose up -d    # 舊 backend image 對不上 baseline,啟動失敗直接退出——預期行為
git push                # CI build 新 image 部署後 backend 恢復

restore 的輸出可以順便對帳:36 萬筆行情資料、11 萬筆收盤價都 COPY 進來,所有 setval / index / trigger 正常。

一個預期外的插曲:CI 時序 race

結果

	之前	之後
PostgreSQL	17	18.4
migration 檔案	120 個(60 組)	2 個(1 組 baseline)
新機 bootstrap	重播 60 步歷史	套一步 baseline
schema 事實來源	散落 60 個檔案	單一可讀檔案

停機約 2 分鐘(compose down 到資料灌回完成),API 中斷約 15 分鐘(等 CI build 新 image),前台頁面在窗口內大部分時間可用。

事後清單

舊資料目錄 dbdata-pg17 和 dump 檔留幾天,確認排程 job(每日抓行情、發票對獎)都跑過一輪再刪
本地開發 DB 砍掉重建,cargo run 自動套 baseline
歷史 migration 沒有消失——都在 git 紀錄裡,只是不再參與 runtime

心得

兩件要停機的事,想辦法排進同一個窗口,成本攤薄後每件事都變便宜
等價性驗證(兩庫 diff)是整件事的信心來源,有了它才敢動生產的 _sqlx_migrations
checksum 事先算好寫進 runbook,窗口內就是照抄貼上,不用臨場思考
每一步都要有回滾路徑:改名保留的舊資料目錄 + dump 檔 + Docker Hub 上的 :<sha> 舊 image tag
CI 的多條 workflow 對同一台機器部署時,path-based 觸發 + 序列化不等於順序保證,跨 workflow 有依賴的變更要預想時間差

PostgreSQL 17 → 18 升級 + 60 個 sqlx migration 壓成單一 baseline

為什麼要做

兩個必須先知道的坑

坑一:PG 大版本不能直接換 image tag

坑二:PG 18 官方 image 改了 volume 佈局

Migration squash 的原理

事前準備(本地)

產生 baseline

驗證等價性

生產切換(停機窗口)

一個預期外的插曲:CI 時序 race

結果

事後清單

心得

留言

PostgreSQL 17 → 18 升級 + 60 個 sqlx migration 壓成單一 baseline

為什麼要做

兩個必須先知道的坑

坑一:PG 大版本不能直接換 image tag

坑二:PG 18 官方 image 改了 volume 佈局

Migration squash 的原理

事前準備(本地)

產生 baseline

驗證等價性

生產切換(停機窗口)

一個預期外的插曲:CI 時序 race

結果

事後清單

心得

留言