一次錯誤部署引發的 PostgreSQL Sequence 災難：為什麼使用者突然無法解鎖動畫？

SELECT COUNT(*) FROM user_unlocked_animations;
-- 結果: 0 😱 所有用戶的解鎖記錄全部消失！

// 從備份拉出資料，直接插入（包含原始的 ID）
blablablabla
}
// ⚠️ 直接指定了 id，但沒想到要更新 sequence...

✅ 資料恢復完成！

[2025-11-06T15:32:47] ERROR: Failed to unlock animation
Error: Unique constraint failed

ERROR: duplicate key value violates unique constraint
"user_unlocked_animations_user_lnk_pkey"
DETAIL: Key (id)=(8) already exists.

-- 資料表有 10 筆資料
SELECT COUNT(*) FROM user_unlocked_animations_user_lnk;
-- 結果: 10

SELECT MAX(id) FROM user_unlocked_animations_user_lnk;
-- 結果: 15

-- Sequence 只記到 7
SELECT last_value FROM user_unlocked_animations_user_lnk_id_seq;
-- 結果: 7

-- 😱 有 10 筆資料，MAX(id)=15，但 sequence 只有 7！

CREATE TABLE user_unlocked_animations_user_lnk (
  id SERIAL PRIMARY KEY  -- 自動建立 sequence
);
-- 對應的 sequence: user_unlocked_animations_user_lnk_id_seq

INSERT INTO table (user_id, data) VALUES (100, 'foo');
-- PostgreSQL: 從 sequence 取得 id=1，sequence 變成 2 ✅

INSERT INTO table (user_id, data) VALUES (101, 'bar');
-- PostgreSQL: 從 sequence 取得 id=2，sequence 變成 3 ✅

-- 系統嘗試插入
INSERT INTO user_unlocked_animations_user_lnk (user_id, animation_id)
VALUES (125, 7);

-- PostgreSQL:
-- 1. 從 sequence 取得 id
SELECT nextval('user_unlocked_animations_user_lnk_id_seq');
-- 結果: 8 (因為 last_value=7，下一個是 8)

-- 2. 嘗試插入 id=8
-- 3. 💥 ERROR! id=8 已經存在（是昨天恢復的舊資料）

-- 修復核心問題：user ↔ unlocked_animation link table
SELECT setval(
  'user_unlocked_animations_user_lnk_id_seq',
  (SELECT MAX(id) FROM user_unlocked_animations_user_lnk),
  true
);
-- 將 sequence 設定為當前最大 id
-- true = 下次會從 max_id + 1 開始

-- 同樣修復其他相關表
SELECT setval('user_unlocked_animations_animation_lnk_id_seq',
              (SELECT MAX(id) FROM user_unlocked_animations_animation_lnk), true);
SELECT setval('user_unlocked_animations_id_seq',
              (SELECT MAX(id) FROM user_unlocked_animations), true);

SELECT
  (SELECT last_value FROM user_unlocked_animations_user_lnk_id_seq) as seq,
  (SELECT MAX(id) FROM user_unlocked_animations_user_lnk) as max_id;

-- 修復前: seq=7,  max_id=15  ❌
-- 修復後: seq=15, max_id=15  ✅ 完美對齊！

# 使用者嘗試解鎖動畫
curl -X POST /api/user-animation/unlock -d '{"animationId": 7}'
# ✅ 成功！插入了 id=16 的新記錄

// ❌ 當時的做法（危險）
// 500 人在線，新舊資料同時寫入
await restoreOldData(); // 舊資料：id=1~8（手動）
// 同時有人解鎖 → sequence 自動生成 id=9,10,11...
// 💥 無法確保資料一致性

// ✅ 正確做法
await enableMaintenanceMode(); // 停止所有寫入
await restoreOldData();
await updateSequence(); // 確保 sequence 正確
await verifyDataIntegrity(); // 驗證
await disableMaintenanceMode();

// 移除 id，讓 PostgreSQL 重新產生
const data = backupData.map(({ id, ...rest }) => rest);
await db.insert(data);
// ✅ sequence 會自動更新

-- ❌ 直接指定 ID（sequence 不會更新）
INSERT INTO table (id, name) VALUES (1, 'foo'), (2, 'bar');

-- ✅ 插入後更新 sequence
SELECT setval('table_id_seq', (SELECT MAX(id) FROM table), true);

## 緊急資料恢復流程

1. [ ] 開啟維護模式
2. [ ] 恢復資料
3. [ ] 更新所有相關表的 sequence ⭐
4. [ ] 驗證 sequence >= MAX(id)
5. [ ] 測試插入功能
6. [ ] 關閉維護模式

// 方法 A: 不保留 ID（推薦）
const data = backupData.map(({ id, ...rest }) => rest);
await db.insert(data);

// 方法 B: 保留 ID + 更新 sequence
await db.insert(backupData);
await db.raw(
  `SELECT setval('table_id_seq', (SELECT MAX(id) FROM table), true)`
);

// config/functions/bootstrap.js
async function checkSequences() {
  const result = await db.raw(`
    SELECT last_value as seq, (SELECT MAX(id) FROM table) as max_id
    FROM table_id_seq
  `);

  if (result.seq < result.max_id) {
    throw new Error("Sequence 不同步！");
  }
}

# 監控 duplicate key 錯誤
FilterPattern: '[..., level=ERROR*, msg="*duplicate key*"]'
Alarm: >= 1 error in 5 minutes → Send alert

# 1. 看資料庫 log，不要只看應用層
/aws/rds/instance/your-db/postgresql

# 2. 搜尋: "duplicate key"

# 3. 檢查 sequence
SELECT last_value FROM table_id_seq;
SELECT MAX(id) FROM table;
# 如果 last_value < MAX(id) → sequence 問題！

# 4. 修復
SELECT setval('table_id_seq', (SELECT MAX(id) FROM table), true);

// ✅ 推薦：不保留 ID
const data = backup.map(({ id, ...rest }) => rest);

// ⚠️ 如果保留 ID，記得更新 sequence
await db.raw(
  `SELECT setval('table_id_seq', (SELECT MAX(id) FROM table), true)`
);

-- 找出所有 link tables
SELECT table_name FROM information_schema.tables
WHERE table_name LIKE '%_lnk';

-- 這些表的 sequence 也要檢查更新！