Migration flow

For homogeneous PostgreSQL migrations to Cloud SQL, Database Migration Service connects to the destination Cloud SQL for PostgreSQL instance and manages the continuous migration process on its behalf. The destination instance establishes the connection to your source database, while Database Migration Service orchestrates and monitors the end-to-end data pipeline.

For standard homogeneous PostgreSQL migrations to Cloud SQL, Database Migration Service supports continuous migrations. In a continuous migration, initial data is loaded from a snapshot dump, and then ongoing data changes are continuously replicated from the source database's write-ahead log (WAL) using logical decoding and the pglogical extension. For continuous quick-start migrations, Database Migration Service uses pgnative.

Continuous migration phases

Continuous migrations progress through the following sequential phases:

Standard migrations

  1. Replication setup and orchestration: When you create the migration job, Database Migration Service configures the destination Cloud SQL for PostgreSQL instance to act as a replica and establishes replication subscriptions with the pglogical extension. When migrating to an existing Cloud SQL instance, Database Migration Service demotes the instance to configure it as an external replica (without placing the database in read-only mode).
  2. Initial snapshot phase: When you start the migration job, Database Migration Service takes a consistent snapshot of the selected databases on the source instance. This process takes a brief table lock (typically under 10 seconds per table) while the snapshot is initialized. The source database continues to accept reads and writes throughout the process.
  3. Data load phase: Database Migration Service streams the initial snapshot data from the source database and loads it into the destination Cloud SQL for PostgreSQL instance.
  4. Constraint and index creation: After the initial data load finishes, table constraints (including primary keys and foreign keys) and secondary indexes are created on the destination instance. Separating data loading from constraint creation optimizes data ingestion speed.
  5. Change data capture (CDC) phase: Once constraints are applied, Database Migration Service enters the continuous CDC phase. During CDC, Database Migration Service captures ongoing DML changes from the source database's logical replication stream using pglogical and applies them to the destination instance in near real time.
  6. Cutover and promotion phase: When you are ready to switch your application traffic to the destination instance, stop writes to the source database, verify that the replication lag reaches zero, and initiate promotion in Database Migration Service. During promotion, Database Migration Service disconnects the destination instance from the source, removes replication slots, and transforms the destination into a standalone, read/write Cloud SQL for PostgreSQL primary instance.

Quick-start migrations

  1. Replication setup and orchestration: When you create the migration job, Database Migration Service automatically sets up network connectivity over private IPs using Private Service Connect interfaces (automatically creating private connectivity configurations, network attachments, and subnets). It establishes replication between the source and destination databases using PostgreSQL logical replication (with pgnative), without requiring pglogical extension installation on the source or demoting the destination instance. The destination can be an active instance serving traffic to other databases.
  2. Initial snapshot phase: When you start the migration job, Database Migration Service takes a consistent snapshot of the selected databases (migrating up to 50 databases per job) on the source database server. The source database continues to accept reads and writes throughout the process.
  3. Data load phase: Database Migration Service streams the initial snapshot data directly into the destination Cloud SQL for PostgreSQL instance over private connectivity. Destination database objects are assigned ownership to the migration user generated by Database Migration Service (when using the Google Cloud console) or specified with Google Cloud CLI.
  4. Constraint and index creation: After the initial data load finishes, table constraints and secondary indexes are created on the destination instance to ensure schema fidelity while maximizing ingestion performance.
  5. Change data capture (CDC) phase: Once constraints are applied, Database Migration Service enters the continuous CDC phase. During CDC, Database Migration Service streams ongoing DML changes from the pgnative PostgreSQL logical replication stream and applies them to the destination instance in near real time. For tables without primary keys, UPDATE and DELETE operations can be replicated when the table's replica identity is configured to INDEX or FULL.
  6. Cutover and promotion phase: When you are ready to switch your application traffic to the destination instance, stop writes to the source database, verify that replication lag reaches zero, and initiate promotion in Database Migration Service. During promotion, Database Migration Service finalizes CDC replication, terminates the replication slots, and detaches the migration pipeline, leaving your destination Cloud SQL for PostgreSQL databases fully operational for client connections.

What's next