Design database backup and recovery solutions

Geographic and automated backup planning is the practice of storing restorable copies in separate locations on a schedule so data survives zonal failure, regional failure, and human error. In Google Cloud, Cloud Storage (opens in a new tab) bucket locations, managed database replication, and schedulers such as Cloud Scheduler provide the foundation. The design must balance availability against compliance, residency rules, recovery point objective (RPO), and recovery time objective (RTO).

1.1Geographic Storage Options for Backup Redundancy

Geographic storage options for backups are bucket configurations that keep objects in two or more separate areas so data survives a regional failure. A dual-region bucket stores data in two regions that you choose, while a multi-region bucket stores data across data centers in the multi-region. Synchronization between regions happens asynchronously, so recovery depends on replication finishing before the failure.

When strict RPO and RTO targets require proof that data reached the second region, use two single-region buckets instead of one dual-region or multi-region bucket. Data flows from the application to both buckets by dual-write, or data flows from the first bucket to the second bucket by a Cloud Storage copy operation. Turbo replication (opens in a new tab) strengthens a dual-region by replicating data to both regions within the dual-region with an RPO of 15 minutes.

Cross-bucket replication is the right choice when the source and destination must be separate buckets in different locations. Dual-regions and turbo replication are the right choices when one bucket must itself span two regions.

1.2Scheduled Exports and Cross-Region Replication Services

Scheduled cross-region backups are automated exports and copies that create restorable copies on a timetable without manual work. For BigQuery, the most cost-effective table backup is a BigQuery export to Cloud Storage scheduled by Cloud Scheduler or Managed Service for Apache Airflow. Export formats include Avro with SNAPPY compression or JSON with GZIP compression, plus columnar formats such as Parquet and ORC that provide high compression and work with engines such as Hive and Presto.

If multi-region storage is not allowed but a second region can be used, the BigQuery Data Transfer Service copies a dataset to a different region for resilience during a regional disaster. The Storage Transfer Service moves or replicates data to, from, or between Cloud Storage, Amazon S3, and on-premises systems. The Backup and DR Service provides centralized backup management for Compute Engine virtual machines (VMs), including Persistent Disk snapshots stored in Cloud Storage buckets with monitoring, auditing, and reporting. For append-only warehouses partitioned by date, first create a copy of the required partitions in a new table and then run the export on the new table.

Exporting BigQuery data to an on-premises disaster recovery location follows an ordered flow from Google Cloud to the on-premises network:

1. Send to processing: Data flows from BigQuery to an Apache Spark job on Managed Service for Apache Spark, which permits schema evolution.
2. Write to staging: Processed files flow from the Spark job to a Cloud Storage bucket.
3. Connect networks: Cloud Interconnect connects the Virtual Private Cloud network to the on-premises network.
4. Transfer offsite: Files flow from Cloud Storage through the Spark job or gcloud storage transfer to the on-premises location.

1.3Managed Database Resilience and Residency Controls

Managed database resilience is determined by where each service stores copies and whether it automatically replicates across regions. Bigtable production instances require a minimum of two clusters in unique zones in the same region, and Google automatically manages replication that creates a separate, independent copy in each zone. Incoming traffic can fail over from one cluster to another cluster in the same instance, which protects against zonal failure but does not by itself protect against loss of the entire region.

BigQuery datasets can be regional or multi-regional, but BigQuery does not automatically provide a backup in another geographic region. To survive regional loss, data must be backed up to another location using cross-region dataset replication or managed disaster recovery, which provisions slots in a secondary region and controls failover of workloads. Short-term human errors are covered differently: time travel supports restoration of queried, deleted, or corrupted data from the last seven days even for deleted tables, plus a fail-safe period that retains deleted data for an additional seven days for emergency recovery through Cloud Customer Care. Firestore in a multi-region location operates in a multi-zone and multi-region replicated configuration and can withstand loss of entire regions without data loss.

Balancing redundancy with residency means enforcing where data at rest can be created while still meeting recovery goals. The Organization Policy Service (opens in a new tab) gives centralized control through the location-restricted resources constraint, which allows or denies multi-regions such as Asia and Europe, regions such as us-east1, or zones such as europe-west1-b. When residency restricts the design to a single region, data flows from the Google Cloud region to a second platform in the same geographic area, such as Amazon S3, on-premises MinIO or Ceph, or Filestore in specific regions and zones. Encryption can help when rules concern who can access data: Cloud External Key Manager (Cloud EKM) encrypts BigQuery and Compute Engine data with keys stored outside Google infrastructure, while customer-supplied encryption keys (CSEK) protect Cloud Storage and Compute Engine data with keys supplied for each operation.

1.4Native Automated Backups and Orchestration at Scale

Native automated backup features are per-service settings that create recoverable copies without custom code, while orchestrators run them on schedule. Cloud SQL uses automated backups plus binary logging to support point-in-time recovery (PITR), which restores to a fresh Cloud SQL instance. Cloud SQL can also use a high availability (HA) configuration and cross-region replicas, and Cloud SQL Enterprise Plus edition uses advanced disaster recovery (DR) to simplify recovery and fallback with zero data loss after cross-regional failover.

Spanner provides managed consistent backups through Spanner Backup and Restore, which can be retained for up to 1 year. Restore from Spanner Backup and Restore has lower RTO than restore from an export because the restore operation directly mounts the backup without copying the data. Spanner also provides PITR for recovery up to 7 days in the past. Scalable backup automation (opens in a new tab) for hundreds of BigQuery tables uses Cloud Scheduler to start work, Pub/Sub to decouple tasks, Cloud Run to run backup services, Cloud Storage to hold policies and data, Datastore for state, and Cloud Logging for monitoring. Data-owner JSON files define table-level policies, while a central Terraform-managed JSON file defines fallback policies for folders, projects, datasets, and tables.

Lifecycle choices trade retention, RTO, and cost against each other. BigQuery long-term storage automatically drops table storage price by 50 percent if the table is not edited for 90 consecutive days, with no loss of durability. The Spanner commit timestamp feature supports incremental backups by selecting only rows added or modified since the last full backup. Columnar formats such as Parquet and ORC add high compression for exports.

1.5Aligning Cloud SQL, AlloyDB, and Spanner Backups with RPO and RTO

Alignment of RPO and RTO with backup configuration means matching acceptable data loss and downtime to backup frequency, retention, and restore method. RPO is the maximum acceptable data loss measured in time, so more frequent backups produce a smaller RPO. RTO is the maximum acceptable time to restore service, so the restore path and standby capacity determine how quickly recovery completes.

AlloyDB for PostgreSQL provides continuous backup by default for near-zero RPO, with PITR at microsecond granularity in a window of 1 to 35 days and a default of 14 days. AlloyDB creates one daily incremental backup and can restore to a new cluster in the same region. Discrete AlloyDB backups handle longer retention: on-demand backups are full backups created manually, while automated backups are incremental backups on a schedule. Both types are immutable and can be retained up to 1 year, with a default of 14 days for automated backups. An on-demand backup can specify a custom cross-region location.

Engine-specific import and export is the practice of matching each Google Cloud database to its supported utility and file format so data moves without losing types or structure. Cloud Spanner uses Dataflow jobs that read from and write to Cloud Storage with Avro or CSV files plus JSON manifests. Firestore in Datastore mode uses managed export and import operations that also stage files in Cloud Storage. Choosing the wrong format or omitting the manifest breaks fidelity on import.

2.1Spanner Avro Export Format and Import Workflow

A Cloud Spanner Avro export is a Dataflow job that copies table data from the Spanner database to a folder in a Cloud Storage bucket. Data flows from Spanner tables through Dataflow workers to Avro files in the destination folder, so the job depends on both Spanner compute capacity and Dataflow access to the bucket. The export folder created by the job holds everything needed for a later import.

The export folder contains:

• spanner-export.json that describes the overall export
• TableName-manifest.json for each table that lists its data files
• TableName.avro-#####-of-##### data files where the first number is the file index starting at zero and the second is the total file count
• ChangeStreamName-manifest.json and ChangeStreamName.avro-00000-of-00001 for each change stream, where the Avro file holds only the schema

To import a Spanner Avro export, data flows in reverse from the Cloud Storage folder through a Dataflow import job into a new Spanner database. The operator selects the export folder itself, enters a new database name that does not already exist in the instance, and chooses the dialect as GoogleSQL or PostgreSQL. The import job creates the database during the run, and the new database can optionally be protected with a customer-managed encryption key.

2.2Spanner CSV and Non-Spanner Avro Imports with Manifests

Spanner CSV export and import uses CSV data files plus a JSON manifest file stored together in a Cloud Storage bucket. Data flows from Spanner through Dataflow to CSV files on export, and from CSV files through Dataflow to Spanner on import. Because CSV does not carry full Spanner type information, the JSON manifest specifies which destination tables are populated by which CSV files, and the target tables must already exist with the correct schema.

Importing CSV files into Spanner follows an ordered workflow:

1. Export source data to CSV: Export data from the non-Spanner database to CSV files and upload the files to a Cloud Storage bucket.
2. Create JSON manifest: Create a JSON manifest file in the same bucket that maps each Spanner destination table to its CSV files.
3. Create Spanner schema: Create the target tables in the Spanner database with column types that match the CSV content.
4. Run Dataflow import: Run a Dataflow import job with gcloud that reads the manifest and CSV files and writes rows into Spanner.

Importing Avro files from a non-Spanner database into Spanner preserves more type fidelity than CSV because Avro carries column types. Data flows from Avro files in Cloud Storage through a Dataflow job into pre-created Spanner tables, so the workflow depends on creating the Spanner schema before running the import. The operator must also create a spanner-export.json file that points to the destination tables and their Avro files, then run the Dataflow import with the gcloud CLI.

2.3Firestore Managed Export, Import, and BigQuery Compatibility

Firestore (opens in a new tab) managed export and import operations authorize as the Firestore service agent and stage data in a Cloud Storage bucket. The service agent is service-PROJECT_NUMBER@gcp-sa-firestore.iam.gserviceaccount.com, which replaces the legacy App Engine service account shared by multiple services. If the bucket is in the same project as the Firestore database, the service agent can access the bucket by default; if the bucket is in another project, the operator must grant the service agent access to that bucket.

An export of all entities without an entity filter cannot be used for selective restore or for BigQuery loading. Data exported without one or more kind names in the entity filter cannot be loaded into BigQuery, and a subset of kinds and namespaces cannot be imported from an export of all entities. To keep BigQuery compatibility, the export request must include an entity filter with kind names, and each kind generates a BigQuery table schema where each unique property becomes a column.

BigQuery enforces a limit of 10,000 columns per table, which affects wide Firestore kinds. If a kind schema exceeds 10,000 columns, the export tries to stay under the limit by treating embedded entities as blobs, which allows loading but prevents querying properties inside those embedded entities. If the column count still exceeds 10,000, no BigQuery schema is generated for that kind and its data cannot be loaded into BigQuery.

2.4Permissions and Quotas for Dataflow Import and Export Jobs

Dataflow import and export jobs for Cloud Spanner require specific Identity and Access Management (IAM) roles on the Dataflow worker service account. The worker identity must be able to read Spanner, run Dataflow, and read and write Cloud Storage objects, with an extra admin role for imports that create databases. Without these grants, data cannot flow between Spanner and the Cloud Storage staging bucket.

The required roles on the Dataflow worker service account are:

• Cloud Spanner Viewer (roles/spanner.viewer)
• Dataflow Worker (roles/dataflow.worker)
• Storage Admin (roles/storage.admin)
• Spanner Database Reader (roles/spanner.databaseReader)
• Spanner Database Admin (roles/spanner.databaseAdmin), which is required only for import jobs

Compute Engine quotas bound how many resources Dataflow can use, and these quotas represent the maximum allowed rather than guaranteed usage. Recommended starting values are 200 CPUs, 200 in-use IP addresses, and 50 TB of standard persistent disk. Dataflow provides autoscaling so the job pays only for resources actually used, and the Dataflow UI shows a warning icon if the job could use more resources, though the job should still finish.

2.5Staging, Performance, and Validation for Large Movements

Staging large movements through Cloud Storage is the method where data flows from the source system to a bucket and then from the bucket into BigQuery, with exports flowing in reverse. For a Snowflake to BigQuery movement, the export is prepared with a named file format set to PARQUET, a storage integration that permits the bucket locations, and an external stage that references the integration and file format. This staged approach lets large datasets land in durable object storage first, so the load into BigQuery can read from files rather than from a live source connection.

The BigQuery Data Transfer Service with the Snowflake connector reduces movement size by supporting incremental transfer. An incremental transfer moves only data that changed since the last transfer instead of loading the entire dataset each time. Both BigQuery and Snowflake use a massively parallel processing (MPP) architecture where queries are distributed across multiple servers, and BigQuery adds slots as the unit of computational capacity. BigQuery supports explicit partitioning and clustering to control how data is distributed, which can improve runtime performance and cost.

Data completeness validation confirms that imported data is whole and returns the same answers as the source. A repeatable validation sequence moves from test design to comparison to correction:

1. Create test cases: Build a wide range of test cases with different scenarios and data.
2. Run on BigQuery: Execute the translated queries and loads against BigQuery tables.
3. Compare results: Compare BigQuery results to the original Snowflake results for row counts, values, and structures.
4. Analyze and fix: Investigate any differences and correct the converted queries, types, or table definitions.

Schema alignment must also be verified: translated queries, views, and user-defined functions (UDFs) must be checked, and row-level and column-level controls are rebuilt with BigQuery policy tags and authorized views mapped from source roles through IAM.

2.6Secure Access and Service-Agent Migration for Data Transfer

Secure access for data transfer means that both the person who starts an export or import and the service account that moves the bytes have only the permissions they need. Every transfer uses two identities: the user account that signs in to start jobs, and the service account that actually reads and writes data, such as the Firestore service agent, the Database Migration Service (DMS) (opens in a new tab) service account service-PROJECT_NUMBER@gcp-sa-datamigration.iam.gserviceaccount.com, or the export service account. A secure pipeline therefore connects the user identity to the database service, the database service identity to the bucket, and the bucket policy back to both identities.

Cloud Storage bucket permissions determine whether the service account can deliver exported files or read files for import. Firestore follows a project-boundary rule that separates default access from explicit grants. Workflows that stay within the same project do not require permission changes because the Firestore service agent can access buckets in the same project by default. Operations that use a bucket in another project fail unless you grant that bucket access to the source project's Firestore service agent. The Firestore Service Agent role grants both read and write permission on the bucket, so use a custom role when you need only read for imports or only write for exports.

Migration to the Firestore service agent moves jobs from the older App Engine service account to the dedicated Firestore service agent. The project-level migration follows an ordered check, fix, and confirm workflow:

1. Check status: Open Import/Export, click Check Bucket Status, and review recently used buckets that lack permission for the service agent.
2. Fix each bucket: For each needed bucket, click Fix, add the Firestore service agent as a new principal, select Service Agents > Firestore Service Agent, and save.
3. Confirm migration: Click Migrate to Firestore Service Agent and confirm again if any checks still fail.

Migration cannot be undone, so complete all needed bucket fixes before final confirmation.

Recovery objectives and point-in-time recovery (PITR) translate business continuity needs into database architectures that limit downtime and data loss. The recovery time objective (RTO) is the maximum acceptable time an application can be offline, or how long after a disaster before service is running again. The recovery point objective (RPO) is the maximum acceptable data loss measured in time. Smaller RTO and RPO values require greater complexity and cost to run.

3.1Defining RTO and RPO Targets by Criticality and Outage Scope

Defining RTO and RPO targets starts with grouping applications by criticality and by scope of loss. Criticality tiers assign stricter targets to customer-facing or revenue-critical systems and looser targets to internal systems. Scope of loss simplifies requirements to three cases: a zonal outage, a regional outage affecting more than one zone, or the extremely unlikely outage of multiple regions.

Business-critical operations subject to RTO and RPO must depend only on data plane components that continuously process operations. The data plane carries live reads and writes, while the management plane manages configuration state. A workload that depends on a VM-creation API or an IAM permission update during an outage cannot meet its RTO because those management operations may be unavailable when recovery is needed.

3.2Selecting High-Availability and Disaster-Recovery Architectures

High availability (HA) (opens in a new tab) is the architecture for surviving zone failures within a single region with zero or near-zero RTO and RPO. Many managed services provide synchronous replication between zones within one region by default, which keeps a standby copy current without data loss and supports an active-active topology with no single point of failure (SPOF). Preferred HA building blocks include Spanner with built-in redundancy across zones and regions, Cloud SQL with its HA feature and replicas across zones, multi-regional Cloud Storage, regional persistent disks, regional Compute Engine managed instance groups (MIGs), and Google Kubernetes Engine (GKE) regional clusters.

Disaster recovery (DR) is the architecture for surviving regional outages with workloads deployed across two or more regions. Unless the workload uses multi-regional or global services, cross-region replication is typically asynchronous, which offers lower latency at the cost of risk of data loss. That tradeoff means DR usually has higher RTO and RPO than HA, plus manual or semi-automated failover steps that must be tested in advance. Snapshots of persistent disk data should be stored in multi-region locations so disks can be recreated in another region.

3.3Validating Recovery Strategies Through Disaster Recovery Testing

Validating recovery strategies through disaster recovery testing is the practice of regularly proving that backups can be restored within RTO and RPO targets. A test is only successful when restored data is correct, restoration finishes within RTO, and data loss stays within RPO during zonal outages, regional outages, or corruption events. Smaller RTO and RPO values generally mean higher cost, tracked as service level objectives (SLOs) within a service level agreement (SLA).

A complete drill tests all of the following areas:

• Backups and backup schedules, including whether jobs ran on time and met frequency targets
• Data transfer from off-platform sites, including transfer performance and completion time
• Recovery from stored backups, including restore speed and whether restored data meets application requirements
• Security controls and system access, including identity, firewall, and permission behavior in the recovery environment
• Network routing, including DNS, load balancing, and connectivity to the recovered database

Safe restoration testing restores backups in an isolated non-production environment that cannot conflict with production users or data. A repeatable restoration test follows an ordered workflow:

1. Check consistency after backup: Run automated consistency checks after each backup to validate that the snapshot is usable.
2. Restore in isolation: Restore the backup to a non-production environment that is network-isolated from production.
3. Validate full stack behavior: Bring up services with the restored data, simulate user scenarios, and measure RTO and RPO against targets.

Backup frequency must be equal to or more frequent than RPO. Google Cloud Backup and DR tracks backup health, while Cloud Monitoring sends alerts when backup intervals exceed the defined RPO.

3.4Controlling RPO with Frequency and Retention and RTO with Readiness

Controlling RPO with frequency and retention and RTO with readiness separates data-loss controls from downtime controls. Backup frequency sets the maximum gap between the last backup and a disaster, so transfer or snapshot jobs must run at least as often as the RPO requires. Backup retention sets how far back recovery can go, which protects against logical errors by allowing restore to a point before corruption occurred.

RTO is controlled by how much infrastructure, software, and data are already running at the recovery site. A hot standby system keeps pre-deployed infrastructure, active database systems, and replicated data running in the recovery region, so failover is fastest but cost is highest. A lower-cost option configures and deploys a Compute Engine VM in advance but keeps it stopped, so attached disks or static IPs may still incur charges while the stopped VM itself does not. The lowest-cost option defers VM creation until recovery using Terraform, an instance template, and a custom image created in advance. Drills must measure provisioning, disk restore, log replay, and traffic cutover time for the chosen level.

3.5Implementing PITR Across Cloud SQL, Spanner, and AlloyDB

Implementing PITR mechanisms means enabling continuous change logs so a database can be restored to a specific timestamp between backups. Cloud SQL uses binary or transaction logs, Spanner retains versioned data, and AlloyDB for PostgreSQL uses continuous backup with data-change logs. In all three cases, PITR depends on uninterrupted log retention, and a restore creates a new instance or cluster rather than overwriting the source.

Cloud SQL PITR restores a primary instance to a specific timestamp by replaying logs on top of a backup to create a new clone. The restore depends on automated backups and continuous logging being enabled before the event, so there must be an unbroken chain of binary logs. For MySQL, Cloud SQL uses the mysqlbinlog utility to replay up to the stop time. Enabling PITR requires a daily backup-start-time in UTC, binary logging with Enable point-in-time recovery, and Days of logs from 1-7 for Enterprise edition or 1-35 for Enterprise Plus edition. A timestamp restore uses Create clone and Clone from an earlier point in time with a PITR time in RFC 3339 UTC.

Spanner PITR lets an application recover data from a specific point in time up to 7 days in the past. Spanner PITR is designed for logical errors such as bad writes or bad rollouts, and it complements Spanner synchronous replication by allowing precise rollback. AlloyDB PITR fully restores an active cluster from any point in time within the recovery window into a new cluster. If continuous backup is disabled and re-enabled, PITR is blocked until the first new backup exists. The original cluster must still exist, and PITR is paused on the original primary during cross-region failover until a new baseline backup is established.

Optimization of storage lifecycle and data deletion keeps each database asset only as long as policy requires, then moves it to cheaper storage or securely deletes it. The lifecycle is controlled by retention metadata, carried out by automated purge and archive workflows, and extended by Cloud Storage lifecycle rules that lower long-term cost. Automated retention policies also control how long database backups and transaction logs are kept to meet RPOs and compliance needs.

4.1Retention Governance Tags and Default Policies

Retention governance tags are metadata fields that state how long a table must be kept and what happens when that time ends. Knowledge Catalog stores these fields in the CDMC controls tag template as retention_period, the time in days to retain the table, and expiration_action, whether to archive or purge the table. Tag Engine applies these tags based on corporate rules, and Report Engine checks whether tags are applied and whether tables meet the retention policy.

These tag values drive the automated workflow, so a table that reaches its retention period is handled according to its assigned purge or archive action. Report Engine publishes findings to Pub/Sub when an expiration action or retention period is not aligned with location policy, or when sensitive data exists without retention tags.

4.2Purge and Archive Workflows for BigQuery Tables

The purge workflow is the secure deletion path that removes the source table while keeping a short recovery window. When the workflow reads an expiration action of purge, it first creates a BigQuery table snapshot with an expiration time to support soft deletion and recovery, then it applies BigQuery table expiration or partition expiration policies to the source table. The snapshot lets the table be retrieved during the soft-deletion period, and expiration of the source table and then the snapshot completes the deletion. Record Manager follows the same pattern by setting an expiration date and creating a snapshot with an expiration time defined in its configuration, 30 days by default.

The archive workflow is the preservation path that moves expired table data from BigQuery to Cloud Storage and then removes the source. When the workflow reads an expiration action of archive, data flows from the source BigQuery table to Cloud Storage and then to a governed external table in this order:

1. Export to Cloud Storage: The workflow exports the table data to Cloud Storage in Parquet format.
2. Register BigLake table: The workflow registers a BigLake table over the exported files to support access control and external metadata tagging.
3. Carry forward governance: The workflow copies metadata and policy tags from the source table to the BigLake table, or re-applies the tags using Tag Engine.
4. Remove the source: The workflow drops or expires the source BigQuery table so primary storage is freed.

Because the BigLake table remains taggable in Knowledge Catalog, archived data stays discoverable and governed after it leaves BigQuery. After data is archived, Cloud Storage object lifecycle management automatically transitions older Parquet objects to lower-cost storage classes without manual copying.

4.3Automated Retention Policies in Backup and DR

Automated retention policies in Backup and DR control how long database backups and transaction logs are kept through backup plan templates. A backup plan template combines snapshot policies, log protection settings, replication policies, and expiration jobs so backups are created, retained, and deleted without manual action. Database backups and log backups have separate frequencies and separate retention periods, and logs can be extended to remote sites for point-in-time recovery.

Backup frequency in Backup and DR is the number of jobs run per unit time, while period is the time between scheduled runs. A schedule that runs every 4 hours has a period of 4 hours and a frequency of 6 times per day. The minimum recommended frequency for a Snapshot policy is 1 hour for local RPO, and a StreamSnap policy can point to any Snapshot policy with a frequency of 1 hour or longer for remote RPO. Database log frequency is set in minutes through the RPO setting, with a smallest value of 15 minutes, and log frequency must not exceed the database backup interval.

Log protection is enabled through the Enable Database Log Backup setting in the advanced settings of a backup plan snapshot policy. When set to Yes, log backups run on their own log backup jobs without regard to day boundaries, windows, or database frequency. Log Backup Retention Period is a mandatory setting defined separately from snapshot retention, which lets logs be kept long enough to cover database copies for point-in-time recovery.

Retention automation also manages production logs and staging storage:

• Truncate or purge log after backup runs log purge at the end of each log backup when set to Yes, while the default is Do Not Truncate.
• Log Backup Retention Period defines how long log backups stay on the staging disk and can differ from snapshot retention.
• Log staging disk growth size defines automatic growth of the log staging disk from 5 to 100 percent.
• Estimated change rate defines daily data change from 0 to 100 percent so the appliance can size the staging disk.
• Compress database log backup instructs the source database to compress logs using the database API before capture.

4.4Extending Retention to Remote Pools and Staying Within RPO

Extended retention in Backup and DR replicates logs from the local snapshot pool to a remote location so point-in-time recovery is possible outside the primary site. StreamSnap replication moves log backups directly from the local snapshot pool to the snapshot pool on the remote backup/recovery appliance. OnVault replication moves logs continuously to one or more OnVault storage pools, such as object storage or Cloud Storage buckets, for long-term and off-site retention.

Replicate Logs is enabled by default once prerequisites are met, and replicated logs can be used for any database backup within the retention range. Send Logs to OnVault Pool sends logs to every OnVault pool in the application profile, with replication following log frequency such as every 15 minutes. Daily, Weekly, Monthly, and Yearly OnVault policies apply only to database backups and do not apply to log backups.

Automated retention depends on the job system that creates, replicates, and expires backups on schedule. A backup/recovery appliance runs six snapshot jobs concurrently by default and queues additional jobs, and recovery jobs such as mount or restore have the highest priority. Failed jobs are retried automatically, with a 4-minute wait before the first retry and a Failed state after 3 failed attempts. RPO compliance (opens in a new tab) also depends on sizing and contention: network congestion, competing clones or restores, higher than normal change rates, loss of changed block tracking, and uneven distribution of change across days can delay backups and cause violations.

4.5Transaction Log Retention and PITR Windows in Cloud SQL

PITR windows define how far back in time a database can be restored to any specific second. The PITR window is determined by how long transaction logs are retained alongside database backups. A longer window gives more granular recovery and better RPO coverage, but it increases storage costs because more log history must be stored and managed.

Transaction log retention in Cloud SQL is controlled by the transactionLogRetentionDays backup configuration setting, which sets how long PITR logs are kept in Cloud Storage. Limits and defaults depend on the instance edition, so the choice of edition directly sets the maximum recoverable window.

When retention is increased, the full new window is not available immediately. The window grows by one day per day until it reaches the new value, so an increase from 7 to 14 days takes 14 days to provide 14 days of history. PITR logs are stored in Cloud Storage in the same region as the instance, plus a smaller duplicate copy on disk to allow replication. The effective on-disk retention is the minimum of transactionLogRetentionDays and the expire_logs_days or binlog_expire_logs_seconds flag value. For customer-managed encryption key (CMEK)-enabled instances, the latest key version is required for all retained days to perform a restore. To avoid storage issues, enable automatic storage increases and avoid purging large amounts of history at once by reducing retention slowly, for example by one day each day.

Key Facts

• Turbo replication in a Cloud Storage dual-region provides a 15-minute RPO, while cross-bucket replication asynchronously copies objects from a source bucket to a destination bucket in a different location.
• Spanner Backup and Restore retains consistent backups for up to 1 year and restores faster than Dataflow export because restore mounts the backup without copying Avro files back.
• AlloyDB continuous backup provides near-zero RPO with PITR from 1 to 35 days and microsecond granularity, while on-demand and automated backups are immutable and retained up to 1 year.
• A Firestore export without an entity filter containing kind names cannot be loaded into BigQuery, and BigQuery allows a maximum of 10,000 columns per table.
• Cloud SQL Enterprise edition allows 1 to 7 days of transaction log retention by default 7 days, while Enterprise Plus edition allows 1 to 35 days by default 14 days.