Sistema de Implementação de Backup & Restauração

# TODO_backup-restore.md ## 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 and bucket accessibility - **Outcome**: All variables populated and connectivity verified - **Commands**: ``` # Check Docker container docker ps | grep statence_db # Test R2 connectivity aws s3 ls --endpoint-url https://<account_id>.r2.cloudflarestorage.com s3://<bucket_name> --profile r2 # Verify pg_dump availability which pg_dump && pg_dump --version # Verify gzip availability which gzip && gzip --version ``` - [ ] **BACKUP-ENV-002 [Configure aws-cli Profile]**: - **Scope**: Specific `aws-cli` configuration profile setup for R2 - **Profile**: Dedicated named profile to avoid AWS S3 conflicts - **Credentials**: Sourced from `.env` file - **Outcome**: `aws s3 ls` against R2 bucket succeeds - **Commands**: ``` # Configure R2 profile aws configure set aws_access_key_id $CF_R2_ACCESS_KEY_ID --profile r2 aws configure set aws_secret_access_key $CF_R2_SECRET_ACCESS_KEY --profile r2 aws configure set region auto --profile r2 # Test connectivity aws s3 ls --endpoint-url $CF_R2_ENDPOINT_URL s3://$CF_R2_BUCKET --profile r2 ``` - [ ] **BACKUP-ENV-003 [Install Required Packages]**: - **Scope**: Install all necessary system packages - **Packages**: aws-cli, postgresql-client, gzip - **Outcome**: All tools available and version-compatible - **Commands**: ``` # Ubuntu/Debian sudo apt update sudo apt install -y awscli postgresql-client gzip # Verify versions aws --version pg_dump --version gzip --version ``` - [ ] **BACKUP-ENV-004 [Create Directory Structure]**: - **Scope**: Create required directories for logs and temp files - **Directories**: `logs/`, `temp/` - **Permissions**: Ensure writable by cron user - **Commands**: ``` mkdir -p logs temp chmod 755 logs temp ``` ## 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 - **Implementation**: ```bash #!/bin/bash set -euo pipefail # Source environment variables SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)" source "${SCRIPT_DIR}/.env" # Logging setup LOG_DIR="${SCRIPT_DIR}/logs" LOG_FILE="${LOG_DIR}/pg_backup.log" mkdir -p "${LOG_DIR}" log() { local timestamp=$(date -u +"%Y-%m-%dT%H:%M:%SZ") echo "[${timestamp}] $1" >> "${LOG_FILE}" } # 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 URL format: ${CF_R2_ENDPOINT_URL}" exit 1 fi # Validate Docker container if ! docker ps --format '{{.Names}}' | grep -q "^${CONTAINER_NAME}$"; then log "FAILURE: Docker container ${CONTAINER_NAME} is not running" exit 1 fi # Generate backup filename TIMESTAMP=$(date -u +"%Y-%m-%d_%H-%M") BACKUP_FILE="db_backup_${TIMESTAMP}.sql.gz" TEMP_DIR="${SCRIPT_DIR}/temp" mkdir -p "${TEMP_DIR}" LOCAL_PATH="${TEMP_DIR}/${BACKUP_FILE}" log "Starting backup: ${BACKUP_FILE}" # Perform pg_dump via docker exec and compress if ! docker exec -i "${CONTAINER_NAME}" pg_dump -U "${POSTGRES_USER}" "${POSTGRES_DB}" --no-owner --no-acl | gzip -9 > "${LOCAL_PATH}"; then log "FAILURE: pg_dump or compression failed" rm -f "${LOCAL_PATH}" exit 1 fi # Verify backup file exists and is non-empty if [ ! -s "${LOCAL_PATH}" ]; then log "FAILURE: Backup file is empty or missing" rm -f "${LOCAL_PATH}" exit 1 fi # Upload to R2 if ! aws s3 cp "${LOCAL_PATH}" "s3://${CF_R2_BUCKET}/${BACKUP_FILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --profile r2; then log "FAILURE: Upload to R2 failed" rm -f "${LOCAL_PATH}" exit 1 fi # Verify upload if ! aws s3 ls "s3://${CF_R2_BUCKET}/${BACKUP_FILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --profile r2 > /dev/null 2>&1; then log "FAILURE: Upload verification failed" rm -f "${LOCAL_PATH}" exit 1 fi # Cleanup local temp file rm -f "${LOCAL_PATH}" # Get file size for logging FILE_SIZE=$(aws s3 ls "s3://${CF_R2_BUCKET}/${BACKUP_FILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --profile r2 | awk '{print $3}') log "SUCCESS: Backup ${BACKUP_FILE} completed (${FILE_SIZE} bytes)" ``` - [ ] **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 - **Implementation**: ```bash #!/bin/bash set -euo pipefail # Source environment variables SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)" source "${SCRIPT_DIR}/.env" # Logging setup LOG_DIR="${SCRIPT_DIR}/logs" LOG_FILE="${LOG_DIR}/pg_backup.log" mkdir -p "${LOG_DIR}" log() { local timestamp=$(date -u +"%Y-%m-%dT%H:%M:%SZ") echo "[${timestamp}] $1" >> "${LOG_FILE}" } # 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 Docker container if ! docker ps --format '{{.Names}}' | grep -q "^${CONTAINER_NAME}$"; then log "FAILURE: Docker container ${CONTAINER_NAME} is not running" exit 1 fi # List available backups echo "Available backups (last 10):" echo "---------------------------" BACKUPS=$(aws s3 ls "s3://${CF_R2_BUCKET}/" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --profile r2 \ --human-readable \ --sort-by lastmodified \ | tail -10) if [ -z "${BACKUPS}" ]; then echo "No backups found in R2 bucket" log "FAILURE: No backups available for restore" exit 1 fi echo "${BACKUPS}" echo "" # Interactive selection echo "Enter backup filename to restore (or press Enter for latest):" read -r SELECTED_BACKUP if [ -z "${SELECTED_BACKUP}" ]; then # Get latest backup SELECTED_BACKUP=$(aws s3 ls "s3://${CF_R2_BUCKET}/" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --profile r2 \ --sort-by lastmodified \ | tail -1 \ | awk '{print $4}') echo "Selected latest backup: ${SELECTED_BACKUP}" fi # Safety gate - destructive warning echo "" echo "WARNING: This will OVERWRITE the database '${POSTGRES_DB}' in container '${CONTAINER_NAME}'" echo "All current data will be LOST and replaced with backup data." echo "" echo "Type 'RESTORE' to confirm (case-sensitive):" read -r CONFIRMATION if [ "${CONFIRMATION}" != "RESTORE" ]; then echo "Restore cancelled by user" log "INFO: Restore cancelled by user for backup ${SELECTED_BACKUP}" exit 0 fi log "Starting restore: ${SELECTED_BACKUP}" # Download and restore TEMP_DIR="${SCRIPT_DIR}/temp" mkdir -p "${TEMP_DIR}" TEMP_FILE="${TEMP_DIR}/restore_temp.sql.gz" # Download backup if ! aws s3 cp "s3://${CF_R2_BUCKET}/${SELECTED_BACKUP}" "${TEMP_FILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --profile r2; then log "FAILURE: Download of backup ${SELECTED_BACKUP} failed" rm -f "${TEMP_FILE}" exit 1 fi # Verify downloaded file if [ ! -s "${TEMP_FILE}" ]; then log "FAILURE: Downloaded backup file is empty" rm -f "${TEMP_FILE}" exit 1 fi # Restore to database if ! gunzip -c "${TEMP_FILE}" | docker exec -i "${CONTAINER_NAME}" psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"; then log "FAILURE: Database restore failed for backup ${SELECTED_BACKUP}" rm -f "${TEMP_FILE}" exit 1 fi # Cleanup temp file rm -f "${TEMP_FILE}" log "SUCCESS: Restore completed from backup ${SELECTED_BACKUP}" echo "Restore completed successfully" ``` - [ ] **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 - **Commands**: ``` # Get absolute path to backup script SCRIPT_DIR=$(pwd) # Add cron entry (crontab -l 2>/dev/null; echo "0 3 * * * cd ${SCRIPT_DIR} && ./backup.sh >> ${SCRIPT_DIR}/logs/pg_backup.log 2>&1") | crontab - # Verify cron entry crontab -l # Test cron command manually cd ${SCRIPT_DIR} && ./backup.sh >> ${SCRIPT_DIR}/logs/pg_backup.log 2>&1 ``` ## 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 - **Content**: ```markdown # PostgreSQL Backup & Restore Installation Guide ## Prerequisites ### System Requirements - Linux VPS (Ubuntu/Debian) - Docker installed and running - PostgreSQL running in Docker container - Cloudflare R2 bucket created ### Required Packages ```bash sudo apt update sudo apt install -y awscli postgresql-client gzip ``` ## Setup Walkthrough ### 1. Clone Repository ```bash git clone <repository-url> cd <repository-directory> ``` ### 2. Configure Environment Create `.env` file with required variables: ```bash cp .env.example .env chmod 600 .env ``` Edit `.env` with your values: ``` CONTAINER_NAME=statence_db POSTGRES_USER=your_user POSTGRES_DB=your_database 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. Configure AWS CLI Profile ```bash aws configure set aws_access_key_id $CF_R2_ACCESS_KEY_ID --profile r2 aws configure set aws_secret_access_key $CF_R2_SECRET_ACCESS_KEY --profile r2 aws configure set region auto --profile r2 ``` ### 4. Test Connectivity ```bash # Test R2 access aws s3 ls --endpoint-url $CF_R2_ENDPOINT_URL s3://$CF_R2_BUCKET --profile r2 # Test Docker container docker ps | grep $CONTAINER_NAME ``` ### 5. Make Scripts Executable ```bash chmod +x backup.sh restore.sh ``` ### 6. Test Backup ```bash ./backup.sh ``` ### 7. Test Restore (on test database) ```bash ./restore.sh ``` ### 8. Configure Cron ```bash SCRIPT_DIR=$(pwd) (crontab -l 2>/dev/null; echo "0 3 * * * cd ${SCRIPT_DIR} && ./backup.sh >> ${SCRIPT_DIR}/logs/pg_backup.log 2>&1") | crontab - ``` ## Troubleshooting ### Common Errors #### R2 Endpoint Format Error **Error**: `Invalid R2 endpoint URL format` **Solution**: Ensure endpoint follows format: `https://<account_id>.r2.cloudflarestorage.com` #### Permission Denied **Error**: `Permission denied` when running scripts **Solution**: Run `chmod +x backup.sh restore.sh` #### Docker Container Not Found **Error**: `Docker container statence_db is not running` **Solution**: Verify container name and start if needed: `docker start statence_db` #### pg_dump Version Mismatch **Error**: `pg_dump: server version mismatch` **Solution**: Install matching version: `sudo apt install postgresql-client-<version>` #### AWS CLI Not Configured **Error**: `Unable to locate credentials` **Solution**: Configure R2 profile: `aws configure --profile r2` ### Logs Check logs at `logs/pg_backup.log` for detailed error information. ``` ## Quality Assurance Task Checklist - [ ] **QA-001 [Script Permissions]**: Verify `chmod +x backup.sh restore.sh` executed - [ ] **QA-002 [Environment Variables]**: Verify `.env` file exists with `chmod 600` and all variables populated - [ ] **QA-003 [R2 Connectivity]**: Verify `aws s3 ls` against R2 bucket succeeds - [ ] **QA-004 [Docker Access]**: Verify `docker exec` works with container - [ ] **QA-005 [Backup Test]**: Run `./backup.sh` and verify: - Backup file created in R2 bucket - Log entry shows SUCCESS - Local temp files cleaned up - [ ] **QA-006 [Restore Test]**: Run `./restore.sh` and verify: - Backup listing works - Interactive selection works - Safety gate prevents accidental restore - Restore completes successfully - [ ] **QA-007 [Cron Setup]**: Verify cron entry with `crontab -l` - [ ] **QA-008 [Log Directory]**: Verify `logs/` directory exists and is writable - [ ] **QA-009 [No Hardcoded Credentials]**: Verify no credentials in scripts - [ ] **QA-010 [Idempotency]**: Verify re-running backup.sh doesn't cause corruption ## Proposed Code Changes ### File: `backup.sh` Full content provided above in BACKUP-SCRIPT-001 ### File: `restore.sh` Full content provided above in RESTORE-SCRIPT-001 ### File: `install.md` Full content provided above in DOC-INSTALL-001 ### File: `.env.example` ``` CONTAINER_NAME=statence_db POSTGRES_USER=your_user POSTGRES_DB=your_database 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 ``` ## Commands Summary ### Environment Setup ```bash # Install packages sudo apt update && sudo apt install -y awscli postgresql-client gzip # Create directories mkdir -p logs temp # Configure AWS CLI aws configure set aws_access_key_id $CF_R2_ACCESS_KEY_ID --profile r2 aws configure set aws_secret_access_key $CF_R2_SECRET_ACCESS_KEY --profile r2 aws configure set region auto --profile r2 # Set permissions chmod +x backup.sh restore.sh chmod 600 .env ``` ### Testing ```bash # Test R2 connectivity aws s3 ls --endpoint-url $CF_R2_ENDPOINT_URL s3://$CF_R2_BUCKET --profile r2 # Test backup ./backup.sh # Test restore ./restore.sh ``` ### Cron Installation ```bash SCRIPT_DIR=$(pwd) (crontab -l 2>/dev/null; echo "0 3 * * * cd ${SCRIPT_DIR} && ./backup.sh >> ${SCRIPT_DIR}/logs/pg_backup.log 2>&1") | crontab - crontab -l ```