Backup Recovery Specialist

Create full database backup:

# Custom format (compressed, recommended)
pg_dump -Fc \
  -h db.your-project.supabase.co \
  -U postgres \
  -d postgres \
  -f backup_$(date +%Y%m%d_%H%M%S).dump

# Plain SQL format (human-readable)
pg_dump -h db.your-project.supabase.co \
  -U postgres \
  -d postgres \
  -f backup.sql

Backup specific tables:

# Backup only specific tables
pg_dump -Fc \
  -h db.your-project.supabase.co \
  -U postgres \
  -d postgres \
  -t documents -t users \
  -f partial_backup.dump

Schema only (no data):

# Backup schema structure only
pg_dump --schema-only \
  -h db.your-project.supabase.co \
  -U postgres \
  -d postgres \
  -f schema_only.sql

3. Automate Backup Schedule

☐ Create backup script ☐ Schedule with cron or task scheduler ☐ Rotate old backups ☐ Monitor backup success

Backup script (backup.sh):

#!/bin/bash
set -e

# Configuration
DB_HOST="db.your-project.supabase.co"
DB_USER="postgres"
DB_NAME="postgres"
BACKUP_DIR="/backups"
RETENTION_DAYS=30

# Create backup
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/backup_$TIMESTAMP.dump"

pg_dump -Fc -h $DB_HOST -U $DB_USER -d $DB_NAME -f $BACKUP_FILE

# Compress
gzip $BACKUP_FILE

# Delete old backups
find $BACKUP_DIR -name "backup_*.dump.gz" -mtime +$RETENTION_DAYS -delete

# Upload to cloud storage (optional)
# aws s3 cp $BACKUP_FILE.gz s3://my-backups/

Schedule with cron (daily at 2 AM):

# Edit crontab
crontab -e

# Add line:
0 2 * * * /path/to/backup.sh >> /var/log/backup.log 2>&1

4. Restore from Backup

☐ Identify backup file to restore ☐ Create new database or clear existing ☐ Restore with pg_restore ☐ Verify data restoration

Restore custom format backup:

# Restore to new database
pg_restore -Fc \
  -h db.your-project.supabase.co \
  -U postgres \
  -d postgres \
  --clean --if-exists \
  backup.dump

Restore plain SQL backup:

# Restore SQL file
psql -h db.your-project.supabase.co \
  -U postgres \
  -d postgres \
  -f backup.sql

Restore specific tables:

# Restore only certain tables
pg_restore -Fc \
  -h db.your-project.supabase.co \
  -U postgres \
  -d postgres \
  -t documents -t users \
  backup.dump

5. Use Point-in-Time Recovery (Supabase Pro)

☐ Identify timestamp to restore to ☐ Use Supabase dashboard PITR feature ☐ Create new project from specific time ☐ Verify restored data

PITR via Supabase Dashboard:

1. Go to project settings
2. Select "Backups" tab
3. Choose "Point in Time Recovery"
4. Select date/time to restore to
5. Creates new project with restored state

6. Test Restore Procedure

☐ Create test database ☐ Restore backup to test database ☐ Verify data completeness ☐ Document restore time

Test restore locally:

# Create test database
createdb test_restore

# Restore backup
pg_restore -Fc -d test_restore backup.dump

# Verify row counts
psql -d test_restore -c "SELECT COUNT(*) FROM documents;"

# Cleanup
dropdb test_restore

7. Handle Partial Data Recovery

☐ Export specific data with queries ☐ Use COPY or pg_dump with WHERE clause ☐ Restore to temporary table ☐ Merge into production

Export specific data:

-- Copy data to CSV
COPY (
  SELECT * FROM documents
  WHERE created_at > '2023-01-01'
) TO '/tmp/recent_documents.csv' CSV HEADER;

Import specific data:

-- Import CSV to table
COPY documents FROM '/tmp/recent_documents.csv' CSV HEADER;

8. Implement Disaster Recovery Plan

☐ Document backup locations ☐ Test restore procedures quarterly ☐ Assign recovery responsibilities ☐ Set RTO/RPO targets

Recovery Time Objective (RTO):

• How quickly must database be restored?
• Target: < 1 hour for production

Recovery Point Objective (RPO):

• How much data loss is acceptable?
• Target: < 24 hours (daily backups)

Common Backup Strategies

3-2-1 Backup Rule

• 3 copies of data
• 2 different storage types
• 1 offsite copy

Example:

1. Production database (primary)
2. Daily backup on same cloud (Supabase)
3. Copy to S3/GCS (different region)

Incremental Backups

# Full backup weekly
pg_dump -Fc -f full_backup_$(date +%Y%m%d).dump

# Incremental (changes only) - requires WAL archiving
# Use Supabase PITR for this

Multi-Environment Backups

• Production: Daily backups, 30-day retention
• Staging: Weekly backups, 7-day retention
• Development: On-demand backups only

Supabase Backup Features

Automatic Daily Backups

• Enabled by default
• Retained based on plan (7-30 days)
• Accessible in dashboard
• One-click restore

Point-in-Time Recovery (Pro+)

• Restore to any point in last 7-30 days
• Second-level precision
• Creates new project (safe)

Manual Backups

• Use pg_dump for control
• Export via Supabase CLI
• Store externally for long-term retention

Recovery Scenarios

Accidental Table Drop

-- If caught immediately, use PITR
-- Or restore from most recent backup

-- If partial, restore to temp database
pg_restore -Fc -d temp_db backup.dump

-- Copy data back
INSERT INTO production.documents
SELECT * FROM temp_db.documents;

Accidental Data Deletion

-- Restore to temp table
CREATE TEMP TABLE documents_backup AS
SELECT * FROM documents WHERE false;

-- Load from backup
-- (restore to temp database, then copy)

-- Merge missing rows
INSERT INTO documents
SELECT * FROM temp_db.documents
WHERE id NOT IN (SELECT id FROM documents);

Corruption or Rollback Needed

• Use PITR to restore to time before corruption
• Create parallel database for comparison
• Migrate users to restored database

Red Flags

Never:

• Run production without backups
• Store backups only in same location as database
• Skip testing restore procedures
• Delete backups without verification
• Restore to production without testing first

Always:

• Test backups regularly (quarterly minimum)
• Store backups in multiple locations
• Document restore procedures
• Automate backup schedule
• Monitor backup success/failure
• Encrypt backups containing sensitive data
• Version backup scripts in git

Supabase Specific:

• Verify backups exist in dashboard
• Understand backup retention for your plan
• Use PITR for Pro+ plans (worth it)
• Export long-term backups externally
• Test restore to new project, not production
• Consider read replicas for HA (not backup replacement)

Backup Verification Checklist

Weekly:

• ☐ Verify latest backup exists
• ☐ Check backup file size (not empty)
• ☐ Review backup logs for errors

Monthly:

• ☐ Test restore to staging environment
• ☐ Verify data completeness
• ☐ Document restore time
• ☐ Update recovery procedures

Quarterly:

• ☐ Full disaster recovery drill
• ☐ Restore to production-like environment
• ☐ Verify application works with restored data
• ☐ Update RTO/RPO estimates