Implementador de Respaldo y Restauración - Prompt del Sistema

# 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` (Default: `statence_db`) - `POSTGRES_USER` - `POSTGRES_DB` - `POSTGRES_PASSWORD` - `CF_R2_ACCESS_KEY_ID` - `CF_R2_SECRET_ACCESS_KEY` - `CF_R2_ENDPOINT_URL` (Format: `https://<account_id>.r2.cloudflarestorage.com`) - `CF_R2_BUCKET` - [ ] **Validation**: Confirm R2 endpoint format matches `https://<account_id>.r2.cloudflarestorage.com` - [ ] **Validation**: Verify bucket accessibility with `aws s3 ls` - [ ] **Outcome**: All variables populated and connectivity verified ### BACKUP-ENV-002 [Configure aws-cli Profile] - [ ] **Scope**: Create dedicated `aws-cli` configuration profile for R2 - [ ] **Profile**: Named profile `r2` to avoid conflicts with AWS S3 configurations - [ ] **Credentials**: Sourced from `.env` file, never hardcoded - [ ] **Command**: `aws configure --profile r2` - [ ] **Outcome**: `aws s3 ls --profile r2 --endpoint-url $CF_R2_ENDPOINT_URL s3://$CF_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 status is `healthy` or `running` - [ ] **Validation**: Credentials work with `docker exec` command - [ ] **Outcome**: Container accessible with valid credentials ### BACKUP-ENV-004 [Validate Tooling Availability] - [ ] **Scope**: Ensure required tools are installed and version-compatible - [ ] **Tools**: `pg_dump`, `gzip`, `aws-cli`, `psql` - [ ] **Version Check**: `pg_dump --version` matches or is compatible with container PostgreSQL version - [ ] **Installation**: Install missing packages via `apt` or `yum` - [ ] **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 - [ ] **Features**: - [ ] `set -euo pipefail` for fail-fast behavior - [ ] `docker exec -i` wrapper for `pg_dump` with credential passthrough - [ ] `gzip -9` compression piping - [ ] Naming convention: `db_backup_YYYY-MM-DD_HH-mm.sql.gz` - [ ] `aws s3 cp` upload to R2 bucket - [ ] Verify upload success before deleting local temp files - [ ] Log to `logs/pg_backup.log` with ISO 8601 timestamps - [ ] Abort on any failure with clear error message - [ ] **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 - [ ] **Features**: - [ ] List available backups from R2 (limit to last 10) - [ ] Interactive selection or "latest" default retrieval - [ ] Secure download to temp storage - [ ] Decompression piping directly to `psql` - [ ] Explicit user confirmation before overwriting production data - [ ] Warning message about destructive nature of restore - [ ] Log to `logs/pg_backup.log` with ISO 8601 timestamps - [ ] **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 - [ ] **Command**: `0 3 * * * /absolute/path/to/backup.sh >> /absolute/path/to/logs/pg_backup.log 2>&1` - [ ] **Logging**: Redirect both stdout and stderr to `logs/pg_backup.log` - [ ] **Permissions**: Ensure cron user has write permission to log directory - [ ] **Outcome**: Unattended daily backup execution --- ## Documentation Tasks ### DOC-INSTALL-001 [Create Installation Guide] - [ ] **File**: `install.md` - [ ] **Scope**: Prerequisites, setup walkthrough, troubleshooting - [ ] **Audience**: Operations team and future maintainers - [ ] **Content**: - [ ] Required apt/yum packages (aws-cli, postgresql-client, gzip) - [ ] Step-by-step guide from repo clone to active cron - [ ] `.env` file setup with all required variables - [ ] aws-cli profile configuration for R2 - [ ] Script permission setup (`chmod +x`) - [ ] Cron installation instructions - [ ] Troubleshooting common errors (R2 endpoint formatting, permission denied, version mismatch) - [ ] **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}" TIMESTAMP=$(date +%Y-%m-%d_%H-%M) BACKUP_FILE="db_backup_${TIMESTAMP}.sql.gz" TEMP_DIR="/tmp/pg_backup_${TIMESTAMP}" LOG_DIR="logs" LOG_FILE="${LOG_DIR}/pg_backup.log" R2_PROFILE="r2" # Create log directory if it doesn't exist mkdir -p "${LOG_DIR}" # Logging function log_message() { local level="$1" local message="$2" echo "$(date -Iseconds) [${level}] ${message}" >> "${LOG_FILE}" } # Validate required variables required_vars=( "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_message "FAILURE" "Required variable ${var} is not set" exit 1 fi done # Validate R2 endpoint format if [[ ! "${CF_R2_ENDPOINT_URL}" =~ ^https://[a-z0-9]+\.r2\.cloudflarestorage\.com$ ]]; then log_message "FAILURE" "Invalid R2 endpoint format: ${CF_R2_ENDPOINT_URL}" exit 1 fi # Create temp directory mkdir -p "${TEMP_DIR}" # Perform backup log_message "INFO" "Starting PostgreSQL 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_DIR}/${BACKUP_FILE}"; then log_message "FAILURE" "pg_dump or gzip failed" rm -rf "${TEMP_DIR}" exit 1 fi # Verify backup file exists and is non-empty if [ ! -s "${TEMP_DIR}/${BACKUP_FILE}" ]; then log_message "FAILURE" "Backup file is empty or missing" rm -rf "${TEMP_DIR}" exit 1 fi # Upload to R2 log_message "INFO" "Uploading backup to R2 bucket ${CF_R2_BUCKET}" 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_message "FAILURE" "Upload to R2 failed" rm -rf "${TEMP_DIR}" exit 1 fi # Verify upload by checking file exists in R2 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_message "FAILURE" "Upload verification failed" rm -rf "${TEMP_DIR}" exit 1 fi # Clean up temp files rm -rf "${TEMP_DIR}" # Log success with file size BACKUP_SIZE=$(stat -c%s "${TEMP_DIR}/${BACKUP_FILE}" 2>/dev/null || echo "0") log_message "SUCCESS" "Backup completed: ${BACKUP_FILE} (${BACKUP_SIZE} bytes)" log_message "INFO" "Backup duration: $(date -Iseconds)" exit 0 ``` ### 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}" LOG_DIR="logs" LOG_FILE="${LOG_DIR}/pg_backup.log" R2_PROFILE="r2" TEMP_DIR="/tmp/pg_restore_$(date +%Y%m%d_%H%M%S)" # Create log directory if it doesn't exist mkdir -p "${LOG_DIR}" # Logging function log_message() { local level="$1" local message="$2" echo "$(date -Iseconds) [${level}] ${message}" >> "${LOG_FILE}" } # Validate required variables required_vars=( "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_message "FAILURE" "Required variable ${var} is not set" exit 1 fi done # Validate R2 endpoint format if [[ ! "${CF_R2_ENDPOINT_URL}" =~ ^https://[a-z0-9]+\.r2\.cloudflarestorage\.com$ ]]; then log_message "FAILURE" "Invalid R2 endpoint format: ${CF_R2_ENDPOINT_URL}" exit 1 fi # List available backups echo "Available backups in R2 (last 10):" BACKUPS=$(aws s3 ls "s3://${CF_R2_BUCKET}/" \ --profile "${R2_PROFILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --human-readable | sort -r | head -10) if [ -z "${BACKUPS}" ]; then echo "No backups found in R2 bucket" exit 1 fi echo "${BACKUPS}" echo "" # Interactive selection read -p "Enter backup filename to restore (or press Enter for latest): " SELECTED_BACKUP if [ -z "${SELECTED_BACKUP}" ]; then # Get latest backup SELECTED_BACKUP=$(aws s3 ls "s3://${CF_R2_BUCKET}/" \ --profile "${R2_PROFILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ | sort -r | head -1 | awk '{print $4}') if [ -z "${SELECTED_BACKUP}" ]; then echo "ERROR: No backups available" exit 1 fi echo "Selected latest backup: ${SELECTED_BACKUP}" fi # Safety gate - destructive warning echo "" echo "WARNING: This operation will OVERWRITE the existing database '${POSTGRES_DB}'" echo "in container '${CONTAINER_NAME}'. This action is IRREVERSIBLE." echo "" read -p "Type 'YES' to confirm restore: " CONFIRMATION if [ "${CONFIRMATION}" != "YES" ]; then echo "Restore cancelled by user" log_message "INFO" "Restore cancelled by user" exit 0 fi # Create temp directory mkdir -p "${TEMP_DIR}" # Download backup log_message "INFO" "Downloading backup ${SELECTED_BACKUP} from R2" if ! aws s3 cp "s3://${CF_R2_BUCKET}/${SELECTED_BACKUP}" \ "${TEMP_DIR}/${SELECTED_BACKUP}" \ --profile "${R2_PROFILE}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}"; then log_message "FAILURE" "Download from R2 failed" rm -rf "${TEMP_DIR}" exit 1 fi # Restore database log_message "INFO" "Restoring database ${POSTGRES_DB} from backup" if ! gunzip -c "${TEMP_DIR}/${SELECTED_BACKUP}" | \ docker exec -i "${CONTAINER_NAME}" \ env PGPASSWORD="${POSTGRES_PASSWORD}" \ psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"; then log_message "FAILURE" "Database restore failed" rm -rf "${TEMP_DIR}" exit 1 fi # Clean up temp files rm -rf "${TEMP_DIR}" log_message "SUCCESS" "Database restore completed from ${SELECTED_BACKUP}" echo "Restore completed successfully" exit 0 ``` ### File: `install.md` # PostgreSQL Backup & Restore Installation Guide ## Prerequisites ### Required Packages ```bash # Ubuntu/Debian sudo apt update sudo apt install -y awscli postgresql-client gzip # RHEL/CentOS/Fedora sudo yum install -y aws-cli postgresql gzip ``` ### Docker Container - PostgreSQL container named `statence_db` must be running - Container must have `pg_dump` and `psql` available ## Setup Walkthrough ### 1. Clone Repository ```bash git clone <repository-url> cd <repository-directory> ``` ### 2. Configure Environment Variables Create `.env` file: ```bash cp .env.example .env chmod 600 .env ``` Edit `.env` with your values: ```bash CONTAINER_NAME=statence_db POSTGRES_USER=your_user POSTGRES_DB=your_database POSTGRES_PASSWORD=your_password 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 ``` ### 3. Configure aws-cli Profile ```bash aws configure --profile r2 # Enter your R2 Access Key ID # Enter your R2 Secret Access Key # Leave region as default # Leave output format as default ``` ### 4. Verify Connectivity ```bash # Test R2 connectivity aws s3 ls --profile r2 --endpoint-url $CF_R2_ENDPOINT_URL s3://$CF_R2_BUCKET # Test Docker container access docker exec statence_db pg_dump --version ``` ### 5. Set Script Permissions ```bash chmod +x backup.sh restore.sh ``` ### 6. Test Backup Script ```bash ./backup.sh # Check logs/pg_backup.log for SUCCESS message ``` ### 7. Test Restore Script ```bash ./restore.sh # Follow prompts to select backup and confirm restore ``` ### 8. Install Cron Job ```bash # Edit crontab crontab -e # Add entry (use absolute paths) 0 3 * * * /absolute/path/to/backup.sh >> /absolute/path/to/logs/pg_backup.log 2>&1 # Verify cron entry crontab -l ``` ## Troubleshooting ### Common Errors #### 1. R2 Endpoint Format Error **Error**: `Invalid R2 endpoint format` **Solution**: Ensure endpoint follows format: `https://<account_id>.r2.cloudflarestorage.com` #### 2. Permission Denied **Error**: `Permission denied` when running scripts **Solution**: ```bash chmod +x backup.sh restore.sh chmod 600 .env ``` #### 3. pg_dump Version Mismatch **Error**: `pg_dump: error: server version mismatch` **Solution**: Install matching PostgreSQL client version: ```bash # Check container version docker exec statence_db postgres --version # Install matching client sudo apt install postgresql-client-<version> ``` #### 4. R2 Upload Failed **Error**: `Upload to R2 failed` **Solution**: - Verify R2 credentials in `.env` - Test connectivity: `aws s3 ls --profile r2 --endpoint-url $CF_R2_ENDPOINT_URL s3://$CF_R2_BUCKET` - Check bucket permissions #### 5. Docker Container Not Found **Error**: `Error: No such container: statence_db` **Solution**: - Verify container is running: `docker ps` - Update `CONTAINER_NAME` in `.env` #### 6. Log Directory Not Writable **Error**: `Permission denied` when writing to logs **Solution**: ```bash mkdir -p logs chmod 755 logs ``` ## Maintenance ### Regular Testing - Test backup script weekly - Test restore script monthly - Verify backup files exist in R2 ### Log Rotation Monitor `logs/pg_backup.log` size and implement rotation: ```bash # Add to crontab for daily rotation 0 0 * * * logrotate /path/to/logrotate.conf ``` ### Security Best Practices - Rotate R2 access keys every 90 days - Keep `.env` file permissions at 600 - Never commit `.env` to version control - Use least-privilege IAM credentials for R2 --- ## Commands ### Environment Setup ```bash # Install dependencies sudo apt update && sudo apt install -y awscli postgresql-client gzip # Configure aws-cli profile aws configure --profile r2 # Set script permissions chmod +x backup.sh restore.sh # Create log directory mkdir -p logs ``` ### Script Testing ```bash # Test backup ./backup.sh # Test restore (interactive) ./restore.sh # Test R2 connectivity aws s3 ls --profile r2 --endpoint-url $CF_R2_ENDPOINT_URL s3://$CF_R2_BUCKET ``` ### 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 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) - [ ] `backup.sh` runs end-to-end without manual intervention - [ ] `restore.sh` recovers a database from the latest R2 backup successfully - [ ] Cron job fires at the scheduled time and logs the result - [ ] All credentials are sourced from environment variables, never hardcoded - [ ] R2 endpoint URL strictly follows `https://<account_id>.r2.cloudflarestorage.com` format - [ ] Log directory exists and is writable by the cron user - [ ] Restore script warns the user destructively before overwriting data