백업 및 복원 구현자 시스템 프롬프트

# 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 - [ ] **BACKUP-ENV-001 [Validate Environment Variables]**: - **Scope**: Validate `.env` variables and R2 connectivity - **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` - **Validation**: Confirm R2 endpoint format (`https://<account_id>.r2.cloudflarestorage.com`) and bucket accessibility - **Outcome**: All variables populated and connectivity verified - [ ] **BACKUP-ENV-002 [Configure aws-cli Profile]**: - **Scope**: Specific `aws-cli` configuration profile setup for R2 - **Profile**: Dedicated named profile (`r2`) to avoid AWS S3 conflicts - **Credentials**: Sourced from `.env` file - **Outcome**: `aws s3 ls` against R2 bucket succeeds - [ ] **BACKUP-ENV-003 [Validate Docker Container Access]**: - **Scope**: Verify PostgreSQL container is running and accessible - **Command**: `docker ps | grep statence_db` - **Validation**: Container is healthy and accepting connections - **Outcome**: Container access confirmed - [ ] **BACKUP-ENV-004 [Validate Tooling Availability]**: - **Scope**: Check required tools are installed - **Tools**: `pg_dump`, `gzip`, `aws-cli`, `psql` - **Validation**: Version compatibility with container PostgreSQL - **Outcome**: All tools available and version-compatible --- ### Implementation Tasks - [ ] **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 - **Outcome**: Automated end-to-end backup with logging - [ ] **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 - **Outcome**: Verified disaster recovery capability - [ ] **CRON-SETUP-001 [Configure Cron Schedule]**: - **Schedule**: Daily at 03:00 AM - **Scope**: Generate verified cron job entry with absolute paths - **Logging**: Redirect output to `logs/pg_backup.log` - **Outcome**: Unattended daily backup execution - [ ] **LOG-SETUP-001 [Create Log Directory]**: - **Scope**: Ensure `logs/` directory exists and is writable - **Permissions**: Writable by cron user - **Outcome**: Log directory ready for use --- ### Documentation Tasks - [ ] **DOC-INSTALL-001 [Create Installation Guide]**: - **File**: `install.md` - **Scope**: Prerequisites, setup walkthrough, troubleshooting - **Audience**: Operations team and future maintainers - **Outcome**: Reproducible setup from repo clone to active cron --- ### Proposed Code Changes #### File: `backup.sh` ```bash #!/bin/bash set -euo pipefail # Load environment variables if [ -f .env ]; then set -a source .env set +a else echo "[ERROR] .env file not found" exit 1 fi # Configuration CONTAINER_NAME="${CONTAINER_NAME:-statence_db}" POSTGRES_USER="${POSTGRES_USER:-postgres}" POSTGRES_DB="${POSTGRES_DB:-postgres}" POSTGRES_PASSWORD="${POSTGRES_PASSWORD:-}" CF_R2_ACCESS_KEY_ID="${CF_R2_ACCESS_KEY_ID:-}" CF_R2_SECRET_ACCESS_KEY="${CF_R2_SECRET_ACCESS_KEY:-}" CF_R2_ENDPOINT_URL="${CF_R2_ENDPOINT_URL:-}" CF_R2_BUCKET="${CF_R2_BUCKET:-}" # Paths SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)" LOG_DIR="${SCRIPT_DIR}/logs" LOG_FILE="${LOG_DIR}/pg_backup.log" TEMP_DIR="${SCRIPT_DIR}/tmp" # Timestamp and filename TIMESTAMP=$(date +%Y-%m-%d_%H-%M) BACKUP_FILE="db_backup_${TIMESTAMP}.sql.gz" TEMP_BACKUP_PATH="${TEMP_DIR}/${BACKUP_FILE}" # Logging function log() { local level="$1" local message="$2" echo "[$(date -Iseconds)] [${level}] ${message}" >> "${LOG_FILE}" } # Validate required variables validate_env() { local missing=0 for var in POSTGRES_PASSWORD CF_R2_ACCESS_KEY_ID CF_R2_SECRET_ACCESS_KEY CF_R2_ENDPOINT_URL CF_R2_BUCKET; do if [ -z "${!var}" ]; then log "ERROR" "Missing required variable: ${var}" missing=1 fi done if [ "${missing}" -eq 1 ]; then exit 1 fi } # Create directories setup_directories() { mkdir -p "${LOG_DIR}" "${TEMP_DIR}" if [ ! -w "${LOG_DIR}" ]; then echo "[ERROR] Log directory not writable: ${LOG_DIR}" exit 1 fi } # Perform backup perform_backup() { log "INFO" "Starting backup for database: ${POSTGRES_DB}" # Execute pg_dump via docker exec 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_BACKUP_PATH}"; then log "ERROR" "pg_dump or gzip failed" return 1 fi # Verify backup file exists and is non-empty if [ ! -s "${TEMP_BACKUP_PATH}" ]; then log "ERROR" "Backup file is empty or missing" return 1 fi log "INFO" "Backup created: ${BACKUP_FILE} ($(du -h "${TEMP_BACKUP_PATH}" | cut -f1))" return 0 } # Upload to R2 upload_to_r2() { log "INFO" "Uploading backup to R2 bucket: ${CF_R2_BUCKET}" # Configure AWS CLI with R2 credentials export AWS_ACCESS_KEY_ID="${CF_R2_ACCESS_KEY_ID}" export AWS_SECRET_ACCESS_KEY="${CF_R2_SECRET_ACCESS_KEY}" export AWS_DEFAULT_REGION="auto" # Upload with retry logic local max_retries=3 local retry_count=0 while [ "${retry_count}" -lt "${max_retries}" ]; do if aws s3 cp "${TEMP_BACKUP_PATH}" "s3://${CF_R2_BUCKET}/${BACKUP_FILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --only-show-errors; then log "INFO" "Upload successful: ${BACKUP_FILE}" return 0 fi retry_count=$((retry_count + 1)) log "WARN" "Upload attempt ${retry_count} failed, retrying..." sleep 5 done log "ERROR" "Upload failed after ${max_retries} attempts" return 1 } # Cleanup temp files cleanup() { if [ -f "${TEMP_BACKUP_PATH}" ]; then rm -f "${TEMP_BACKUP_PATH}" log "INFO" "Cleaned up temp file: ${TEMP_BACKUP_PATH}" fi } # Main execution main() { setup_directories validate_env if perform_backup && upload_to_r2; then cleanup log "SUCCESS" "Backup completed successfully: ${BACKUP_FILE}" echo "[SUCCESS] Backup completed: ${BACKUP_FILE}" else cleanup log "FAILURE" "Backup failed" echo "[FAILURE] Backup failed - check logs: ${LOG_FILE}" exit 1 fi } # Trap for cleanup on exit trap cleanup EXIT # Run main main ``` #### File: `restore.sh` ```bash #!/bin/bash set -euo pipefail # Load environment variables if [ -f .env ]; then set -a source .env set +a else echo "[ERROR] .env file not found" exit 1 fi # Configuration CONTAINER_NAME="${CONTAINER_NAME:-statence_db}" POSTGRES_USER="${POSTGRES_USER:-postgres}" POSTGRES_DB="${POSTGRES_DB:-postgres}" POSTGRES_PASSWORD="${POSTGRES_PASSWORD:-}" CF_R2_ACCESS_KEY_ID="${CF_R2_ACCESS_KEY_ID:-}" CF_R2_SECRET_ACCESS_KEY="${CF_R2_SECRET_ACCESS_KEY:-}" CF_R2_ENDPOINT_URL="${CF_R2_ENDPOINT_URL:-}" CF_R2_BUCKET="${CF_R2_BUCKET:-}" # Paths SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)" LOG_DIR="${SCRIPT_DIR}/logs" LOG_FILE="${LOG_DIR}/pg_backup.log" TEMP_DIR="${SCRIPT_DIR}/tmp" # Logging function log() { local level="$1" local message="$2" echo "[$(date -Iseconds)] [${level}] ${message}" >> "${LOG_FILE}" } # Validate required variables validate_env() { local missing=0 for var in POSTGRES_PASSWORD CF_R2_ACCESS_KEY_ID CF_R2_SECRET_ACCESS_KEY CF_R2_ENDPOINT_URL CF_R2_BUCKET; do if [ -z "${!var}" ]; then log "ERROR" "Missing required variable: ${var}" missing=1 fi done if [ "${missing}" -eq 1 ]; then exit 1 fi } # Create directories setup_directories() { mkdir -p "${LOG_DIR}" "${TEMP_DIR}" } # List available backups list_backups() { echo "Available backups (last 10):" echo "============================" export AWS_ACCESS_KEY_ID="${CF_R2_ACCESS_KEY_ID}" export AWS_SECRET_ACCESS_KEY="${CF_R2_SECRET_ACCESS_KEY}" export AWS_DEFAULT_REGION="auto" aws s3 ls "s3://${CF_R2_BUCKET}/" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --human-readable \ | sort -r \ | head -10 } # Select backup to restore select_backup() { local choice echo "" read -p "Enter backup filename to restore (or 'latest' for most recent): " choice if [ "${choice}" = "latest" ] || [ -z "${choice}" ]; then # Get latest backup BACKUP_FILE=$(aws s3 ls "s3://${CF_R2_BUCKET}/" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ | sort -r \ | head -1 \ | awk '{print $4}') if [ -z "${BACKUP_FILE}" ]; then echo "[ERROR] No backups found in bucket" exit 1 fi echo "Selected latest backup: ${BACKUP_FILE}" else BACKUP_FILE="${choice}" fi } # Download and restore perform_restore() { local temp_backup="${TEMP_DIR}/${BACKUP_FILE}" log "INFO" "Downloading backup: ${BACKUP_FILE}" # Download backup if ! aws s3 cp "s3://${CF_R2_BUCKET}/${BACKUP_FILE}" "${temp_backup}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --only-show-errors; then log "ERROR" "Failed to download backup" return 1 fi log "INFO" "Backup downloaded, starting restore..." # Restore database if ! gunzip -c "${temp_backup}" | docker exec -i "${CONTAINER_NAME}" \ env PGPASSWORD="${POSTGRES_PASSWORD}" \ psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"; then log "ERROR" "Restore failed" return 1 fi # Cleanup temp file rm -f "${temp_backup}" log "SUCCESS" "Restore completed successfully from: ${BACKUP_FILE}" return 0 } # Safety confirmation confirm_restore() { echo "" echo "⚠️ WARNING: This will OVERWRITE the current database '${POSTGRES_DB}'" echo "in container '${CONTAINER_NAME}' with data from backup." echo "" read -p "Type 'YES' to confirm restore: " confirmation if [ "${confirmation}" != "YES" ]; then echo "Restore cancelled." exit 1 fi } # Main execution main() { setup_directories validate_env echo "=== PostgreSQL Backup Restore Tool ===" echo "" list_backups select_backup confirm_restore if perform_restore; then echo "[SUCCESS] Restore completed successfully" else echo "[FAILURE] Restore failed - check logs: ${LOG_FILE}" exit 1 fi } # Run main main ``` #### File: `install.md` ```markdown # PostgreSQL Backup & Restore Installation Guide ## Prerequisites ### System Packages (Ubuntu/Debian) ```bash sudo apt update sudo apt install -y awscli postgresql-client docker.io ``` ### Docker Container - PostgreSQL container must be running with name `statence_db` - Container must have `pg_dump` and `psql` available ### Cloudflare R2 - R2 bucket created - API tokens with read/write permissions to bucket - Account ID for endpoint URL ## Installation Steps ### 1. Clone Repository ```bash git clone <repository-url> cd <repository-directory> ``` ### 2. Configure Environment Create `.env` file: ```bash cp .env.example .env chmod 600 .env ``` Edit `.env` with your values: ```bash CONTAINER_NAME=statence_db POSTGRES_USER=postgres POSTGRES_DB=postgres POSTGRES_PASSWORD=your_password CF_R2_ACCESS_KEY_ID=your_access_key CF_R2_SECRET_ACCESS_KEY=your_secret_key CF_R2_ENDPOINT_URL=https://<account_id>.r2.cloudflarestorage.com CF_R2_BUCKET=your_bucket_name ``` ### 3. Set Permissions ```bash chmod +x backup.sh restore.sh mkdir -p logs tmp ``` ### 4. Test Backup Script ```bash ./backup.sh ``` Verify backup appears in R2 bucket: ```bash aws s3 ls s3://your_bucket_name --endpoint-url https://<account_id>.r2.cloudflarestorage.com ``` ### 5. Test Restore Script ```bash ./restore.sh ``` ### 6. Configure Cron Add to crontab: ```bash crontab -e ``` Add line: ```bash 0 3 * * * /absolute/path/to/backup.sh >> /absolute/path/to/logs/pg_backup.log 2>&1 ``` Verify cron: ```bash crontab -l ``` ## Troubleshooting ### R2 Connection Issues - Verify endpoint URL format: `https://<account_id>.r2.cloudflarestorage.com` - Check credentials have correct permissions - Test with: `aws s3 ls --endpoint-url <endpoint> s3://<bucket>` ### Permission Denied - Ensure scripts are executable: `chmod +x backup.sh restore.sh` - Check log directory permissions: `chmod 755 logs/` - Verify `.env` file permissions: `chmod 600 .env` ### pg_dump Version Mismatch - Install matching PostgreSQL client: `sudo apt install postgresql-client-<version>` - Check container version: `docker exec statence_db psql --version` ### Docker Connection Issues - Verify container running: `docker ps | grep statence_db` - Check container logs: `docker logs statence_db` ## Common Errors | Error | Solution | |-------|----------| | `Unable to locate credentials` | Check AWS credentials in `.env` | | `Access Denied` | Verify R2 bucket permissions | | `Connection refused` | Check Docker container is running | | `pg_dump: error` | Verify PostgreSQL credentials | | `No space left on device` | Clean up temp files, check disk space | ``` --- ### Commands #### Environment Setup ```bash # Install required packages sudo apt update sudo apt install -y awscli postgresql-client docker.io # Clone repository git clone <repository-url> cd <repository-directory> # Create .env from template cp .env.example .env chmod 600 .env # Edit .env with your values nano .env # Make scripts executable chmod +x backup.sh restore.sh # Create required directories mkdir -p logs tmp ``` #### Script Testing ```bash # Test backup script ./backup.sh # Verify backup in R2 aws s3 ls s3://<bucket> --endpoint-url https://<account_id>.r2.cloudflarestorage.com # Test restore script (interactive) ./restore.sh # Test restore with latest backup ./restore.sh # Then type 'latest' when prompted ``` #### Cron Installation ```bash # Edit crontab crontab -e # Add line (use absolute paths) 0 3 * * * /absolute/path/to/backup.sh >> /absolute/path/to/logs/pg_backup.log 2>&1 # Verify crontab crontab -l # Test cron command manually /absolute/path/to/backup.sh >> /absolute/path/to/logs/pg_backup.log 2>&1 ``` --- ### Quality Assurance Task 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)