#!/bin/bash# 文件名: pg_full_backup.sh# 功能: 使用 pg_basebackup 执行全量物理备份，压缩备份目录并保留元数据文件set -euo pipefailBACKUP_ROOT="/home/postgres/backupfiles/phybackup"PG_HOST="localhost"PG_PORT="5432"PG_USER="postgres"export PGPASSWORD="${PGPASSWORD:-}" # 建议使用 ~/.pgpassTIMESTAMP=$(date +%Y%m%d_%H%M%S)BACKUP_DIR="${BACKUP_ROOT}/full_${TIMESTAMP}"BACKUP_TAR="${BACKUP_ROOT}/full_${TIMESTAMP}.tar.gz"META_FILE="${BACKUP_ROOT}/full_meta_${TIMESTAMP}.txt"log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "${BACKUP_ROOT}/full_backup.log"; }mkdir -p "$BACKUP_DIR"log "开始全量备份 -> $BACKUP_DIR"pg_basebackup -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -D "$BACKUP_DIR" \--checkpoint=fast --wal-method=stream# 获取停止 LSN（全量备份结束后的当前 LSN）STOP_LSN=$(psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -d postgres \-t -c "SELECT pg_current_wal_lsn();" | tr -d ' ')if [[ -z "$STOP_LSN" ]]; thenlog "错误: 无法获取当前 LSN"exit 1fi# 写入元数据文件（独立保留）cat > "$META_FILE" <<EOFFULL_BACKUP_STOP_LSN=$STOP_LSNBACKUP_TIME=$TIMESTAMPBACKUP_METHOD=pg_basebackupBACKUP_TAR_FILE=$(basename "$BACKUP_TAR")EOF# 压缩备份目录log "正在压缩备份目录 -> $BACKUP_TAR"tar czf "$BACKUP_TAR" -C "$BACKUP_ROOT" "$(basename "$BACKUP_DIR")"if [[ $? -eq 0 ]]; thenlog "压缩完成，删除原始目录 $BACKUP_DIR"rm -rf "$BACKUP_DIR"elselog "错误: 压缩失败，保留原始目录"exit 1filog "全量备份完成，停止 LSN = $STOP_LSN，元数据文件: $META_FILE"
WAL归档备份
这个归档备份，需要开启archive_mode，并设置archive_command，设置这两项参数，需要重启数据库才能生效。
vi postgresql.confarchive_mode = onarchive_command = 'cp %p usr/local/pgsql/data/pg_archive/%f'
归档备份脚本如下：
#!/bin/bash# 文件名: pg_incr_from_archive.sh# 功能: 基于 WAL 归档目录生成增量备份包（累积自上次全量）# 改进: 使用 cp 代替 ln，增加详细错误检查，确保打包成功set -uo pipefail # 移除 -e，避免命令失败直接退出，改为手动检查# ========== 配置 ==========BACKUP_ROOT="/home/postgres/backupfiles/phybackup"WAL_ARCHIVE_DIR="/usr/local/pgsql/data/pg_archive"PG_HOST="localhost"PG_PORT="5444"PG_USER="postgres"export PGPASSWORD="${PGPASSWORD:-}"# ========== 函数 ==========log() {echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "${BACKUP_ROOT}/incr_backup.log"}error_exit() {log "ERROR: $*"exit 1}# 根据 LSN 获取 WAL 文件名lsn_to_wal() {psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -d postgres -t -c "SELECT pg_walfile_name('$1');" | tr -d ' '}# 生成两个 WAL 文件名之间的所有文件名（十六进制递增）wal_range() {local start="$1" end="$2"local start_tl="${start:0:8}" start_log="${start:8:8}" start_seg="${start:16:8}"local end_tl="${end:0:8}" end_log="${end:8:8}" end_seg="${end:16:8}"if [[ "$start_tl" != "$end_tl" ]]; thenlog "警告: 起始和结束时间线不同 ($start_tl vs $end_tl)，可能无法枚举全部，仅返回起始文件"echo "$start"returnfilocal cur_log=$((16#$start_log))local cur_seg=$((16#$start_seg))local end_log_val=$((16#$end_log))local end_seg_val=$((16#$end_seg))while :; doprintf "%s%08X%08X\n" "$start_tl" $cur_log $cur_segif [[ $cur_log -eq $end_log_val && $cur_seg -eq $end_seg_val ]]; thenbreakfi((cur_seg++))if ((cur_seg >= 256)); thencur_seg=0((cur_log++))fi# 防止无限循环（最多 256*256 个文件）if ((cur_log > end_log_val + 10)); thenlog "警告: 枚举超出范围，提前终止"breakfidone}# 查找最近一次全量备份的元数据文件get_latest_full_meta() {find "$BACKUP_ROOT" -maxdepth 1 -type f -name "full_meta_*.txt" 2>/dev/null | sort -r | head -1}# ========== 主流程 ==========main() {# 检查目录if [[ ! -d "$BACKUP_ROOT" ]]; thenerror_exit "备份根目录不存在: $BACKUP_ROOT"fiif [[ ! -d "$WAL_ARCHIVE_DIR" ]]; thenerror_exit "WAL 归档目录不存在: $WAL_ARCHIVE_DIR"fi# 1. 找到全量元数据LATEST_META=$(get_latest_full_meta)if [[ -z "$LATEST_META" ]]; thenerror_exit "未找到全量备份元数据文件（full_meta_*.txt）"filog "使用全量备份元数据: $(basename "$LATEST_META")"# 2. 读取停止 LSNSTOP_LSN=$(grep '^FULL_BACKUP_STOP_LSN=' "$LATEST_META" | cut -d= -f2)if [[ -z "$STOP_LSN" ]]; thenerror_exit "无法从元数据中获取停止 LSN"filog "全量备份停止 LSN = $STOP_LSN"# 3. 获取当前 LSNCURRENT_LSN=$(psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -d postgres -t -c "SELECT pg_current_wal_lsn();" | tr -d ' ')if [[ -z "$CURRENT_LSN" ]]; thenerror_exit "无法获取当前 LSN"filog "当前 LSN = $CURRENT_LSN"# 4. 检查是否有新 WALif [[ "$CURRENT_LSN" == "$STOP_LSN" ]]; thenlog "没有新 WAL 产生，跳过本次增量备份"exit 0fi# 5. 将 LSN 转换为 WAL 文件名START_WAL=$(lsn_to_wal "$STOP_LSN")END_WAL=$(lsn_to_wal "$CURRENT_LSN")if [[ -z "$START_WAL" || -z "$END_WAL" ]]; thenerror_exit "LSN 转 WAL 文件名失败"filog "WAL 文件名范围: $START_WAL -> $END_WAL"# 6. 生成 WAL 文件列表并复制到临时目录TEMP_DIR=$(mktemp -d -t pg_incr_XXXXXX)if [[ ! -d "$TEMP_DIR" ]]; thenerror_exit "创建临时目录失败"filog "临时目录: $TEMP_DIR"COUNT=0MISSING_COUNT=0for wal_file in $(wal_range "$START_WAL" "$END_WAL"); dosrc="${WAL_ARCHIVE_DIR}/${wal_file}"if [[ -f "$src" ]]; then# 使用 cp 代替 ln（避免跨文件系统问题）if cp "$src" "$TEMP_DIR/" 2>/dev/null; then((COUNT++))log "已复制: $wal_file"elselog "警告: 复制失败 $wal_file (权限或空间不足)"((MISSING_COUNT++))fielselog "警告: WAL 文件缺失: $src"((MISSING_COUNT++))fidoneif [[ $COUNT -eq 0 ]]; thenlog "未找到任何可用的 WAL 文件，增量备份为空"rm -rf "$TEMP_DIR"exit 0filog "成功复制 $COUNT 个 WAL 文件，缺失 $MISSING_COUNT 个"# 7. 打包TIMESTAMP=$(date +%Y%m%d_%H%M%S)INCR_TAR="${BACKUP_ROOT}/incr_${TIMESTAMP}.tar.gz"if tar -zcvf "$INCR_TAR" -C "$TEMP_DIR" . >/dev/null 2>&1; thenlog "增量包创建成功: $INCR_TAR"elseerror_exit "tar 打包失败"fi# 8. 生成元数据文件cat > "${INCR_TAR}.info" <<EOFBASE_FULL_BACKUP_META=$(basename "$LATEST_META")START_LSN=$STOP_LSNEND_LSN=$CURRENT_LSNWAL_COUNT=$COUNTBACKUP_TIME=$TIMESTAMPEOF# 9. 清理临时目录rm -rf "$TEMP_DIR"log "增量备份完成，包大小: $(du -h "$INCR_TAR" | cut -f1)"}# 执行main "$@"
部署自动任务
全库备份，只需每天执行一次，增量备份，设定每2小时执行一次
crontab -e0 02 * * * bin/bash home/postgres/scripts/pg_full_backup.sh10 01,03,05,07,09,11,13,15,17,19,21,23 * * * /bin/bash /home/postgres/scripts/pg_incr_from_archive.sh
两个脚本生成的文件及内容
主要生成的文件有全库备份的摘要信息、输出日志、备份集文件、增量备份日志、增量备份摘要内容和增量备份的备份集。
full_meta_20260428_151115.txtfull_20260428_151115.tar.gzfull_backup.logincr_20260428_173747.tarincr_20260428_173747.tar.infoincr_20260429_153422.tar.gzincr_20260429_153422.tar.gz.infoincr_backup.log1[postgres@mytest01 phybackup]$ cat full_meta_20260428_151115.txtFULL_BACKUP_STOP_LSN=1/27000060BACKUP_TIME=20260428_151115BACKUP_METHOD=pg_basebackupBACKUP_TAR_FILE=full_20260428_151115.tar.gz[postgres@mytest01 phybackup]$[postgres@mytest01 phybackup]$ cat full_backup.log[2026-04-28 14:57:35] 开始全量备份 -> /home/postgres/backupfiles/phybackup/full_20260428_145735[2026-04-28 14:58:03] 全量备份完成，停止 LSN =[2026-04-28 15:11:15] 开始全量备份 -> /home/postgres/backupfiles/phybackup/full_20260428_151115[2026-04-28 15:11:43] 正在压缩备份目录 -> /home/postgres/backupfiles/phybackup/full_20260428_151115.tar.gz[2026-04-28 15:13:19] 压缩完成，删除原始目录 /home/postgres/backupfiles/phybackup/full_20260428_151115[2026-04-28 15:13:20] 全量备份完成，停止 LSN = 1/27000060，元数据文件: /home/postgres/backupfiles/phybackup/full_meta_20260428_151115.txt[postgres@mytest01 phybackup]$[postgres@mytest01 phybackup]$ cat incr_20260428_173747.tar.infoBASE_FULL_BACKUP_META=full_meta_20260428_151115.txtSTART_LSN=1/27000060END_LSN=1/2C000060WAL_COUNT=5BACKUP_TIME=20260428_173747[postgres@mytest01 phybackup]$[postgres@mytest01 phybackup]$ cat incr_backup.log[2026-04-28 17:32:46] 使用全量备份元数据: full_meta_20260428_151115.txt[2026-04-28 17:32:46] 全量备份停止 LSN = 1/27000060[2026-04-28 17:32:46] 当前 LSN = 1/2B000000[2026-04-28 17:32:46] WAL 文件名范围: 000000050000000100000027 -> 00000005000000010000002A[2026-04-28 17:37:47] 使用全量备份元数据: full_meta_20260428_151115.txt[2026-04-28 17:37:47] 全量备份停止 LSN = 1/27000060[2026-04-28 17:37:47] 当前 LSN = 1/2C000060[2026-04-28 17:37:47] WAL 文件名范围: 000000050000000100000027 -> 00000005000000010000002C[2026-04-28 17:37:47] 临时目录: /tmp/pg_incr_EB3JDG[2026-04-28 17:37:47] 已复制: 000000050000000100000027[2026-04-28 17:37:47] 已复制: 000000050000000100000028[2026-04-28 17:37:47] 已复制: 000000050000000100000029[2026-04-28 17:37:47] 已复制: 00000005000000010000002A[2026-04-28 17:37:47] 已复制: 00000005000000010000002B[2026-04-28 17:37:47] 警告: WAL 文件缺失: /usr/local/pgsql/data/pg_archive/00000005000000010000002C[2026-04-28 17:37:47] 成功复制 5 个 WAL 文件，缺失 1 个[2026-04-28 17:37:47] 增量包创建成功: /home/postgres/backupfiles/phybackup/incr_20260428_173747.tar[2026-04-28 17:37:47] 增量备份完成，包大小: 81M[2026-04-29 15:34:22] 使用全量备份元数据: full_meta_20260428_151115.txt[2026-04-29 15:34:22] 全量备份停止 LSN = 1/27000060[2026-04-29 15:34:22] 当前 LSN = 1/2C000148[2026-04-29 15:34:22] WAL 文件名范围: 000000050000000100000027 -> 00000005000000010000002C[2026-04-29 15:34:22] 临时目录: /tmp/pg_incr_uxK8dP[2026-04-29 15:34:22] 已复制: 000000050000000100000027[2026-04-29 15:34:22] 已复制: 000000050000000100000028[2026-04-29 15:34:22] 已复制: 000000050000000100000029[2026-04-29 15:34:22] 已复制: 00000005000000010000002A[2026-04-29 15:34:22] 已复制: 00000005000000010000002B[2026-04-29 15:34:22] 警告: WAL 文件缺失: /usr/local/pgsql/data/pg_archive/00000005000000010000002C[2026-04-29 15:34:22] 成功复制 5 个 WAL 文件，缺失 1 个[2026-04-29 15:34:22] 增量包创建成功: /home/postgres/backupfiles/phybackup/incr_20260429_153422.tar.gz[2026-04-29 15:34:22] 增量备份完成，包大小: 88K
异机备份恢复
基于上面生成的备份文件，拿到异机做备份恢复，操作如下。
1、恢复数据库文件
tar -xzvf /data/pg_backup/pgfull_20260429_095341.tar.gz -C /data/pg/pgdata/mv pgfull_20260429_095341 pgfullchmod 700 pgfull
2、恢复WAL归档
tar -xvf /data/pg_backup/incr_20260429_151001.tar -C /data/pg/backup/backupfiles/archive
3、编辑postgresql.conf参数
vi postgresql.confarchive_command = 'cp %p /data/pg/pgdata/pgfull/pg_archive/%f'restore_command = 'cp /data/pg/backup/backupfiles/archive/%f %p'
4、启动并恢复数据库
pg_ctl -D /data/pg/pgdata/pgfull -l logfile start
到此，整个数据库已经恢复好了。
文章转载自skylines，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
