Cloud SQL Lifecycle, Maintenance & Disaster Recovery Runbook

1. Google Cloud SQL Lifecycle & Maintenance Architecture

The GERCIA Platform persistence tier runs on managed Google Cloud SQL for PostgreSQL in the southamerica-east1 region (São Paulo, Brazil). Unlike unmanaged on-premises or bare-metal installations, routine operating system patches, binary engine updates, hardware failure recoveries, and physical disk expansions are handled transparently by the Google Cloud managed plane.

Operational Responsibilities Matrix

Operational Dimension	Managed by Google Cloud SQL	Managed by GERCIA Engineering (SRE)
OS & Security Patching	Automatic live kernel updates and OS vulnerabilities remediation	Configuration of weekly maintenance windows and notification channels
Regional High Availability (HA)	Synchronous block-level replication and automated zero-touch failover ($< 60$s RTO)	Health check verification and synthetic connection probe monitoring
Continuous Backups & PITR	Automated daily transaction log (WAL) archiving to Cloud Storage	Recovery verification drills and retention policy configuration
Database Engine Tuning	Dynamic disk expansion and managed memory baselines	PostgreSQL database flags optimization (`work_mem`, `autovacuum`, etc.)
Schema & EF Core Migrations	None (Neutral persistence layer)	Linear ticket-driven migrations executed via `Gercia.Server --migrate` (Cloud Run Job)

2. Managed Maintenance Windows & Zero-Downtime Governance

To prevent maintenance disruptions during peak clinic hours, all Cloud SQL instances enforce strict Maintenance Windows:

flowchart LR
    subgraph SCHEDULE ["Scheduled Maintenance (Google Cloud)"]
        NOTIFY["1. Advance Notification<br>(GCP Alert & devsecops@gercia.com.br)"]
        WINDOW["2. Off-Peak Maintenance Window<br>(Sundays 03:00 - 04:00 BRT)"]
        FAILOVER["3. Switchover to Standby Replica<br>(Zero-Loss Failover < 60s)"]
        COMPLETE["4. Primary Updated & Synced"]
    end

    NOTIFY --> WINDOW --> FAILOVER --> COMPLETE

Maintenance Window Configuration

Designated Window: Sundays from 03:00 to 04:00 BRT (UTC-3), representing the lowest clinical quotation and telemedicine consultation volume.
Notification Lead Time: Configured with a 1-week advance notice via Google Cloud Monitoring alerts sent to devsecops@gercia.com.br.
Maintenance Denial Periods: Activated during critical regulatory audit cycles or high-volume healthcare events (e.g., end-of-year prescription revalidations).

# Configure maintenance window for Sunday at 03:00 BRT (06:00 UTC)
gcloud sql instances patch gercia-db-prd \
    --project=gercia-gcp \
    --maintenance-window-day=SUN \
    --maintenance-window-hour=6 \
    --maintenance-release-channel=preview

3. Point-in-Time Recovery (PITR) & Disaster Recovery Runbook

Cloud SQL provides continuous transaction log archiving (WAL) enabling recovery to any specific second within the retention window (7 to 35 days).

A. Point-in-Time Recovery Execution Procedure

Whenever an accidental data truncation or catastrophic application bug occurs:

Identify Target Timestamp: Determine the exact UTC timestamp immediately preceding the incident:
Terminal window
```
TARGET_TIME="2026-10-10T14:30:00.000Z"
```

Provision Target Recovery Instance: Execute PITR into a new isolated instance to avoid destructive in-place overwrites:

gcloud sql instances clone gercia-db-prd gercia-db-recovery \
    --project=gercia-gcp \
    --point-in-time=$TARGET_TIME

Verify Data Integrity: Connect via private VPC peering, execute read-only validation queries against clinical and pharmacy schemas.
Traffic Cutover: Update CONNECTION_STRING_SERVER_PRD in Google Secret Manager to direct application traffic to the restored instance.

sequenceDiagram
    autonumber
    actor SRE as SRE / Lead Engineer
    participant GCP as Google Cloud SQL API
    participant WAL as Cloud Storage WAL Archive
    participant GSM as Google Secret Manager
    participant App as Gercia.Server (Cloud Run)

    SRE->>GCP: Clone instance with --point-in-time ($TARGET_TIME)
    GCP->>WAL: Replay base snapshot + continuous WAL logs
    GCP-->>SRE: Instance 'gercia-db-recovery' ready (Private IP)
    SRE->>SRE: Validate data integrity & tenant balances
    SRE->>GSM: Update CONNECTION_STRING_SERVER_PRD
    App->>App: Reactive password refresh picks up new target
    App-->>SRE: Live traffic restored with zero data loss

4. Database Flags Governance & Performance Baseline

PostgreSQL flags on Google Cloud SQL are managed declaratively to ensure compatibility with multi-tenant Row-Level Security (RLS) and high-concurrency Minimal APIs:

Flag Name	Recommended Value	Scope & Rationale
`cloudsql.enable_pgaudit`	`on`	Enables military-grade audit logging for all DDL, ROLE, and administrative SQL statements.
`log_min_duration_statement`	`250` (ms)	Logs all queries exceeding 250ms to Cloud Logging for Query Insights correlation.
`autovacuum_vacuum_cost_limit`	`2000`	Accelerates autovacuum throughput on high-write compounding tables (`pharmacy.order_status_history`).
`work_mem`	`16MB`	Allocated per sort/hash operation to prevent disk spill during clinical reporting queries.
`temp_file_limit`	`2147483648` (2 GB)	Hard cap on temporary scratch files per backend to protect shared memory.

# Apply standardized database flags via gcloud
gcloud sql instances patch gercia-db-prd \
    --project=gercia-gcp \
    --database-flags=cloudsql.enable_pgaudit=on,log_min_duration_statement=250,autovacuum_vacuum_cost_limit=2000,work_mem=16384

5. Major Engine Version Upgrades

When upgrading between major PostgreSQL releases (e.g., PostgreSQL 18 to PostgreSQL 19):

Pre-Upgrade Validation: Create a read-only clone of the production instance using gcloud sql instances clone and test the target version upgrade in staging.

Execute In-Place Major Upgrade:

gcloud sql instances patch gercia-db-prd \
    --project=gercia-gcp \
    --database-version=POSTGRES_19

Automated Verification: Google Cloud SQL performs pre-upgrade checks (pg_upgrade --check), provisions the new binary, updates the catalog, and verifies checksums. In the event of a validation error, the instance automatically rolls back to the previous version with zero data corruption.