Skip to main content

Cloud SQL Quick Start Guide

⚠️ DEPRECATED: This document is archived and refers to the old flowpos-db instances.
Current Architecture: Database instances have been consolidated. The application now uses:

  • Staging: metabase-db-staging (hosts both flowpos_staging and metabase databases)
  • Production: metabase-db-production (hosts both flowpos_production and metabase databases)

See deploy/gcp/cloud-sql/README.md for current setup instructions.

Configuration: Enterprise Plus Edition (Development Tier)

  • 4 vCPU, 16 GB RAM, 250 GB SSD
  • 35-day point-in-time recovery
  • 99.99% availability SLA
  • Sub-second maintenance downtime
  • Estimated cost: ~$200-300/month

Quick Setup (Using Script)​

  1. Set environment variables:

    export GCP_PROJECT_ID="your-project-id"
    export GCP_REGION="us-central1"
    export STAGING_DB_PASSWORD="your-secure-staging-password"
    export PRODUCTION_DB_PASSWORD="your-secure-production-password"

  2. Run the setup script:

    cd deploy/gcp
    ./setup-cloud-sql.sh

  3. Grant database permissions:

    # Install Cloud SQL Proxy if needed
    # https://cloud.google.com/sql/docs/postgres/connect-instance-auth-proxy

    # Start Cloud SQL Proxy
    cloud_sql_proxy -instances=$GCP_PROJECT_ID:$GCP_REGION:flowpos-db=tcp:5432 &

    # Connect and run permissions script
    psql -h 127.0.0.1 -U postgres -d postgres -f grant-database-permissions.sql

  4. Save credentials:

    • The script will create cloud-sql-credentials.txt
    • IMPORTANT: Save these credentials securely and delete the file!

  5. Add to GitHub Secrets:

    • Go to your GitHub repo → Settings → Secrets and variables → Actions
    • Add DATABASE_URL secret for staging environment
    • Add DATABASE_URL secret for production environment
    • Use the connection strings from the credentials file

Manual Setup (Using Console)​

See setup-cloud-sql.md for detailed step-by-step instructions.

Connection Strings Format​

postgresql://USERNAME:PASSWORD@PRIVATE_IP:5432/DATABASE_NAME

Example:

postgresql://flowpos_staging:password123@10.1.2.3:5432/flowpos_staging

Next Steps​

  1. ✅ Instance created
  2. ✅ Databases created
  3. ✅ Users created
  4. ✅ Permissions granted
  5. ⏭️ Update GitHub Secrets
  6. ⏭️ Update deployment workflows
  7. ⏭️ Run migrations

Troubleshooting​

Can't connect from Cloud Run?​

  1. Ensure VPC connector is configured:

    gcloud compute networks vpc-access connectors describe cloudrun-vpc-connector \
      --region=us-central1

  2. Check Cloud Run service has --vpc-connector flag:

    gcloud run services describe flowpos-backend \
      --region=us-central1 \
      --format="value(spec.template.spec.containers[0].env)"

  3. Verify private IP is accessible from VPC

Permission denied errors?​

Run the grant-database-permissions.sql script again.

Need to reset passwords?​

gcloud sql users set-password flowpos_staging \
  --instance=flowpos-db \
  --password=NEW_PASSWORD

gcloud sql users set-password flowpos_production \
  --instance=flowpos-db \
  --password=NEW_PASSWORD