#!/usr/bin/env bash
# PostgreSQL 17 PITR 找回误删数据 演示 v1.0（surinch.com · 盈尺）
# 只在测试机上跑！会在 $LAB 下新建一个源实例（端口 5433）和若干恢复实例（5434–5436），
# 自造 orders 200 万行（约 170MB）、归档约 700MB；不连接你已有的任何实例。
# 测试版本：PostgreSQL 17.11（Debian 包）。用法：PGBIN=/usr/lib/postgresql/17/bin LAB=/tmp/surinch-pitr-lab ./pg-pitr-demo-v1.0.sh
# 结束后清理：rm -rf "$LAB"
set -uo pipefail
B=${PGBIN:-/usr/lib/postgresql/17/bin}; L=${LAB:-/tmp/surinch-pitr-lab}; S=$L
mkdir -p "$L"
ms() { echo $(( ($(date +%s%N)-$1)/1000000 )); }
q() { $B/psql -h $S -p $1 -U postgres -d shop -X -At -c "$2"; }
for p in 5433 5434 5435 5436; do for d in src r1 r2 r3 r4; do $B/pg_ctl -D $L/$d status >/dev/null 2>&1 && $B/pg_ctl -D $L/$d stop -m immediate >/dev/null 2>&1; done; done
rm -rf $L/src $L/arch $L/base $L/r1 $L/r2 $L/r3 $L/r4 $L/*.log; mkdir -p $L/arch
echo "# $(date '+%F %T') $($B/postgres --version)"
$B/initdb -D $L/src -U postgres --auth=trust -k >/dev/null
cat >> $L/src/postgresql.conf <<C
port=5433
listen_addresses=''
unix_socket_directories='$S'
wal_level=replica
archive_mode=on
archive_command='test ! -f $L/arch/%f && cp %p $L/arch/%f'
max_wal_size=2GB
log_timezone='Asia/Shanghai'
timezone='Asia/Shanghai'
C
$B/pg_ctl -D $L/src -l $L/src.log start -w | tail -1
$B/createdb -h $S -p 5433 -U postgres shop
q 5433 "CREATE TABLE orders(id bigserial PRIMARY KEY, user_id int NOT NULL, status text NOT NULL, amount numeric(10,2) NOT NULL, created_at timestamptz NOT NULL DEFAULT now());
INSERT INTO orders(user_id,status,amount,created_at) SELECT 1+(g::bigint*7919)%200000, CASE WHEN g%10<7 THEN 'paid' ELSE 'refunded' END, round((g%100000)/100.0,2), now()-interval '1 day'*(g%365) FROM generate_series(1,2000000) g;
CREATE TABLE payments(id bigserial PRIMARY KEY, order_id bigint, amount numeric(10,2), created_at timestamptz DEFAULT now());" >/dev/null
echo "源库 orders 行数: $(q 5433 'select count(*) from orders')  库大小: $(q 5433 "select pg_size_pretty(pg_database_size('shop'))")"
t=$(date +%s%N); $B/pg_basebackup -h $S -p 5433 -U postgres -D $L/base -Fp -X stream -c fast; echo "pg_basebackup_ms=$(ms $t) size=$(du -sh $L/base | cut -f1)"
# 基础备份之后的正常业务写入
q 5433 "INSERT INTO payments(order_id,amount) SELECT g, 9.99 FROM generate_series(1,2000) g; UPDATE orders SET status='paid' WHERE id BETWEEN 1 AND 3000;" >/dev/null
sleep 1
T_GOOD=$(q 5433 "select clock_timestamp()")
echo "T_GOOD（误操作前一刻，应用侧记下的时间）= $T_GOOD"
sleep 2
# 误操作：本想删退款单，漏了 WHERE 里的日期条件
XID=$(q 5433 "BEGIN; SELECT txid_current(); DELETE FROM orders WHERE status='refunded'; COMMIT;" | grep -E "^[0-9]+$")
T_BAD=$(q 5433 "select clock_timestamp()")
echo "误删事务 xid=$XID，误删后 orders 行数: $(q 5433 'select count(*) from orders')，T_BAD=$T_BAD"
sleep 2
# 误操作之后还有正常写入（PITR 回整库会把它们一起丢掉）
q 5433 "INSERT INTO payments(order_id,amount) SELECT g, 19.99 FROM generate_series(100001,100500) g;" >/dev/null
echo "误删后又写入 payments，当前 payments 行数: $(q 5433 'select count(*) from payments')"
echo "源库当前 WAL: $(q 5433 'select pg_walfile_name(pg_current_wal_lsn())')；归档目录文件数: $(ls $L/arch | wc -l)；最后归档: $(ls $L/arch | grep -v backup | tail -1)"
echo "$XID" > $L/xid.txt; echo "$T_GOOD" > $L/t_good.txt
waitrec() { # 等恢复真正停下：日志出现 pausing / FATAL / 恢复完成
  local log=$1 t0=$(date +%s%N)
  for i in $(seq 1 600); do grep -qE "pausing at the end of recovery|FATAL|database system is ready to accept connections$|archive recovery complete" $log && break; sleep 0.1; done
  echo "wait_until_target_ms=$(( ($(date +%s%N)-t0)/1000000 ))"
}
mkrestore() { # $1=dir $2=port $3...=recovery settings
  local d=$1 p=$2; shift 2; rm -rf $L/$d; cp -a $L/base $L/$d; rm -f $L/$d/postgresql.auto.conf
  sed -i "s/^port=5433/port=$p/; s/^archive_mode=on/archive_mode=off/" $L/$d/postgresql.conf
  echo "restore_command='cp $L/arch/%f %p'" >> $L/$d/postgresql.conf
  for kv in "$@"; do echo "$kv" >> $L/$d/postgresql.conf; done
  touch $L/$d/recovery.signal
}
echo "源库 pg_switch_wal() 前已归档到: $(ls $L/arch | grep -v backup | tail -1)"
q 5433 "select pg_switch_wal()" >/dev/null; sleep 2; echo "pg_switch_wal() 后已归档到: $(ls $L/arch | grep -v backup | tail -1)"
echo; echo "=== R2 按时间恢复 recovery_target_time=T_GOOD（默认 recovery_target_action=pause）"
mkrestore r2 5434 "recovery_target_time='$T_GOOD'"
t=$(date +%s%N); $B/pg_ctl -D $L/r2 -l $L/r2.log start -w -t 120 | tail -1; echo "pg_ctl_return_ms=$(ms $t)"; waitrec $L/r2.log; echo "start_to_target_ms=$(ms $t)"
sleep 1
grep -E "starting point-in-time|recovery stopping|pausing at the end|redo done" $L/r2.log | sed 's/^[0-9-]* [0-9:.]* [A-Z+0-9]* //'
echo "pg_is_in_recovery=$(q 5434 'select pg_is_in_recovery()') pause_state=$(q 5434 'select pg_get_wal_replay_pause_state()')"
echo "R2 orders=$(q 5434 'select count(*) from orders') refunded=$(q 5434 "select count(*) from orders where status='refunded'") payments=$(q 5434 'select count(*) from payments')"
echo "R2 写入测试: $(q 5434 "insert into payments(order_id,amount) values (1,1)" 2>&1)"
echo; echo "=== 用 pg_waldump 找误删事务（从 WAL 里查 COMMIT 记录）"
START_LSN=$(grep "START WAL LOCATION" $L/base/backup_label | awk '{print $4}')
echo "从基础备份起点 $START_LSN 扫归档，只看 xid=$XID 的记录（真实排查时不知道 xid，可以去掉 -x，按时间附近找 COMMIT 和大量 DELETE）"
$B/pg_waldump -p $L/arch -s $START_LSN -x $XID 2>/dev/null > $L/waldump-xid.txt
echo "DELETE 记录数: $(grep -c 'desc: DELETE' $L/waldump-xid.txt)"
grep -E "desc: COMMIT" $L/waldump-xid.txt | sed 's/.*lsn: /lsn: /'
echo; echo "=== R3 按 xid 恢复 recovery_target_xid=$XID + recovery_target_inclusive=false"
mkrestore r3 5435 "recovery_target_xid='$XID'" "recovery_target_inclusive=false"
t=$(date +%s%N); $B/pg_ctl -D $L/r3 -l $L/r3.log start -w -t 120 | tail -1; echo "pg_ctl_return_ms=$(ms $t)"; waitrec $L/r3.log; echo "start_to_target_ms=$(ms $t)"; sleep 1
grep -E "recovery stopping" $L/r3.log | sed 's/^[0-9-]* [0-9:.]* [A-Z+0-9]* //'
echo "R3 orders=$(q 5435 'select count(*) from orders') payments=$(q 5435 'select count(*) from payments')"
echo; echo "=== R4 同一个 xid 但保留默认 recovery_target_inclusive=true"
mkrestore r4 5436 "recovery_target_xid='$XID'"
t=$(date +%s%N); $B/pg_ctl -D $L/r4 -l $L/r4.log start -w -t 120 | tail -1; echo "pg_ctl_return_ms=$(ms $t)"; waitrec $L/r4.log; echo "start_to_target_ms=$(ms $t)"; sleep 1
grep -E "recovery stopping" $L/r4.log | sed 's/^[0-9-]* [0-9:.]* [A-Z+0-9]* //'
echo "R4 orders=$(q 5436 'select count(*) from orders') refunded=$(q 5436 "select count(*) from orders where status='refunded'")"
echo; echo "=== 只把被删的行从 R2 搬回源库（不回滚整库，误删后写入的 payments 不丢）"
t=$(date +%s%N)
$B/psql -h $S -p 5434 -U postgres -d shop -X -q -c "\copy (select * from orders where status='refunded') to '$L/refunded.csv' csv"
echo "导出 refunded 行: $(wc -l < $L/refunded.csv) 行，$(ms $t) ms"
t=$(date +%s%N)
$B/psql -h $S -p 5433 -U postgres -d shop -X -q -v ON_ERROR_STOP=1 <<SQL
BEGIN;
CREATE TEMP TABLE refunded_back (LIKE orders);
\copy refunded_back from '$L/refunded.csv' csv
INSERT INTO orders SELECT * FROM refunded_back ON CONFLICT (id) DO NOTHING;
COMMIT;
SQL
echo "导回源库耗时 $(ms $t) ms；源库 orders=$(q 5433 'select count(*) from orders') refunded=$(q 5433 "select count(*) from orders where status='refunded'") payments=$(q 5433 'select count(*) from payments')"
echo "序列检查: max(id)=$(q 5433 'select max(id) from orders') last_value=$(q 5433 'select last_value from orders_id_seq')"

echo; echo "=== 第二场：小误操作（DROP TABLE payments，WAL 很少），不切 WAL 就恢复"
for d in r3 r4; do $B/pg_ctl -D $L/$d stop -m fast -w >/dev/null; done
T_GOOD2=$(q 5433 "select clock_timestamp()"); echo "T_GOOD2=$T_GOOD2"; sleep 2
q 5433 "DROP TABLE payments" ; sleep 3
echo "源库当前 WAL: $(q 5433 'select pg_walfile_name(pg_current_wal_lsn())')；最后归档: $(ls $L/arch | grep -v backup | tail -1)"
echo "--- R5 不切 WAL 直接恢复到 T_GOOD2"
mkrestore r3 5435 "recovery_target_time='$T_GOOD2'"
t=$(date +%s%N); $B/pg_ctl -D $L/r3 -l $L/r5.log start -w -t 120 | tail -1; echo "pg_ctl_return_ms=$(ms $t)"; waitrec $L/r5.log; echo "start_to_target_ms=$(ms $t)"
grep -E "FATAL|recovery stopping|redo done|starting point-in-time" $L/r5.log | sed 's/^[0-9-]* [0-9:.]* [A-Z+0-9]* //' | tail -4
echo "--- 源库执行 pg_switch_wal() 后重来"
q 5433 "select pg_switch_wal()" >/dev/null; sleep 2; echo "最后归档: $(ls $L/arch | grep -v backup | tail -1)"
mkrestore r4 5436 "recovery_target_time='$T_GOOD2'"
t=$(date +%s%N); $B/pg_ctl -D $L/r4 -l $L/r6.log start -w -t 120 | tail -1; echo "pg_ctl_return_ms=$(ms $t)"; waitrec $L/r6.log; echo "start_to_target_ms=$(ms $t)"; sleep 1
grep -E "recovery stopping|pausing" $L/r6.log | sed 's/^[0-9-]* [0-9:.]* [A-Z+0-9]* //'
echo "R6 查 payments（暂停在 DROP 提交前，设 5 秒超时）: $(PGOPTIONS='-c statement_timeout=5s' q 5436 'select count(*) from payments' 2>&1)"
echo "R6 恢复进程持有的锁: $(q 5436 "select l.mode||' on '||c.relname||' granted='||l.granted from pg_locks l join pg_class c on c.oid=l.relation where c.relname='payments'")"
echo "--- 确认后 promote（结束恢复、切新时间线）"
t=$(date +%s%N); q 5436 "select pg_promote(true, 60)"; echo "promote_ms=$(ms $t) in_recovery=$(q 5436 'select pg_is_in_recovery()') current_wal=$(q 5436 'select pg_walfile_name(pg_current_wal_lsn())')"
echo "promote 后 payments=$(q 5436 'select count(*) from payments')"
ls $L/r4/pg_wal | grep -E "history|^00000002" | head -3
for d in src r2 r3 r4; do $B/pg_ctl -D $L/$d stop -m fast -w >/dev/null 2>&1; done; echo "all stopped"