PostgreSQL自动备份脚本

#!/bin/bash # ========================================== # PostgreSQL自动备份脚本 # 用途：使用pg_dump自动备份PostgreSQL数据库 # 使用方法：加入crontab每天凌晨执行 # ========================================== # ========== 配置 ========== PG_HOST="127.0.0.1" PG_PORT=5432 PG_USER="postgres" PG_PASSWORD="" BACKUP_DIR="/backup/postgresql" BACKUP_PREFIX="pg_backup" KEEP_DAYS=7 DB_NAME="" # 留空备份所有数据库 BACKUP_FORMAT="custom" # plain / custom / directory LOG_FILE="/var/log/pg_backup.log" ALERT_EMAIL="" # ========================== mkdir -p "$BACKUP_DIR" mkdir -p "$(dirname "$LOG_FILE")" log_msg() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE" } send_alert() { local title="$1" local content="$2" log_msg "【告警】$title: $content" if [ -n "$ALERT_EMAIL" ]; then echo -e "$content" | mail -s "【PostgreSQL备份】$title" "$ALERT_EMAIL" 2>/dev/null fi } # 检查pg_dump if ! command -v pg_dump >/dev/null 2>&1; then log_msg "❌ pg_dump未安装，请安装PostgreSQL客户端" exit 1 fi # 设置密码环境变量 if [ -n "$PG_PASSWORD" ]; then export PGPASSWORD="$PG_PASSWORD" fi # 检查连接 if ! psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -c "SELECT 1;" >/dev/null 2>&1; then log_msg "❌ 无法连接PostgreSQL: $PG_HOST:$PG_PORT" send_alert "PostgreSQL备份失败" "无法连接数据库: $PG_HOST:$PG_PORT" exit 1 fi DATE=$(date '+%Y%m%d_%H%M%S') log_msg "===== PostgreSQL备份开始 =====" log_msg "PostgreSQL: $PG_HOST:$PG_PORT" log_msg "用户: $PG_USER" # 获取数据库版本 PG_VERSION=$(psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -t -c "SHOW server_version;" 2>/dev/null | tr -d ' ') log_msg "PostgreSQL版本: $PG_VERSION" BACKUP_SUCCESS=true backup_database() { local db="$1" local backup_file="$BACKUP_DIR/${BACKUP_PREFIX}_${db}_${DATE}" log_msg "--- 备份数据库: $db ---" case "$BACKUP_FORMAT" in plain) backup_file="${backup_file}.sql" if pg_dump -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -F p "$db" > "$backup_file" 2>>"$LOG_FILE"; then gzip -f "$backup_file" backup_file="${backup_file}.gz" else return 1 fi ;; custom) backup_file="${backup_file}.dump" if ! pg_dump -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -F c -f "$backup_file" "$db" 2>>"$LOG_FILE"; then return 1 fi ;; directory) if ! pg_dump -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -F d -f "$backup_file" "$db" 2>>"$LOG_FILE"; then return 1 fi tar -czf "${backup_file}.tar.gz" -C "$BACKUP_DIR" "$(basename "$backup_file")" rm -rf "$backup_file" backup_file="${backup_file}.tar.gz" ;; esac if [ -f "$backup_file" ]; then FILE_SIZE=$(du -h "$backup_file" | awk '{print $1}') log_msg "✓ 备份成功: $backup_file ($FILE_SIZE)" return 0 else log_msg "✗ 备份文件未生成" return 1 fi } if [ -n "$DB_NAME" ]; then # 备份指定数据库 if ! backup_database "$DB_NAME"; then BACKUP_SUCCESS=false send_alert "PostgreSQL备份失败" "数据库 $DB_NAME 备份失败" fi else # 备份所有数据库 DATABASES=$(psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" 2>/dev/null | tr -d ' ') log_msg "发现数据库: $(echo $DATABASES | tr '\n' ' ')" for db in $DATABASES; do [ -z "$db" ] && continue if ! backup_database "$db"; then BACKUP_SUCCESS=false send_alert "PostgreSQL备份失败" "数据库 $db 备份失败" fi done fi # 清理旧备份 log_msg "--- 清理旧备份 ---" DELETED=$(find "$BACKUP_DIR" -name "${BACKUP_PREFIX}_*" -mtime +$KEEP_DAYS -print -delete | wc -l) log_msg "已删除 $DELETED 个超过${KEEP_DAYS}天的旧备份" # 备份统计 TOTAL_FILES=$(find "$BACKUP_DIR" -name "${BACKUP_PREFIX}_*" | wc -l) TOTAL_SIZE=$(du -sh "$BACKUP_DIR" | awk '{print $1}') log_msg "备份文件总数: $TOTAL_FILES" log_msg "备份目录大小: $TOTAL_SIZE" if [ "$BACKUP_SUCCESS" = true ]; then log_msg "===== PostgreSQL备份完成 ✓ =====" else log_msg "===== PostgreSQL备份完成（存在失败项）⚠️ =====" fi

[{"name": "PG_HOST", "label": "PostgreSQL\u4e3b\u673a", "default": "127.0.0.1"}, {"name": "PG_PORT", "label": "PostgreSQL\u7aef\u53e3", "default": "5432"}, {"name": "PG_USER", "label": "\u7528\u6237\u540d", "default": "postgres"}, {"name": "PG_PASSWORD", "label": "\u5bc6\u7801(\u53ef\u9009)", "default": ""}, {"name": "DB_NAME", "label": "\u6570\u636e\u5e93\u540d(\u7559\u7a7a\u5907\u4efd\u6240\u6709)", "default": ""}, {"name": "BACKUP_DIR", "label": "\u5907\u4efd\u76ee\u5f55", "default": "/backup/postgresql"}, {"name": "KEEP_DAYS", "label": "\u4fdd\u7559\u5929\u6570", "default": "7"}]