Cloud SQL Managed PostgreSQL with PITR

Overview

Cloud SQL is Google Cloud’s fully managed relational database service that handles provisioning, patching, replication, backups, and failover for PostgreSQL (and MySQL/SQL Server). For teams running on GCP, Cloud SQL eliminates the operational burden of managing PostgreSQL instances on Compute Engine or GKE while providing enterprise features like regional high availability, automated backups with point-in-time recovery (PITR), IAM-based database authentication, SSL enforcement, and deletion protection—all configurable at provisioning time through infrastructure-as-code tools.

Point-in-time recovery (PITR) is the capability that distinguishes Cloud SQL from a simple managed database: it continuously streams write-ahead logs (WAL) to Cloud Storage, enabling recovery to any second within the retention window (up to 7 days by default, configurable to 365 days). This means that even if an application bug corrupts data at 3:47 PM, you can restore to 3:46:59 PM and lose at most one second of writes. Combined with automated daily backups, PITR provides a comprehensive data protection strategy that satisfies SOC2 backup and recovery controls.

In a typical production environment on GCP, teams provision Cloud SQL PostgreSQL instances for each environment using Pulumi. The production instance runs with regional HA (automatic failover to a standby in a different zone), PITR enabled with 14-day retention, IAM database authentication for the application service accounts, and deletion protection to prevent accidental pulumi destroy from dropping the database. These settings help satisfy controls around data durability, access management, and disaster recovery.

Core Concepts

Regional High Availability

Cloud SQL regional HA provisions a primary instance and a standby replica in a different availability zone within the same region. The standby receives synchronous replication of all writes and automatically becomes the primary if the original zone experiences an outage. Failover typically completes in under 60 seconds and is transparent to applications using the Cloud SQL connection name (which resolves to the current primary). This is not a read replica—the standby cannot serve reads and exists solely for failover protection.

Point-in-Time Recovery (PITR)

PITR works by continuously archiving PostgreSQL’s write-ahead log (WAL) segments to Cloud Storage in addition to taking periodic full backups. To recover, Cloud SQL restores the most recent full backup before the target timestamp and then replays WAL segments up to that exact second. The recovery creates a new Cloud SQL instance (it does not modify the original), allowing the team to validate recovered data before switching traffic. PITR retention is configurable from 1 to 365 days, with each day of retention adding approximately the storage cost of one day’s WAL volume.

IAM Database Authentication

Cloud SQL supports IAM-based database authentication, where database users authenticate using their GCP IAM identity rather than a password. For Kubernetes workloads using Workload Identity, this means pods can connect to PostgreSQL using short-lived OAuth2 tokens issued by the GKE metadata server—no database password to store, rotate, or risk leaking. This is enabled by setting databaseFlags for cloudsql.iam_authentication and creating IAM database users that map to GCP service accounts.

SSL Enforcement and Encryption

Cloud SQL can enforce SSL/TLS for all database connections by setting requireSsl: true in the IP configuration. Combined with the server CA certificate (automatically managed by Cloud SQL), this ensures all data in transit between the application and database is encrypted. For defense in depth, data at rest is encrypted by default using Google-managed keys, with the option to use customer-managed encryption keys (CMEK) for regulatory requirements.

Deletion Protection

Cloud SQL’s deletionProtection flag prevents the instance from being deleted through any API call, including Pulumi destroy operations. This is a critical safety net for production databases—an accidental pulumi destroy or a misconfigured CI/CD pipeline cannot drop the database when this flag is set. Removing deletion protection requires an explicit two-step process: first update the instance to disable the flag, then delete it. A production setup enables this for all production instances and disables it only for ephemeral dev environments.

How It Works

Provisioning a production-grade Cloud SQL PostgreSQL instance involves configuring the instance tier, network connectivity, HA, backups, PITR, authentication, and protection settings. The infrastructure project manages all of this as a single Pulumi resource with environment-specific configuration.

Core Instance Provisioning

The sql.DatabaseInstance resource is the central declaration. Key settings include the database version, tier (machine type), region, and the settings block that controls HA, backups, IP configuration, and database flags.

package infra

import (
    "fmt"

    "github.com/pulumi/pulumi-gcp/sdk/v7/go/gcp/sql"
    "github.com/pulumi/pulumi/sdk/v3/go/pulumi"
)

type CloudSQLConfig struct {
    Project          string
    Region           string
    Tier             string // e.g. "db-custom-4-16384" (4 vCPU, 16GB RAM)
    DiskSizeGb       int
    PITRDays         int
    EnableHA         bool
    EnableDeletion   bool
    VPCNetworkLink   pulumi.StringOutput
}

func NewCloudSQLInstance(ctx *pulumi.Context, env string, cfg CloudSQLConfig) (*sql.DatabaseInstance, error) {
    availabilityType := "ZONAL"
    if cfg.EnableHA {
        availabilityType = "REGIONAL"
    }

    instance, err := sql.NewDatabaseInstance(ctx, env+"-postgres", &sql.DatabaseInstanceArgs{
        Name:               pulumi.String(fmt.Sprintf("%s-postgres-%s", env, "primary")),
        Project:            pulumi.String(cfg.Project),
        Region:             pulumi.String(cfg.Region),
        DatabaseVersion:    pulumi.String("POSTGRES_16"),
        DeletionProtection: pulumi.Bool(cfg.EnableDeletion),

        Settings: &sql.DatabaseInstanceSettingsArgs{
            Tier:             pulumi.String(cfg.Tier),
            AvailabilityType: pulumi.String(availabilityType),
            DiskSize:         pulumi.Int(cfg.DiskSizeGb),
            DiskType:         pulumi.String("PD_SSD"),
            DiskAutoresize:   pulumi.Bool(true),

            BackupConfiguration: &sql.DatabaseInstanceSettingsBackupConfigurationArgs{
                Enabled:                    pulumi.Bool(true),
                PointInTimeRecoveryEnabled: pulumi.Bool(true),
                TransactionLogRetentionDays: pulumi.Int(cfg.PITRDays),
                BackupRetentionSettings: &sql.DatabaseInstanceSettingsBackupConfigurationBackupRetentionSettingsArgs{
                    RetainedBackups: pulumi.Int(30),
                    RetentionUnit:   pulumi.String("COUNT"),
                },
            },

            IpConfiguration: &sql.DatabaseInstanceSettingsIpConfigurationArgs{
                Ipv4Enabled:    pulumi.Bool(false),
                PrivateNetwork: cfg.VPCNetworkLink,
                RequireSsl:     pulumi.Bool(true),
            },

            DatabaseFlags: sql.DatabaseInstanceSettingsDatabaseFlagArray{
                &sql.DatabaseInstanceSettingsDatabaseFlagArgs{
                    Name:  pulumi.String("cloudsql.iam_authentication"),
                    Value: pulumi.String("on"),
                },
                &sql.DatabaseInstanceSettingsDatabaseFlagArgs{
                    Name:  pulumi.String("log_checkpoints"),
                    Value: pulumi.String("on"),
                },
                &sql.DatabaseInstanceSettingsDatabaseFlagArgs{
                    Name:  pulumi.String("log_connections"),
                    Value: pulumi.String("on"),
                },
                &sql.DatabaseInstanceSettingsDatabaseFlagArgs{
                    Name:  pulumi.String("log_disconnections"),
                    Value: pulumi.String("on"),
                },
            },

            InsightsConfig: &sql.DatabaseInstanceSettingsInsightsConfigArgs{
                QueryInsightsEnabled:  pulumi.Bool(true),
                QueryStringLength:     pulumi.Int(4096),
                RecordApplicationTags: pulumi.Bool(true),
                RecordClientAddress:   pulumi.Bool(true),
            },

            MaintenanceWindow: &sql.DatabaseInstanceSettingsMaintenanceWindowArgs{
                Day:         pulumi.Int(7), // Sunday
                Hour:        pulumi.Int(3), // 3 AM UTC
                UpdateTrack: pulumi.String("stable"),
            },
        },
    })
    if err != nil {
        return nil, err
    }

    return instance, nil
}

Private Service Access for VPC Connectivity

Cloud SQL instances with private IP must connect through VPC peering via Private Service Access. This requires allocating an IP range in the VPC and creating a service networking connection before the Cloud SQL instance can be provisioned.

package infra

import (
    "github.com/pulumi/pulumi-gcp/sdk/v7/go/gcp/compute"
    "github.com/pulumi/pulumi-gcp/sdk/v7/go/gcp/servicenetworking"
    "github.com/pulumi/pulumi/sdk/v3/go/pulumi"
)

func NewPrivateServiceAccess(ctx *pulumi.Context, env string, vpcID pulumi.IDOutput) (*servicenetworking.Connection, error) {
    ipRange, err := compute.NewGlobalAddress(ctx, env+"-sql-range", &compute.GlobalAddressArgs{
        Name:         pulumi.String(env + "-sql-private-range"),
        Purpose:      pulumi.String("VPC_PEERING"),
        AddressType:  pulumi.String("INTERNAL"),
        PrefixLength: pulumi.Int(16),
        Network:      vpcID,
    })
    if err != nil {
        return nil, err
    }

    conn, err := servicenetworking.NewConnection(ctx, env+"-sql-vpc-conn",
        &servicenetworking.ConnectionArgs{
            Network: vpcID,
            Service: pulumi.String("servicenetworking.googleapis.com"),
            ReservedPeeringRanges: pulumi.StringArray{
                ipRange.Name,
            },
        },
    )
    if err != nil {
        return nil, err
    }

    return conn, nil
}

IAM Database User Creation

For workloads using Workload Identity, the infrastructure project creates IAM database users that map to GCP service accounts. The application connects using pg with an OAuth2 token instead of a password, eliminating credential rotation entirely.

package infra

import (
    "fmt"

    "github.com/pulumi/pulumi-gcp/sdk/v7/go/gcp/sql"
    "github.com/pulumi/pulumi/sdk/v3/go/pulumi"
)

func NewIAMDatabaseUser(ctx *pulumi.Context, instanceName pulumi.StringOutput, gsaEmail string) error {
    // IAM user name format: GSA email without the domain suffix
    // e.g. "search-api@project.iam.gserviceaccount.com" -> "search-api@project.iam"
    _, err := sql.NewUser(ctx, fmt.Sprintf("iam-user-%s", gsaEmail), &sql.UserArgs{
        Instance: instanceName,
        Name:     pulumi.String(gsaEmail),
        Type:     pulumi.String("CLOUD_IAM_SERVICE_ACCOUNT"),
    })
    return err
}

Point-in-Time Recovery Procedure

When data recovery is needed, the team uses gcloud to clone the instance to a specific timestamp. This creates a new instance with the recovered data, allowing validation before switching traffic.

#!/bin/bash
# Recover Cloud SQL instance to a specific point in time

PROJECT="mydb-prod"
SOURCE_INSTANCE="prod-postgres-primary"
RECOVERY_INSTANCE="prod-postgres-recovered"
RECOVERY_TIME="2026-04-11T15:46:59Z"

echo "=== Creating recovered instance ==="
gcloud sql instances clone "$SOURCE_INSTANCE" "$RECOVERY_INSTANCE" \
  --project="$PROJECT" \
  --point-in-time="$RECOVERY_TIME"

echo "=== Waiting for instance to be ready ==="
gcloud sql instances describe "$RECOVERY_INSTANCE" \
  --project="$PROJECT" \
  --format='value(state)'

echo "=== Verifying data integrity ==="
gcloud sql connect "$RECOVERY_INSTANCE" \
  --project="$PROJECT" \
  --user=postgres \
  --database=mydb \
  << 'SQL'
SELECT count(*) FROM documents WHERE updated_at > '2026-04-11T15:46:59Z';
SELECT max(updated_at) FROM documents;
SQL

echo "=== Recovery instance ready for validation ==="
echo "Instance: $RECOVERY_INSTANCE"
echo "Connection: gcloud sql connect $RECOVERY_INSTANCE --project=$PROJECT"

Implementation Patterns

Environment-Specific Tiering

A production setup uses different Cloud SQL tiers per environment to balance cost and performance. Dev environments use db-f1-micro (shared vCPU, 0.6GB RAM) with no HA and no deletion protection. Staging uses db-custom-2-8192 (2 vCPU, 8GB RAM) with ZONAL availability. Production uses db-custom-4-16384 (4 vCPU, 16GB RAM) with REGIONAL HA and full deletion protection. The Pulumi stack configuration drives this differentiation.

var envConfigs = map[string]CloudSQLConfig{
    "dev": {
        Tier:           "db-f1-micro",
        DiskSizeGb:     10,
        PITRDays:       1,
        EnableHA:       false,
        EnableDeletion: false,
    },
    "staging": {
        Tier:           "db-custom-2-8192",
        DiskSizeGb:     50,
        PITRDays:       7,
        EnableHA:       false,
        EnableDeletion: false,
    },
    "prod": {
        Tier:           "db-custom-4-16384",
        DiskSizeGb:     200,
        PITRDays:       14,
        EnableHA:       true,
        EnableDeletion: true,
    },
}

Query Insights for Performance Debugging

Cloud SQL Query Insights captures query execution plans, latency distributions, and lock contention metrics. A production setup enables this with full query string capture (up to 4096 characters) and application tag recording, which allows filtering query performance by service name. This is invaluable when debugging slow search queries or identifying N+1 query patterns in the billing service.

Maintenance Window Scheduling

Cloud SQL applies patches during a configurable maintenance window. A production setup sets this to Sunday 3 AM UTC for all production instances, which is outside peak traffic hours. The stable update track delays patches by one week compared to the canary track, providing time to monitor for issues reported by other Cloud SQL customers before the patch hits production.

Practical Applications

Cloud SQL PostgreSQL serves as the primary relational datastore across many production stacks. Common use cases include:

Document Metadata: The search infrastructure stores document metadata (titles, authors, ingestion timestamps, index shard assignments) in PostgreSQL. Full-text content lives in Cloud Storage, but all query planning and routing is driven by PostgreSQL queries.
Tenant and Billing Data: The SaaS billing system stores tenant accounts, subscription plans, usage records, and Stripe customer mappings. PITR is critical here—billing data corruption could result in incorrect invoices and compliance violations.
Evaluation Results: The ML evaluation pipeline writes evaluation scores, run metadata, and comparison results to PostgreSQL. These results drive model deployment decisions, so data integrity and recoverability are essential.
Session and Auth State: User sessions, API keys, and OAuth tokens are stored in PostgreSQL with IAM authentication ensuring that only the auth service GSA can access these tables.

Common Pitfalls

Private IP requires advance VPC setup: Cloud SQL private IP connectivity requires Private Service Access (VPC peering) to be configured before the instance is created. If this dependency is missing, Pulumi will fail with an opaque networking error. Always use pulumi.DependsOn to sequence the service networking connection before the Cloud SQL instance.
PITR increases storage costs: Enabling PITR with long retention windows (14+ days) stores all WAL segments for that period. For write-heavy workloads, this can double or triple storage costs. Monitor cloudsql.googleapis.com/database/disk/bytes_used_by_data_type to track WAL storage separately.
Deletion protection blocks Pulumi destroy: This is intentional but catches teams off guard during infrastructure teardowns. You must first run a Pulumi update that sets deletionProtection: false before destroying the stack.
IAM auth token expiry: OAuth2 tokens used for IAM database authentication expire after 1 hour. Connection pools must be configured to refresh tokens before expiry. The Cloud SQL Go connector handles this automatically, but raw pgx connections need a custom BeforeConnect hook.
Maintenance window disruptions: Even with HA, the maintenance window causes a brief (30–60 second) connection drop during failover. Applications must handle transient connection errors gracefully with retry logic. Set connection pool MaxIdleTime lower than the failover duration to avoid stale connections.