#!/usr/bin/env bash
# ============================================================
# 第 14 章 · 演示 1：pg_dump 四种格式 + 仅 schema / 仅 data
# ------------------------------------------------------------
# 目的：把 learn_pg 库分别用 plain / custom / directory / tar
#       四种格式各导出一份，并对比文件大小、耗时；
#       再演示「仅结构」「仅数据」「按表过滤」用法。
#
# 前置：
#   - 已运行 ../init.sql 准备好 ch14_users / ch14_orders 等表
#   - psql / pg_dump 在 PATH 中
#   - 默认连接：host=127.0.0.1 port=5432 dbname=learn_pg user=postgres
#     如果用密码连接，可在 ~/.pgpass 写：127.0.0.1:5432:learn_pg:postgres:xxx
#     或 export PGPASSWORD=xxx
#
# 用法：
#   bash 01_pg_dump_examples.sh
# ============================================================
set -euo pipefail

PGHOST=${PGHOST:-127.0.0.1}
PGPORT=${PGPORT:-5432}
PGDATABASE=${PGDATABASE:-learn_pg}
PGUSER=${PGUSER:-postgres}
export PGHOST PGPORT PGDATABASE PGUSER

BACKUP_DIR=${BACKUP_DIR:-/tmp/pg_dump_demo}
mkdir -p "$BACKUP_DIR"
rm -rf "$BACKUP_DIR"/*

bar() { echo -e "\n============================================================\n$1\n============================================================"; }

bar "Step 0：基础信息"
psql -c "SELECT version();" -c "SELECT pg_size_pretty(pg_database_size('${PGDATABASE}')) AS db_size;"

bar "Step 1：plain (-Fp) → 纯 SQL 文本"
time pg_dump -Fp -f "$BACKUP_DIR/learn.sql"
ls -lh "$BACKUP_DIR/learn.sql"
echo "---- 前 30 行预览 ----"
head -n 30 "$BACKUP_DIR/learn.sql"

bar "Step 2：custom (-Fc) → 自定义二进制（生产首选）"
time pg_dump -Fc -Z 6 -f "$BACKUP_DIR/learn.dump"
ls -lh "$BACKUP_DIR/learn.dump"
echo "---- 用 pg_restore -l 看目录 ----"
pg_restore -l "$BACKUP_DIR/learn.dump" | head -n 20

bar "Step 3：directory (-Fd) + 并行 dump (-j 4)"
time pg_dump -Fd -j 4 -Z 6 -f "$BACKUP_DIR/learn_dir"
ls -lh "$BACKUP_DIR/learn_dir/" | head
du -sh "$BACKUP_DIR/learn_dir"

bar "Step 4：tar (-Ft) （历史遗物，仅作展示）"
time pg_dump -Ft -f "$BACKUP_DIR/learn.tar"
ls -lh "$BACKUP_DIR/learn.tar"

bar "Step 5：仅 schema（--schema-only）"
pg_dump -Fp --schema-only -f "$BACKUP_DIR/schema_only.sql"
echo "字符数:"; wc -c "$BACKUP_DIR/schema_only.sql"
grep -c "^CREATE TABLE" "$BACKUP_DIR/schema_only.sql" \
    | xargs -I{} echo "包含 CREATE TABLE 数量: {}"

bar "Step 6：仅数据（--data-only）"
pg_dump -Fp --data-only -f "$BACKUP_DIR/data_only.sql"
echo "字符数:"; wc -c "$BACKUP_DIR/data_only.sql"

bar "Step 7：按表过滤 (-t / -T) 与按 schema 过滤 (-n / -N)"
pg_dump -Fc -t 'public.ch14_users' -t 'public.ch14_orders' \
    -f "$BACKUP_DIR/users_orders.dump"
echo "users_orders.dump 内容:"
pg_restore -l "$BACKUP_DIR/users_orders.dump"

bar "Step 8：导出全局对象（角色/表空间）"
pg_dumpall --globals-only -f "$BACKUP_DIR/globals.sql"
echo "globals.sql 行数:"; wc -l "$BACKUP_DIR/globals.sql"
echo "前 10 行:"; head -n 10 "$BACKUP_DIR/globals.sql"

bar "Step 9：四种格式大小对比"
ls -lhS "$BACKUP_DIR"/learn.sql "$BACKUP_DIR"/learn.dump \
        "$BACKUP_DIR"/learn.tar "$BACKUP_DIR"/learn_dir/

bar "完成！备份文件位于 $BACKUP_DIR"
echo "下一个脚本 02_pg_restore_demo.sh 会用 learn.dump 来演示 pg_restore"