Backup & Restore Implementer System-Prompt

# 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 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 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` to confirm `statence_db` container status - **Credentials**: Test `docker exec` with `pg_isready` using env credentials - **Outcome**: Container healthy and credentials valid - [ ] **BACKUP-ENV-004 [Verify Tool Availability]**: - **Scope**: Check required tools are installed - **Tools**: `pg_dump`, `gzip`, `aws-cli`, `psql` - **Versions**: Ensure `pg_dump` version matches container PostgreSQL version - **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 --- ### 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 "FAILURE: .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:-}" # Logging setup LOG_DIR="logs" LOG_FILE="${LOG_DIR}/pg_backup.log" mkdir -p "${LOG_DIR}" # Timestamp function timestamp() { date -u +"%Y-%m-%dT%H:%M:%SZ" } # Log function log() { local level="$1" local message="$2" echo "$(timestamp) [${level}] ${message}" >> "${LOG_FILE}" } # Validate required variables validate_env() { local missing=0 for var in 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; do if [ -z "${!var}" ]; then log "FAILURE" "Missing required variable: ${var}" missing=1 fi done if [ "${missing}" -eq 1 ]; then log "FAILURE" "Environment validation failed" exit 1 fi } # Validate R2 endpoint format validate_r2_endpoint() { if [[ ! "${CF_R2_ENDPOINT_URL}" =~ ^https://[a-z0-9]+\.r2\.cloudflarestorage\.com$ ]]; then log "FAILURE" "Invalid R2 endpoint format: ${CF_R2_ENDPOINT_URL}" exit 1 fi } # Check container health check_container() { if ! docker ps --format '{{.Names}}' | grep -q "^${CONTAINER_NAME}$"; then log "FAILURE" "Container ${CONTAINER_NAME} is not running" exit 1 fi } # Create backup create_backup() { local backup_file="db_backup_$(date -u +%Y-%m-%d_%H-%M).sql.gz" local temp_dir="/tmp/pg_backup_$$" mkdir -p "${temp_dir}" log "INFO" "Starting backup: ${backup_file}" # Dump and compress 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" "pg_dump or gzip failed" rm -rf "${temp_dir}" exit 1 fi # Verify backup file if [ ! -s "${temp_dir}/${backup_file}" ]; then log "FAILURE" "Backup file is empty" rm -rf "${temp_dir}" exit 1 fi # Upload to R2 if ! aws s3 cp "${temp_dir}/${backup_file}" "s3://${CF_R2_BUCKET}/${backup_file}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --profile r2; then log "FAILURE" "Upload to R2 failed" rm -rf "${temp_dir}" 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 -rf "${temp_dir}" exit 1 fi # Cleanup rm -rf "${temp_dir}" log "SUCCESS" "Backup completed: ${backup_file}" } # Main execution main() { log "INFO" "Backup process started" validate_env validate_r2_endpoint check_container create_backup log "INFO" "Backup process completed successfully" } 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 "FAILURE: .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:-}" # Logging setup LOG_DIR="logs" LOG_FILE="${LOG_DIR}/pg_backup.log" mkdir -p "${LOG_DIR}" # Timestamp function timestamp() { date -u +"%Y-%m-%dT%H:%M:%SZ" } # Log function log() { local level="$1" local message="$2" echo "$(timestamp) [${level}] ${message}" >> "${LOG_FILE}" } # Validate required variables validate_env() { local missing=0 for var in 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; do if [ -z "${!var}" ]; then log "FAILURE" "Missing required variable: ${var}" missing=1 fi done if [ "${missing}" -eq 1 ]; then log "FAILURE" "Environment validation failed" exit 1 fi } # List available backups list_backups() { echo "Available backups (last 10):" aws s3 ls "s3://${CF_R2_BUCKET}/" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --profile r2 | sort -r | head -10 | awk '{print $4}' } # Select backup select_backup() { local backups backups=$(list_backups) if [ -z "${backups}" ]; then log "FAILURE" "No backups found in R2 bucket" exit 1 fi echo "${backups}" echo "" read -p "Enter backup filename (or 'latest' for most recent): " selection if [ "${selection}" = "latest" ] || [ -z "${selection}" ]; then selection=$(echo "${backups}" | head -1) fi echo "${selection}" } # Confirm restore confirm_restore() { local backup_file="$1" echo "" echo "WARNING: You are about to restore database '${POSTGRES_DB}' from backup:" echo " ${backup_file}" echo "This will OVERWRITE existing data in the database." echo "" read -p "Type 'RESTORE' to confirm: " confirmation if [ "${confirmation}" != "RESTORE" ]; then log "INFO" "Restore cancelled by user" exit 0 fi } # Perform restore perform_restore() { local backup_file="$1" local temp_dir="/tmp/pg_restore_$$" mkdir -p "${temp_dir}" log "INFO" "Starting restore: ${backup_file}" # Download backup if ! aws s3 cp "s3://${CF_R2_BUCKET}/${backup_file}" "${temp_dir}/${backup_file}" \ --endpoint-url "${CF_R2_ENDPOINT_URL}" \ --profile r2; then log "FAILURE" "Download from R2 failed" rm -rf "${temp_dir}" exit 1 fi # Restore 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" "Restore to database failed" rm -rf "${temp_dir}" exit 1 fi # Cleanup rm -rf "${temp_dir}" log "SUCCESS" "Restore completed: ${backup_file}" } # Main execution main() { log "INFO" "Restore process started" validate_env local backup_file backup_file=$(select_backup) confirm_restore "${backup_file}" perform_restore "${backup_file}" log "INFO" "Restore process completed successfully" } main "$@" ``` #### File: `install.md` # Installation Guide: PostgreSQL Backup & Restore Pipeline ## Prerequisites ### System Packages (Ubuntu/Debian) ```bash sudo apt-get update sudo apt-get install -y \ postgresql-client \ awscli \ gzip \ docker.io ``` ### Verify Docker Container ```bash # Ensure PostgreSQL container is running docker ps | grep statence_db # Verify pg_dump version matches container version docker exec statence_db pg_dump --version pg_dump --version ``` ## Setup Walkthrough ### 1. Clone Repository ```bash git clone <repository-url> cd <repository-directory> ``` ### 2. Configure Environment ```bash # Create .env file from template cp .env.example .env # Edit .env with your values nano .env ``` Required variables in `.env`: ```bash 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 # Configure dedicated R2 profile aws configure --profile r2 # Enter your R2 Access Key ID # Enter your R2 Secret Access Key # Default region: auto # Default output format: json # Test connectivity aws s3 ls --endpoint-url "$CF_R2_ENDPOINT_URL" --profile r2 "s3://$CF_R2_BUCKET" ``` ### 4. Set Permissions ```bash # Secure environment file chmod 600 .env # Make scripts executable chmod +x backup.sh restore.sh # Create log directory mkdir -p logs chmod 755 logs ``` ### 5. Test Backup Script ```bash # Run backup manually ./backup.sh # Verify backup in R2 aws s3 ls --endpoint-url "$CF_R2_ENDPOINT_URL" --profile r2 "s3://$CF_R2_BUCKET" ``` ### 6. Test Restore Script ```bash # Run restore (will prompt for confirmation) ./restore.sh ``` ### 7. Configure Cron Job ```bash # Edit crontab crontab -e # Add entry for daily backup at 03:00 AM 0 3 * * * cd /absolute/path/to/script && /bin/bash backup.sh >> /absolute/path/to/logs/pg_backup.log 2>&1 # Verify cron entry crontab -l ``` ## Troubleshooting ### Common Errors #### R2 Endpoint Format Error **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 +x backup.sh restore.sh chmod 600 .env ``` #### Docker Container Not Found **Error**: `Container statence_db is not running` **Solution**: ```bash docker ps -a | grep statence_db docker start statence_db ``` #### pg_dump Version Mismatch **Error**: `pg_dump: error: server version mismatch` **Solution**: Install matching PostgreSQL client version: ```bash sudo apt-get install postgresql-client-<version> ``` #### AWS CLI Profile Not Found **Error**: `The config profile (r2) could not be found` **Solution**: ```bash aws configure --profile r2 ``` #### Log Directory Not Writable **Error**: `Permission denied` when writing logs **Solution**: ```bash sudo chown -R $USER:$USER logs/ chmod 755 logs/ ``` ### Verification Commands ```bash # Check backup log tail -f logs/pg_backup.log # List backups in R2 aws s3 ls --endpoint-url "$CF_R2_ENDPOINT_URL" --profile r2 "s3://$CF_R2_BUCKET" # Test database connectivity docker exec statence_db pg_isready -U "$POSTGRES_USER" ``` --- ### Commands #### Environment Setup ```bash # Install dependencies sudo apt-get update && sudo apt-get install -y postgresql-client awscli gzip # Configure AWS CLI aws configure --profile r2 # Set permissions chmod 600 .env chmod +x backup.sh restore.sh mkdir -p logs ``` #### Script Testing ```bash # Test backup ./backup.sh # Test restore (with confirmation) ./restore.sh # Test restore with specific backup ./restore.sh db_backup_2024-01-01_03-00.sql.gz ``` #### Cron Installation ```bash # Add to crontab crontab -e # Add line: 0 3 * * * cd /absolute/path && /bin/bash backup.sh >> /absolute/path/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)