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#
# 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#
# 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:
./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:
# 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:
./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:
# 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:
# 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#
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#
-
Identify the backup to restore:
bash./list-backups.sh -
Verify backup integrity:
bashsha256sum -c backup.dump.sha256 -
Stop application services:
bashdocker compose stop api -
Restore the database:
bash./restore.sh --clean backup.dump -
Verify the restore:
bashpsql -h localhost -U yemaya -d yemaya -c "SELECT COUNT(*) FROM users;" -
Restart application services:
bashdocker compose start api
Point-in-Time Recovery#
For point-in-time recovery, you need PostgreSQL WAL archiving enabled:
-
Enable WAL archiving in
postgresql.conf:textwal_level = replica archive_mode = on archive_command = 'cp %p /var/backups/wal/%f' -
Restore base backup:
bash./restore.sh backup.dump -
Configure recovery target in
recovery.conf:textrecovery_target_time = '2025-01-15 14:30:00' restore_command = 'cp /var/backups/wal/%f %p' -
Start PostgreSQL to begin recovery
Disaster Recovery#
In case of complete data loss:
-
Retrieve latest backup from S3:
bashaws s3 cp s3://yemaya-backups/database/monthly/latest.dump ./ -
Create fresh database:
bashdocker compose up -d postgres ./restore.sh --clean latest.dump -
Run Prisma migrations:
bashDATABASE_URL=$DATABASE_URL_DIRECT npx prisma migrate deploy -
Start all services:
bashdocker compose up -d
Monitoring#
Backup Health Check#
# 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:
# 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
--cleanflag to drop existing database - Or specify
--new-dbto restore to a different database
Getting Help#
- Check container logs:
docker compose logs backup - Enable verbose mode:
set -xat script start - Test connectivity:
pg_isready -h $POSTGRES_HOST -p $POSTGRES_PORT