# Yemaya Database Backup & Recovery

This directory contains scripts and configurations for PostgreSQL database
backup and recovery.

## Overview

The backup system provides:

- **Automated backups** with configurable retention periods
- **Tiered storage** (daily, weekly, monthly)
- **Point-in-time recovery** capability
- **S3-compatible offsite storage** sync
- **Checksum verification** for data integrity

## Quick Start

### Manual Backup

```bash
# Using Docker Compose
docker compose run --rm backup /scripts/backup.sh

# Full backup (default)
docker compose run --rm backup /scripts/backup.sh

# Schema-only backup
docker compose run --rm -e BACKUP_TYPE=schema backup /scripts/backup.sh

# Data-only backup
docker compose run --rm -e BACKUP_TYPE=data backup /scripts/backup.sh
```

### Manual Restore

```bash
# List available backups
docker compose run --rm backup /scripts/list-backups.sh

# Restore from a specific backup
docker compose run --rm backup /scripts/restore.sh /var/backups/yemaya/daily/yemaya_full_20250101_120000.dump

# Restore with verification
docker compose run --rm backup /scripts/restore.sh --verify /path/to/backup.dump

# Restore to a new database (for testing)
docker compose run --rm backup /scripts/restore.sh --new-db --target-db yemaya_test /path/to/backup.dump
```

## Scripts

### backup.sh

Creates database backups with automatic rotation.

**Environment Variables:** | Variable | Default | Description |
|----------|---------|-------------| | `BACKUP_DIR` | `/var/backups/yemaya` |
Base backup directory | | `POSTGRES_HOST` | `localhost` | PostgreSQL host | |
`POSTGRES_PORT` | `5432` | PostgreSQL port | | `POSTGRES_DB` | `yemaya` |
Database name | | `POSTGRES_USER` | `yemaya` | Database user | | `PGPASSWORD`
| - | Database password | | `BACKUP_RETENTION_DAYS` | `30` | Days to keep daily
backups | | `BACKUP_TYPE` | `full` | Type: `full`, `schema`, `data` |

**Backup Types:**

- `full` - Complete database dump (custom format, compressed)
- `schema` - Schema only (SQL format)
- `data` - Data only (custom format, compressed)

**Retention Policy:**

- Daily backups: 30 days (configurable)
- Weekly backups (Sunday): 90 days
- Monthly backups (1st of month): 365 days

### restore.sh

Restores database from backup.

**Usage:**

```bash
./restore.sh [OPTIONS] <backup_file>

Options:
    -h, --host          PostgreSQL host
    -p, --port          PostgreSQL port
    -d, --database      Database name
    -U, --user          Database user
    -n, --new-db        Create a new database
    -t, --target-db     Target database name
    -c, --clean         Drop and recreate database
    -v, --verify        Verify checksum before restore
    --dry-run           Show what would be done
```

**Examples:**

```bash
# Standard restore
./restore.sh backup.dump

# Restore to test database
./restore.sh --new-db --target-db yemaya_staging backup.dump

# Clean restore with verification
./restore.sh --clean --verify backup.dump

# Preview restore operations
./restore.sh --dry-run backup.dump
```

### list-backups.sh

Lists available backups with metadata.

**Usage:**

```bash
./list-backups.sh [OPTIONS]

Options:
    -d, --dir       Backup directory
    -t, --type      Filter by type (daily, weekly, monthly)
    -n, --count     Number of backups to show
    -j, --json      Output in JSON format
```

### s3-sync.sh

Syncs backups to S3-compatible storage.

**Environment Variables:** | Variable | Default | Description |
|----------|---------|-------------| | `S3_BUCKET` | `yemaya-backups` | S3
bucket name | | `S3_ENDPOINT` | - | S3 endpoint (for MinIO) | | `S3_PREFIX` |
`database` | S3 path prefix | | `AWS_ACCESS_KEY_ID` | - | AWS/MinIO access key |
| `AWS_SECRET_ACCESS_KEY` | - | AWS/MinIO secret key |

## Docker Compose

The backup service is available via Docker Compose with the `backup` profile:

```bash
# Start backup service
docker compose --profile backup up -d backup

# Run manual backup
docker compose run --rm backup /scripts/backup.sh

# List backups
docker compose run --rm backup /scripts/list-backups.sh

# Sync to S3
docker compose run --rm backup /scripts/s3-sync.sh
```

## Scheduled Backups

### Using Cron (Host)

Add to crontab:

```bash
# Daily backup at 2 AM
0 2 * * * docker compose -f /path/to/docker-compose.yml run --rm backup /scripts/backup.sh >> /var/log/yemaya-backup.log 2>&1

# Weekly S3 sync on Sunday at 4 AM
0 4 * * 0 docker compose -f /path/to/docker-compose.yml run --rm backup /scripts/s3-sync.sh >> /var/log/yemaya-backup.log 2>&1
```

### Using Kubernetes CronJob

```yaml
apiVersion: batch/v1
kind: CronJob
metadata:
  name: yemaya-backup
spec:
  schedule: '0 2 * * *' # Daily at 2 AM
  jobTemplate:
    spec:
      template:
        spec:
          containers:
            - name: backup
              image: yemaya-backup:latest
              command: ['/scripts/backup.sh']
              envFrom:
                - secretRef:
                    name: yemaya-db-credentials
              volumeMounts:
                - name: backup-storage
                  mountPath: /var/backups/yemaya
          restartPolicy: OnFailure
          volumes:
            - name: backup-storage
              persistentVolumeClaim:
                claimName: yemaya-backups
```

## Recovery Procedures

### Standard Recovery

1. **Identify the backup to restore:**

   ```bash
   ./list-backups.sh
   ```

2. **Verify backup integrity:**

   ```bash
   sha256sum -c backup.dump.sha256
   ```

3. **Stop application services:**

   ```bash
   docker compose stop api
   ```

4. **Restore the database:**

   ```bash
   ./restore.sh --clean backup.dump
   ```

5. **Verify the restore:**

   ```bash
   psql -h localhost -U yemaya -d yemaya -c "SELECT COUNT(*) FROM users;"
   ```

6. **Restart application services:**
   ```bash
   docker compose start api
   ```

### Point-in-Time Recovery

For point-in-time recovery, you need PostgreSQL WAL archiving enabled:

1. **Enable WAL archiving** in `postgresql.conf`:

   ```
   wal_level = replica
   archive_mode = on
   archive_command = 'cp %p /var/backups/wal/%f'
   ```

2. **Restore base backup:**

   ```bash
   ./restore.sh backup.dump
   ```

3. **Configure recovery target** in `recovery.conf`:

   ```
   recovery_target_time = '2025-01-15 14:30:00'
   restore_command = 'cp /var/backups/wal/%f %p'
   ```

4. **Start PostgreSQL** to begin recovery

### Disaster Recovery

In case of complete data loss:

1. **Retrieve latest backup from S3:**

   ```bash
   aws s3 cp s3://yemaya-backups/database/monthly/latest.dump ./
   ```

2. **Create fresh database:**

   ```bash
   docker compose up -d postgres
   ./restore.sh --clean latest.dump
   ```

3. **Run Prisma migrations:**

   ```bash
   DATABASE_URL=$DATABASE_URL_DIRECT npx prisma migrate deploy
   ```

4. **Start all services:**
   ```bash
   docker compose up -d
   ```

## Monitoring

### Backup Health Check

```bash
# Check last backup timestamp
ls -lt /var/backups/yemaya/daily/ | head -2

# Verify backup size isn't anomalous
du -sh /var/backups/yemaya/daily/*.dump | tail -5

# Check for backup failures in logs
grep -i "error\|fail" /var/log/yemaya-backup.log
```

### Alerting Integration

Add to your monitoring system:

```yaml
# Example Prometheus alerting rule
- alert: YemayaBackupMissing
  expr:
    time() - file_modification_timestamp{path="/var/backups/yemaya/daily"} >
    86400 * 2
  for: 1h
  labels:
    severity: critical
  annotations:
    summary: 'Database backup is overdue'
```

## Security Considerations

- **Encryption**: Backups contain sensitive data; encrypt at rest and in transit
- **Access Control**: Restrict backup directory permissions
- **Credential Management**: Use environment variables or secrets, never
  hardcode
- **Network Security**: Ensure backup traffic uses encrypted connections
- **Offsite Storage**: Keep copies in geographically separate locations

## Troubleshooting

### Common Issues

**"Connection refused" during backup:**

- Verify PostgreSQL is running
- Check POSTGRES_HOST and POSTGRES_PORT
- Ensure network connectivity

**"Permission denied" errors:**

- Check BACKUP_DIR permissions
- Verify PGPASSWORD is set correctly
- Confirm database user has required privileges

**Backup file size is 0:**

- Check disk space on backup volume
- Verify database isn't empty
- Check for pg_dump errors in logs

**Restore fails with "relation already exists":**

- Use `--clean` flag to drop existing database
- Or specify `--new-db` to restore to a different database

### Getting Help

- Check container logs: `docker compose logs backup`
- Enable verbose mode: `set -x` at script start
- Test connectivity: `pg_isready -h $POSTGRES_HOST -p $POSTGRES_PORT`
