Running Production PostgreSQL on Cloud SQL: HA, DR, Backups, Maintenance, and Upgrades
A regional Cloud SQL instance, a cross-region replica, and point-in-time recovery solve three different incidents. Calling all three “backup” produces a recovery plan that fails exactly when the distinction matters.
Situation
A production database must withstand at least four classes of event: a zonal infrastructure failure, a regional outage, accidental or malicious data change, and a planned engine or platform change. The desired RPO and RTO are business requirements; Cloud SQL features do not convert them into guarantees automatically.
The operating design must state which feature covers each event, who initiates recovery, which endpoint changes, and what the application observes.
The Problem
Regional HA is not regional disaster recovery. A read replica is not a backup because logical corruption can replicate. An automated backup is not an immediate rollback because restore creates another instance. Maintenance availability is not guaranteed merely because HA is enabled. Major-version upgrades have prechecks, downtime, and rollback constraints.
The core question is: how should these mechanisms be composed and tested so the measured recovery behavior satisfies the workload’s objectives?
The Recovery Architecture
flowchart TD
App[Application] --> Primary[Regional HA primary — region A]
Primary --> Standby[HA standby — another zone]
Primary --> DR[Cross-region read replica — region B]
Primary --> Backup[Automated and on-demand backups]
Primary --> Logs[Transaction logs for PITR]
Backup --> Restore[New restored instance]
Logs --> Restore
DR --> Promote[Promoted DR primary]
| Event | Primary mechanism | Important boundary |
|---|---|---|
| Zonal failure | Regional HA failover | Sessions close; clients reconnect to the same instance endpoint. |
| Regional outage | Promote a cross-region replica or restore elsewhere | Replication is asynchronous; endpoint and connection name change after promotion or restore. |
Bad deployment or DROP TABLE | PITR to a new instance | Recovery is not an in-place rewind; reconcile good data and redirect clients deliberately. |
| Media or administrative recovery | Automated or on-demand backup restore | A successful backup is not evidence that the application can be restored inside its RTO. |
Google documents regional HA as synchronous disk replication between zones. It also documents cross-region replicas as asynchronous. That means “zero RPO” must not be promised for regional recovery: observed lag at the failure boundary defines potential loss. For zonal HA, test the service behavior and application retry path rather than converting a typical failover duration into a contractual number.
In Practice
Make recovery a workflow, not a checkbox
PITR depends on automated backups and transaction-log retention. A restore creates a new Cloud SQL instance. The runbook must therefore include validation, credentials and IAM, networking, connection-string changes, and a decision about reconciling writes accepted after the chosen restore time.
Cross-region promotion has a similar control-plane consequence. Promotion makes the replica independent and writable; applications do not automatically discover it as the original primary. Track replica lag, establish an authority for promotion, and prevent the old primary from accepting writes during failover or failback.
Record recovery evidence in every drill: the last confirmed source transaction, the first confirmed target transaction, DNS or configuration changes, validation queries, and the incident commander’s promotion decision. That produces an auditable RPO and RTO instead of an estimate inferred from a dashboard.
Treat maintenance as a client-resilience test
Cloud SQL maintenance behavior differs by edition and configuration. Current documentation says Enterprise instances lose connectivity for less than 30 seconds on average during maintenance, while Enterprise Plus supports near-zero-downtime planned maintenance under documented prerequisites and exclusions. Those figures are service descriptions, not universal results. High activity, read replicas, connectors, DDL, and client timeout configuration can change observed impact.
Use bounded exponential backoff with jitter, fail fast on connection establishment, and make transactions idempotent where they can be retried. A pool that holds dead sockets indefinitely can turn a brief database event into a long application outage.
Upgrade with a reversible decision point
Cloud SQL supports in-place major-version upgrades for supported source and target versions. Run the service precheck and review extensions, flags, application drivers, and removed PostgreSQL behavior. Test against a clone or restored copy with production-like data. Record the documented rollback procedure and its eligibility constraints before starting; never describe the operation as “a few minutes” without measuring the actual database.
Where It Breaks
| Failure mode | Why the design fails | Control |
|---|---|---|
| Backup exists but restore misses RTO | Restore, validation, networking, and cutover were never timed | Run scheduled restore drills and measure the whole application recovery. |
| DR replica loses acknowledged writes | Asynchronous lag existed at promotion | Monitor lag, define acceptable RPO, and quiesce the primary when possible. |
| Split authority after regional recovery | Old and promoted instances both receive writes | Use explicit fencing, traffic control, and a single incident commander. |
| Replica disk pressure or lag | Long queries and write volume delay replay | Monitor lag and disk; isolate analytics or cancel queries according to a stated policy. |
| Upgrade rollback is assumed | Rollback rules were not reviewed before the change | Keep a tested logical or service-level fallback and explicit go or no-go checkpoints. |
What to Do Next
- Problem: “HA enabled” is being used as a substitute for a complete recovery design.
- Solution: Map every incident class to HA, replica promotion, PITR, or backup restore and document endpoint and data-loss implications.
- Proof: Google documents different replication, restore, and client behavior for each mechanism.
- Action: Execute four game days: manual HA failover, cross-region promotion, PITR recovery, and a major-version rehearsal on a nonproduction copy.