94 lines
2.2 KiB
Markdown
94 lines
2.2 KiB
Markdown
# 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
|