forked from xiaohei/taiji-AI-PAD
150 lines
4.5 KiB
Bash
150 lines
4.5 KiB
Bash
#!/bin/bash
|
|
# 备份 Azure PostgreSQL 数据库脚本
|
|
#
|
|
# 使用方法：./backup_postgres.sh [database_name]
|
|
# 默认备份数据库：postgres
|
|
|
|
set -e
|
|
|
|
# 配置
|
|
DB_HOST="taijipda.postgres.database.azure.com"
|
|
DB_USER="taiji"
|
|
DB_PASSWORD="By@123456."
|
|
DB_NAME="${1:-postgres}"
|
|
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
|
|
BACKUP_FILE="backup_${DB_NAME}_${TIMESTAMP}.dump"
|
|
BACKUP_DIR="./backups"
|
|
|
|
echo "=========================================="
|
|
echo "Azure PostgreSQL 数据库备份脚本"
|
|
echo "=========================================="
|
|
echo "服务器: ${DB_HOST}"
|
|
echo "数据库: ${DB_NAME}"
|
|
echo "备份文件: ${BACKUP_FILE}"
|
|
echo "=========================================="
|
|
|
|
# 创建备份目录
|
|
mkdir -p "${BACKUP_DIR}"
|
|
|
|
# 设置 PGPASSWORD 环境变量避免密码提示
|
|
export PGPASSWORD="${DB_PASSWORD}"
|
|
|
|
# 测试数据库连接
|
|
echo ""
|
|
echo "Step 1: 测试数据库连接..."
|
|
if psql -h "${DB_HOST}" -U "${DB_USER}" -d "${DB_NAME}" -c "SELECT version();" > /dev/null 2>&1; then
|
|
echo " ✓ 数据库连接成功"
|
|
else
|
|
echo " ✗ 数据库连接失败！"
|
|
echo " 请检查："
|
|
echo " - 数据库服务器是否可访问"
|
|
echo " - 防火墙规则是否允许当前 IP"
|
|
echo " - 用户名和密码是否正确"
|
|
exit 1
|
|
fi
|
|
|
|
# 显示数据库信息
|
|
echo ""
|
|
echo "Step 2: 获取数据库信息..."
|
|
echo " 数据库大小:"
|
|
psql -h "${DB_HOST}" -U "${DB_USER}" -d "${DB_NAME}" -c "SELECT pg_size_pretty(pg_database_size('${DB_NAME}'));" -t
|
|
|
|
echo " 表数量:"
|
|
TABLE_COUNT=$(psql -h "${DB_HOST}" -U "${DB_USER}" -d "${DB_NAME}" -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';" -t | tr -d ' ')
|
|
echo " ${TABLE_COUNT} 个表"
|
|
|
|
if [ "$TABLE_COUNT" -gt 0 ]; then
|
|
echo " 表列表:"
|
|
psql -h "${DB_HOST}" -U "${DB_USER}" -d "${DB_NAME}" -c "\dt" 2>/dev/null || true
|
|
fi
|
|
|
|
# 执行备份
|
|
echo ""
|
|
echo "Step 3: 开始备份..."
|
|
echo " 使用自定义格式 (pg_dump -Fc)"
|
|
echo " 备份路径: ${BACKUP_DIR}/${BACKUP_FILE}"
|
|
|
|
# 使用 pg_dump 创建备份
|
|
# -Fc: 自定义格式（推荐，支持并行恢复和选择性恢复）
|
|
# -v: 详细输出
|
|
# -Z 6: 压缩级别 6（0-9，默认是中等压缩）
|
|
pg_dump -h "${DB_HOST}" \
|
|
-U "${DB_USER}" \
|
|
-d "${DB_NAME}" \
|
|
-Fc \
|
|
-v \
|
|
-Z 6 \
|
|
-f "${BACKUP_DIR}/${BACKUP_FILE}" 2>&1 | grep -v "^pg_dump: last built-in OID is" || true
|
|
|
|
# 验证备份文件
|
|
echo ""
|
|
echo "Step 4: 验证备份文件..."
|
|
if [ -f "${BACKUP_DIR}/${BACKUP_FILE}" ]; then
|
|
BACKUP_SIZE=$(du -h "${BACKUP_DIR}/${BACKUP_FILE}" | cut -f1)
|
|
echo " ✓ 备份文件已创建"
|
|
echo " 文件大小: ${BACKUP_SIZE}"
|
|
|
|
# 列出备份内容（不恢复）
|
|
echo ""
|
|
echo " 备份内容预览:"
|
|
pg_restore -l "${BACKUP_DIR}/${BACKUP_FILE}" | head -20
|
|
echo " ..."
|
|
else
|
|
echo " ✗ 备份文件创建失败！"
|
|
exit 1
|
|
fi
|
|
|
|
# 创建符号链接到最新备份（可选）
|
|
echo ""
|
|
echo "Step 5: 创建快捷链接..."
|
|
LATEST_LINK="${BACKUP_DIR}/latest_${DB_NAME}.dump"
|
|
ln -sf "${BACKUP_FILE}" "${LATEST_LINK}"
|
|
echo " ✓ 最新备份链接: ${LATEST_LINK}"
|
|
|
|
# 也创建一个 SQL 格式的备份（纯文本，便于查看和版本控制）
|
|
echo ""
|
|
echo "Step 6: 创建 SQL 格式备份（可选）..."
|
|
SQL_BACKUP="${BACKUP_DIR}/backup_${DB_NAME}_${TIMESTAMP}.sql"
|
|
pg_dump -h "${DB_HOST}" \
|
|
-U "${DB_USER}" \
|
|
-d "${DB_NAME}" \
|
|
--no-owner \
|
|
--no-acl \
|
|
-f "${SQL_BACKUP}" 2>&1 | grep -v "^pg_dump: last built-in OID is" || true
|
|
|
|
if [ -f "${SQL_BACKUP}" ]; then
|
|
SQL_SIZE=$(du -h "${SQL_BACKUP}" | cut -f1)
|
|
echo " ✓ SQL 备份已创建"
|
|
echo " 文件大小: ${SQL_SIZE}"
|
|
|
|
# 压缩 SQL 文件
|
|
gzip "${SQL_BACKUP}"
|
|
GZIP_SIZE=$(du -h "${SQL_BACKUP}.gz" | cut -f1)
|
|
echo " ✓ SQL 备份已压缩: ${SQL_BACKUP}.gz (${GZIP_SIZE})"
|
|
fi
|
|
|
|
# 清除密码环境变量
|
|
unset PGPASSWORD
|
|
|
|
# 显示所有备份文件
|
|
echo ""
|
|
echo "Step 7: 当前所有备份文件:"
|
|
ls -lh "${BACKUP_DIR}/" | grep -E "backup_${DB_NAME}|latest_${DB_NAME}"
|
|
|
|
echo ""
|
|
echo "=========================================="
|
|
echo "备份完成！"
|
|
echo "=========================================="
|
|
echo ""
|
|
echo "备份文件："
|
|
echo " 自定义格式: ${BACKUP_DIR}/${BACKUP_FILE}"
|
|
echo " SQL 格式: ${SQL_BACKUP}.gz"
|
|
echo " 最新链接: ${LATEST_LINK}"
|
|
echo ""
|
|
echo "恢复命令："
|
|
echo " pg_restore -h ${DB_HOST} -U ${DB_USER} -d NEW_DB_NAME ${BACKUP_DIR}/${BACKUP_FILE}"
|
|
echo ""
|
|
echo "或使用 SQL 格式恢复："
|
|
echo " gunzip -c ${SQL_BACKUP}.gz | psql -h ${DB_HOST} -U ${DB_USER} -d NEW_DB_NAME"
|
|
echo ""
|