Cloud SQL for PostgreSQL in Production: Private IP, HA, Backups & Terraform | 2026

What Is Cloud SQL for PostgreSQL?

Cloud SQL for PostgreSQL is Google Cloud's fully managed relational database service. Google operates the underlying compute, storage, replication, patching, and backups; you consume a PostgreSQL endpoint (private or public IP) and pay per vCPU, memory, storage, and network. In 2026 the service ships in two editions: Enterprise (the standard tier, 99.95% HA SLA) and Enterprise Plus (larger instance shapes, up to 128 vCPUs, data cache, and a 99.99% HA SLA). Production deployments attach the instance to your VPC with a private IP through private services access, so traffic never traverses the public internet.

Think of Cloud SQL like renting a managed apartment in a guarded building instead of building your own house: you bring your furniture (schemas and data), the building staff handles plumbing, power, and security (patches, failover, backups), and the private entrance (private IP) means only residents of your VPC can reach your door.

Why Run Cloud SQL for PostgreSQL with Private IP and HA in Production?

The default quickstart path — a public IP, ZONAL instance, backups left at defaults, and a password typed into the console — is a production incident waiting to happen. A public endpoint exposes PostgreSQL to the internet, a zonal instance dies with its zone (RPO/RTO unbounded), a missing maintenance window means restarts during business hours, and terraform destroy without deletion protection can erase the database and its backups in one command.

This architecture provisions PostgreSQL 16 with Terraform: a private-only endpoint over private services access (see the sibling guide on [Shared VPC with Terraform](/tutorial/shared-vpc-terraform) for the network layout), `availability_type = REGIONAL` for automatic zonal failover, PITR with 7 days of WAL archiving, a cross-region read replica, pinned maintenance windows, and `deletion_protection = true`. The same Terraform discipline extends to the app tier — pair it with [Cloud Armor WAF Rate Limiting & Bot Defense](/tutorial/cloud-armor-waf-rate-limiting-bot-defense) at the edge, [GKE Autopilot Production Checklist](/tutorial/gke-autopilot-production-checklist-pdb-hpa-vpa-spot-cost) for the compute tier, and [Terraform Module Structure & Versioning with GitHub Actions](/tutorial/terraform-module-structure-versioning-github-actions) to ship database changes through CI.

Prerequisites

• GCP project with active billing and a VPC network (or the default network)
• Terraform v1.6+ and the google provider ~> 5.x/6.x installed
• gcloud CLI v480.0+ installed and authenticated (gcloud auth application-default login)
• IAM roles: Cloud SQL Admin, Compute Network Admin, Service Networking Admin (or Owner in a sandbox)
• A GCE VM or Cloud SQL Auth Proxy client inside the VPC for private-IP connectivity tests

Step-by-Step Guide

Step 1: Configure the Terraform Provider and Enable APIs

Pin the google provider and enable the Cloud SQL Admin, Compute Engine, and Service Networking APIs as Terraform resources. Service Networking API activation is a hard prerequisite for private services access; doing it in Terraform keeps the project fully reproducible.

terraform {
  required_version = ">= 1.6.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 5.45"
    }
  }
}

provider "google" {
  project = var.project_id
  region  = "europe-west1"
}

resource "google_project_service" "apis" {
  for_each = toset([
    "sqladmin.googleapis.com",
    "compute.googleapis.com",
    "servicenetworking.googleapis.com",
  ])
  service            = each.key
  disable_on_destroy = false
}

Step 2: Allocate a Private IP Range and Create Private Services Access

Reserve an internal /16 range for Google-managed services and peer the Service Networking producer into your VPC. Without this peering, Cloud SQL cannot assign a private IP — google_sql_database_instance creation will fail with a network dependency error.

resource "google_compute_global_address" "private_ip_range" {
  name          = "google-managed-services-range"
  purpose       = "VPC_PEERING"
  address_type  = "INTERNAL"
  prefix_length = 16
  network       = var.network_id
}

resource "google_service_networking_connection" "private_vpc_connection" {
  network                 = var.network_id
  service                 = "servicenetworking.googleapis.com"
  reserved_peering_ranges = [google_compute_global_address.private_ip_range.name]
  depends_on              = [google_project_service.apis]
}

Step 3: Provision the HA PostgreSQL 16 Instance with Private IP

Create the google_sql_database_instance with PostgreSQL 16, a dedicated-CPU tier, REGIONAL availability, and a private-only network configuration. availability_type = "REGIONAL" creates a synchronous standby in a second zone with automatic failover and the 99.95%/99.99% SLA; ipv4_enabled = false guarantees no public endpoint exists.

resource "google_sql_database_instance" "postgres" {
  name             = "prod-postgres-16"
  database_version = "POSTGRES_16"
  region           = "europe-west1"

  # Enterprise Plus alternative:
  # edition = "ENTERPRISE_PLUS" + tier = "db-perf-optimized-4"
  settings {
    edition           = "ENTERPRISE"
    tier              = "db-custom-4-16384" # 4 dedicated vCPU / 16 GB
    availability_type = "REGIONAL"          # HA: synchronous standby in another zone
    disk_type         = "PD_SSD"
    disk_size         = 100
    disk_autoresize   = true

    ip_configuration {
      ipv4_enabled    = false # private IP only
      private_network = var.network_id
    }

    user_labels = {
      environment = "production"
      team        = "platform"
    }
  }

  deletion_protection = true

  depends_on = [google_service_networking_connection.private_vpc_connection]
}

Step 4: Enable Automated Backups and Point-in-Time Recovery (PITR)

Add a backup_configuration block with daily backups, WAL archiving, and explicit retention settings to the instance. PITR (point_in_time_recovery_enabled) archives write-ahead logs so you can restore to any second in the window — the difference between losing a day of data and losing nothing after a bad migration.

# Add inside settings {} of google_sql_database_instance.postgres
backup_configuration {
  enabled                        = true
  start_time                     = "02:00" # UTC daily backup window
  point_in_time_recovery_enabled = true
  transaction_log_retention_days = 7
  location                       = "europe-west1"

  backup_retention_settings {
    retained_backups = 30
    retention_unit   = "COUNT"
  }
}

Step 5: Create the Application Database and User with Secret Manager

Declare the logical database and a least-privilege user whose password lives in Secret Manager, never in Terraform source. Hardcoding passwords in HCL leaks them into version control and the Terraform state file in plaintext form; a data source read keeps the secret in one auditable place.

resource "google_sql_database" "app" {
  name     = "appdb"
  instance = google_sql_database_instance.postgres.name
}

# Store the password once:
#   printf '%s' "$(openssl rand -base64 24)" | \
#     gcloud secrets create prod-pg-app-password --data-file=-
data "google_secret_manager_secret_version" "app_password" {
  secret = "prod-pg-app-password"
}

resource "google_sql_user" "app" {
  name     = "app_user"
  instance = google_sql_database_instance.postgres.name
  password = data.google_secret_manager_secret_version.app_password.secret_data
}

Step 6: Add a Cross-Region Read Replica

Provision a read replica in a second region with master_instance_name for read scaling and disaster recovery. Replicas offload reporting queries from the primary and can be promoted to a standalone instance during a regional outage (regional failures exceed what REGIONAL zonal failover covers).

resource "google_sql_database_instance" "replica" {
  name                 = "prod-postgres-16-replica"
  database_version     = "POSTGRES_16"
  region               = "europe-west4"
  master_instance_name = google_sql_database_instance.postgres.name

  replica_configuration {
    failover_target = false
  }

  settings {
    edition           = "ENTERPRISE"
    tier              = "db-custom-4-16384"
    availability_type = "ZONAL"

    ip_configuration {
      ipv4_enabled    = false
      private_network = var.network_id
    }
  }

  deletion_protection = true
}

Step 7: Pin the Maintenance Window and Deny Period

Set an explicit weekly maintenance slot and an optional deny-maintenance period around critical business dates. Without a maintenance window, Google may apply engine updates at any time — on REGIONAL instances the failover still causes 60–120 seconds of downtime, which is unacceptable at 14:00 on a Tuesday.

# Add inside settings {} of google_sql_database_instance.postgres
maintenance_window {
  day          = 7        # Sunday
  hour         = 3        # 03:00 UTC
  update_track = "stable" # receives updates after the canary track
}

# Add inside settings {} — optional freeze for peak season
deny_maintenance_period {
  start_date = "2026-11-15"
  end_date   = "2026-12-15"
  time       = "00:00:00"
}

Step 8: Connect with psql over the Private IP

Retrieve the instance private IP and connect with psql from a VM inside the VPC (via IAP tunnel or a bastion). This proves end-to-end private connectivity — no public IP, no Cloud SQL Auth Proxy required for in-VPC clients, and confirms the peering from step 2 actually routes.

# Get the private IP of the primary
gcloud sql instances describe prod-postgres-16 \
  --format='value(ipAddresses.ipAddress)'

# From a VM in the same VPC (or via `gcloud compute ssh --tunnel-through-iap`):
psql "host=10.120.0.3 port=5432 dbname=appdb user=app_user sslmode=require"

# Quick sanity checks inside psql:
#   SELECT version();
#   SHOW archive_mode;   -- expect: on (PITR WAL archiving)

Step 9: Apply, Trigger a Failover Drill, and Verify PITR

Run terraform apply, then manually trigger a failover and a PITR clone to prove the resilience story before go-live. An HA configuration you have never failed over is a hypothesis, not a guarantee — game-day testing validates connection retry logic and RTO.

terraform init
terraform plan -out=prod.tfplan
terraform apply prod.tfplan

# Trigger a manual failover (primary and standby swap zones)
gcloud sql instances failover prod-postgres-16

# Prove PITR by cloning to a new instance at a chosen second
gcloud sql instances clone prod-postgres-16 pitr-drill \
  --point-in-time "$(date -u -d '10 minutes ago' +%Y-%m-%dT%H:%M:%S.000Z)"

Verification & Health Check

Best Practices

• Private IP Only — Never Expose PostgreSQL Publicly
• Always Set REGIONAL Availability and a Maintenance Window
• Keep Deletion Protection and Backup Retention On
• Right-Size with Dedicated vCPU Tiers, Autoresize Storage

Common Mistakes

• {"errorCode":"PRIVATE_IP_CONNECTIVITY_ERROR","symptoms":"Terraform apply of google_sql_database_instance fails with an error about network peering or 'Network associated with ... does not have private services access'.","rootCause":"The private services access peering (google_service_networking_connection) does not exist yet, or the instance was applied before the peering finished.","fixCommand":"gcloud services vpc-peerings list --network=<vpc-name>","code":"resource \"google_service_networking_connection\" \"private_vpc_connection\" {\n network = var.network_id\n service = \"servicenetworking.googleapis.com\"\n reserved_peering_ranges = [google_compute_global_address.private_ip_range.name]\n}\n\nresource \"google_sql_database_instance\" \"postgres\" {\n # ...\n depends_on = [google_service_networking_connection.private_vpc_connection]\n}\n","language":"hcl","filename":"network.tf","prevention":"Always model the peering as an explicit depends_on in CI so the dependency survives module refactors."}
• {"errorCode":"PITR_RESTORE_FAILED / WAL logs unavailable","symptoms":"gcloud sql instances clone --point-in-time fails with 'point-in-time recovery is not enabled' or the target timestamp is out of range.","rootCause":"PITR was enabled after the target timestamp, or the requested time is older than transaction_log_retention_days (max 7 days) of archived WAL.","fixCommand":"gcloud sql instances patch prod-postgres-16 --enable-point-in-time-recovery --retained-transaction-log-days=7","code":"backup_configuration {\n enabled = true\n point_in_time_recovery_enabled = true\n transaction_log_retention_days = 7\n}\n","language":"hcl","filename":"sql.tf","prevention":"Enable PITR at instance creation; WAL archiving only covers the period after it is switched on."}
• {"errorCode":"INSUFFICIENT_TIER / shared-core limitations","symptoms":"Read replica creation or edition = ENTERPRISE_PLUS fails with an invalid tier error on db-f1-micro or db-g1-small.","rootCause":"Shared-core tiers are Enterprise-edition only, single-zone only, and do not support the Enterprise Plus feature set.","fixCommand":"gcloud sql instances patch prod-postgres-16 --tier=db-custom-2-7680","code":"settings {\n edition = \"ENTERPRISE\"\n tier = \"db-custom-2-7680\" # dedicated vCPU\n}\n","language":"hcl","filename":"sql.tf","prevention":"Gate instance shapes with a Terraform variable validation rule so only db-custom-*/db-perf-optimized-* tiers reach production."}
• {"errorCode":"Error 409 operation failed because another operation was already in progress","symptoms":"terraform apply fails on the instance while a backup, failover, or maintenance operation is running.","rootCause":"Cloud SQL allows only one long-running operation per instance at a time; overlapping Terraform changes collide with it.","fixCommand":"gcloud sql operations list --instance=prod-postgres-16 --filter='status!=DONE'","code":"# Serialize Terraform runs in CI and avoid applying during the maintenance window.","language":"bash","filename":"check-operations.sh","prevention":"Serialize Terraform runs in CI (single state lock) and avoid applying during the scheduled maintenance window."}

Cost Analysis

References

• Cloud SQL for PostgreSQL Documentation
• About High Availability for Cloud SQL Instances
• About Point-in-Time Recovery (PITR)
• Terraform google_sql_database_instance Resource
• Configuring Private Services Access