备份与恢复实施者系统提示

# TODO_backup-restore.md ## Backup & Restore Implementation Plan ### Context - **Target database**: PostgreSQL running in Docker container (`statence_db`) - **Offsite storage**: Cloudflare R2 bucket via S3-compatible API - **Host environment**: Linux VPS (Ubuntu/Debian) --- ## Environment & Prerequisites ### System Architecture Validation - [ ] **BACKUP-ENV-001 [Validate PostgreSQL Container Access]**: - **Scope**: Confirm Docker container `statence_db` is running and healthy - **Validation**: `docker ps` shows container with status "Up" - **Credentials**: Verify `POSTGRES_USER`, `POSTGRES_DB`, `POSTGRES_PASSWORD` are correct - **Outcome**: Container accessible with valid credentials - [ ] **BACKUP-ENV-002 [Validate Cloudflare R2 Connectivity]**: - **Scope**: Confirm R2 bucket exists and S3 API endpoint is reachable - **Endpoint**: `https://<account_id>.r2.cloudflarestorage.com` - **Validation**: `aws s3 ls --endpoint-url <endpoint> s3://<bucket>` succeeds - **Outcome**: R2 bucket accessible with provided credentials - [ ] **BACKUP-ENV-003 [Validate Required Tooling]**: - **Scope**: Ensure `pg_dump`, `gzip`, and `aws-cli` are installed - **Versions**: `pg_dump --version` matches container PostgreSQL version - **Permissions**: All tools executable by the backup user - **Outcome**: All required tools available and version-compatible - [ ] **BACKUP-ENV-004 [Validate Environment File Schema]**: - **Scope**: Verify `.env` file contains all required variables - **Variables**: `CONTAINER_NAME`, `POSTGRES_USER`, `POSTGRES_DB`, `POSTGRES_PASSWORD`, `CF_R2_ACCESS_KEY_ID`, `CF_R2_SECRET_ACCESS_KEY`, `CF_R2_ENDPOINT_URL`, `CF_R2_BUCKET` - **Format**: R2 endpoint strictly follows `https://<account_id>.r2.cloudflarestorage.com` - **Outcome**: All variables populated with correct formats ### Configuration Management - [ ] **BACKUP-ENV-005 [Configure aws-cli Profile]**: - **Scope**: Create dedicated AWS CLI profile for R2 operations - **Profile Name**: `r2` - **Configuration**: `aws configure --profile r2` with R2 credentials - **Validation**: `aws s3 ls --profile r2 --endpoint-url <endpoint> s3://<bucket>` succeeds - **Outcome**: Dedicated profile avoids conflicts with other S3 configurations - [ ] **BACKUP-ENV-006 [Secure Environment File]**: - **Scope**: Set appropriate file permissions on `.env` - **Permissions**: `chmod 600 .env` - **Ownership**: Owned by the backup execution user - **Outcome**: Credentials protected from unauthorized access --- ## Implementation Tasks ### Backup Script Development - [ ] **BACKUP-SCRIPT-001 [Create Backup Script]**: - **File**: `backup.sh` - **Scope**: Full error handling, `pg_dump`, compression, upload, cleanup - **Dependencies**: Docker, aws-cli, gzip, pg_dump - **Features**: - `docker exec` wrapper for `pg_dump` with credential passthrough - `gzip -9` compression piping - `db_backup_YYYY-MM-DD_HH-mm.sql.gz` naming convention - `aws s3 cp` upload to R2 bucket with verification - Immediate local temp file cleanup after successful upload - Abort on any failure with `set -euo pipefail` - Logging to `logs/pg_backup.log` - **Outcome**: Automated end-to-end backup with logging - [ ] **BACKUP-SCRIPT-002 [Set Script Permissions]**: - **Scope**: Make backup script executable - **Permissions**: `chmod 700 backup.sh` - **Ownership**: Owned by the backup execution user - **Outcome**: Script executable only by owner ### Restore Script Development - [ ] **RESTORE-SCRIPT-001 [Create Restore Script]**: - **File**: `restore.sh` - **Scope**: Interactive backup selection, download, decompress, restore with safety gate - **Dependencies**: Docker, aws-cli, gunzip, psql - **Features**: - List available backups from R2 (limit to last 10) - Interactive selection or "latest" default retrieval - Secure download to temp storage - Pipe decompressed stream directly to `psql` - Explicit user confirmation before overwriting production data - Abort on any failure with `set -euo pipefail` - Logging to `logs/pg_backup.log` - **Outcome**: Verified disaster recovery capability - [ ] **RESTORE-SCRIPT-002 [Set Script Permissions]**: - **Scope**: Make restore script executable - **Permissions**: `chmod 700 restore.sh` - **Ownership**: Owned by the restore execution user - **Outcome**: Script executable only by owner ### Scheduling and Observability - [ ] **CRON-SETUP-001 [Configure Cron Schedule]**: - **Schedule**: Daily at 03:00 AM - **Cron Entry**: `0 3 * * * /absolute/path/to/backup.sh >> /absolute/path/to/logs/pg_backup.log 2>&1` - **Absolute Paths**: All executables and file references use absolute paths - **Logging**: Redirect both stdout and stderr to log file - **Outcome**: Unattended daily backup execution - [ ] **CRON-SETUP-002 [Verify Cron Installation]**: - **Scope**: Confirm cron job is properly installed - **Validation**: `crontab -l` shows the entry - **Test**: Run the exact cron command manually to verify - **Outcome**: Cron job fires at scheduled time and logs result - [ ] **CRON-SETUP-003 [Configure Log Directory]**: - **Scope**: Create and secure log directory - **Directory**: `logs/` with write permissions for cron user - **Permissions**: `chmod 700 logs/` - **Outcome**: Log directory exists and is writable by cron user --- ## Documentation Tasks - [ ] **DOC-INSTALL-001 [Create Installation Guide]**: - **File**: `install.md` - **Scope**: Prerequisites, setup walkthrough, troubleshooting - **Audience**: Operations team and future maintainers - **Sections**: - Prerequisites listing for apt/yum packages - Step-by-step guide from repo clone to active cron - Troubleshooting guide for common errors - R2 endpoint formatting requirements - Permission and security best practices - **Outcome**: Reproducible setup from repo clone to active cron --- ## Proposed Code Changes ### File: `backup.sh` ```bash #!/bin/bash set -euo pipefail # Load environment variables SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)" set -a source "${SCRIPT_DIR}/.env" set +a # Configuration TIMESTAMP=$(date +%Y-%m-%d_%H-%M) BACKUP_FILE="db_backup_${TIMESTAMP}.sql.gz" TEMP_DIR="${SCRIPT_DIR}/tmp" LOG_FILE="${SCRIPT_DIR}/logs/pg_backup.log" R2_PROFILE="r2" # Logging function log() { local level="$1" local message="$2" echo "[$(date -Iseconds)] [${level}] ${message}" >> "${LOG_FILE}" } # Ensure directories exist mkdir -p "${TEMP_DIR}" "${SCRIPT_DIR}/logs" # Validate required variables required_vars=( "CONTAINER_NAME" "POSTGRES_USER" "POSTGRES_DB" "POSTGRES_PASSWORD" "CF_R2_ACCESS_KEY_ID" "CF_R2_SECRET_ACCESS_KEY" "CF_R2_ENDPOINT_URL" "CF_R2_BUCKET" ) for var in "${required_vars[@]}"; do if [[ -z "${!var:-}" ]]; then log "FAILURE" "Required variable ${var} is not set" exit 1 fi done # Validate R2 endpoint format if [[ ! "${CF_R2_ENDPOINT_URL}" =~ ^https://[a-zA-Z0-9]+\.r2\.cloudflarestorage\.com$ ]]; then log "FAILURE" "Invalid R2 endpoint format: ${CF_R2_ENDPOINT_URL}" exit 1 fi log "INFO" "Starting backup process" # Step 1: Create backup using pg_dump via docker exec log "INFO" "Creating PostgreSQL dump" if ! docker exec -i "${CONTAINER_NAME}" \ env PGPASSWORD="${POSTGRES_PASSWORD}" \ pg_dump -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \ --no-owner --no-acl \ | gzip -9 > "${TEMP_DIR}/${BACKUP_FILE}"; then log "FAILURE" "Failed to create PostgreSQL dump" exit 1 fi # Verify backup file is not empty if [[ ! -s "${TEMP_DIR}/${BACKUP_FILE}" ]]; then log "FAILURE" "Backup file is empty" rm -f "${TEMP_DIR}/${BACKUP_FILE}" exit 1 fi log "INFO" "Backup created: ${BACKUP_FILE} ($(du -h "${TEMP_DIR}/${BACKUP_FILE}" | cut -f1))" # Step 2: Upload to R2 log "INFO" "Uploading backup to R2" if ! aws s3 cp "${TEMP_DIR}/${BACKUP_FILE}" \ "s3://${CF_R2_BUCKET}/${BACKUP_FILE}" \ --profile "${R2_PROFILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}"; then log "FAILURE" "Failed to upload backup to R2" rm -f "${TEMP_DIR}/${BACKUP_FILE}" exit 1 fi # Step 3: Verify upload log "INFO" "Verifying upload" if ! aws s3 ls "s3://${CF_R2_BUCKET}/${BACKUP_FILE}" \ --profile "${R2_PROFILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" > /dev/null 2>&1; then log "FAILURE" "Upload verification failed" rm -f "${TEMP_DIR}/${BACKUP_FILE}" exit 1 fi # Step 4: Cleanup local temp file rm -f "${TEMP_DIR}/${BACKUP_FILE}" log "SUCCESS" "Backup completed successfully: ${BACKUP_FILE}" ``` ### File: `restore.sh` ```bash #!/bin/bash set -euo pipefail # Load environment variables SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)" set -a source "${SCRIPT_DIR}/.env" set +a # Configuration TEMP_DIR="${SCRIPT_DIR}/tmp" LOG_FILE="${SCRIPT_DIR}/logs/pg_backup.log" R2_PROFILE="r2" # Logging function log() { local level="$1" local message="$2" echo "[$(date -Iseconds)] [${level}] ${message}" >> "${LOG_FILE}" } # Ensure directories exist mkdir -p "${TEMP_DIR}" "${SCRIPT_DIR}/logs" # Validate required variables required_vars=( "CONTAINER_NAME" "POSTGRES_USER" "POSTGRES_DB" "POSTGRES_PASSWORD" "CF_R2_ACCESS_KEY_ID" "CF_R2_SECRET_ACCESS_KEY" "CF_R2_ENDPOINT_URL" "CF_R2_BUCKET" ) for var in "${required_vars[@]}"; do if [[ -z "${!var:-}" ]]; then log "FAILURE" "Required variable ${var} is not set" exit 1 fi done # Validate R2 endpoint format if [[ ! "${CF_R2_ENDPOINT_URL}" =~ ^https://[a-zA-Z0-9]+\.r2\.cloudflarestorage\.com$ ]]; then log "FAILURE" "Invalid R2 endpoint format: ${CF_R2_ENDPOINT_URL}" exit 1 fi log "INFO" "Starting restore process" # Step 1: List available backups log "INFO" "Listing available backups" BACKUPS=$(aws s3 ls "s3://${CF_R2_BUCKET}/" \ --profile "${R2_PROFILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ | grep "db_backup_.*\.sql\.gz$" \ | sort -r \ | head -10) if [[ -z "${BACKUPS}" ]]; then log "FAILURE" "No backups found in R2 bucket" exit 1 fi echo "Available backups:" echo "${BACKUPS}" | awk '{print NR": "$4" ("$3" "$4")"}' # Step 2: Interactive selection or latest read -p "Select backup number (default: 1 for latest): " SELECTION SELECTION=${SELECTION:-1} if ! [[ "${SELECTION}" =~ ^[0-9]+$ ]] || [[ "${SELECTION}" -lt 1 ]] || [[ "${SELECTION}" -gt 10 ]]; then log "FAILURE" "Invalid selection: ${SELECTION}" exit 1 fi BACKUP_FILE=$(echo "${BACKUPS}" | sed -n "${SELECTION}p" | awk '{print $4}') if [[ -z "${BACKUP_FILE}" ]]; then log "FAILURE" "Could not determine backup file from selection" exit 1 fi # Step 3: Safety confirmation echo "" echo "WARNING: This will overwrite the production database '${POSTGRES_DB}'" echo "in container '${CONTAINER_NAME}' with backup '${BACKUP_FILE}'." read -p "Type 'YES' to confirm: " CONFIRMATION if [[ "${CONFIRMATION}" != "YES" ]]; then log "INFO" "Restore cancelled by user" exit 0 fi # Step 4: Download backup log "INFO" "Downloading backup: ${BACKUP_FILE}" if ! aws s3 cp "s3://${CF_R2_BUCKET}/${BACKUP_FILE}" \ "${TEMP_DIR}/${BACKUP_FILE}" \ --profile "${R2_PROFILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}"; then log "FAILURE" "Failed to download backup" exit 1 fi # Step 5: Restore database log "INFO" "Restoring database" if ! gunzip -c "${TEMP_DIR}/${BACKUP_FILE}" \ | docker exec -i "${CONTAINER_NAME}" \ env PGPASSWORD="${POSTGRES_PASSWORD}" \ psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"; then log "FAILURE" "Failed to restore database" rm -f "${TEMP_DIR}/${BACKUP_FILE}" exit 1 fi # Step 6: Cleanup rm -f "${TEMP_DIR}/${BACKUP_FILE}" log "SUCCESS" "Database restored successfully from: ${BACKUP_FILE}" ``` ### File: `install.md` # PostgreSQL Backup & Restore Installation Guide ## Prerequisites ### System Packages (Ubuntu/Debian) ```bash # Update package list sudo apt update # Install required packages sudo apt install -y \ awscli \ postgresql-client \ docker.io \ gzip ``` ### Docker Container Ensure the PostgreSQL container is running: ```bash docker ps | grep statence_db ``` ### Cloudflare R2 Bucket 1. Create an R2 bucket in Cloudflare dashboard 2. Generate an API token with read/write permissions to the bucket 3. Note your account ID from the R2 dashboard ## Setup Walkthrough ### Step 1: Clone Repository ```bash git clone <repository-url> cd <repository-directory> ``` ### Step 2: Configure Environment Create `.env` file: ```bash cp .env.example .env chmod 600 .env ``` Edit `.env` with your values: ```bash # Database Configuration CONTAINER_NAME=statence_db POSTGRES_USER=your_db_user POSTGRES_DB=your_db_name POSTGRES_PASSWORD=your_db_password # Cloudflare R2 Configuration CF_R2_ACCESS_KEY_ID=your_r2_access_key CF_R2_SECRET_ACCESS_KEY=your_r2_secret_key CF_R2_ENDPOINT_URL=https://<account_id>.r2.cloudflarestorage.com CF_R2_BUCKET=your_bucket_name ``` ### Step 3: Configure AWS CLI Profile ```bash aws configure --profile r2 ``` Enter: - AWS Access Key ID: Your R2 access key - AWS Secret Access Key: Your R2 secret key - Default region name: auto - Default output format: json ### Step 4: Test R2 Connectivity ```bash aws s3 ls --profile r2 --endpoint-url https://<account_id>.r2.cloudflarestorage.com s3://your_bucket_name ``` ### Step 5: Make Scripts Executable ```bash chmod 700 backup.sh restore.sh mkdir -p logs chmod 700 logs ``` ### Step 6: Test Backup Script ```bash ./backup.sh ``` Verify: - Check `logs/pg_backup.log` for SUCCESS message - Check R2 bucket for uploaded backup file ### Step 7: Test Restore Script ```bash ./restore.sh ``` Follow prompts to restore from a backup. ### Step 8: Install Cron Job ```bash crontab -e ``` Add the following line: ```bash 0 3 * * * /absolute/path/to/backup.sh >> /absolute/path/to/logs/pg_backup.log 2>&1 ``` Verify installation: ```bash crontab -l ``` ## Troubleshooting ### R2 Endpoint Format Issues **Error**: `Invalid endpoint URL` **Solution**: Ensure endpoint follows format: `https://<account_id>.r2.cloudflarestorage.com` ### Permission Denied **Error**: `Permission denied` when running scripts **Solution**: ```bash chmod 700 backup.sh restore.sh chmod 600 .env ``` ### pg_dump Version Mismatch **Error**: `pg_dump: error: server version mismatch` **Solution**: Install matching PostgreSQL client version: ```bash sudo apt install postgresql-client-<version> ``` ### Docker Container Not Found **Error**: `Error: No such container: statence_db` **Solution**: Verify container is running: ```bash docker ps | grep statence_db ``` ### AWS CLI Not Configured **Error**: `Unable to locate credentials` **Solution**: Configure R2 profile: ```bash aws configure --profile r2 ``` ### Upload Failed **Error**: `upload failed: Unable to locate credentials` **Solution**: Verify `.env` file has correct R2 credentials and profile is configured ### Log File Not Writable **Error**: `Permission denied` when writing to log **Solution**: ```bash chmod 700 logs/ ``` ## Security Best Practices - Never hardcode credentials in scripts - Use environment variables exclusively - Rotate R2 access keys regularly - Restrict file permissions on `.env` and scripts - Test restore procedures regularly ## Maintenance ### Regular Testing - Test backup creation weekly - Test restore procedure monthly - Verify backup file integrity ### Log Rotation Monitor `logs/pg_backup.log` size and implement rotation: ```bash # Add to crontab for daily log rotation 0 0 * * * /usr/sbin/logrotate /etc/logrotate.d/pg_backup ``` --- ## Commands ### Environment Setup ```bash # Install dependencies sudo apt update sudo apt install -y awscli postgresql-client docker.io gzip # Configure AWS CLI profile aws configure --profile r2 # Set permissions chmod 700 backup.sh restore.sh chmod 600 .env mkdir -p logs chmod 700 logs ``` ### Script Testing ```bash # Test backup ./backup.sh # Test restore ./restore.sh # Check logs cat logs/pg_backup.log ``` ### Cron Installation ```bash # Edit crontab crontab -e # Add entry 0 3 * * * /absolute/path/to/backup.sh >> /absolute/path/to/logs/pg_backup.log 2>&1 # Verify crontab -l ``` --- ## Quality Assurance Checklist - [ ] `aws-cli` commands work with the specific R2 endpoint format - [ ] `pg_dump` version matches or is compatible with the container version - [ ] gzip compression levels are applied correctly - [ ] Scripts have executable permissions (`chmod +x`) - [ ] Logs are writable by the cron user - [ ] Restore script warns user destructively before overwriting data - [ ] Scripts are idempotent where possible - [ ] Hardcoded credentials do NOT appear in scripts (env vars only) - [ ] R2 endpoint URL strictly follows `https://<account_id>.r2.cloudflarestorage.com` format - [ ] Backup files are verified after upload before local cleanup - [ ] Restore script has explicit user confirmation gate - [ ] Cron job uses absolute paths for all executables and files