# Database Backup & Restore Scripts

## Overview
Automated database backup system that exports PostgreSQL database, compresses it with gzip, and uploads to Cloudflare R2 storage.

## Requirements
```bash
pip install boto3
```

## Configuration
All configuration is stored in `.env` file:
- `R2_ENDPOINT`: Cloudflare R2 endpoint URL
- `R2_ACCESS_KEY`: R2 API access key
- `R2_SECRET_KEY`: R2 API secret key
- `R2_BUCKET`: R2 bucket name
- Database credentials (DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD)

## Usage

### Create Backup
```bash
cd backend
python backup_db.py
```

This will:
1. Export the database using `pg_dump`
2. Compress the dump with gzip (typically 80-90% reduction)
3. Upload to R2 with timestamp
4. List all backups in R2
5. Clean up old local backups (keeps last 3)

### Restore from Backup
```bash
cd backend
python restore_db.py
```

This will:
1. List all available backups in R2
2. Let you select which backup to restore
3. Download and decompress the backup
4. Restore to the database (with confirmation prompt)

**WARNING**: Restore will drop all existing tables and recreate them from backup!

## Automated Backups

### Linux/Mac (Cron)
Add to crontab:
```bash
# Daily backup at 2 AM
0 2 * * * cd /path/to/backend && python backup_db.py >> backup.log 2>&1
```

### Windows (Task Scheduler)
Create a scheduled task:
1. Open Task Scheduler
2. Create Basic Task
3. Name: "Recipe DB Backup"
4. Trigger: Daily at 2:00 AM
5. Action: Start a program
   - Program: `python`
   - Arguments: `backup_db.py`
   - Start in: `C:\path\to\backend`

## Backup File Format
Files are named: `recipes_db_YYYYMMDD_HHMMSS.sql.gz`

Example: `recipes_db_20251221_140530.sql.gz`

## Storage
- Local backups stored in: `backend/backups/`
- R2 backups stored in: `my-recipes-db-bkp` bucket
- Local backups auto-cleanup (keeps last 3)
- R2 backups are never auto-deleted (manual cleanup if needed)

## Troubleshooting

### pg_dump not found
Install PostgreSQL client tools:
- **Windows**: Install PostgreSQL and add to PATH
- **Linux**: `sudo apt install postgresql-client`
- **Mac**: `brew install postgresql`

### Connection errors
Verify database credentials in `.env` file

### R2 upload errors
- Check R2 credentials
- Verify bucket exists
- Ensure API token has "Edit" permissions