备份与恢复 #

备份类型：

逻辑备份
├── pg_dump - 单数据库备份
├── pg_dumpall - 整个集群备份
├── 可移植性好
└── 恢复粒度细

物理备份
├── 文件系统备份
├── 更快
├── 恢复粒度粗
└── 需要停止服务

时间点恢复（PITR）
├── 基于WAL日志
├── 可恢复到任意时间点
├── 需要基础备份+WAL
└── 配置复杂

备份策略建议：

生产环境
├── 每日全量备份
├── WAL归档（PITR）
├── 异地备份
└── 定期恢复测试

开发环境
├── 每周全量备份
├── 重要数据单独备份
└── 恢复测试

测试环境
├── 按需备份
└── 可快速重建

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

# 备份为自定义格式（推荐）
pg_dump -U postgres -d tsdb -Fc > backup.dump

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

# 备份所有数据库
pg_dumpall -U postgres > all_backup.sql

# 只备份结构
pg_dump -U postgres -d tsdb --schema-only > schema.sql

# 只备份数据
pg_dump -U postgres -d tsdb --data-only > data.sql

# 备份特定表
pg_dump -U postgres -d tsdb -t sensor_data > sensor_backup.sql

# 备份特定模式
pg_dump -U postgres -d tsdb -n public > public_backup.sql

# 排除特定表
pg_dump -U postgres -d tsdb -T temp_table > backup.sql

# 包含创建数据库语句
pg_dump -U postgres -d tsdb --create > backup.sql

# 使用gzip压缩
pg_dump -U postgres -d tsdb | gzip > backup.sql.gz

# 使用自定义格式（自动压缩）
pg_dump -U postgres -d tsdb -Fc > backup.dump

# 指定压缩级别
pg_dump -U postgres -d tsdb -Fc -Z9 > backup.dump

# 备份远程数据库
pg_dump -h remote_host -p 5432 -U postgres -d tsdb > backup.sql

# 通过SSH备份
ssh user@remote_host "pg_dump -U postgres tsdb" > backup.sql

# 使用环境变量
export PGHOST=remote_host
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=tsdb
pg_dump > backup.sql

# 恢复SQL备份
psql -U postgres -d tsdb < backup.sql

# 恢复自定义格式
pg_restore -U postgres -d tsdb backup.dump

# 恢复目录格式
pg_restore -U postgres -d tsdb backup_dir/

# 恢复前创建数据库
createdb -U postgres tsdb_new
pg_restore -U postgres -d tsdb_new backup.dump

# 只恢复结构
pg_restore -U postgres -d tsdb --schema-only backup.dump

# 只恢复数据
pg_restore -U postgres -d tsdb --data-only backup.dump

# 恢复特定表
pg_restore -U postgres -d tsdb -t sensor_data backup.dump

# 恢复特定模式
pg_restore -U postgres -d tsdb -n public backup.dump

# 清理后恢复
pg_restore -U postgres -d tsdb --clean backup.dump

# 并行恢复
pg_restore -U postgres -d tsdb -j 4 backup.dump

# 创建新数据库
createdb -U postgres tsdb_restore

# 恢复数据
pg_restore -U postgres -d tsdb_restore backup.dump

# 验证恢复
psql -U postgres -d tsdb_restore -c "SELECT count(*) FROM sensor_data;"

# pg_dump会自动处理超表
# 备份时包含超表定义和数据

# 备份超表
pg_dump -U postgres -d tsdb -t sensor_data > sensor_backup.sql

# 备份包含超表的数据库
pg_dump -U postgres -d tsdb -Fc > tsdb_backup.dump

# 恢复超表
pg_restore -U postgres -d tsdb -t sensor_data backup.dump

# 恢复后验证
psql -U postgres -d tsdb -c "
SELECT * FROM timescaledb_information.hypertables;
"

# 备份连续聚合
pg_dump -U postgres -d tsdb -t hourly_stats > cagg_backup.sql

# 恢复连续聚合
psql -U postgres -d tsdb < cagg_backup.sql

# 验证
psql -U postgres -d tsdb -c "
SELECT * FROM timescaledb_information.continuous_aggregates;
"

# postgresql.conf

# 启用WAL归档
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'

# WAL配置
max_wal_senders = 3
wal_keep_size = 1GB

# 创建归档目录
mkdir -p /archive
chown postgres:postgres /archive

# 重启PostgreSQL
sudo systemctl restart postgresql

# 使用pg_basebackup
pg_basebackup -U postgres -D /backup/base -Ft -z -P

# 参数说明
# -D: 备份目录
# -Ft: tar格式
# -z: 压缩
# -P: 显示进度

# 1. 停止PostgreSQL
sudo systemctl stop postgresql

# 2. 清空数据目录
rm -rf /var/lib/postgresql/15/main/*

# 3. 恢复基础备份
tar -xf /backup/base/base.tar.gz -C /var/lib/postgresql/15/main/

# 4. 创建恢复配置
cat > /var/lib/postgresql/15/main/postgresql.auto.conf << EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2024-01-15 10:00:00'
recovery_target_action = 'promote'
EOF

# 5. 创建recovery.signal
touch /var/lib/postgresql/15/main/recovery.signal

# 6. 启动PostgreSQL
sudo systemctl start postgresql

# 7. 验证恢复
psql -U postgres -c "SELECT pg_is_in_recovery();"

# 恢复到特定事务ID
cat > /var/lib/postgresql/15/main/postgresql.auto.conf << EOF
restore_command = 'cp /archive/%f %p'
recovery_target_xid = '12345'
recovery_target_action = 'promote'
EOF

#!/bin/bash
# backup_timescaledb.sh

# 配置
BACKUP_DIR="/backup/timescaledb"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="tsdb"
RETENTION_DAYS=7

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

# 执行备份
pg_dump -U postgres -d $DB_NAME -Fc > $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "Backup successful: ${DB_NAME}_${DATE}.dump"
else
    echo "Backup failed!"
    exit 1
fi

# 删除旧备份
find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete

# 记录日志
echo "$(date): Backup completed" >> $BACKUP_DIR/backup.log

# 添加到crontab
crontab -e

# 每天凌晨2点执行备份
0 2 * * * /path/to/backup_timescaledb.sh

# 每小时执行增量备份
0 * * * * /path/to/incremental_backup.sh

#!/bin/bash
# verify_backup.sh

BACKUP_FILE=$1
TEST_DB="test_restore_$(date +%Y%m%d_%H%M%S)"

# 创建测试数据库
createdb -U postgres $TEST_DB

# 恢复备份
pg_restore -U postgres -d $TEST_DB $BACKUP_FILE

# 检查恢复结果
TABLES=$(psql -U postgres -d $TEST_DB -t -c "
SELECT count(*) FROM information_schema.tables 
WHERE table_schema = 'public';
")

# 清理测试数据库
dropdb -U postgres $TEST_DB

if [ "$TABLES" -gt 0 ]; then
    echo "Backup verification passed"
    exit 0
else
    echo "Backup verification failed"
    exit 1
fi

-- 查看最后备份时间
SELECT 
    schemaname,
    tablename,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables;

-- 查看数据库大小
SELECT 
    datname,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
WHERE datname = 'tsdb';

#!/bin/bash
# check_backup.sh

BACKUP_DIR="/backup/timescaledb"
ALERT_EMAIL="admin@example.com"

# 检查最新备份
LATEST_BACKUP=$(ls -t $BACKUP_DIR/*.dump | head -1)
BACKUP_AGE=$((($(date +%s) - $(stat -c %Y $LATEST_BACKUP)) / 3600))

if [ $BACKUP_AGE -gt 24 ]; then
    echo "Warning: Latest backup is $BACKUP_AGE hours old" | mail -s "Backup Alert" $ALERT_EMAIL
fi

# 检查备份大小
BACKUP_SIZE=$(stat -c %s $LATEST_BACKUP)
if [ $BACKUP_SIZE -lt 1000000 ]; then
    echo "Warning: Backup size is too small: $BACKUP_SIZE bytes" | mail -s "Backup Alert" $ALERT_EMAIL
fi

灾难恢复计划：

1. 评估损坏程度
   ├── 数据库是否可启动
   ├── 数据是否完整
   └── 确定恢复方案

2. 选择恢复方法
   ├── 全量恢复
   ├── 时间点恢复
   └── 表级别恢复

3. 执行恢复
   ├── 准备恢复环境
   ├── 执行恢复操作
   └── 验证恢复结果

4. 恢复服务
   ├── 更新应用配置
   ├── 验证应用功能
   └── 监控系统状态

恢复检查清单：

恢复前
├── 确认备份文件存在
├── 确认备份文件完整
├── 准备恢复环境
└── 通知相关人员

恢复中
├── 记录恢复步骤
├── 监控恢复进度
└── 处理恢复错误

恢复后
├── 验证数据完整性
├── 验证应用功能
├── 更新监控配置
└── 记录恢复报告

备份策略最佳实践：

备份频率
├── 全量备份：每日
├── 增量备份：每小时
└── WAL归档：持续

备份保留
├── 每日备份：保留7天
├── 每周备份：保留4周
├── 每月备份：保留12月
└── 年度备份：永久保留

备份验证
├── 每周验证备份
├── 每月恢复演练
└── 记录验证结果

# 加密备份
pg_dump -U postgres -d tsdb | gzip | openssl enc -aes-256-cbc -salt -out backup.sql.gz.enc

# 解密恢复
openssl enc -aes-256-cbc -d -in backup.sql.gz.enc | gunzip | psql -U postgres -d tsdb

# 限制备份文件权限
chmod 600 backup.dump
chown postgres:postgres backup.dump