摘要：
postgresql 数据库的相关：
第116期: PostgreSQL 区域设置（Locale）与排序规则（Collation）与操作系统的关联 ： https://www.modb.pro/db/2015028765127172096
第117期: pg_basebackup + waiting for checkpoint: https://www.modb.pro/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大小（MB）
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，临时调小）
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）。
