摘要：
postgresql 數據庫的相關：
第116期: PostgreSQL 區域設置（Locale）與排序規則（Collation）與操作系統的關聯 ： http://m.sunline.cc/db/2015028765127172096
第117期: pg_basebackup + waiting for checkpoint: http://m.sunline.cc/db/2015065764509327360
文檔概述
本文針對 Ubuntu 系統下 PostgreSQL 12.22 執行 pg_basebackup 備份時出現的兩類核心問題——長時間卡在 waiting for checkpoint、數據復制階段速度慢，提供從「問題定位」到「落地優化」的全流程解決方案，所有命令和配置均適配 PostgreSQL 12.22 + Ubuntu（Debian系）環境，兼顧新手友好性和生產環境實用性。
一、問題背景
用戶執行以下備份命令時，先長時間卡在 waiting for checkpoint 階段，完成后又出現數據復制速度過慢的問題：
sudo -u postgres pg_basebackup -h 10.10.9.167 -p 5432 -U replica -D /opt/postgres/data -Fp -Xs -P -R -S standby_slot
二、pg_basebackup
2.1 pg_basebackup 備份核心流程
pg_basebackup 是 PostgreSQL 原生物理備份工具，核心流程為：
graph TD
A[啟動備份] --> B[請求主庫觸發Checkpoint]
B --> C{等待Checkpoint完成}
C -->|完成| D[流式復制WAL日志+全量數據文件]
C -->|超時| E[Checkpoint執行慢/IO瓶頸]
D --> F[數據寫入從庫目錄]
F -->|速度正常| G[備份完成]
F -->|速度慢| H[網絡/IO/參數未優化]
2.2 Checkpoint 核心作用
Checkpoint（檢查點）是 PostgreSQL 保障數據一致性的核心機制：
- 將內存中未刷盤的「臟數據」全部寫入磁盤；
pg_basebackup需等待 Checkpoint 完成，才能獲取「時間點一致」的數據集，避免備份數據殘缺。
三、問題1：長時間卡在 waiting for checkpoint
3.1 問題定位（先判斷是否異常）
| 等待時長 | 場景判定 | 核心原因 |
|---|---|---|
| 幾秒 ~ 30秒 | 正常 | 主庫臟頁少、IO性能好，Checkpoint快速完成 |
| 超過30秒/幾分鐘 | 異常 | 主庫IO瓶頸、臟頁過多、Checkpoint配置不合理、主庫負載過高 |
定位命令（主庫執行）
# 切換到postgres用戶（所有PostgreSQL命令需該用戶執行）
sudo su - postgres
# 1. 查看當前是否有Checkpoint在執行
psql -c "SELECT pid, query, state, now()-query_start AS duration FROM pg_stat_activity WHERE query LIKE '%checkpoint%';"
# 2. 查看最后一次Checkpoint的耗時和臟頁量（核心）
psql -c "
SELECT
checkpoint_type, -- 自動/手動觸發
checkpoint_start_time, -- 開始時間
checkpoint_end_time, -- 結束時間
ROUND(checkpoint_duration / 1000, 2) AS duration_sec, -- 耗時（秒）
buffers_written, -- 刷盤臟頁數量（越大越慢）
ROUND(wal_written / 1024 / 1024, 2) AS wal_written_mb -- 生成WAL大?。∕B）
FROM pg_stat_checkpoint
ORDER BY checkpoint_end_time DESC
LIMIT 1;
"
# 3. 排查主庫IO瓶頸（持續觀察%util列）
iostat -x 1 10 # 每1秒輸出1次，共10次
3.2 解決方案（按優先級排序）
方案1：手動觸發 Checkpoint（快速跳過等待）
適用于緊急備份場景，臨時觸發 Checkpoint 讓備份繼續（生產環境低峰期執行，避免IO突增）：
# 主庫執行（postgres用戶）
psql -c "SELECT pg_checkpoint();" # 立即觸發Checkpoint
# 備選：觸發WAL切換，間接觸發Checkpoint
psql -c "SELECT pg_switch_wal();"
方案2：優化主庫 Checkpoint 配置（根本緩解）
臨時調整主庫 postgresql.conf 參數，加快 Checkpoint 執行速度（修改后無需重啟，重載即可）：
# 編輯主庫配置文件（Ubuntu apt安裝默認路徑）
sudo nano /etc/postgresql/12/main/postgresql.conf
修改以下參數：
# 降低Checkpoint完成目標（默認0.9，調小后刷盤更激進）
checkpoint_completion_target = 0.5
# 減小WAL最大尺寸（避免單次刷盤數據量過大，默認1GB）
max_wal_size = 512MB
# 縮短Checkpoint觸發間隔（默認5min，臨時調?。?/span>
checkpoint_timeout = 3min
# 限制每秒刷盤臟頁數（SSD設64kB，機械盤設128kB）
checkpoint_flush_after = 64kB
重載配置使修改生效：
sudo -u postgres psql -c "SELECT pg_reload_conf();"
方案3：解決主庫IO瓶頸（長期根治）
若 iostat 顯示 %util ≈ 100%（磁盤IO打滿），按以下方式優化：
| 優化方式 | 操作命令/說明 | 適用場景 |
|---|---|---|
| 臨時調整磁盤預讀 | sudo blockdev --setra 16384 /dev/sda（替換為數據盤） |
機械盤（HDD）臨時提速 |
| 低峰期備份 | 避開業務高峰（如凌晨）執行pg_basebackup | 所有場景，無副作用 |
| 硬件升級 | 機械盤換SSD（讀速度提升5-10倍） | 生產環境長期優化 |
方案4：重啟卡住的備份進程
若調整后仍卡住，終止進程并重新執行：
# 終止卡住的pg_basebackup進程
sudo kill -9 $(ps -ef | grep pg_basebackup | grep -v grep | awk '{print $2}')
# 重新執行備份命令（基礎版）
sudo -u postgres pg_basebackup -h 10.10.9.167 -p 5432 -U replica -D /opt/postgres/data -Fp -Xs -P -R -S standby_slot
四、問題2：數據復制階段速度慢
4.1 先定位慢的核心環節
復制流程：主庫讀數據 → 網絡傳輸 → 從庫寫數據，用以下命令定位瓶頸：
| 排查環節 | 執行節點 | 命令 | 核心判斷依據 |
|---|---|---|---|
| 網絡瓶頸 | 從庫 | sudo apt install iftop -y && iftop -i eth0 -F 10.10.9.167/32（替換eth0為實際網卡） |
100M網卡峰值≈12MB/s，1G網卡≈120MB/s；若接近上限且穩定，說明網絡跑滿 |
| 主庫讀IO | 主庫 | iostat -x 1 |
%util≈100% 或 rMB/s遠低于硬件上限（機械盤<100MB/s，SSD>500MB/s） |
| 從庫寫IO | 從庫 | iostat -x 1 |
%util≈100% 或 wMB/s遠低于硬件上限 |
4.2 針對性優化方案（按成本從低到高）
場景1：網絡瓶頸（最常見）
方案1：啟用壓縮傳輸（成本最低，效果顯著）
添加 -z [壓縮級別] 參數，降低網絡傳輸量（壓縮級別4-6為平衡值）：
# 壓縮級別6（平衡壓縮率/CPU占用）
sudo -u postgres pg_basebackup -h 10.10.9.167 -p 5432 -U replica -D /opt/postgres/data -Fp -Xs -P -R -S standby_slot -z 6
方案2：網絡硬件/配置優化
-
短期：調整MTU為9000（巨幀），減少網絡分片（需交換機/網卡支持）：
# 主/從庫臨時調整（替換eth0） sudo ifconfig eth0 mtu 9000 -
長期：100M網卡升級為1G/10G內網網卡。
場景2：主庫讀IO瓶頸
- 優先在低峰期備份，減少主庫讀數據競爭；
- 機械盤臨時調整預讀參數（同3.2節方案3）；
- 長期將主庫數據盤換成SSD。
場景3：從庫寫IO瓶頸
方案1：臨時優化文件系統（測試環境）
減少寫數據時的同步開銷（生產環境需評估數據安全性）：
# 卸載從庫數據目錄（確保未被占用）
sudo umount /opt/postgres/data
# 重新掛載，禁用訪問時間記錄
sudo mount -o remount,noatime,nodiratime /opt/postgres/data
方案2：硬件/存儲優化（生產環境）
- 從庫數據盤換SSD（優先）；
- 單盤換RAID 10（提升寫吞吐量和可靠性）；
- 避免從庫數據目錄放在NFS/共享存儲（網絡存儲寫速遠低于本地盤）。
場景4：并行復制優化（多核提速）
PostgreSQL 10+ 支持 -j [并行數] 參數（等價 --jobs），利用多核提升復制速度：
# -j 4：并行數=CPU核心數/2（如8核用4線程，避免主庫過載）
sudo -u postgres pg_basebackup -h 10.10.9.167 -p 5432 -U replica -D /opt/postgres/data -Fp -Xs -P -R -S standby_slot -z 6 -j 4
若提示“無-j參數”（替代方案）
若定制化編譯的PostgreSQL移除了該參數，用以下方式實現“偽并行”：
# 1. 先復制非核心文件（單線程）
sudo -u postgres /usr/lib/postgresql/12/bin/pg_basebackup -h 10.10.9.167 -p 5432 -U replica -D /opt/postgres/data -Fp -Xs -P -R -S standby_slot -z 6 --exclude=base
# 2. 并行復制base目錄（4線程）
sudo -u postgres rsync -av --progress --stats -e "ssh" postgres@10.10.9.167:/var/lib/postgresql/12/main/base/ /opt/postgres/data/base/ --no-recursive | xargs -P 4 -I {} rsync -av --progress {} /opt/postgres/data/base/
場景5：數據量過大（全量備份慢）
方案1：增量備份（替代全量）
首次全量備份后，用 pg_probackup 做增量備份（僅復制變化數據）：
# 安裝pg_probackup（Ubuntu）
sudo apt install postgresql-12-probackup -y
# 初始化備份目錄（postgres用戶）
sudo su - postgres
pg_probackup init -B /opt/postgres/probackup
# 執行增量備份（遠程主庫）
pg_probackup backup \
-B /opt/postgres/probackup \
-D /var/lib/postgresql/12/main \
--remote-host=10.10.9.167 \
--remote-port=5432 \
--remote-user=replica \
--stream \
-t incremental \
-P \
--compress-algorithm=zstd \
--compress-level=6 \
-n "incremental_backup_$(date +%Y%m%d)"
方案2：清理主庫無用數據
刪除過期數據、測試表、歷史日志，減少備份數據量。
五、預防措施（長期優化）
- 固定低峰期備份：凌晨執行pg_basebackup，此時主庫臟頁少、負載低；
- 監控Checkpoint指標：長期監控
pg_stat_checkpoint，單次耗時超1分鐘及時優化； - 配置復制槽：保留
-S standby_slot參數，防止主庫WAL被提前清理； - 避免超大事務：拆分主庫大事務，減少臟頁堆積；
- 定期備份驗證：用
pg_probackup validate校驗備份完整性，避免備份損壞。
六、常見問題FAQ
Q1：執行pg_basebackup提示“permission denied”？
A1：未切換到postgres用戶執行，所有命令需加 sudo -u postgres 前綴。
Q2：-j參數提示無效？
A2：大概率執行了低版本pg_basebackup，需用絕對路徑：/usr/lib/postgresql/12/bin/pg_basebackup。
Q3：壓縮后備份速度反而變慢？
A3：壓縮級別過高（如9）導致主庫CPU過載，建議調至4-6；或主庫CPU核心數少，優先降低壓縮級別。
Q4：備份完成后從庫啟動失??？
A4：檢查從庫 standby.signal 文件是否存在（PostgreSQL 12+ 必需），或 primary_conninfo 配置是否正確（-R參數已自動生成，可查看 /opt/postgres/data/postgresql.auto.conf）。
