Backup & Restore Runbook

Synced from the TurfAI source on 2026-06-21.

CASA T-2 Item: 14.1.4

Overview

TurfAI uses Google Cloud managed services that provide built-in backup capabilities. This runbook documents how to configure, verify, and restore from backups.

Cloud SQL (PostgreSQL)

Enable Automated Backups

# Enable daily automated backups with point-in-time recovery
gcloud sql instances patch turfai-db \
  --backup-start-time=02:00 \
  --enable-bin-log \
  --enable-point-in-time-recovery \
  --retained-backups-count=14 \
  --project=YOUR_PROJECT_ID

Verify Backup Configuration

# Check backup settings
gcloud sql instances describe turfai-db \
  --format="yaml(settings.backupConfiguration)" \
  --project=YOUR_PROJECT_ID

# List recent backups
gcloud sql backups list \
  --instance=turfai-db \
  --project=YOUR_PROJECT_ID

Restore from Backup

Option A: Restore to a new instance (recommended — non-destructive)

# List available backups
gcloud sql backups list --instance=turfai-db

# Restore to a new instance
gcloud sql instances restore-backup turfai-db-restored \
  --restore-instance=turfai-db \
  --backup-id=BACKUP_ID \
  --project=YOUR_PROJECT_ID

# Verify data in restored instance
# Then swap connection strings in deploy config if validated

Option B: Point-in-time recovery

# Restore to a specific timestamp (UTC)
gcloud sql instances clone turfai-db turfai-db-pitr \
  --point-in-time="2026-03-20T10:30:00Z" \
  --project=YOUR_PROJECT_ID

Option C: Restore to same instance (destructive — last resort)

gcloud sql instances restore-backup turfai-db \
  --backup-id=BACKUP_ID \
  --project=YOUR_PROJECT_ID

# WARNING: This overwrites the current database

Manual Backup (before major changes)

gcloud sql backups create \
  --instance=turfai-db \
  --description="Pre-migration backup $(date +%Y%m%d)" \
  --project=YOUR_PROJECT_ID

Redis (Memorystore)

Snapshot Policy

# Enable RDB snapshots (persistence)
gcloud redis instances update turfai-redis \
  --region=us-central1 \
  --rdb-snapshot-period=12h \
  --rdb-snapshot-start-time="2026-03-20T00:00:00Z" \
  --project=YOUR_PROJECT_ID

Export Snapshot

# Export Redis data to GCS
gcloud redis instances export gs://turfai-backups/redis/snapshot-$(date +%Y%m%d).rdb \
  --instance=turfai-redis \
  --region=us-central1 \
  --project=YOUR_PROJECT_ID

Import/Restore

# Import from GCS snapshot
gcloud redis instances import gs://turfai-backups/redis/snapshot-20260320.rdb \
  --instance=turfai-redis \
  --region=us-central1 \
  --project=YOUR_PROJECT_ID

Notes on Redis Data

Redis stores ephemeral data that can be regenerated:

Rate limiting counters (auto-repopulate)
JWT blacklist entries (expire naturally)
Step-up authentication flags (expire in 5 min)

A Redis data loss does not require restoration in most cases. New rate limit windows and blacklist entries will be created naturally.

Google Cloud Storage (GCS)

Enable Object Versioning

# Enable versioning on the documents bucket
gcloud storage buckets update gs://turfai-documents \
  --versioning \
  --project=YOUR_PROJECT_ID

# Verify
gcloud storage buckets describe gs://turfai-documents \
  --format="yaml(versioning)"

Set Lifecycle Policy (cleanup old versions)

# Create lifecycle config
cat > /tmp/lifecycle.json << 'EOF'
{
  "lifecycle": {
    "rule": [
      {
        "action": {"type": "Delete"},
        "condition": {
          "numNewerVersions": 3,
          "isLive": false
        }
      }
    ]
  }
}
EOF

gcloud storage buckets update gs://turfai-documents \
  --lifecycle-file=/tmp/lifecycle.json

Restore Deleted Object

# List object versions
gcloud storage ls -la gs://turfai-documents/path/to/file

# Copy specific version to restore
gcloud storage cp gs://turfai-documents/path/to/file#VERSION gs://turfai-documents/path/to/file

GCP Secret Manager

Secrets are versioned by default

# List all secret versions
gcloud secrets versions list turfai-jwt-secret

# Access a specific version
gcloud secrets versions access VERSION_NUMBER --secret=turfai-jwt-secret

Secrets should be backed up to a secure offline location (e.g., encrypted USB) as a last resort.

Recovery Time Objectives

Component	RTO	RPO	Backup Method
Cloud SQL	1 hour	5 minutes (PITR)	Automated daily + PITR
Redis	15 minutes	12 hours (snapshot)	RDB snapshots (data is ephemeral)
GCS	Immediate	Immediate (versioning)	Object versioning
Secrets	Immediate	Immediate (versioned)	Secret Manager versioning

Testing Procedure

Perform quarterly:

Cloud SQL: Clone instance via PITR, verify data integrity with SELECT count(*) FROM workflows
GCS: Delete a test file, restore from version history
Redis: Export snapshot, import to test instance, verify key count
Document results: record test date, outcome, and any issues

Last updated: 2026-03-20 Next backup test: 2026-06-20 (quarterly)

On this page