#!/usr/bin/env bash
# postgresql-14-upgrade-statistics-lab-v1.0.sh
# 盈尺（Surinch）PG14 → 17 / 18 升级演示：pg_upgrade --check 报错、升级耗时、升级后统计信息缺失对查询的影响
# 版本 v1.0 · 2026-10-31 · 实测环境：PostgreSQL 14.24 / 17.11 / 18.6（Debian / PGDG 包），自造数据
# ⚠ 仅限测试机！脚本会在 LAB 目录里 initdb 新集群、反复删除重建，只监听本机 Unix socket，不连接任何现有实例。
# 用法：
#   B14=/usr/lib/postgresql/14/bin B17=/usr/lib/postgresql/17/bin B18=/usr/lib/postgresql/18/bin \
#   LAB=/tmp/pg-upgrade-lab ROWS=5000000 bash postgresql-14-upgrade-statistics-lab-v1.0.sh
# 需要磁盘约 4 GB（ROWS=5000000 时），耗时约 3–5 分钟。结果在 $LAB/out/。
set -euo pipefail
B14=${B14:-/usr/lib/postgresql/14/bin}; B17=${B17:-/usr/lib/postgresql/17/bin}; B18=${B18:-/usr/lib/postgresql/18/bin}
LAB=${LAB:-/tmp/pg-upgrade-lab}; ROWS=${ROWS:-5000000}; SOCK=$LAB/sock; OUT=$LAB/out; U=$(id -un)
for b in $B14 $B17 $B18; do [ -x "$b/pg_upgrade" ] || { echo "缺少 $b/pg_upgrade"; exit 1; }; done
mkdir -p $LAB $SOCK $OUT; cd $LAB
stop_all(){ for d in pg14 new17 new18; do [ -f $LAB/$d/postmaster.pid ] && $1/pg_ctl -D $LAB/$d -m fast -w stop >/dev/null 2>&1 || true; done; }
trap 'for b in $B14 $B17 $B18; do stop_all $b; done' EXIT
conf(){ cat >> $1/postgresql.conf <<C
port = $2
listen_addresses = ''
unix_socket_directories = '$SOCK'
shared_buffers = 256MB
work_mem = 16MB
maintenance_work_mem = 256MB
max_wal_size = 4GB
autovacuum = ${3:-off}
C
}
ms(){ python3 -c "import time;print(time.time())"; }
el(){ python3 -c "print(round($2-$1,2))"; }
q(){ psql -X -h $SOCK -p $1 -U $U -d upg_lab -v ON_ERROR_STOP=1 "${@:2}"; }

QUERIES=$(cat <<'Q'
\pset pager off
\echo '== Q1 done+vip join'
EXPLAIN (ANALYZE) SELECT count(*), sum(o.amount) FROM orders o JOIN customers c ON c.id=o.customer_id WHERE o.status='done' AND c.tier='vip';
\echo '== Q2 pending latest 50'
EXPLAIN (ANALYZE) SELECT id, customer_id, created_at FROM orders WHERE status='pending' ORDER BY created_at DESC LIMIT 50;
\echo '== Q3 since 2026-10-01 by city'
EXPLAIN (ANALYZE) SELECT c.city, count(*) FROM orders o JOIN customers c ON c.id=o.customer_id WHERE o.created_at >= '2026-10-01' GROUP BY c.city;
\echo '== Q4 correlated city+district'
EXPLAIN (ANALYZE) SELECT count(*) FROM orders o JOIN customers c ON c.id=o.customer_id WHERE c.city='city_7' AND c.district='city_7_d7';
Q
)
runq(){ # port label：每条查询跑 3 次，保留计划，打印 Execution Time
  for i in 1 2 3; do echo "$QUERIES" | q $1 > $OUT/plan-$2-run$i.txt; done
  echo "-- $2"; for i in 1 2 3; do grep -E "^== |Execution Time" $OUT/plan-$2-run$i.txt | paste -sd' ' ; done | tee -a $OUT/summary.txt
}

echo "== 1/6 建 PG14 源集群并造数（orders $ROWS 行）"
rm -rf pg14 && $B14/initdb -D pg14 -E UTF8 --locale=C.UTF-8 -U $U >/dev/null
conf pg14 5514; $B14/pg_ctl -D pg14 -l pg14.log -w start >/dev/null
psql -X -h $SOCK -p 5514 -U $U -d postgres -v ON_ERROR_STOP=1 -q <<SQL
CREATE DATABASE upg_lab;
\c upg_lab
SELECT setseed(0.31);
CREATE TABLE customers(id int PRIMARY KEY, city text NOT NULL, district text NOT NULL, tier text NOT NULL);
INSERT INTO customers SELECT g, 'city_'||(g%50), 'city_'||(g%50)||'_d'||(g%500), CASE WHEN g%100=0 THEN 'vip' ELSE 'normal' END FROM generate_series(1,200000) g;
CREATE TABLE orders(id bigint PRIMARY KEY, customer_id int NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL, amount numeric(10,2) NOT NULL, note text);
INSERT INTO orders SELECT g, 1+((g::bigint*7919)%200000)::int,
  CASE WHEN r<0.97 THEN 'done' WHEN r<0.99 THEN 'shipped' WHEN r<0.998 THEN 'pending' ELSE 'refund' END,
  timestamptz '2026-10-30 00:00+08' - (g::double precision/$ROWS)*interval '730 days', round((random()*500)::numeric,2), md5(g::text)
FROM (SELECT g, random() r FROM generate_series(1,$ROWS) g) s;
CREATE INDEX orders_status_idx ON orders(status);
CREATE INDEX orders_customer_idx ON orders(customer_id);
CREATE INDEX orders_created_idx ON orders(created_at);
CREATE INDEX customers_city_district_idx ON customers(city, district);
CREATE STATISTICS customers_city_district_stx (dependencies, ndistinct) ON city, district FROM customers;
VACUUM ANALYZE;
CREATE DATABASE legacy_app;
\c legacy_app
CREATE TABLE app_acl(id int, acl aclitem[]);
INSERT INTO app_acl VALUES (1, '{$U=r/$U}');
CREATE TABLE app_hooks(id int, fn regproc);
INSERT INTO app_hooks VALUES (1, 'now');
SQL
q 5514 -Atc "select version(); select pg_size_pretty(pg_database_size('upg_lab'));" | tee $OUT/source.txt
: > $OUT/summary.txt; runq 5514 pg14-baseline
$B14/pg_ctl -D pg14 -m fast -w stop >/dev/null

echo "== 2/6 pg_upgrade --check：PG17（reg* / aclitem 阻断）和 PG18 默认 initdb（校验和不一致）"
rm -rf new17 new18
$B17/initdb -D new17 -E UTF8 --locale=C.UTF-8 -U $U >/dev/null; conf new17 5517
$B18/initdb -D new18 -E UTF8 --locale=C.UTF-8 -U $U >/dev/null; conf new18 5518
mkdir -p run && cd run
$B17/pg_upgrade -b $B14 -B $B17 -d $LAB/pg14 -D $LAB/new17 -p 5514 -P 5517 -s $SOCK -U $U --check > $OUT/check-14to17.txt 2>&1 || echo "PG17 --check 失败（预期）：$(grep -E 'reg\*|aclitem' $OUT/check-14to17.txt | paste -sd';')"
$B18/pg_upgrade -b $B14 -B $B18 -d $LAB/pg14 -D $LAB/new18 -p 5514 -P 5518 -s $SOCK -U $U --check > $OUT/check-14to18-default.txt 2>&1 || echo "PG18 --check 失败（预期）：$(grep -i checksums $OUT/check-14to18-default.txt | head -1)"
cd $LAB

echo "== 3/6 删掉阻断库，备份一份干净的 PG14，升级到 PG17（--link）"
$B14/pg_ctl -D pg14 -l pg14.log -w start >/dev/null
psql -X -h $SOCK -p 5514 -U $U -d postgres -qc "DROP DATABASE legacy_app"
$B14/pg_ctl -D pg14 -m fast -w stop >/dev/null
rm -rf base14 && cp -a pg14 base14
cd run; t0=$(ms)
$B17/pg_upgrade -b $B14 -B $B17 -d $LAB/pg14 -D $LAB/new17 -p 5514 -P 5517 -s $SOCK -U $U --link > $OUT/upg-14to17-link.txt 2>&1
echo "PG17 --link 用时 $(el $t0 $(ms)) s" | tee -a $OUT/summary.txt; cd $LAB
$B17/pg_ctl -D new17 -l new17.log -w start >/dev/null
q 5517 -Atc "select 'pg17 pg_stats rows', count(*) from pg_stats where schemaname='public'" | tee -a $OUT/summary.txt
runq 5517 pg17-no-stats

echo "== 4/6 PG17：vacuumdb --analyze-in-stages"
t0=$(ms); $B17/vacuumdb -h $SOCK -p 5517 -U $U --all --analyze-in-stages > $OUT/vacuumdb-pg17.txt 2>&1
echo "PG17 vacuumdb --analyze-in-stages 用时 $(el $t0 $(ms)) s" | tee -a $OUT/summary.txt
runq 5517 pg17-analyzed
$B17/pg_ctl -D new17 -m fast -w stop >/dev/null

echo "== 5/6 从干净备份再升级到 PG18（--no-data-checksums 初始化 + --swap）"
rm -rf pg14 new18 && cp -a base14 pg14
$B18/initdb -D new18 -E UTF8 --locale=C.UTF-8 -U $U --no-data-checksums >/dev/null; conf new18 5518
cd run; t0=$(ms)
$B18/pg_upgrade -b $B14 -B $B18 -d $LAB/pg14 -D $LAB/new18 -p 5514 -P 5518 -s $SOCK -U $U --swap > $OUT/upg-14to18-swap.txt 2>&1
echo "PG18 --swap 用时 $(el $t0 $(ms)) s" | tee -a $OUT/summary.txt; cd $LAB
$B18/pg_ctl -D new18 -l new18.log -w start >/dev/null
q 5518 -Atc "select 'pg18 pg_stats rows', count(*) from pg_stats where schemaname='public'; select 'pg18 ext stats data rows', count(*) from pg_statistic_ext_data" | tee -a $OUT/summary.txt
runq 5518 pg18-stats-kept

echo "== 6/6 PG18：只补缺失统计 --missing-stats-only"
t0=$(ms); $B18/vacuumdb -h $SOCK -p 5518 -U $U --all --analyze-in-stages --missing-stats-only > $OUT/vacuumdb-pg18.txt 2>&1
echo "PG18 --missing-stats-only 用时 $(el $t0 $(ms)) s" | tee -a $OUT/summary.txt
q 5518 -Atc "select 'pg18 ext stats data rows', count(*) from pg_statistic_ext_data" | tee -a $OUT/summary.txt
runq 5518 pg18-missing-filled
echo "完成。汇总：$OUT/summary.txt；Q4 的行数估算看 $OUT/plan-*-run1.txt 里 customers 那一行的 rows=。"