PostgreSQL 数据库备份脚本实战指南

在数据库管理中，备份是一项至关重要的工作。今天我要分享一个功能强大的 PostgreSQL 数据库备份脚本，它不仅支持 Windows 和 Linux 系统，还提供了灵活的备份选项，让你的数据库管理工作更加高效。

脚本功能亮点

这个备份脚本具有以下几个突出特点：

1. 1. 跨平台兼容：同时支持 Windows 和 Linux 系统
2. 2. 灵活的备份范围：可以按需导出指定数据库、指定 schema 或指定表
3. 3. 多种导出模式：支持导出表结构+数据、仅表结构或仅数据
4. 4. 操作安全保障：执行前需要手动确认，避免误操作
5. 5. 便捷的导入提示：备份完成后自动生成对应的导入命令

脚本实现原理

脚本通过调用 PostgreSQL 的 pg_dump 工具来实现数据库备份。它首先检测操作系统类型，然后根据用户输入构建适合的备份命令。整个过程中，脚本会记录详细日志，并在完成后提供导入指令。

使用方法详解

1. 基本信息配置

脚本会引导你输入以下基本信息：

• o pg_dump 可执行文件的完整路径
• o 数据库服务器 IP 地址（默认：localhost）
• o 数据库端口号（默认：5432）
• o 数据库用户名（默认：postgres）
• o 数据库密码（默认：123456）
• o 要备份的数据库名称

2. 选择备份范围

脚本提供三种备份范围选项：

• o 导出整个数据库
• o 导出指定 schema
• o 导出指定 schema 下的特定表

3. 选择导出类型

根据需求选择以下导出类型：

• o 仅结构：只导出表结构，不包含数据
• o 仅数据：只导出数据，不包含表结构
• o 结构和数据：同时导出表结构和数据

4. 设置输出文件

输入导出文件的基本名称，脚本会自动添加时间戳，形成最终的输出文件名。

5. 确认执行

脚本会显示将要执行的命令（出于安全考虑，密码不会显示），需要你确认后才会执行。

6. 完成备份

备份完成后，脚本会输出导入命令，方便你在需要时恢复数据。

脚本代码

以下是完整的脚本代码：

#!/bin/bash

# 检测操作系统类型
if [[ "$OSTYPE" == "msys" ]] || [[ "$OSTYPE" == "win32" ]] || [[ "$OSTYPE" == "cygwin" ]]; then
 IS_WINDOWS=true
else
 IS_WINDOWS=false
fi

# 设置日志文件
LOG_FILE="pg_export_$(date +%Y%m%d_%H%M%S).log"

# 日志函数
log() {
 echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# 错误处理函数
error_exit() {
 log "错误: $1"
 exit 1
}

# 验证输入函数
validate_input() {
 if [ -z "$1" ]; then
 error_exit "输入不能为空"
 fi
}

# 验证pg_dump路径
validate_pg_dump() {
 if $IS_WINDOWS; then
 # Windows下检查.exe后缀
 win_path=$(echo "$1" | sed 's|^/||; s|/|\\|g')
 echo "检查路径: $win_path"
 # 检查路径是否存在
 if [ ! -f "$win_path" ] && [ ! -f "${win_path}.exe" ]; then
 error_exit "pg_dump路径无效: $win_path"
 fi
 # 保存处理后的路径
 PG_DUMP="$win_path"
 else
 if [ ! -x "$1" ]; then
 error_exit "pg_dump路径无效或没有执行权限: $1"
 fi
 fi
}

# 获取基本信息
if $IS_WINDOWS; then
 echo "请输入pg_dump可执行文件的完整路径（例如: /C:/Program Files/PostgreSQL/16/bin/pg_dump）："
else
 echo "请输入pg_dump可执行文件的完整路径（例如: /usr/bin/pg_dump）："
fi
read PG_DUMP
validate_input "$PG_DUMP"
validate_pg_dump "$PG_DUMP"

echo "请输入数据库服务器IP地址(默认：localhost)："
read DB_HOST
DB_HOST=${DB_HOST:-localhost}
validate_input "$DB_HOST"

echo "请输入数据库端口号（默认：5432）："
read DB_PORT
DB_PORT=${DB_PORT:-5432}

echo "请输入数据库用户名(默认：postgres)："
read DB_USER
DB_USER=${DB_USER:-postgres}
validate_input "$DB_USER"

echo "请输入数据库密码(默认：123456)："
read -s DB_PASS
DB_PASS=${DB_PASS:-123456}
validate_input "$DB_PASS"

echo "请输入数据库名称："
read DB_NAME
validate_input "$DB_NAME"

# 选择导出范围
echo "请选择导出范围："
echo "1) 导出整个数据库"
echo "2) 导出指定schema"
echo "3) 导出指定schema下的特定表"
read EXPORT_SCOPE

SCHEMA_NAME=""
TABLE_NAME=""
EXPORT_PARAMS=""

case $EXPORT_SCOPE in
 1)
 log "选择导出整个数据库"
 ;;
 2)
 echo "请输入schema名称："
 read SCHEMA_NAME
 validate_input "$SCHEMA_NAME"
 EXPORT_PARAMS="$EXPORT_PARAMS -n $SCHEMA_NAME"
 ;;
 3)
 echo "请输入schema名称："
 read SCHEMA_NAME
 validate_input "$SCHEMA_NAME"
 echo "请输入表名："
 read TABLE_NAME
 validate_input "$TABLE_NAME"
 EXPORT_PARAMS="$EXPORT_PARAMS -t $SCHEMA_NAME.$TABLE_NAME"
 ;;
 *)
 error_exit "无效的选择"
 ;;
esac

# 选择导出类型
echo "请选择导出类型："
echo "1) 仅结构"
echo "2) 仅数据"
echo "3) 结构和数据"
read EXPORT_TYPE

case $EXPORT_TYPE in
 1)
 EXPORT_PARAMS="$EXPORT_PARAMS --schema-only"
 ;;
 2)
 EXPORT_PARAMS="$EXPORT_PARAMS --data-only"
 ;;
 3)
 # 默认导出结构和数据，不需要额外参数
 ;;
 *)
 error_exit "无效的选择"
 ;;
esac

# 设置输出文件名
echo "请输入导出文件名（不需要扩展名）："
read OUTPUT_NAME
validate_input "$OUTPUT_NAME"

# 添加时间戳到文件名
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
OUTPUT_FILE="${OUTPUT_NAME}_${TIMESTAMP}.sql"

# 构建完整的命令
if $IS_WINDOWS; then
 # Windows下使用处理后的路径
 EXPORT_CMD="\"$PG_DUMP\" -h \"$DB_HOST\" -p \"$DB_PORT\" -U \"$DB_USER\" -d \"$DB_NAME\" $EXPORT_PARAMS -O -f \"$OUTPUT_FILE\""
else
 # Linux下使用原始路径
 EXPORT_CMD="\"$PG_DUMP\" -h \"$DB_HOST\" -p \"$DB_PORT\" -U \"$DB_USER\" -d \"$DB_NAME\" $EXPORT_PARAMS -O -f \"$OUTPUT_FILE\""
fi

# 显示将要执行的命令（隐藏密码）
echo "即将执行以下命令："
echo "$EXPORT_CMD"
echo "注意：实际执行时将通过环境变量传递数据库密码"

# 确认执行
echo "是否确认执行？(y/n)"
read CONFIRM
if [[ ! "$CONFIRM" =~ ^[Yy]$ ]]; then
 log "用户取消操作"
 exit 0
fi

# 设置环境变量以避免密码在命令行中显示
export PGPASSWORD="$DB_PASS"

# 执行导出
log "开始导出数据库..."

eval "$EXPORT_CMD" 2>> "$LOG_FILE"

if [ $? -eq 0 ]; then
 # 设置输出文件权限（仅在Linux下）
 if ! $IS_WINDOWS; then
 chmod 600 "$OUTPUT_FILE"
 fi
 log "导出成功完成！"
 log "导出文件：$OUTPUT_FILE"
 # 输出下导入命令
 echo "导入命令：psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f $OUTPUT_FILE"
else
 error_exit "导出过程中发生错误，请查看日志文件：$LOG_FILE"
fi

# 清除环境变量
unset PGPASSWORD 

适用场景

这个脚本在以下场景特别有用：

1. 1. 定期备份：可以结合定时任务，实现数据库的定期备份
2. 2. 迁移准备：在数据库迁移前，导出数据库结构和数据
3. 3. 开发测试：为开发环境准备测试数据
4. 4. 部分数据恢复：只恢复特定的 schema 或表

安全注意事项

1. 1. 脚本使用环境变量传递密码，避免了密码在命令行中明文显示
2. 2. 在 Linux 系统中，脚本会自动设置备份文件的权限为 600，确保只有文件所有者可以读写
3. 3. 执行前需要确认，防止误操作

总结

这个 PostgreSQL 备份脚本，它提供了灵活的备份选项，适用于各种场景。通过使用这个脚本，你可以更加高效地备份数据库，简单方便。

希望这个脚本能够帮助到你的数据库管理工作！如有任何问题或改进建议，欢迎在评论区留言。