┌─────────────────────────────────────────────────────────────┐
│                    备份策略对比                               │
├─────────────────────────────────────────────────────────────┤
│  逻辑备份    │  pg_dump/pg_dumpall  │  全量/增量/跨版本       │
│  物理备份    │  文件级复制          │  快速、全量              │
│  连续归档    │  WAL + 物理备份      │  PITR、点时间恢复        │
└─────────────────────────────────────────────────────────────┘

# 备份单个数据库
pg_dump -U postgres -d mydb -f backup.sql

# 备份压缩格式
pg_dump -U postgres -d mydb -Fc -f backup.dump

# 备份目录格式（并行备份）
pg_dump -U postgres -d mydb -Fd -j 4 -f backup_dir

# 仅备份表结构
pg_dump -U postgres -d mydb --schema-only -f schema.sql

# 仅备份数据
pg_dump -U postgres -d mydb --data-only -f data.sql

# 备份指定表
pg_dump -U postgres -d mydb -t users -t orders -f tables.sql

# 排除指定表
pg_dump -U postgres -d mydb --exclude-table=logs -f backup.sql

# 备份前执行操作
pg_dump -U postgres -d mydb --pre-data-restore="SET session_replication_role = 'replica';" -f backup.sql

# 备份所有数据库（包括角色、表空间）
pg_dumpall -U postgres -f all_databases.sql

# 仅备份角色和表空间
pg_dumpall -U postgres --roles-only -f roles.sql
pg_dumpall -U postgres --tablespaces-only -f tablespaces.sql

# 备份全局对象
pg_dumpall -U postgres --globals-only -f globals.sql

#!/bin/bash
# backup.sh - PostgreSQL 备份脚本

# 配置
PGHOST=localhost
PGPORT=5432
PGUSER=postgres
BACKUP_DIR=/var/backups/postgresql
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 日志
echo "[$(date)] 开始备份..." >> $BACKUP_DIR/backup.log

# 逻辑备份
pg_dump -h $PGHOST -p $PGPORT -U $PGUSER -Fc mydb -f $BACKUP_DIR/mydb_$DATE.dump

# 删除 7 天前的备份
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete

# 压缩备份
gzip $BACKUP_DIR/mydb_$DATE.dump

echo "[$(date)] 备份完成: mydb_$DATE.dump.gz" >> $BACKUP_DIR/backup.log

# 恢复完整数据库
dropdb -U postgres mydb
createdb -U postgres mydb
pg_restore -U postgres -d mydb -c backup.dump

# 恢复压缩格式
pg_restore -U postgres -d mydb backup.dump

# 恢复目录格式
pg_restore -U postgres -d mydb -Fd backup_dir -j 4

# 仅恢复表结构
pg_restore -U postgres -d mydb --schema-only backup.dump

# 仅恢复数据
pg_restore -U postgres -d mydb --data-only backup.dump

# 恢复指定表
pg_restore -U postgres -d mydb --table=users backup.dump

# 恢复 SQL 备份
psql -U postgres -d mydb -f backup.sql

# 恢复并显示进度
psql -U postgres -d mydb -f backup.sql 2>&1 | pv -l > /dev/null

# 恢复中遇到错误继续
psql -U postgres -d mydb --set ON_ERROR_STOP=off -f backup.sql

# 1. 确保 WAL 归档开启
# postgresql.conf:
wal_level = replica
max_wal_senders = 3
archive_mode = on
archive_command = 'test ! -f /var/backups/postgresql/wal/%f && cp %p /var/backups/postgresql/wal/%f'

# 2. 创建备份目录
mkdir -p /var/backups/postgresql/base
mkdir -p /var/backups/postgresql/wal

# 3. 执行基础备份
psql -U postgres -c "SELECT pg_start_backup('base_backup');"
rsync -a --exclude=pg_wal/* /var/lib/postgresql/data/ /var/backups/postgresql/base/
psql -U postgres -c "SELECT pg_stop_backup();"

# 4. 归档当前 WAL
psql -U postgres -c "SELECT pg_switch_wal();"

# 基础备份
pg_basebackup -h localhost -U postgres -D /var/backups/postgresql/base -Ft -z -P

# 创建恢复配置
cat > /var/backups/postgresql/base/recovery.conf << 'EOF'
restore_command = 'cp /var/backups/postgresql/wal/%f "%p"'
recovery_target_timeline = 'latest'
EOF

# 完整备份脚本
pg_basebackup -h $PGHOST -p $PGPORT -U $PGUSER -D $BACKUP_DIR/base -Ft -z -P -X stream

# postgresql.conf

# WAL 级别
wal_level = replica

# 流复制连接数
max_wal_senders = 3

# 启用归档
archive_mode = on

# 归档命令
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

# 归档格式
archive_timeout = 300  # 5分钟强制归档

# 1. 停止 PostgreSQL
sudo systemctl stop postgresql

# 2. 备份当前数据目录
mv /var/lib/postgresql/data /var/lib/postgresql/data.broken

# 3. 恢复基础备份
mkdir /var/lib/postgresql/data
tar -xzf /var/backups/postgresql/base/backup.tar.gz -C /var/lib/postgresql/data

# 4. 创建恢复配置
cat > /var/lib/postgresql/data/recovery.conf << 'EOF'
# 恢复到指定时间点
restore_command = 'cp /var/backups/postgresql/wal/%f "%p"'
recovery_target_time = '2024-06-15 14:30:00+08'
recovery_target_action = 'promote'
EOF

# 或恢复到指定 WAL 位置
cat > /var/lib/postgresql/data/recovery.conf << 'EOF'
restore_command = 'cp /var/backups/postgresql/wal/%f "%p"'
recovery_target_xid = '12345'
recovery_target_action = 'promote'
EOF

# 或恢复到最新可用状态
cat > /var/lib/postgresql/data/recovery.conf << 'EOF'
restore_command = 'cp /var/backups/postgresql/wal/%f "%p"'
recovery_target = 'immediate'
EOF

# 5. 设置权限
chown -R postgres:postgres /var/lib/postgresql/data

# 6. 启动 PostgreSQL
sudo systemctl start postgresql

# 恢复到指定时间
recovery_target_time = '2024-06-15 14:30:00+08'

# 恢复到指定事务 ID
recovery_target_xid = '12345'

# 恢复到指定 LSN
recovery_target_lsn = '0/2000000'

# 恢复到指定名称（需要先创建恢复点）
recovery_target_name = 'before_app_update'

# 恢复到 latest
recovery_target = 'latest'

# 恢复到立即可用
recovery_target = 'immediate'

# 是否包含指定时间点之后的事务
recovery_target_inclusive = on

# 恢复到 WAL 之前
recovery_target_timeline = 'latest'

#!/bin/bash
# wal_archive.sh - WAL 归档脚本

WAL_DIR=/var/backups/postgresql/wal
ARCHIVE_DIR=/mnt/nfs/wal_archive

# 归档 WAL 文件
if [ -f "$1" ]; then
    FILE=$(basename $1)
    DEST=$ARCHIVE_DIR/$(date +%Y/%m)/$FILE
    
    mkdir -p $(dirname $DEST)
    cp $1 $DEST
    
    # 上传到云存储
    # aws s3 cp $DEST s3://mybucket/wal/$DEST
    
    echo "[$(date)] Archived: $FILE" >> /var/log/wal_archive.log
fi

# 验证备份完整性
pg_restore --help > /dev/null

# 测试恢复（隔离环境）
docker run --rm -e POSTGRES_PASSWORD=test postgres:16 \
  -v backup.dump:/backup.dump \
  psql -U postgres -f /backup.dump

# 检查备份大小
ls -lh /var/backups/postgresql/*.dump
du -sh /var/backups/postgresql/base/