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
Database-specific replication mechanisms are the source-side logging systems that let a Google Cloud database stream committed changes back to an external replica. In Cloud SQL (opens in a new tab), MySQL uses binary logs with global transaction identifier (GTID)-based replication, while PostgreSQL uses logical decoding of the write-ahead log (WAL) with pglogical and replication slots. Both mechanisms require logging to be enabled and retained before a replica connects, which makes reverse replication from Cloud SQL to an external server possible as a fallback path.
| Source type | Change capture log | Agent or extension | Ordering requirement |
|---|---|---|---|
| MySQL source | Binary logs with row-based logging and GTID | Built-in replication stream read by the replica | Enable and retain logs before the replica imports, then the replica replays logs to catch up |
| PostgreSQL source | WAL with wal_level = logical | pglogical extension with replication slots | Install and preload pglogical, then enable logical decoding and restart before replication starts |
The table shows that MySQL depends on binary log retention and GTID position, while PostgreSQL depends on logical slots that hold WAL changes until the replica confirms receipt.
MySQL binary log streaming is the process where the source writes row-level changes to binary logs and the replica reads those logs in GTID order. Data flows from the source binary logs (opens in a new tab) to the Cloud SQL replica, and after the initial import the replica applies all updates made since the export until it catches up to the source. The replica depends on the source to retain logs long enough to complete that catch-up, so logs must stay available throughout migration.
A MySQL source must meet these logging and engine requirements before replication can start:
If GTID-based replication is not used, Cloud SQL cannot identify the exact binary log position from which to begin, and a global read-only lock is needed during the dump to get a consistent starting point.
PostgreSQL logical replication with pglogical is logical decoding where the source keeps row changes in WAL and the pglogical extension exposes them through replication slots to the replica. Changes flow from the source WAL through a replication slot to the Cloud SQL replica, and the slot retains changes on the source until the replica consumes them. Because the slot holds WAL on the source, the source instance must be sized and configured to support the maximum number of slots it will serve.
Configuration of a PostgreSQL source instance follows an ordered sequence:
postgres database on the source instance if it does not exist.shared_preload_libraries to include pglogical with ALTER SYSTEM SET shared_preload_libraries = 'pglogical'.wal_level to logical with ALTER SYSTEM SET wal_level = 'logical'.wal_sender_timeout to 0, which disables termination of inactive replication connections.max_replication_slots to the maximum number of replication slots the source can support.Two platform variants change one setting. If the source is Amazon RDS, set rds.logical_replication to 1 to enable WAL logs at the logical level, typically through a new parameter group. If the source is Cloud SQL, set the cloudsql.logical_decoding and cloudsql.enable_pglogical flags to on. After the instance settings, each database needs its own extension. Run CREATE EXTENSION IF NOT EXISTS pglogical on every database to be migrated, except template0 and template1, plus rdsadmin for Amazon RDS sources.
Exam tip: Tables without primary keys migrate only the initial snapshot and INSERT statements during change data capture; UPDATE and DELETE statements must be migrated manually unless a primary key is added.
The replication access and network path are the shared dependencies that let the log stream travel from source to replica for either database engine. The replication user account lives on the source (opens in a new tab) and must have sufficient permission to allow replication, and the source firewall must allow the replica to reach the source database port. Traffic flows from the replica to the source for log requests, and change data flows back from the source to the replica.
For MySQL, the replication user requires the REPLICATION_SLAVE privilege and is initially configured to accept connections from anywhere with host %, then restricted to the replica outgoing IP address after connectivity is confirmed. For PostgreSQL, the user configured in the connection profile needs privileges on each migrated database and on the default postgres database. When the replica uses private IP, the source firewall must allow the entire internal IP range allocated for private services access on the VPC network that the destination uses in the privateNetwork field of its ipConfiguration settings.
Network topology and security configuration for reverse replication is the design of private, encrypted pathways that carry database writes from Google Cloud back to the original source system. Data flows from the Google Cloud target database through a Virtual Private Cloud (VPC) (opens in a new tab) network and across a hybrid or cross-cloud connection to the source environment, where firewall rules and DNS forwarding control what traffic is allowed. A reliable topology keeps the source in sync after cutover so applications can be pointed back to the source with minimal downtime if the new target has issues.
Secure private connectivity options are the Google Cloud network services that link a VPC network to on-premises data centers or other clouds without using the public internet. Cross-Cloud Interconnect provides direct high-speed links between cloud networks with MACsec encryption and up to 100 Gbps per link. Dedicated Interconnect and Partner Interconnect connect through a colocation facility, while HA VPN (High Availability Virtual Private Network) uses VPN tunnels for lower-bandwidth needs or to get started before upgrading. Private Service Connect (opens in a new tab) exposes a database or service through endpoints and service attachments inside VPC networks.
The choice depends on location, bandwidth, privacy, and the database type. For SQL Server failback, failback migration jobs support only Private Service Connect interfaces for connecting to the original SQL Server instance, so a producer setup with a proxy virtual machine (VM) and service attachment is required for self-hosted, Amazon RDS, and Microsoft Azure sources.
| Connection option | Best for | Key characteristic |
|---|---|---|
| Cross-Cloud Interconnect | High-throughput links between Google Cloud and another cloud provider | Direct connection up to 100 Gbps per link with MACsec encryption |
| Dedicated Interconnect | Private on-premises connectivity with high bandwidth | Requires two connection locations in the same region but different edge availability domains |
| Partner Interconnect | Private on-premises connectivity through a partner facility | Traffic transits Partner Interconnect and then the Google network backbone |
| HA VPN | Lower-bandwidth or initial cross-cloud and hybrid links | VPN tunnels that can also achieve a 99.99% uptime SLA |
Exam tip: SQL Server failback requires Private Service Connect even when the original forward migration used VPN or Interconnect, while PSC-enabled Cloud SQL for SQL Server needs only the service attachment URI from the instance details page.
A Transit VPC is a central VPC network in a hub project that lands external connections and serves as a shared transit path for other VPC networks. Customer locations, application host VPCs, services-access VPCs, and managed services VPCs all connect to the Transit VPC, which then routes traffic between Google Cloud and on-premises or other cloud networks. Network Connectivity Center (NCC) can connect application VPCs as spokes and Interconnect attachments and HA VPN instances as hybrid spokes, while VPC Network Peering can connect application VPCs directly to the transit hub.
Routing across network domains must provide resiliency, throughput, and flow symmetry for stateful inspection. Designs use multiple paths and Cloud Router with Border Gateway Protocol (BGP) to exchange routes between domains:
Duplicate Interconnect connections are configured as active/active with Equal Cost Multi-Path (ECMP) routing to use both paths at once, or as active/passive where one path stands by for failover.
Firewall rules and DNS resolution control whether reverse replication traffic and name lookups can cross the boundary between Google Cloud and the source network. Cloud DNS is authoritative for Google Cloud zones while external servers are authoritative for external zones, so forwarding must be enabled bidirectionally and firewalls must be set to allow DNS resolution traffic. Cloud Next Generation Firewall (Cloud NGFW) policies enforce east-west restrictions between application VPCs and within an NCC hub. In transit cases where external networks communicate through Google Cloud, firewall rules in the Cross-Cloud Network must also allow DNS requests and replies between those external networks.
The hybrid DNS design places an inbound server policy and forwarding zone in the Transit VPC and peering zones in services and application VPCs. On-premises servers forward Google Cloud names to the Cloud DNS inbound forwarding address, while Cloud DNS forwards on-premises names to on-premises servers. Outbound requests from Google to on-premises are sourced from the DNS egress proxy range 35.199.192.0/19, which Cloud Routers advertise to the on-premises network so return traffic can find its path.
Reverse replication over the secure path is the fallback strategy that replicates writes from the new Google Cloud target back to the initial source to keep the source in sync after production cutover. The replication stream flows from the target database through the secure VPC and hybrid connection to the source instance, where it must handle data type changes, reverse any transformations applied during forward migration, and route rows to the correct shard. Choose this approach when the source instance can be kept for some time and the migration uses continuous replication, because the stream cannot be fully tested until after cutover to the target.
Building the flow follows an ordered sequence:
If fallback is needed, the application connection is pointed from the Google Cloud target back to the source database and continues serving requests. Because reverse replication has a small period with no fallback before the stream starts, production cutover should only occur after test results are satisfactory and success criteria and timeboxes are defined for each migration task.
Data integrity and fallback execution is the practice of keeping the original source database synchronized with the Google Cloud target after cutover, so application workloads can be moved back safely if problems occur. Reverse replication (opens in a new tab) replicates writes made on the new cloud target back to the original source, which lets the application point back to the source and continue serving requests without significant downtime. Successful execution depends on monitoring replication health, validating data parity, and only switching traffic when lag is near zero and tests are satisfactory.
A fallback plan defines the action items for rolling back the migration after production cutover if issues appear on the target instance. The fallback plan must be treated as a full database migration, including its own planning, success criteria, timeboxed tasks, and testing. A migration dry run collects expected times for each task, so the team knows when to postpone cutover if a job or backup restore does not finish in the expected window. Production cutover should not occur until test results for both the migration and the fallback are satisfactory, because having no tested fallback can add unforeseen effort and avoidable disruption.
The reverse replication flow from Google Cloud to source reverses the direction of change data capture (CDC) after promotion, where the cloud database becomes the source and the original database becomes the destination. In Database Migration Service (DMS) (opens in a new tab) for AlloyDB for PostgreSQL, the failback job requires access to the original migration job and conversion workspace, and the original and failback jobs must live in the same project that hosts the AlloyDB cluster. In Spanner, the flow reads distributed changes on Spanner and writes them back to the source database.
The high-level Spanner reverse replication flow follows an ordered sequence:
Data flows from the Spanner change stream to the ordering step and then to the source database, so the ordering step depends on complete capture before writes begin. Choose this approach when the original source can be kept for some time and the migration uses continuous replication.
Data integrity during reverse replication means the source receives the same logical writes that were committed on the cloud target, despite schema and platform differences. A reverse replication process needs to handle three integrity requirements:
DMS failback jobs perform only CDC operations with no full backfills, replicate only tables that were part of the original migration job, and do not support schema changes during failback replication. For SQL Server tables without primary keys or unique indexes, DMS replicates in append-only mode where INSERT and UPDATE operations are applied as INSERT, and point-in-time consistency is not maintained because the SQL Server writer bypasses foreign key validation to avoid integrity errors. Failback jobs also do not fire NOT FOR REPLICATION triggers, so triggers and foreign keys must be prepared with that option to prevent violations when parallel changes are applied.
Validation for fallback means confirming replication health and data parity before moving application workloads back to the original source. Replication lag must be monitored until the source has caught up with the target, and data validation checks must confirm that row counts and content match across both systems. The difference between safe and fast return is visible in Cloud SQL (opens in a new tab) advanced disaster recovery, where a switchover keeps the primary in read-only mode until the designated disaster recovery (DR) replica catches up and therefore results in zero data loss, while a replica failover promotes the DR replica immediately and can lose data if lag exists when failover starts. Only after lag is resolved and validation passes should the team point the application back to the source, with IP addresses retained on the instances so the topology can return to its original primary and replica roles.
Exam tip: Reverse replication cannot be fully tested end-to-end until after cutover to the cloud target, while forward replication to a third instance can be fully tested at all times.
Fallback strategies differ by how much downtime they require, whether new writes are preserved, and whether they can be tested before cutover:
| Strategy | How fallback works | Choose when |
|---|---|---|
| Simple fallback | Application switches back to the original source; optionally stop target writes, back up and restore to source | Downtime is acceptable or transactions committed on the target are not needed |
| Reverse replication | Writes on the new target replicate back to the original source after cutover | Source can be kept and migration uses continuous replication, with a brief period of no fallback |
| Forward replication | Writes on the new target replicate to a third instance that can serve reads | Coverage is needed at all times or the initial source must be discarded soon after cutover |
| Duplicate writes | Application writes to both source and target for gradual cutover | No-downtime return is needed and the application can be refactored to write twice |
Simple fallback is the least complex but may lose target writes or require downtime for backup and restore. Reverse replication preserves target writes with minimal downtime on return, forward replication adds continuous coverage through a third copy, and duplicate writes allow instant return at the cost of application refactoring.
wal_level = logical, preloading pglogical, replication slots, and creating the pglogical extension in each migrated database.Last updated