Database Backup & Restore

Protect your data with regular backups and tested restore procedures.

Quick Reference

💾

Backup All (Local)

.\scripts\backup-databases.ps1

☁️

Backup Production

gcloud sql backups create --instance=forge-postgres-prod

🔄

Restore Single Database

docker exec -i bidmanager-bids-db psql -U postgres bids_db < backups/bids-backup.sql

☁️

Restore Production

gcloud sql backups restore BACKUP_ID --backup-instance=forge-postgres-prod

Local Development Backups

Automated Backup Script

Use the provided PowerShell script to backup all databases:

# Backup all services (bids, projects, field)
.\scripts\backup-databases.ps1

# Creates timestamped files in ./backups/:
# - bids-backup-20250129-143022.sql
# - projects-backup-20250129-143022.sql
# - field-backup-20250129-143022.sql

Manual Backup (Single Database)

# Bids database
docker exec bidmanager-bids-db pg_dump -U postgres bids_db > backups/bids-backup.sql

# Projects database
docker exec bidmanager-projects-db pg_dump -U postgres projects_db > backups/projects-backup.sql

# Field database
docker exec bidmanager-field-db pg_dump -U postgres field_db > backups/field-backup.sql

tip

Add -Fc flag for compressed binary format (smaller files):

docker exec bidmanager-bids-db pg_dump -U postgres -Fc bids_db > backups/bids-backup.dump

Restore Local Database

warning

Restoring will overwrite the current database. Backup first if needed!

SQL Format
Dump Format
Fresh Start

# Stop the service first
docker-compose stop bids-backend

# Restore from SQL file
docker exec -i bidmanager-bids-db psql -U postgres bids_db < backups/bids-backup.sql

# Restart service
docker-compose start bids-backend

# Stop the service
docker-compose stop bids-backend

# Restore from dump file
docker exec -i bidmanager-bids-db pg_restore -U postgres -d bids_db -c < backups/bids-backup.dump

# Restart service
docker-compose start bids-backend

# Delete volumes and restart
docker-compose down -v
docker-compose up -d

# Restore from backup
docker exec -i bidmanager-bids-db psql -U postgres bids_db < backups/bids-backup.sql

Production Backups (Cloud SQL)

Automated Backups

Cloud SQL provides automated daily backups:

Schedule: Daily at 3:00 AM CST
Retention: 7 days
Location: us-south1 (same as instance)
Backup Window: 4-hour window

View automated backups:

gcloud sql backups list --instance=forge-postgres-prod

# Example output:
# ID            WINDOW_START           STATUS
# 1234567890    2025-01-28T09:00:00Z   SUCCESSFUL
# 1234567889    2025-01-27T09:00:00Z   SUCCESSFUL

Manual Backups

Create a backup before risky operations:

Create Backup

gcloud sql backups create --instance=forge-postgres-prod

# Example output:
# Created backup [1234567891].

Verify Backup

gcloud sql backups describe 1234567891 --instance=forge-postgres-prod

# Check status: SUCCESSFUL

Label Backup (Optional)

# Label for easy identification
gcloud sql backups update 1234567891 \
  --instance=forge-postgres-prod \
  --description="Before schema migration - 2025-01-29"

tip

Before major changes:

Database schema migrations
Bulk data operations
Version upgrades

Always create a labeled backup!

Restore Production Database

warning

⚠️ DESTRUCTIVE OPERATION

Restoring will:

Stop the instance (downtime)
Overwrite all databases
Lose data created after backup timestamp

Always verify the backup ID and timestamp before proceeding!

Identify Backup

# List available backups
gcloud sql backups list --instance=forge-postgres-prod

# Note the backup ID and timestamp

Stop Services

# Stop all Cloud Run services to prevent writes
gcloud run services update forge-bids-backend --region us-south1 --max-instances=0
gcloud run services update forge-projects-backend --region us-south1 --max-instances=0
gcloud run services update forge-field-backend --region us-south1 --max-instances=0

Restore Database

# Restore from backup ID
gcloud sql backups restore 1234567891 \
  --backup-instance=forge-postgres-prod \
  --async

# Monitor progress
gcloud sql operations list --instance=forge-postgres-prod --limit=1

Restart Services

# Re-enable Cloud Run services
gcloud run services update forge-bids-backend --region us-south1 --max-instances=10
gcloud run services update forge-projects-backend --region us-south1 --max-instances=10
gcloud run services update forge-field-backend --region us-south1 --max-instances=10

Verify Data

# Test API endpoints
curl https://bids.precisionsiteservices.com/api/health

# Test authentication
# Open incognito window and log in

Export Database (For Migration)

Export to Cloud Storage for long-term archival or migration:

# Export to GCS bucket
gcloud sql export sql forge-postgres-prod gs://forge-475221-backups/bids-db-2025-01-29.sql \
  --database=bids_db

# Export all databases
gcloud sql export sql forge-postgres-prod gs://forge-475221-backups/all-databases-2025-01-29.sql

# Export as compressed dump
gcloud sql export sql forge-postgres-prod gs://forge-475221-backups/bids-db-2025-01-29.sql.gz \
  --database=bids_db \
  --offload

info

Exports to GCS are faster and don't block the database. Use for large databases.

Import Database (From Export)

# Import from GCS bucket
gcloud sql import sql forge-postgres-prod gs://forge-475221-backups/bids-db-2025-01-29.sql \
  --database=bids_db

# Import compressed dump
gcloud sql import sql forge-postgres-prod gs://forge-475221-backups/bids-db-2025-01-29.sql.gz \
  --database=bids_db

Backup Strategy

When to Backup

Always Backup Before
Optional Backups
Production Backups

Critical Operations:

docker-compose down -v (deletes volumes)
npx prisma migrate (schema changes)
npx prisma db push (schema push)
Major refactoring
Switching branches with schema differences
Production deployments with DB changes

Retention Policy

Backup Type	Retention	Storage
Dev Daily	3 days	Local disk
Dev Milestones	Indefinite	Local disk + Git LFS
Prod Automated	7 days	Cloud SQL
Prod Manual	30 days	Cloud SQL
Prod Archival	Indefinite	Cloud Storage (Nearline)

tip

Label milestone backups clearly:

milestone-phase1-complete-20250129.sql
milestone-phase2-complete-20250301.sql
pre-production-launch-20250401.sql

Backup Validation

warning

Untested backups are not backups!

Test your restore process regularly to ensure backups are valid.

Monthly Backup Test

Create Test Environment

# Spin up test database container
docker run --name test-restore -e POSTGRES_PASSWORD=testpass -d postgres:15

Restore Backup

# Restore from backup file
docker exec -i test-restore psql -U postgres -d postgres < backups/bids-backup.sql

Verify Data

# Check row counts
docker exec test-restore psql -U postgres bids_db -c "SELECT COUNT(*) FROM \"User\";"
docker exec test-restore psql -U postgres bids_db -c "SELECT COUNT(*) FROM \"Bid\";"

Cleanup

docker stop test-restore && docker rm test-restore

Automated Validation Script

#!/bin/bash
# scripts/validate-backup.sh

BACKUP_FILE=$1
TEST_CONTAINER="backup-test-$(date +%s)"

# Start test database
docker run --name $TEST_CONTAINER -e POSTGRES_PASSWORD=test -d postgres:15

# Wait for startup
sleep 5

# Restore backup
docker exec -i $TEST_CONTAINER psql -U postgres -d postgres < $BACKUP_FILE

# Validate
TABLES=$(docker exec $TEST_CONTAINER psql -U postgres -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='public';")

if [ $TABLES -gt 0 ]; then
  echo "✅ Backup valid: $TABLES tables restored"
  docker stop $TEST_CONTAINER && docker rm $TEST_CONTAINER
  exit 0
else
  echo "❌ Backup invalid: No tables found"
  docker stop $TEST_CONTAINER && docker rm $TEST_CONTAINER
  exit 1
fi

Disaster Recovery

Recovery Time Objective (RTO)

Target time to restore service after failure:

Development: 30 minutes (restore from local backup)
Production: 1 hour (restore from Cloud SQL backup)

Recovery Point Objective (RPO)

Maximum acceptable data loss:

Development: 1 day (daily backups)
Production: 1 day (automated backups)

Disaster Scenarios

🗑️Accidental Data Deletion

Action:

Identify when deletion occurred
Find backup before deletion
Restore specific tables/rows (not full database)

Time: 15-30 minutes

⚠️Database Corruption

Action:

Stop all services immediately
Create backup of corrupted state (for forensics)
Restore from most recent valid backup
Replay transactions if needed

Time: 1-2 hours

🗄️Schema Migration Failure

Action:

Rollback migration if possible
If rollback fails, restore from pre-migration backup
Fix migration script
Re-run migration

Time: 30-60 minutes

☁️Cloud SQL Instance Failure

Action:

GCP automatically fails over to standby (High Availability)
If HA not enabled, create new instance from backup
Update DNS/connection strings

Time: 30 minutes (with HA) or 2-4 hours (without HA)

Backup Security

warning

Backups contain sensitive data!

Never commit backups to Git
Encrypt backups at rest and in transit
Restrict access to backup files
Rotate backup encryption keys

Encryption

Local Backups:

# Encrypt with GPG
gpg --symmetric --cipher-algo AES256 backups/bids-backup.sql

# Decrypt
gpg --decrypt backups/bids-backup.sql.gpg > backups/bids-backup.sql

Cloud SQL Backups:

Automatically encrypted at rest
Encrypted in transit (TLS)
Managed by Google

Access Control

Cloud SQL Backups:

# Grant backup restore permission
gcloud projects add-iam-policy-binding forge-475221 \
  --member='user:admin@precisionsiteservices.com' \
  --role='roles/cloudsql.admin'

Cloud Storage Exports:

# Restrict bucket access
gcloud storage buckets add-iam-policy-binding gs://forge-475221-backups \
  --member='user:admin@precisionsiteservices.com' \
  --role='roles/storage.objectViewer'

Next Steps

📈

Monitoring

Set up alerts for backup failures

🔀

Database Migrations

Learn Prisma migration workflow

☁️

GCP Setup

Configure Cloud SQL and backups

🔑

Environment Variables

Configure database connection strings

Quick Reference​

Backup All (Local)

Backup Production

Restore Single Database

Restore Production

Local Development Backups​

Automated Backup Script​

Manual Backup (Single Database)​

Restore Local Database​

Production Backups (Cloud SQL)​

Automated Backups​

Manual Backups​

Restore Production Database​

Export Database (For Migration)​

Import Database (From Export)​

Backup Strategy​

When to Backup​

Retention Policy​

Backup Validation​

Monthly Backup Test​

Automated Validation Script​

Disaster Recovery​

Recovery Time Objective (RTO)​

Recovery Point Objective (RPO)​

Disaster Scenarios​

Backup Security​

Encryption​

Access Control​

Next Steps​

Monitoring

Database Migrations

GCP Setup

Environment Variables

Quick Reference

Local Development Backups

Automated Backup Script

Manual Backup (Single Database)

Restore Local Database

Production Backups (Cloud SQL)

Automated Backups

Manual Backups

Restore Production Database

Export Database (For Migration)

Import Database (From Export)

Backup Strategy

When to Backup

Retention Policy

Backup Validation

Monthly Backup Test

Automated Validation Script

Disaster Recovery

Recovery Time Objective (RTO)

Recovery Point Objective (RPO)

Disaster Scenarios

Backup Security

Encryption

Access Control

Next Steps