#!/usr/bin/env bash
# surinch.com · 盈尺：PostgreSQL 17 全量 + 增量备份校验演示 v1.0
# 流程：pg_basebackup 全量 -> pg_verifybackup -> 制造变更 -> 增量备份(--incremental) -> pg_verifybackup
#       -> pg_combinebackup 合成 -> pg_verifybackup -> 在另一个端口启动合成目录 -> 比对行数和金额合计
# 仅限测试实例！会 pgbench -i 重建 pgbench_* 表。实验集群需要 summarize_wal=on、wal_level=replica、允许复制连接。
# 测试环境：PostgreSQL 17.11（initdb -k 开启数据校验和），pgbench -s 40 自造数据（约 606MB）。
# 用法：PGHOST=... PGPORT=5440 PGUSER=postgres WORK=/data/drill ./pg-backup-verify-incremental-demo-v1.0.sh [random|concentrated]
set -uo pipefail
BIN=${PGBIN:-/usr/lib/postgresql/17/bin}; WORK=${WORK:?请设置 WORK=演练目录}; WORKLOAD=${1:-random}; ALTPORT=${ALTPORT:-5441}
ms(){ echo $(( ($(date +%s%N) - $1)/1000000 )); }
step(){ echo; echo "=== $* ($(date +%T))"; }
fp(){ "$BIN/psql" -X -At "$@" -c "select 'accounts', count(*), sum(abalance) from pgbench_accounts" -c "select 'history', count(*), sum(delta) from pgbench_history" -c "select 'branches', sum(bbalance) from pgbench_branches"; }
mkdir -p "$WORK"; cd "$WORK"; rm -rf full incr1 combined
[ "$("$BIN/psql" -X -At -c 'show summarize_wal')" = on ] || { echo "summarize_wal 不是 on，增量备份会失败"; exit 2; }
step "pgbench -i -s 40"; "$BIN/pgbench" -i -s 40 -q 2>&1 | tail -1
step "full"; t=$(date +%s%N); "$BIN/pg_basebackup" -D full -c fast -Fp; echo "exit=$? elapsed_ms=$(ms $t)"; du -sh full
step "verify full"; "$BIN/pg_verifybackup" full; echo "exit=$?"
if [ "$WORKLOAD" = random ]; then
  step "随机更新 20 秒"; "$BIN/pgbench" -T 20 -c 4 -j 2 2>&1 | grep -E "actually processed|tps"
else
  step "集中更新：前 1% 账户 + 追加 1 万行流水"
  "$BIN/psql" -X -c "UPDATE pgbench_accounts SET abalance=abalance+1 WHERE aid <= 40000" \
    -c "INSERT INTO pgbench_history(tid,bid,aid,delta,mtime) SELECT 1,1,g,1,now() FROM generate_series(1,10000) g" \
    -c "UPDATE pgbench_branches SET bbalance=bbalance+50000 WHERE bid=1" -c "CHECKPOINT"
fi
step "source fingerprint"; fp
step "incremental"; t=$(date +%s%N); "$BIN/pg_basebackup" -D incr1 -c fast -Fp --incremental=full/backup_manifest; echo "exit=$? elapsed_ms=$(ms $t)"; du -sh incr1
echo "INCREMENTAL.* 文件数=$(find incr1 -name 'INCREMENTAL.*' | wc -l)"
step "verify incr1"; "$BIN/pg_verifybackup" incr1; echo "exit=$?"
step "combine"; t=$(date +%s%N); "$BIN/pg_combinebackup" full incr1 -o combined; echo "exit=$? elapsed_ms=$(ms $t)"; du -sh combined
step "verify combined"; "$BIN/pg_verifybackup" combined; echo "exit=$?"
step "start combined on $ALTPORT"
sed -i "s/^#\{0,1\}port *=.*/port=$ALTPORT/" combined/postgresql.conf 2>/dev/null || echo "port=$ALTPORT" >> combined/postgresql.conf
"$BIN/pg_ctl" -D combined -l "$WORK/combined.log" -o "-p $ALTPORT" start -w | tail -1
fp -p "$ALTPORT"
"$BIN/pg_ctl" -D combined stop -m fast | tail -1
echo "两段 fingerprint 一致 = 这次合成备份能恢复出增量备份那一刻的数据"