Design, migrate, and run scalable, highly available databases on Google Cloud across relational and NoSQL services, and track your readiness for the Professional Cloud Database Engineer exam.
Prepare and test your skills
Prepare and test your skills
Geographic redundancy and disaster recovery planning is the practice of storing backup copies in two or more separate geographic areas so data survives a regional failure. In Google Cloud, Cloud Storage (opens in a new tab) bucket locations and managed database replication provide the foundation, while scheduled exports and replication move copies to a second location. The design must balance maximum availability against compliance mandates and data residency rules that may restrict where data can live.
Multi-regional and dual-regional storage in Cloud Storage keeps objects in at least two geographically separate areas regardless of storage class. A dual-region bucket uses two regions that you choose, while a multi-region bucket uses data centers across the multi-region, which can include data centers not listed as available regions. Synchronization between regions happens asynchronously, so recovery depends on replication finishing before the failure.
When strict recovery point objective (RPO) and recovery time objective (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 strengthens a dual-region by replicating data to both regions within the dual-region with an RPO of 15 minutes.
| Option | Scope | Replication behavior |
|---|---|---|
| Dual-regions | Two chosen regions in one bucket | Stores data in both regions with asynchronous sync |
| Turbo replication | Both regions within a dual-region | Replicates to both regions with 15-minute RPO |
| Cross-bucket replication | Source bucket to destination bucket in a different location | Asynchronously replicates objects from source to destination |
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.
Scheduled cross-region backups create restorable copies on a timetable using automation rather than manual exports. 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, subject to BigQuery export limits.
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 compliance. 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, including recent partitions that might still be updated.
Exporting BigQuery data to an on-premises disaster recovery location follows an ordered flow from Google Cloud to the on-premises network:
gcloud storage transfer to the on-premises location.Managed database resilience depends on 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 or replica in another geographic region. To survive regional loss, data must be backed up to another location using cross-region dataset replication for continuous replication or managed disaster recovery, which provisions slots in a secondary region and controls failover of workloads from one region to another. Short-term human errors are covered differently: time travel supports self-service 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. Table snapshots are metadata-only operations that extend protection within the same region without increasing durability.
Firestore can store data in a regional location or a multi-region location that operates in a multi-zone and multi-region replicated configuration. Select a multi-region location when locality rules permit it and the goal is to maximize availability and durability, because multi-region locations can withstand loss of entire regions without data loss. A nightly extract example shows the tradeoff: nightly BigQuery extract jobs to Archive Storage in a second region give an RPO of 24 hours, but RTO can be days because data must be transferred and restored and because recovery-region slots may need provisioning.
Exam tip: For BigQuery multi-regions, avoid backing up to regions within the scope of the multi-region, such as avoiding us-central1 when backing up the US multi-region, because of the chance of correlated failure.
Balancing redundancy with residency means enforcing where data at rest can be created while still meeting recovery goals. The Organization Policy Service gives centralized control across the resource hierarchy through the location-restricted resources constraint. That constraint allows or denies multi-regions such as Asia and Europe, regions such as us-east1 or europe-west1, or individual zones such as europe-west1-b. Domain-restricted sharing adds identity control by allowing only specified Cloud Identity or Google Workspace identities to be added to Identity and Access Management (IAM) policies.
When residency restricts the design to a single region, geographic redundancy cannot be achieved with Google Cloud alone. In that case data flows from the Google Cloud region to a second platform in the same geographic area under a multi-cloud or hybrid strategy. Options include Amazon S3 with interoperability configuration, on-premises object stores such as MinIO or Ceph, and the Transfer service for on-premises data or gcloud storage to copy data between environments. Filestore supports this model by deploying managed file storage in specific regions and zones to match locality requirements.
Encryption can allow a multi-region architecture when locality rules concern who can access data rather than where bits are stored. Cloud External Key Manager (Cloud EKM) encrypts BigQuery and Compute Engine data with keys stored and managed outside Google infrastructure, while customer-supplied encryption keys (CSEK) protect Cloud Storage and Compute Engine data with keys supplied for each operation and then purged from Google servers. Without the external keys the data cannot be decrypted or safely written, so the key manager becomes a dependency of the data plane and RTO depends on key availability.
Automation orchestration and lifecycle management is the practice of using built-in database backup features together with schedulers to create, copy, retain, and delete backups automatically. In Google Cloud, Cloud SQL, Spanner, BigQuery, Firestore, and Bigtable provide native backup features, while Cloud Scheduler, Managed Airflow (opens in a new tab), Storage Transfer Service, and Dataflow orchestrate those features on a schedule. Together they control when backups run, where backup data flows, how long backups are kept, and how cost stays low as retention grows.
Native automated backup features are the per-service settings that create recoverable copies without custom code. Cloud SQL uses automated backups plus binary logging to support point-in-time recovery (PITR), which restores the database from a backup to a fresh Cloud SQL instance. Cloud SQL can also use a high availability (HA) configuration and cross-region replicas for zonal or regional failure, and Cloud SQL Enterprise Plus edition uses advanced disaster recovery (DR) to simplify recovery and fallback with zero data loss after a 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 to a specific time in the past up to a maximum of 7 days, which covers cases such as an operator error or a bad application rollout.
BigQuery replicates data to two zones in a single region for availability, but that replication does not protect against table corruption or accidental deletion. BigQuery provides a time travel window of up to seven days plus a fail-safe period of an additional seven days, during which deleted data is held in fail-safe storage for emergency recovery through Cloud Customer Care. If an error is not found within that combined window, the data is no longer recoverable from its last stable state, so tables that cannot be rebuilt from source data need regular exports or copies.
External orchestration coordinates backup work across services on a schedule and moves backup data from the source database to Cloud Storage or another project, dataset, or table. Managed Airflow creates a directed acyclic graph (DAG) that runs on a schedule, for example daily, to copy data or export data to Cloud Storage. The DAG depends on Google Cloud operators, so the workflow depends on the operator for each source system to move data from that system to the backup destination.
Managed Airflow can orchestrate exports across database types using dedicated operators:
Cloud Scheduler is a fully managed cron job scheduler that triggers scheduled units of work at defined times or regular intervals. Cloud Scheduler or Managed Airflow can periodically schedule a BigQuery export job that writes to Cloud Storage in formats such as Avro with SNAPPY compression or JSON with GZIP compression, while respecting BigQuery limits on export jobs. Storage Transfer Service schedules periodic synchronization from a data source to a data sink, with filters based on file creation dates, filename filters, and preferred times of day, and it is the recommended way to transfer objects from Amazon S3 to Cloud Storage.
Exporting BigQuery data to columnar storage for disaster recovery follows an ordered flow from the warehouse to the recovery location:
Policy-driven automation at scale is a reference pattern for backing up hundreds or thousands of BigQuery tables when basic scripts are no longer enough. The pattern uses Cloud Scheduler to start work on schedule, Pub/Sub to decouple producers from processors, Cloud Run to run containerized backup services, Cloud Storage to hold policies and backup data, Datastore for state, and Cloud Logging for transparency and monitoring. For each table, the tagger service logs the results of the backup services and updates the backup state in the Cloud Storage metadata layer, so operators can see which policy applied and whether the backup succeeded.
A scalable solution must handle different Google Cloud API limits, provide a standardized framework for defining backup policies, and provide transparency and monitoring for backup operations. Backup policies are split by ownership so data owners and governance teams can work together. Manual table-level policies take precedence over fallback policies when the solution determines the backup policy of a table.
Lifecycle management and cost optimization control how long backups are kept, what format they use, and how much data is copied each cycle. BigQuery long-term storage automatically drops the price of storage for a table by 50 percent if the table is not edited for 90 consecutive days, with no degradation of performance, durability, or availability. If the table is edited, it reverts to regular storage pricing and the 90-day countdown starts again, so stable historical tables naturally cost less while active tables do not.
Spanner lifecycle choices trade retention, RTO, and compute cost against each other:
| Spanner option | Retention and recovery window | Recovery and cost behavior |
|---|---|---|
| Spanner Backup and Restore | Consistent backups retained for up to 1 year | Lower RTO than export because restore mounts backup without copying data |
| Dataflow export and import | Full export to Avro files in Cloud Storage, then re-import to a new database | Read and write speed depends on configured nodes, which directly affects RTO |
| Point-in-time recovery (PITR) | Recover to a point in time up to 7 days in the past | Covers recent operator or rollout errors without a full backup restore |
| Warm standby instance | Standby instance sized for storage and throughput | Uses minimum nodes required to meet storage and read and write needs for small RTO values |
BigQuery corruption recovery beyond time travel relies on copies and exports rather than in-place repair. Options include querying to a point in time with snapshot decorators if caught within 7 days, exporting data and rebuilding a new table without corrupted rows, storing time-period tables so only a subset must be restored, making dataset copies at specific times including cross-region copies, and keeping original data in Cloud Storage for reload to a new table.
Export cost and time drop when only changed data is copied. The Spanner commit timestamp feature supports incremental backups by selecting only rows added or modified since the last full backup. For append-only warehouses partitioned by date, the pattern is to first create a copy of the required partitions in a new table, then run the export on the new table and transfer files with the gcloud storage command. For example, an append-only orders table with a 7-day return policy needs exports that include the last 7 days of partitions, because recent records can still be updated by returns. Columnar formats such as Parquet and ORC add high compression and interoperability with engines such as Hive and Presto.
Exam tip: Spanner Backup and Restore has lower RTO than Dataflow export because restore mounts the backup without copying data, while export must copy Avro files back into a new database.
Alignment of RPO and RTO with backup configurations means matching how much data loss and downtime a workload can tolerate to backup frequency, retention, and restore method. RPO is the maximum acceptable data loss measured in time, so more frequent backups and continuous log capture produce a smaller RPO. RTO is the maximum acceptable time to restore service, so the restore path, standby capacity, and cross-region readiness determine how quickly recovery completes.
Cloud SQL (opens in a new tab) is the fully managed relational database service for MySQL and PostgreSQL that uses automated backups combined with binary logging to meet tight RPO requirements. When both automated backups and binary logging are enabled, point-in-time recovery (PITR) restores data from a backup and replays logs to a fresh Cloud SQL instance to reach a specific moment before corruption or deletion. This flow moves from the source instance to backup storage to a new instance, so recovery creates a new instance rather than overwriting the existing one.
For RTO, Cloud SQL depends on availability configuration in addition to backups. A high availability (HA) configuration protects against zonal failure, while cross-region replicas protect against regional failure and maximize uptime by keeping a readable copy in another region. For Cloud SQL Enterprise Plus edition, advanced disaster recovery (DR) simplifies cross-regional failover and fallback with zero data loss after failover, which supports near-zero RPO for regional outages.
AlloyDB for PostgreSQL is the managed PostgreSQL-compatible service that provides continuous backup and recovery by default for near-zero RPO. Continuous backup supports point-in-time recovery (PITR) with microsecond granularity to any moment in the retention window, and the window is configurable from 1 to 35 days with a default of 14 days. As part of continuous protection, AlloyDB creates one daily incremental backup that stores only data changed since the last backup, and continuous recovery can restore to a new cluster in the same region as the source cluster.
Discrete scheduled backups in AlloyDB handle longer retention needs beyond the PITR window. An on-demand backup is a full backup created manually at any time, while an automated backup is an incremental backup created on a user-defined schedule. Both types are immutable after creation and can be retained up to 1 year, with automated backups using a default retention of 14 days when no period is set. Backups are stored in the same region as the cluster by default, but an on-demand backup can specify a custom cross-region location to support regional recovery.
Spanner is the globally distributed relational database service that offers managed backup, export, and point-in-time recovery (PITR) with different RTO and retention tradeoffs. Spanner Backup and Restore creates consistent backups that can be retained for up to 1 year, and restore has a lower RTO than export-based recovery because the restore operation directly mounts the backup without copying the data. In contrast, a full export uses Dataflow templates to write the database to a set of Avro files in a Cloud Storage bucket and another template to re-import those files into a new Spanner database, which requires copying data back.
Spanner RTO also depends on provisioned compute capacity and standby strategy. When the Dataflow connector copies data out of Spanner to Cloud Storage or back into Spanner, the read and write speed depends on the number of configured nodes, and that speed directly affects RTO values. The commit timestamp feature supports incremental backups by selecting only rows added or modified since the last full backup, which reduces backup size and transfer time. Spanner PITR recovers data from a specific point in time up to a maximum of 7 days in the past for cases such as accidental writes or bad application rollouts, and a warm standby Spanner instance sized with the minimum nodes for storage and throughput supports small RTO values.
Choosing frequency, retention, and PITR means using backup frequency to control RPO, retention to control how far back recovery can go, and the restore path to control RTO. Choose continuous protection with log replay when the RPO must be near zero, choose scheduled full and incremental backups when compliance or history requires months of retention, and choose PITR when the need is surgical recovery from recent logical corruption.
| Option | Retention window | Recovery characteristic |
|---|---|---|
| AlloyDB continuous backup and PITR | 1 to 35 days, default 14 days | RPO of zero with microsecond granularity to a new cluster |
| AlloyDB on-demand and automated backups | Up to 1 year, default 14 days for automated | Immutable full or incremental copies for long-term restore |
| Spanner Backup and Restore | Up to 1 year | Lower RTO than export because restore mounts backup without copying data |
| Spanner PITR | Up to 7 days | Recovery from recent operator or application errors |
Exam tip: Spanner Backup and Restore has lower RTO than Dataflow export because restore mounts the backup without copying data, while export must copy Avro files back into a new database.
Last updated