Eve library

Yemaya Database Backup & Recovery

Other

Revision 1

On this page

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:

    text
    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:

    text
    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