Скрипт автоматического бэкапа PostgreSQL

Решение

1. Полный bash-скрипт для бэкапа PostgreSQL

#!/bin/bash

################################################################################
# PostgreSQL Backup Script with Rotation, Compression and Remote Upload
# Usage: ./backup_postgres.sh [config_file]
# Cron: 0 3 * * * /usr/local/bin/backup_postgres.sh /etc/backup/postgres.conf
################################################################################

set -euo pipefail

# Color codes
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Configuration file
CONFIG_FILE="${1:-/etc/backup/postgres.conf}"
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
LOG_FILE="${LOG_DIR:-/var/log/backups}/postgres_backup_$(date +%Y%m%d).log"
ERROR_LOG="${LOG_DIR:-/var/log/backups}/postgres_backup_errors.log"

################################################################################
# Logging Functions
################################################################################

log() {
    local level=$1
    shift
    local message="$@"
    local timestamp
    timestamp=$(date '+%Y-%m-%d %H:%M:%S')
    echo "[$timestamp] [$level] $message" | tee -a "$LOG_FILE"
}

log_info() {
    log "INFO" "$@"
}

log_error() {
    log "ERROR" "$@" >&2
    echo "[$@]" >> "$ERROR_LOG"
}

log_success() {
    echo -e "${GREEN}[SUCCESS]${NC} $@" | tee -a "$LOG_FILE"
}

################################################################################
# Notification Functions
################################################################################

send_email_notification() {
    local subject="$1"
    local body="$2"
    local success="${3:-false}"
    
    if [[ -z "$EMAIL_RECIPIENTS" ]]; then
        return
    fi
    
    # Only send error emails, or explicitly requested
    if [[ "$success" == "true" ]] && [[ "$NOTIFY_ON_SUCCESS" != "true" ]]; then
        return
    fi
    
    echo "$body" | mail -s "$subject" "$EMAIL_RECIPIENTS"
    log_info "Email notification sent to $EMAIL_RECIPIENTS"
}

send_telegram_notification() {
    local message="$1"
    local success="${2:-false}"
    
    if [[ -z "$TELEGRAM_BOT_TOKEN" ]] || [[ -z "$TELEGRAM_CHAT_ID" ]]; then
        return
    fi
    
    # Only send error messages, or explicitly requested
    if [[ "$success" == "true" ]] && [[ "$NOTIFY_ON_SUCCESS" != "true" ]]; then
        return
    fi
    
    local emoji="❌"
    [[ "$success" == "true" ]] && emoji="✅"
    
    curl -s -X POST "https://api.telegram.org/bot${TELEGRAM_BOT_TOKEN}/sendMessage" \
        -d "chat_id=${TELEGRAM_CHAT_ID}" \
        -d "text=${emoji} ${message}" > /dev/null
    
    log_info "Telegram notification sent"
}

################################################################################
# Configuration Loading
################################################################################

load_config() {
    if [[ ! -f "$CONFIG_FILE" ]]; then
        log_error "Configuration file not found: $CONFIG_FILE"
        exit 1
    fi
    
    # Source configuration
    # shellcheck source=/dev/null
    source "$CONFIG_FILE"
    
    # Validate required variables
    local required_vars=("DB_HOST" "DB_NAME" "DB_USER" "BACKUP_DIR")
    for var in "${required_vars[@]}"; do
        if [[ -z "${!var:-}" ]]; then
            log_error "Required config variable not set: $var"
            exit 1
        fi
    done
    
    log_info "Configuration loaded from $CONFIG_FILE"
}

################################################################################
# PostgreSQL Backup Functions
################################################################################

perform_backup() {
    local backup_file
    local backup_date
    backup_date=$(date +%Y%m%d_%H%M%S)
    backup_file="${BACKUP_DIR}/postgres_${DB_NAME}_${backup_date}.sql"
    backup_file_gz="${backup_file}.gz"
    
    log_info "Starting PostgreSQL backup for database: $DB_NAME"
    log_info "Backup file: $backup_file"
    
    # Create backup directory if doesn't exist
    mkdir -p "$BACKUP_DIR"
    
    # Set PostgreSQL password if provided
    if [[ -n "$DB_PASSWORD" ]]; then
        export PGPASSWORD="$DB_PASSWORD"
    fi
    
    # Perform pg_dump
    if ! pg_dump \
        --host="$DB_HOST" \
        --port="${DB_PORT:-5432}" \
        --username="$DB_USER" \
        --format=plain \
        --verbose \
        --no-password \
        --no-owner \
        --no-privileges \
        "$DB_NAME" > "$backup_file"; then
        
        log_error "pg_dump failed for database $DB_NAME"
        rm -f "$backup_file"
        return 1
    fi
    
    log_info "Backup file created: $(du -h "$backup_file" | cut -f1)"
    
    # Compress backup
    log_info "Compressing backup..."
    if ! gzip -f "$backup_file"; then
        log_error "Failed to compress backup"
        rm -f "$backup_file" "$backup_file_gz"
        return 1
    fi
    
    log_success "Backup compressed: $(du -h "$backup_file_gz" | cut -f1)"
    
    # Unset password
    unset PGPASSWORD
    
    # Return path to compressed backup
    echo "$backup_file_gz"
}

################################################################################
# Remote Upload Functions
################################################################################

upload_to_s3() {
    local backup_file="$1"
    local backup_name
    backup_name=$(basename "$backup_file")
    
    if [[ -z "$S3_BUCKET" ]] || [[ -z "$AWS_REGION" ]]; then
        log_info "S3 upload not configured, skipping"
        return 0
    fi
    
    log_info "Uploading to S3: s3://${S3_BUCKET}/${S3_PATH}/${backup_name}"
    
    # Check if AWS CLI is installed
    if ! command -v aws &> /dev/null; then
        log_error "AWS CLI not found. Install with: pip install awscli"
        return 1
    fi
    
    # Set AWS credentials if provided
    if [[ -n "$AWS_ACCESS_KEY_ID" ]]; then
        export AWS_ACCESS_KEY_ID
        export AWS_SECRET_ACCESS_KEY
    fi
    
    if ! aws s3 cp "$backup_file" \
        "s3://${S3_BUCKET}/${S3_PATH:-backups}/${backup_name}" \
        --region "$AWS_REGION" \
        --sse AES256; then
        
        log_error "Failed to upload backup to S3"
        return 1
    fi
    
    log_success "Backup uploaded to S3"
    return 0
}

upload_to_sftp() {
    local backup_file="$1"
    local backup_name
    backup_name=$(basename "$backup_file")
    
    if [[ -z "$SFTP_HOST" ]] || [[ -z "$SFTP_USER" ]]; then
        log_info "SFTP upload not configured, skipping"
        return 0
    fi
    
    log_info "Uploading to SFTP: ${SFTP_USER}@${SFTP_HOST}:${SFTP_PATH}/${backup_name}"
    
    # Check if SFTP is available
    if ! command -v lftp &> /dev/null; then
        log_error "lftp not found. Install with: apt-get install lftp"
        return 1
    fi
    
    local lftp_cmd
    if [[ -n "${SFTP_PASSWORD:-}" ]]; then
        lftp_cmd="lftp -u ${SFTP_USER},${SFTP_PASSWORD} ${SFTP_HOST}"
    else
        # Use SSH key
        lftp_cmd="lftp -u ${SFTP_USER} sftp://${SFTP_HOST}"
    fi
    
    if ! $lftp_cmd -e "cd ${SFTP_PATH:-/backups}; put ${backup_file}; quit"; then
        log_error "Failed to upload backup to SFTP"
        return 1
    fi
    
    log_success "Backup uploaded to SFTP"
    return 0
}

################################################################################
# Backup Rotation Functions
################################################################################

rotate_backups() {
    local backup_type="$1"
    local retention_days="$2"
    local pattern
    
    case $backup_type in
        daily)
            pattern="postgres_${DB_NAME}_[0-9]\{8\}_[0-9]\{6\}.sql.gz"
            ;;
        weekly)
            pattern="postgres_${DB_NAME}_weekly_[0-9]\{8\}.sql.gz"
            ;;
        monthly)
            pattern="postgres_${DB_NAME}_monthly_[0-9]\{6\}.sql.gz"
            ;;
        *)
            log_error "Unknown backup type: $backup_type"
            return 1
            ;;
    esac
    
    log_info "Rotating $backup_type backups (keeping last $retention_days days)"
    
    # Find and delete old backups
    find "$BACKUP_DIR" \
        -maxdepth 1 \
        -type f \
        -name "$pattern" \
        -mtime +"$retention_days" \
        -delete
    
    log_info "Backup rotation completed for $backup_type backups"
}

rotate_all_backups() {
    # Rotate daily backups (keep 7 days)
    rotate_backups "daily" "${RETENTION_DAILY:-7}"
    
    # Rotate weekly backups (keep 4 weeks = 28 days)
    rotate_backups "weekly" "${RETENTION_WEEKLY:-28}"
    
    # Rotate monthly backups (keep 12 months = 365 days)
    rotate_backups "monthly" "${RETENTION_MONTHLY:-365}"
}

################################################################################
# Create Backup Copies for Weekly/Monthly
################################################################################

create_weekly_backup() {
    local daily_backup="$1"
    local week_num
    week_num=$(date +%Y_week_%U)
    local weekly_backup="${BACKUP_DIR}/postgres_${DB_NAME}_weekly_${week_num}.sql.gz"
    
    if [[ ! -f "$weekly_backup" ]]; then
        cp "$daily_backup" "$weekly_backup"
        log_info "Weekly backup created: $(basename "$weekly_backup")"
    fi
}

create_monthly_backup() {
    local daily_backup="$1"
    local month_num
    month_num=$(date +%Y%m)
    local monthly_backup="${BACKUP_DIR}/postgres_${DB_NAME}_monthly_${month_num}.sql.gz"
    
    if [[ ! -f "$monthly_backup" ]]; then
        cp "$daily_backup" "$monthly_backup"
        log_info "Monthly backup created: $(basename "$monthly_backup")"
    fi
}

################################################################################
# Backup Verification
################################################################################

verify_backup() {
    local backup_file="$1"
    
    log_info "Verifying backup integrity..."
    
    # Check file exists and has content
    if [[ ! -f "$backup_file" ]]; then
        log_error "Backup file not found: $backup_file"
        return 1
    fi
    
    local file_size
    file_size=$(stat -f%z "$backup_file" 2>/dev/null || stat -c%s "$backup_file" 2>/dev/null)
    
    if [[ "$file_size" -lt 1000 ]]; then
        log_error "Backup file is too small: $file_size bytes"
        return 1
    fi
    
    # Verify gzip integrity
    if ! gzip -t "$backup_file"; then
        log_error "Backup file is corrupted"
        return 1
    fi
    
    log_success "Backup verification passed: $(du -h "$backup_file" | cut -f1)"
    return 0
}

################################################################################
# Cleanup
################################################################################

cleanup() {
    log_info "Cleaning up temporary files..."
    # Unset sensitive variables
    unset PGPASSWORD
    unset AWS_SECRET_ACCESS_KEY
    unset SFTP_PASSWORD
}

################################################################################
# Main Function
################################################################################

main() {
    local start_time
    start_time=$(date +%s)
    
    log_info "========================================"
    log_info "PostgreSQL Backup Script Started"
    log_info "========================================"
    
    # Load configuration
    load_config
    
    # Perform backup
    local backup_file
    if ! backup_file=$(perform_backup); then
        log_error "Backup creation failed"
        send_email_notification \
            "PostgreSQL Backup Failed" \
            "Database: $DB_NAME\nError: Backup creation failed\nLog: $ERROR_LOG"
        send_telegram_notification "🚨 PostgreSQL Backup Failed for $DB_NAME"
        exit 1
    fi
    
    # Verify backup
    if ! verify_backup "$backup_file"; then
        log_error "Backup verification failed"
        rm -f "$backup_file"
        send_email_notification \
            "PostgreSQL Backup Verification Failed" \
            "Database: $DB_NAME\nFile: $backup_file"
        send_telegram_notification "🚨 PostgreSQL Backup Verification Failed for $DB_NAME"
        exit 1
    fi
    
    # Upload to remote storage
    local upload_success=true
    
    if ! upload_to_s3 "$backup_file"; then
        upload_success=false
    fi
    
    if ! upload_to_sftp "$backup_file"; then
        upload_success=false
    fi
    
    if [[ "$upload_success" == "false" ]]; then
        log_error "One or more uploads failed"
        send_email_notification \
            "PostgreSQL Backup Upload Failed" \
            "Database: $DB_NAME\nFile: $backup_file\nSome uploads failed"
        send_telegram_notification "⚠️ PostgreSQL Backup Upload Failed for $DB_NAME"
    fi
    
    # Create weekly and monthly copies
    create_weekly_backup "$backup_file"
    create_monthly_backup "$backup_file"
    
    # Rotate old backups
    rotate_all_backups
    
    # Cleanup
    cleanup
    
    # Calculate duration
    local end_time
    end_time=$(date +%s)
    local duration=$((end_time - start_time))
    
    log_success "Backup completed successfully in ${duration}s"
    log_info "Backup file: $backup_file"
    log_info "========================================"
    
    # Send success notification
    send_email_notification \
        "PostgreSQL Backup Successful" \
        "Database: $DB_NAME\nFile: $(basename "$backup_file")\nSize: $(du -h "$backup_file" | cut -f1)\nDuration: ${duration}s" \
        "true"
    
    send_telegram_notification "✅ PostgreSQL Backup Successful for $DB_NAME (${duration}s)" "true"
}

# Run main function
main "$@"

2. Файл конфигурации

# /etc/backup/postgres.conf
# PostgreSQL Backup Configuration

# Database Connection
DB_HOST="localhost"
DB_PORT="5432"
DB_NAME="myapp"
DB_USER="postgres"
DB_PASSWORD="${PGPASSWORD:-}"  # Set via environment variable for security

# Backup Settings
BACKUP_DIR="/backups/postgres"
RETENTION_DAILY="7"      # Keep daily backups for 7 days
RETENTION_WEEKLY="28"    # Keep weekly backups for 4 weeks
RETENTION_MONTHLY="365"  # Keep monthly backups for 1 year

# S3 Upload Configuration
S3_BUCKET="my-backup-bucket"
S3_PATH="backups/postgres"
AWS_REGION="us-east-1"
AWS_ACCESS_KEY_ID="${AWS_ACCESS_KEY_ID:-}"  # Set via environment
AWS_SECRET_ACCESS_KEY="${AWS_SECRET_ACCESS_KEY:-}"  # Set via environment

# SFTP Upload Configuration
SFTP_HOST="backup.example.com"
SFTP_USER="backup"
SFTP_PASSWORD="${SFTP_PASSWORD:-}"  # Set via environment
SFTP_PATH="/backups/postgres"

# Email Notifications
EMAIL_RECIPIENTS="devops@example.com,backup@example.com"
NOTIFY_ON_SUCCESS="true"

# Telegram Notifications
TELEGRAM_BOT_TOKEN="${TELEGRAM_BOT_TOKEN:-}"  # Set via environment
TELEGRAM_CHAT_ID="${TELEGRAM_CHAT_ID:-}"      # Set via environment

# Logging
LOG_DIR="/var/log/backups"

3. Установка и запуск

# Создать директории
sudo mkdir -p /backups/postgres /var/log/backups /etc/backup

# Загрузить скрипт
sudo cp backup_postgres.sh /usr/local/bin/
sudo chmod +x /usr/local/bin/backup_postgres.sh

# Загрузить конфигурацию
sudo cp postgres.conf /etc/backup/
sudo chmod 600 /etc/backup/postgres.conf  # Защитить пароли

# Создать логов директорию с правами
sudo mkdir -p /var/log/backups
sudo chmod 755 /var/log/backups

# Тестовый запуск
sudo /usr/local/bin/backup_postgres.sh /etc/backup/postgres.conf

# Проверить логи
sudo tail -f /var/log/backups/postgres_backup_*.log

4. Cron Setup

# Добавить в crontab root пользователя
sudo crontab -e

# Ежедневный бэкап в 03:00
0 3 * * * export PGPASSWORD='password'; /usr/local/bin/backup_postgres.sh /etc/backup/postgres.conf

# Или с использованием .pgpass файла (более безопасно)
# ~/.pgpass формат: hostname:port:database:username:password
0 3 * * * /usr/local/bin/backup_postgres.sh /etc/backup/postgres.conf

# Еженедельный полный бэкап (Sunday в 02:00)
0 2 * * 0 /usr/local/bin/backup_postgres.sh /etc/backup/postgres.conf --full

# Проверка логов каждый день в 10:00
0 10 * * * tail -50 /var/log/backups/postgres_backup_*.log | mail -s "Backup Status" devops@example.com

5. pg_dump vs pg_basebackup

pg_dump (Логический дамп)

# Что это:
# - Логический дамп базы данных в SQL-совместимый формат
# - Создает SQL команды для воссоздания объектов и данных

# Плюсы:
# - Портативен (работает на любой версии PostgreSQL)
# - Может выбирать что дампить (схемы, таблицы)
# - Меньше размер с компрессией
# - Удобно для миграций между серверами

# Минусы:
# - Медленнее при больших объемах (сотни ГБ)
# - Требует больше памяти
# - Невозможно использовать для point-in-time recovery (PITR)

# Пример
pg_dump --host=localhost --username=postgres mydb | gzip > mydb.sql.gz

pg_basebackup (Физический бэкап)

# Что это:
# - Физический бэкап (копирование файлов БД)
# - Создает точный снимок файловой системы

# Плюсы:
# - Очень быстро для больших БД (петабайты)
# - Поддерживает PITR (point-in-time recovery)
# - Идеален для реплик
# - Использует потоковую репликацию

# Минусы:
# - Нужно совпадение версий PostgreSQL
# - Большой размер (нет сжатия)
# - Требует архивирования WAL логов
# - Более сложная восстановлению

# Пример
pg_basebackup --progress --wal-method=stream --format=tar --gzip -D /backup/basebackup

Сравнение

6. Восстановление из бэкапа

Из pg_dump

# Базовое восстановление
gzip -dc mydb.sql.gz | psql --host=localhost --username=postgres mydb

# Восстановление с обработкой ошибок
psql --host=localhost --username=postgres mydb < <(gzip -dc mydb.sql.gz)

# Восстановление в новую БД
creatdb newdb
psql newdb < <(gzip -dc mydb.sql.gz)

# Восстановление с фильтром (только определенная схема)
psql < <(gzip -dc mydb.sql.gz | grep -E '^(CREATE|INSERT|UPDATE) ' | head -100)

# Параллельное восстановление (pg_dump -Fd формат)
tar -xzf mydb.tar.gz -C /tmp/restore
pg_restore --host=localhost --username=postgres --dbname=mydb --jobs=4 /tmp/restore

Из pg_basebackup

# 1. Остановить PostgreSQL
sudo systemctl stop postgresql

# 2. Удалить старую БД
sudo rm -rf /var/lib/postgresql/13/main

# 3. Распаковать бэкап
tar -xzf basebackup.tar.gz -C /var/lib/postgresql/13/main
sudo chown postgres:postgres -R /var/lib/postgresql/13/main

# 4. Опционально: восстановить до конкретного момента времени
# Отредактировать recovery.conf или recovery.signal + postgresql.conf
# recovery_target_timeline = 'latest'
# recovery_target_time = '2024-01-01 12:00:00'

# 5. Запустить PostgreSQL
sudo systemctl start postgresql

# 6. PostgreSQL восстановится используя WAL архивы

7. Консистентность при высокой нагрузке

Проблема

# При pg_dump во время активной работы:
# - Данные могут измениться во время дампа
# - Может быть несогласованность
# - Индексы могут быть неполными

Решение 1: Транзакции

# pg_dump использует транзакции по умолчанию
pg_dump --single-transaction mydb > dump.sql

# Это гарантирует консистентность
# Но требует долгую блокировку

Решение 2: Снимок

# Использование снимков для консистентности
pg_dump \
  --verbose \
  --snapshot='000001-1' \
  mydb > dump.sql

# Требует параллельного соединения для получения снимка

Решение 3: Replica

# Лучший способ - дамп из replica
# Replica не влияет на production

pg_dump --host=replica.example.com mydb > dump.sql

# Или полный бэкап с replica
pg_basebackup --host=replica.example.com --progress -D /backup

Решение 4: WAL Archiving + PITR

# Для идеальной консистентности:
# 1. Создать basebackup
pg_basebackup -D /backup -l 'daily backup'

# 2. Архивировать WAL логи
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'

# 3. Восстановить на конкретный момент
recovery_target_time = '2024-01-01 12:00:00'

Практический скрипт

#!/bin/bash
# Безопасный бэкап с консистентностью

DB_NAME="myapp"
BACKUP_FILE="/backups/postgres_consistent_$(date +%Y%m%d_%H%M%S).sql.gz"

# Использовать single-transaction для консистентности
pg_dump \
  --single-transaction \
  --verbose \
  --no-owner \
  --no-privileges \
  "$DB_NAME" | gzip > "$BACKUP_FILE"

echo "Backup created: $BACKUP_FILE"
echo "Size: $(du -h "$BACKUP_FILE" | cut -f1)"