# 归档丢失常见原因
1. 存储故障：归档目录所在磁盘损坏
2. 人为误删：误删除归档文件或目录
3. 保留策略：归档保留时间设置过短
4. 网络故障：远程归档传输失败
5. 权限问题：归档进程无写入权限
6. 空间不足：归档目录空间满导致归档失败
7. 配置错误：归档命令配置错误

# 归档丢失的影响
– PITR恢复无法进行到丢失点之后
– 备库无法继续应用日志，复制中断
– 数据一致性无法保证

# 归档监控脚本
#!/bin/bash
# archive_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

ARCHIVE_DIR=”/opengauss/archive”
ALERT_THRESHOLD=3600 # 1小时未归档告警
LOG_FILE=”/opengauss/logs/archive_monitor.log”

# 检查最新归档时间
latest_archive=$(ls -lt $ARCHIVE_DIR/*.backup 2>/dev/null | head -1 | awk ‘{print $6,$7,$8}’)
latest_time=$(date -d “$latest_archive” +%s 2>/dev/null || echo 0)
current_time=$(date +%s)
time_diff=$((current_time – latest_time))

if [ $time_diff -gt $ALERT_THRESHOLD ]; then
echo “$(date): 告警 – 归档异常，最近归档时间: $latest_archive” >> $LOG_FILE
# 发送告警通知
fi

# 检查归档连续性
cd $ARCHIVE_DIR
for file in $(ls -1 *.backup 2>/dev/null | sort); do
# 检查时间线连续性
timeline=$(echo $file | cut -d’.’ -f1 | cut -c1-8)
# 记录检查日志
echo “$(date): 检查归档文件 $file” >> $LOG_FILE
done

# 备份策略优化建议
1. 增加全量备份频率：从每天1次增加到每天2次
2. 缩短增量备份间隔：从每4小时缩短到每2小时
3. 定期验证备份：每周验证一次备份可用性
4. 多地备份存储：本地+异地+云端三重备份

# 备份脚本优化
#!/bin/bash
# enhanced_backup.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

BACKUP_DIR=”/opengauss/backup”
ARCHIVE_BACKUP_DIR=”/opengauss/backup/archives”
DATE=$(date +%Y%m%d_%H%M%S)

# 备份数据
gs_probackup backup -B $BACKUP_DIR –instance=fgedudb -b FULL

# 备份归档日志
tar czvf $ARCHIVE_BACKUP_DIR/archive_backup_$DATE.tar.gz /opengauss/archive/

# 同步到远程
rsync -avz $BACKUP_DIR/ backup-server:/backup/opengauss/

# 检测归档日志连续性
#!/bin/bash
# check_archive_gap.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

ARCHIVE_DIR=”/opengauss/archive”

# 获取所有归档文件列表
archives=$(ls -1 $ARCHIVE_DIR/00000001* 2>/dev/null | sort)

prev_logid=””
prev_seg=””
gap_found=0

for archive in $archives; do
filename=$(basename $archive)
# 解析文件名: 0000000100000001000000AB
timeline=${filename:0:8}
logid=${filename:8:8}
seg=${filename:16:8}

if [ -n “$prev_logid” ]; then
# 检查连续性
prev_logid_dec=$((16#$prev_logid))
prev_seg_dec=$((16#$prev_seg))
curr_logid_dec=$((16#$logid))
curr_seg_dec=$((16#$seg))学习交流加群风哥微信: itpux-com

expected_seg=$((prev_seg_dec + 1))
if [ $curr_seg_dec -ne $expected_seg ] && [ $curr_logid_dec -eq $prev_logid_dec ]; then
echo “发现归档缺失: 时间线 $timeline, 日志ID $logid, 段号 $prev_seg -> $seg”
gap_found=1
fi
fi

prev_logid=$logid
prev_seg=$seg
done

if [ $gap_found -eq 0 ]; then
echo “归档日志连续，未发现缺失”
fi

# 检查备份与归档对应关系
$ gs_probackup show -B /opengauss/backup –instance=fgedudb

BACKUP INSTANCE ‘fgedudb’
================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
================================================================================
fgedudb 9.2 QMR8YJ 2026-04-09 03:00:01+08 FULL STREAM 1/0 45m 485GB 2GB 1.05 0/12000028 0/150000F0 OK

# 检查备份需要的归档是否存在
start_lsn=”0/12000028″
stop_lsn=”0/150000F0″

# 解析LSN到文件名
start_file=$(echo $start_lsn | awk -F’/’ ‘{printf “%08X%08X\n”, strtonum(“0x”$1), strtonum(“0x”$2)}’)
echo “备份需要的起始归档: $start_file”

# 检查文件是否存在
if [ -f “/opengauss/archive/00000001$start_file” ]; then
echo “归档文件存在”
else学习交流加群风哥QQ113257174
echo “警告: 归档文件缺失”
fi

# 场景：归档日志0000000100000005000000A0到0000000100000005000000B0缺失

# 步骤1：确定可恢复的最大LSN
# 最后一个可用归档是00000001000000050000009F
# 对应LSN为0/5000009F

# 步骤2：执行不完全恢复到缺失前的最后一个可用点
$ gs_probackup restore -B /opengauss/backup –instance=fgedudb -D /opengauss/fgdata_new -i QMR8YJ –recovery-target-lsn=”0/5000009F”

INFO: Restore begin.
INFO: Checking backup QMR8YJ
INFO: Backup QMR8YJ is valid
INFO: Restoring backup QMR8YJ
INFO: Recovery target LSN: 0/5000009F
INFO: Copying data files
INFO: Data files are copied
INFO: Generating recovery.conf
WARNING: Some WAL files are missing, recovery will stop at the last available point
INFO: Restore completed successfully

# 步骤3：启动数据库
$ gs_ctl start -D /opengauss/fgdata_new

waiting for server to start…. done
server started

# 步骤4：验证恢复结果
$ gsql -d fgedudb -c “SELECT pg_last_wal_replay_lsn();
”

pg_last_wal_replay_lsn
————————
0/5000009F更多视频教程www.fgedu.net.cn
(1 row)

$ gsql -d fgedudb -c “SELECT pg_last_xact_replay_timestamp();
”

pg_last_xact_replay_timestamp
——————————-
2026-04-09 08:45:32.123456+08
(1 row)

# 场景：备库因归档缺失导致复制中断

# 步骤1：检查备库状态
$ gsql -h fgedu-standby -d postgres -c “SELECT * FROM pg_stat_wal_receiver;
”

pid | status | receive_start_lsn | receive_start_tli | received_lsn | received_tli | latest_end_lsn | latest_end_time | slot_name
—–+——–+——————-+——————-+————–+————————-+—————-+—————–+———–
12345 | streaming | 0/50000000 | 1 | 0/500000A0 | 1 | 0/500000A0 | 2026-04-09 08:45:00+08 | fgedudb_slot

# 步骤2：检查复制延迟
$ gsql -h fgedu-standby -d postgres -c “SELECT EXTRACT(EPOCH FROM (now() – pg_last_xact_replay_timestamp()))/60 AS delay_minutes;
”

delay_minutes
—————
120.5
(1 row)

# 步骤3：停止备库
$ gs_ctl stop -D /opengauss/fgdata

waiting for server to shut down…. done
server stopped

# 步骤4：清理备库数据目录
$ rm -rf /opengauss/fgdata/*

# 步骤5：从主库重新拉取基础备份更多学习教程公众号风哥教程itpux_com
$ gs_probackup backup -B /opengauss/backup –instance=fgedudb -b FULL –remote-host=fgedu-primary –remote-user=opengauss

INFO: Backup begin.
INFO: Remote host: fgedu-primary
INFO: Starting backup…
INFO: Creating backup directory
INFO: Copying data files
INFO: Data files are copied
INFO: Backup completed successfully

# 步骤6：恢复备份到备库
$ gs_probackup restore -B /opengauss/backup –instance=fgedudb -D /opengauss/fgdata -i QMR9ZK

# 步骤7：配置备库参数
cat >> /opengauss/fgdata/postgresql.conf << EOF primary_conninfo = 'host=fgedu-primary port=5432 user=repl password=repl_pass' recovery_target_timeline = 'latest' hot_standby = on EOF # 步骤8：创建standby.signal文件 touch /opengauss/fgdata/standby.signal # 步骤9：启动备库 $ gs_ctl start -D /opengauss/fgdata waiting for server to start.... done server started # 步骤10：验证复制状态from DB视频:www.itpux.com $ gsql -h fgedu-standby -d postgres -c "SELECT * FROM pg_stat_wal_receiver;
”

pid | status | receive_start_lsn | receive_start_tli | received_lsn | received_tli | latest_end_lsn | latest_end_time | slot_name
—–+——–+——————-+——————-+————–+————–+—————-+—————–+———–
12346 | streaming | 0/60000000 | 1 | 0/60000100 | 1 | 0/60000100 | 2026-04-09 11:30:00+08 | fgedudb_slot

# 步骤1：确认缺失的归档
$ ls -la /opengauss/archive/0000000100000003000000*

-rw——- 1 opengauss opengauss 16777216 Apr 9 10:00 /opengauss/archive/00000001000000030000004F
-rw——- 1 opengauss opengauss 16777216 Apr 9 10:15 /opengauss/archive/000000010000000300000051

# 发现000000010000000300000050缺失

# 步骤2：检查远程备份
$ ls -la /backup/remote/archive/000000010000000300000050

-rw——- 1 backup backup 16777216 Apr 9 10:08 /backup/remote/archive/000000010000000300000050

# 步骤3：从远程备份恢复归档
$ cp /backup/remote/archive/000000010000000300000050 /opengauss/archive/
$ chown opengauss:opengauss /opengauss/archive/000000010000000300000050
$ chmod 600 /opengauss/archive/000000010000000300000050

# 步骤4：验证归档完整性
$ pg_waldump /opengauss/archive/000000010000000300000050 | head -5

rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 0/30000050, prev 0/30000028, desc: SWITCH
rmgr: Heap len (rec/tot): 54/ 54, tx: 5678, lsn: 0/30000068, prev 0/30000050, desc: INSERT off 15 flags 0x08, blkref #0: rel 1663/16384/16385 blk 1234

# 步骤5：继续PITR恢复
$ gs_probackup restore -B /opengauss/backup –instance=fgedudb -D /opengauss/fgdata -i QMR8YJ –recovery-target-time=”2026-04-09 12:00:00+08″

INFO: Restore begin.
INFO: Checking backup QMR8YJ
INFO: Backup QMR8YJ is valid
INFO: Restoring backup QMR8YJ
INFO: Recovery target time: 2026-04-09 12:00:00+08
INFO: Copying data files
INFO: Data files are copied
INFO: Applying WAL files
INFO: WAL files are applied
INFO: Restore completed successfully

# 步骤1：确定缺失范围
# 缺失归档：000000010000000400000060 到 000000010000000400000080

# 步骤2：检查远程和备份中是否存在
$ find /backup -name “00000001000000040000006*” 2>/dev/null
# 无结果，远程也没有

# 步骤3：确定可恢复的最大点
# 最后一个可用归档是00000001000000040000005F
# 对应LSN 0/4000005F

# 步骤4：执行不完全恢复
$ gs_probackup restore -B /opengauss/backup –instance=fgedudb -D /opengauss/fgdata_recovery -i QMR8YJ –recovery-target-lsn=”0/4000005F”

# 步骤5：启动并验证
$ gs_ctl start -D /opengauss/fgdata_recovery

$ gsql -d fgedudb -c “SELECT pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();
”

pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
————————+——————————–
0/4000005F | 2026-04-09 09:30:45.123456+08
(1 row)

# 步骤6：导出可恢复的数据
$ gs_dump -d fgedudb -f /tmp/recovered_data.sql

# 步骤7：评估数据丢失情况
$ gsql -d fgedudb -c “SELECT COUNT(*) FROM fgedu.fgedu_orders WHERE create_time > ‘2026-04-09 09:30:45’;
”

count
——-
250
(1 row)

# 步骤8：从应用日志或其他来源补回丢失数据
# 如果有应用日志或业务记录，可以手动补回这250条记录

# 步骤1：检查备库复制状态
$ gsql -h fgedu-standby -d postgres -c “SELECT * FROM pg_stat_wal_receiver;
”

pid | status | receive_start_lsn | receive_start_tli | received_lsn | received_tli | latest_end_lsn | latest_end_time | slot_name
—–+——–+——————-+——————-+————–+————–+—————-+—————–+———–
12345 | stopped | 0/70000000 | 1 | 0/70000050 | 1 | 0/70000050 | 2026-04-09 08:00:00+08 | fgedudb_slot

# 步骤2：检查主库复制槽状态
$ gsql -h fgedu-primary -d postgres -c “SELECT * FROM pg_replication_slots WHERE slot_name=’fgedudb_slot’;
”

slot_name | plugin | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
———–+——–+———–+——–+———-+——–+————+——+————–+————-+———————
fgedudb_slot | | physical | | | f | | | | 0/70000050 |

# 步骤3：检查主库当前LSN
$ gsql -h fgedu-primary -d postgres -c “SELECT pg_current_wal_lsn();
”

pg_current_wal_lsn
——————–
0/90000100
(1 row)

# 步骤4：计算延迟
# 主库LSN: 0/90000100
# 备库LSN: 0/70000050
# 差距约512MB，差距较大，选择重建备库

# 步骤5：重建备库
$ gs_ctl stop -D /opengauss/fgdata
$ rm -rf /opengauss/fgdata/*

# 步骤6：从主库重新备份
$ gs_probackup backup -B /opengauss/backup –instance=fgedudb -b FULL –remote-host=fgedu-primary –remote-user=opengauss

# 步骤7：恢复并启动备库
$ gs_probackup restore -B /opengauss/backup –instance=fgedudb -D /opengauss/fgdata -i QMR9AL
touch /opengauss/fgdata/standby.signal
$ gs_ctl start -D /opengauss/fgdata

# 步骤8：验证复制
$ gsql -h fgedu-standby -d postgres -c “SELECT * FROM pg_stat_wal_receiver;
”

pid | status | receive_start_lsn | receive_start_tli | received_lsn | received_tli | latest_end_lsn | latest_end_time | slot_name
—–+——–+——————-+——————-+————–+————–+—————-+—————–+———–
12346 | streaming | 0/90000000 | 1 | 0/90000100 | 1 | 0/90000100 | 2026-04-09 14:30:00+08 | fgedudb_slot

# 步骤9：优化归档保留策略
$ gsql -h fgedu-primary -d postgres -c “ALTER SYSTEM SET archive_cleanup_command = ‘pg_archivecleanup /opengauss/archive %r’;
”
$ gsql -h fgedu-primary -d postgres -c “SELECT pg_reload_conf();
”

# 每日检查清单
□ 检查归档日志是否正常生成
□ 检查归档目录空间使用率
□ 检查归档日志连续性
□ 检查备库复制延迟

# 每周检查清单
□ 验证最近归档日志可读性
□ 检查归档备份完整性
□ 清理过期归档日志
□ 更新归档监控报告

# 每月检查清单
□ 进行归档恢复演练
□ 验证远程归档备份
□ 优化归档保留策略
□ 归档管理文档更新