xDev Asia

升級 PostgreSQL 主要版本、Patroni 版本、零停機升級技術、回滾程序和實驗室升級 PG 17 至 18。

PostgreSQL 與 Patroni 和 PostgreSQL 高可用性etcd

第 5 部分：安全性與安全性進階

xdev.asia

目標___HTMLTAG_66__HTMLTAG_67_學完本課程後，您將：__HTMLTAG_68__HTMLTAG_69__HTMLTAG_70_規劃與執行 PostgreSQL 主要版本升級__HTMLTAG_71__HTMLTAG_7220726___71HT pg_upgrade 進行就地升級升級___HTMLTAG_75__HTMLTAG_76_為升級實作邏輯複製_HTMLTAG_77__HTMLTAG_78_安全回溯失敗的升級_HTMLTAG_79__HTMLTAG_80__HTMLTAG_81_11。升級計劃_HTMLTAG_82__HTMLTAG_83_1.1。升級前核對錶_HTMLTAG_84__CODEBLOCK_0__HTMLTAG_85___1.2。版本相容性矩陣

___HTMLTAG_88__HTMLTAG_89__HTMLTAG_90__HTMLTAG_91___從 → 到___HTMLTAG_92__HTMLTAG_93___方法___HTMLTAG_94__HTMLTAG_95___停機時間___HTMLTAG_96__HT ___風險___HTMLTAG_98__HTMLTAG_99______HTMLTAG_100__HTMLTAG_101__HTMLTAG_102__HTMLTAG_103___17 → 18___HTMLTAG_104__HTMLTAG_105___17 → 18___HTMLTAG_104__HTMLTAG_10512_up _108__HTMLTAG_109___低___HTMLTAG_110__HTMLTAG_111__HTMLTAG_112__HTMLTAG_113___15 → 18___HTMLTAG_114__HTMLTAG_115___pg_110___MLTAG_114__HTMLTAG_115___pg_1upgrade___MLTAG_114__HTMLTAG_115___pg_1upgrade___MLGMLTAG10111_41_Uup 118__HTMLTAG_119___中___HTMLTAG_120__HTMLTAG_121__HTMLTAG_122__HTMLTAG_123___12 → 18___HTMLTAG_124__HTMLTAG_125___邏輯複製___HTMLTAG_126__HTMLTAG_127___無___HTMLTAG_128__HTMLTAG_129___中___HTMLTAG_130__HTMLTAG_131__HTMLTAG_132___HTMLTAG_130__HTMLTAG_131__HTMLTAG_132313131312__HT. 18___HTMLTAG_134__HTMLTAG_135___轉儲/恢復___HTMLTAG_136__HTMLTAG_137___小時___HTMLTAG_138__HTMLTAG_139___高___HTMLTAG_140__HTMLTAG_141__HTTAG_141__HT14G140__HT

1.3。記錄目前狀態___HTMLTAG_146__CODEBLOCK_1__HTMLTAG_147_2。 PostgreSQL 小版升級_HTMLTAG_148__HTMLTAG_149_2.1。小升級過程（例如，18.0 → 18.1）_HTMLTAG_150__CODEBLOCK_2__HTMLTAG_151_2.2。滾動次要升級_HTMLTAG_152__CODEBLOCK_3__HTMLTAG_153___3。使用 pg_upgrade___HTMLTAG_154__HTMLTAG_155_3.1 升級 PostgreSQL 主要版本。架構_HTMLTAG_156__CODEBLOCK_4__HTMLTAG_157_3.2。安裝新的 PostgreSQL 版本_HTMLTAG_158__CODEBLOCK_5__HTMLTAG_159_3.3。準備升級（節點 2 - 第一份）_HTMLTAG_160__CODEBLOCK_6__HTMLTAG_161___3.4。更新 v18

# /etc/patroni/patroni.yml
postgresql:
  bin_dir: /usr/lib/postgresql/18/bin  # Changed from 17
  data_dir: /var/lib/postgresql/18/data  # Changed from 17
  # ... rest of config

# Start Patroni with new version
sudo systemctl start patroni
Verify node2 is now running v18
psql -h node2 -U postgres -c "SELECT version();"

3.5 的 Patroni 設定。升級剩餘節點___HTMLTAG_164__CODEBLOCK_9__HTMLTAG_165_3.6。升級後任務_HTMLTAG_166__CODEBLOCK_10__HTMLTAG_167_4。透過邏輯複製實現零停機升級_HTMLTAG_168__HTMLTAG_169_4.1。架構_HTMLTAG_170__CODEBLOCK_11__HTMLTAG_171_4.2。設定新的 v18 叢集_HTMLTAG_172__CODEBLOCK_12__HTMLTAG_173_4.3。在 v17 上建立發布（來源）_HTMLTAG_174__CODEBLOCK_13__HTMLTAG_175_4.4。在 v18（目標）_HTMLTAG_176__CODEBLOCK_14__HTMLTAG_177_4.5 上建立訂閱。監視複製延遲_HTMLTAG_178__CODEBLOCK_15__HTMLTAG_179_4.6。切換程序_HTMLTAG_180__CODEBLOCK_16__HTMLTAG_181_5。 Patroni 版本升級_HTMLTAG_182__HTMLTAG_183_5.1。檢查相容性_HTMLTAG_184__CODEBLOCK_17__HTMLTAG_185___5.2。升級 Patroni（Python 套件）

___CODEBLOCK_18__HTMLTAG_187___5.3。滾動 Patroni 升級___HTMLTAG_188__CODEBLOCK_19__HTMLTAG_189___6。 etcd 升級___HTMLTAG_190__HTMLTAG_191___6.1。 etcd 次要升級___HTMLTAG_192__CODEBLOCK_20__HTMLTAG_193___6.2。 etcd 主要升級（例如 3.4 → 3.5）___CODEBLOCK_21__HTMLTAG_195___7。回滾策略___HTMLTAG_196__HTMLTAG_197___7.1。回滾 pg_upgrade___HTMLTAG_198__CODEBLOCK_22__HTMLTAG_199___7.2。回滾邏輯複製___HTMLTAG_200__CODEBLOCK_23__HTMLTAG_201___7.3。回滾 Patroni 升級___HTMLTAG_202__CODEBLOCK_24__HTMLTAG_203___8。測試升級___HTMLTAG_204__HTMLTAG_205___8.1。暫存環境測試___HTMLTAG_206__CODEBLOCK_25__HTMLTAG_207___8.2。升級排練

# Practice upgrade multiple times Time each step Identify bottlenecks Refine procedures Example timing log: Step 1: Stop Patroni - 5 seconds Step 2: pg_upgrade --check - 30 seconds Step 3: pg_upgrade - 10 minutes Step 4: Start Patroni - 15 seconds Step 5: Replication catchup - 2 minutes Total: ~13 minutes

9。最佳實務___HTMLTAG_210__HTMLTAG_211_✅ 執行_HTMLTAG_212__HTMLTAG_213__HTMLTAG_214__HTMLTAG_215_先在暫存階段進行測試-多次_HTMLTAG_217__HTMLTAG_218__HTMLTAG_219_備份一切 - 完整備援 + WAL 歸檔__HTMLTAG_221__HTMLTAG_222__HTMLTAG_223___使用 peck_pMLgrade__22212223_。及早發現問題___HTMLTAG_225__HTMLTAG_226__HTMLTAG_227_文檔程序 - 逐步操作手冊__HTMLTAG_229__HTMLTAG_230__HTMLTAG_2311MLTAG_229__HTMLTAG_230__HTMLTAG_23111]安排時間段4__4_&&&&&&&&&&&&&&F&&&&A&&&T&T&&&&;非高峰時間_HTMLTAG_233__HTMLTAG_234__HTMLTAG_235_密切監控 - 期間升級後_HTMLTAG_237__HTMLTAG_238__HTMLTAG_239_保留舊版本_MLTAG_240_12週內不要刪除_HTMLTAG_241__HTMLTAG_242__HTMLTAG_243_使用邏輯複製 - 對於零停機_HTMLTAG_245__HTMLTAG_246__HTMLTAG_247___HTMLTAG_245__HTMLTAG_246__HTMLTAG_247___HTMLTAG_245__HTMLTAG_246__HTMLTAG_247_1升級升級_MLTAG__2488 PostgreSQL升級後_HTMLTAG_249__HTMLTAG_250__HTMLTAG_251_真空分析-主要後升級_HTMLTAG_253__HTMLT AG_254__HTMLTAG_255❌請勿___HTMLTAG_256__HTMLTAG_257__HTMLTAG_258__HTMLTAG_259_不要跳過備份-關鍵安全網_HTMLTAG_261__HTMLTAG_262__HTMLTAG_263_不要升級全部立即-滾動升級_HTMLTAG_265__HTMLTAG_266__HTMLTAG_26 7_不要立即刪除舊集群 -保留回滾__HTMLTAG_269__HTMLTAG_270__HTMLTAG_271_不要忽略發布註釋 -重大變更_HTMLTAG_273__HTMLTAG_274__HTMLTAG_275_不要跳過測試 - 分階段至關重要__HTMLTAG_277__HTMLTAG_278__HTMLTAG_279___*高峰時段計畫維護視窗_HTMLTAG_281__HTMLTAG_282__HTMLTAG_283___不要忘記擴充_ - 可能需要更新___HTMLTAG_285__HTMLTAG_286__HTMLTAG_287_10。實驗室練習_HTMLTAG_288__HTMLTAG_289_實驗 1：次要版本升級_HTMLTAG_290__HTMLTAG_291__HTMLTAG_292_任務：HTMLTAG_294__HTMLTAG_295ML版本___HTMLTAG_297__HTMLTAG_298_更新軟體包副本_HTMLTAG_299__HTMLTAG_300_在副本上重新啟動 Patroni__HTMLTAG_301__HTMLTAG_302_切換到升級副本__HTMLTAG_303__4MLG_3034UMLG303HTMLTAG_305__HTMLTAG_306__HTMLTAG_307___實驗 2：使用 pg_upgrade 進行主要版本升級___HTMLTAG_308__HTMLTAG_309__HTMLTAG_310___任務：HTMLTAG_31131312132132： PostgreSQL 18節點___HTMLTAG_315__HTMLTAG_316___執行 pg_upgrade --檢查副本___HTMLTAG_317__HTMLTAG_318___在副本上執行 pg_upgrade___HTMLTAG_319__HTMLTAG_320___設定_HTMLTAG_321__HTMLTAG_322_完成滾動升級_HTMLTAG_323__HTMLTAG_324__HTMLTAG_325_實驗3：透過邏輯複製進行零停機升級_HTMLTAG_326__HTMLTAG_327__HTMLTAG_328___任務：HTMLTAG_330__HTMLTAG_331__HTMLTAG_332_設定新的 v18_31TAGML_3311313]上建立發布____HTMLTAG_335__HTMLTAG_336_在 v18上建立訂閱_HTMLTAG_337__HTMLTAG_338_監視複製延遲__HTMLTAG_339__HTMLTAG_340_執行切換__HTMLTAG_341__HTMLTAG_342_驗證應用程式功能_HTMLTAG_343HTMLTATAG444451 4：回滾過程___HTMLTAG_346__HTMLTAG_347__HTMLTAG_348_任務：_HTMLTAG_350__HTMLTAG_351__HTMLTAG_352_模擬節點失敗的升級_HTMLTAG_353HTMLTAGMLTAGMLTAG14_帕特摘要_HTMLTAG_364__HTMLTAG_365___升級方法比較

HTMLTAG_368__HTMLTAG_369__HTMLTAG_370__HTMLTAG_371___方法___HTMLTAG_372__HTMLTAG_373___停機時間 HTMLTAG_374__HTMLTAG_375___複雜性___HTMLTAG_376__HTMLTAG_377___風險___HTMLTAG_378__HTMLTAG_379___使用案例___HTM LTAG_380__HTMLTAG_381__HTMLTAG_382__HTMLTAG_383__HTMLTAG_384__HTMLTAG_385___pg_upgrade HTMLTAG_387___分鐘___HTMLTAG_388__HTMLTAG_389___低___HTMLTAG_390__HTMLTAG_391___低____HTMLTAG_392__HTMLTAG_393___次要版本跳轉___HTMLTAG_394__HTTAG_394__HTTAG_394__HTTAG_394__HTTAG_394__HTTAG_394__HT 395__HTMLTAG_396__HTMLTAG_397___邏輯複製___HTMLTAG_398__HTMLTAG_399___無___HTMLTAG_400__HTMLTAG_401___高___HTMLTAG_402__HTMLTAG_403___中___MLG_____ 404__HTMLTAG_405___零停機時間必需___HTMLTAG_406__HTMLTAG_407__HTMLTAG_408__HTMLTAG_409___轉儲/恢復___HTMLTAG_410__HTMLTAG_411___小時___HTMLTAGTM_412__H12__H__H AG_413___低___HTMLTAG_414__HTMLTAG_415___低___HTMLTAG_416__HTMLTAG_417___舊版___HTMLTAG_418__HTMLTAG_419__HTMLTAG_420__HTMLTAG_418__HTMLTAG_419__HTMLTAG_420__HTMLTAG_421___pg_upgrade --link___HTMLTAG_422__HTMLTAG_423___秒___HTMLTAG_424__HTMLTAG_425___中___HTMLTAG_426__HTMLTAG_427_ 中___HTMLTAG_428__HTMLTAG_429___同一伺服器升級___HTMLTAG_430__HTMLTAG_431__HTMLTAG_432__HTMLTAG_433_

典型時間表___HTMLTAG_436__CODEBLOCK_27__HTMLTAG_437_升級清單__HTMLTAG_438__CODEBLOCK_28__HTMLTAG_439___後續步驟_

__MLTAG_440__4525課程將涵蓋真實案例研究：___HTMLTAG_444__HTMLTAG_445__HTMLTAG_446___生產架構範例___HTMLTAG_447__HTMLTAG_448______10000 成本技術___HTMLTAG_451__HTMLTAG_452___從失敗中學到的教訓___HTMLTAG_453__HTMLTAG_454___產業特定實施___HTMLTAG_455__HTMLTAG_456___

第 24 課：升級策略

Verify node2 is now running v18

Time each step

Identify bottlenecks

Refine procedures

Example timing log:

Step 1: Stop Patroni - 5 seconds

Step 2: pg_upgrade --check - 30 seconds

Step 3: pg_upgrade - 10 minutes

Step 4: Start Patroni - 15 seconds

Step 5: Replication catchup - 2 minutes

Total: ~13 minutes

典型時間表___HTMLTAG_436__CODEBLOCK_27__HTMLTAG_437___升級清單____HTMLTAG_438__CODEBLOCK_28__HTMLTAG_439___後續步驟_

典型時間表___HTMLTAG_436__CODEBLOCK_27__HTMLTAG_437_升級清單__HTMLTAG_438__CODEBLOCK_28__HTMLTAG_439___後續步驟_