Migrate PostgreSQL to Cloud SQL With Database Migration Service

To migrate PostgreSQL to Cloud SQL with Database Migration Service (DMS), you configure logical replication on the source with the pglogical extension, open a connectivity path, run a continuous migration job that does an initial dump then streams changes, wait for replication lag to drop to seconds, stop writes on the source, and promote. The mechanism is native PostgreSQL logical replication, so the parts that logical replication cannot carry (sequence positions, large objects, tables without a primary key) are exactly the parts that will bite you at cutover. Everything below is about getting those right.

This is written for teams moving a self-managed or cloud-hosted PostgreSQL instance to Cloud SQL for PostgreSQL with a downtime window measured in minutes rather than hours.

What to check before you change anything

DMS does not copy your database with pg_dump and walk away. <cite index="0-6">It relies on pglogical for the migration work, which means the pglogical extension has to be installed on each of the databases you want to migrate.</cite> That single fact drives every prerequisite.

Check these on the source before you create anything in Google Cloud:

• The pglogical extension is available and installed in every database you plan to migrate, not just the default one.
• wal_level is set to logical. Without it, logical decoding does not run and the job never leaves the "starting" state.
• max_replication_slots and max_wal_senders have headroom. <cite index="2-4,2-5">Database Migration Service requires one slot for each database that's migrated, so if there are 5 databases and 2 migration jobs created for the source, the number of replication slots must be at least 5 * 2 = 10, plus reserves for table synchronization.</cite>
• Every table you care about has a primary key. This is the one that quietly corrupts a migration. More on it below.

Run an inventory query first so you are not guessing:

-- Tables with no primary key: these will not replicate UPDATE/DELETE
SELECT n.nspname AS schema, c.relname AS table
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog','information_schema')
  AND NOT EXISTS (
    SELECT 1 FROM pg_constraint k
    WHERE k.conrelid = c.oid AND k.contype = 'p'
  );

If that query returns rows, decide what to do about each one before you build the job, not during the CDC phase.

Configure the source database

The source parameters are the same idea across a self-managed instance, Amazon RDS, and Azure Database for PostgreSQL, only the mechanism to set them differs. On a managed source, <cite index="2-0,2-1,2-2,2-3">you set shared_preload_libraries to include pglogical, set wal_level to logical, and set max_replication_slots to at least the number of subscriptions expected to connect, plus reserves for table synchronization.</cite> On RDS you do this through a parameter group; on Azure you set the server parameters and add pglogical to azure.extensions.

Then create a dedicated migration user with replication privileges and grant it read on the schemas being moved. Keep it separate from your application role so you can revoke it cleanly after cutover.

One subtlety that surprises people: DDL is not replicated automatically. If you alter a table during the migration window, you run it through pglogical so both sides stay consistent. <cite index="0-0">The user running this command must have the same username on both the source and the destination, and should be the superuser or the owner of the artifact being migrated.</cite> In practice, freeze schema changes for the duration of the migration. It is cheaper than reasoning about DDL replication under load.

Choose a connectivity method

DMS has to reach your source from inside Google Cloud, and the method you pick affects security and how much you have to expose. <cite index="6-0,6-1,6-2,6-3">IP allowlist describes connectivity to the public IP of your source database, forward-SSH tunnel provides a dedicated SSH tunnel, Private Service Connect interfaces connect to your source private IP with network attachments, and VPC peering connects to the private IP of your source over peered VPC networks.</cite>

For anything carrying real data, prefer the private path. An IP allowlist is fine for a throwaway test but means a public database endpoint for the life of the job, even if scoped to one egress address.

What Database Migration Service does not migrate

This is the section to read twice, because DMS reports the job as healthy while leaving these gaps. The trade-off you are accepting by using logical replication is fidelity on exactly these objects, and the cost lands after cutover when the application misbehaves and the migration job is already gone.

Tables without a primary key

<cite index="7-2">For tables that don't have primary keys, Database Migration Service supports migration of the initial snapshot and INSERT statements during the change data capture (CDC) phase, and you should migrate UPDATE and DELETE statements manually.</cite> Read that plainly: on a primary-key-less table, updates and deletes made on the source during the window silently do not reach Cloud SQL. Add a primary key or a replica identity before you start, or plan to reconcile that table by hand at cutover.

Sequences

<cite index="0-3">The SEQUENCE states (for example, last_value) on the new Cloud SQL destination might vary from the source SEQUENCE states.</cite> If you promote and let the application write without fixing this, your next INSERT can collide with an existing ID. After the source is frozen, read last_value from each sequence on the source and setval it on the destination before any application traffic lands.

Large objects, materialized views, users

<cite index="0-1">Large objects can't be replicated because PostgreSQL's logical decoding facility doesn't support decoding changes to large objects.</cite> If you use pg_largeobject, that data needs its own copy step. <cite index="7-3">Database Migration Service doesn't migrate data from materialized views, just the view schema, so you run REFRESH MATERIALIZED VIEW on the destination to populate them.</cite> And <cite index="7-6">information about users and user roles isn't migrated</cite>, so recreate roles and grants on the destination yourself. Planning a database move end to end, including these edges, is what our cloud migration service exists to de-risk.

Speeding up the initial dump for a large database

For a multi-hundred-gigabyte database, the initial dump and index rebuild dominates the timeline. Two levers help. First, memory for index and constraint rebuilds: <cite index="4-0">maintenance_work_mem should be set much higher than the default of 64 MB, and in past experiments setting it to 1 GB gave good results.</cite> Second, parallelism and dump speed: <cite index="4-1">the default migration option is OPTIMAL, which offers balanced performance with optimal load on the source, and MAXIMUM provides the highest dump speeds if you want to further improve migration speed.</cite> MAXIMUM pushes harder on the source, so do not point it at a database that is already near its I/O ceiling during business hours.

The cutover: promote without losing writes

The job runs in two phases. It completes a full dump and load, then enters continuous change data capture (CDC). <cite index="1-2,1-3">After the full dump phase completes and the job is in CDC phase, the promotion option becomes available; immediately after transitioning to CDC there may be a long replication delay while Cloud SQL catches up on changes that occurred during the dump and load.</cite>

Do not promote into that lag. <cite index="1-4,1-5">Wait for the replication delay to trend down significantly, ideally on the order of minutes or seconds, which you can review on the migration job page.</cite> Then run the cutover in this order:

1. Put the application into maintenance mode and stop all writers. <cite index="1-6,1-7">To avoid data loss, stop all writes, running scripts, and client connections to the source database.</cite>
2. Watch replication lag reach zero, confirming every last change reached Cloud SQL.
3. Fix the objects DMS left behind: setval on sequences, reconcile any primary-key-less tables, REFRESH MATERIALIZED VIEW, confirm roles and grants exist.
4. Promote. <cite index="1-8,1-9">The destination instance is promoted to a primary writeable instance and the migration job status becomes Completed.</cite>
5. Repoint the application at Cloud SQL and lift maintenance mode.

Keep the source read-only but alive for a while. There is a specific failure mode if you tear it down too early: <cite index="8-0,8-1">when promoting, if the source instance isn't reachable from the Cloud SQL instance, the replication settings can't be cleaned up during promotion, and you must clean up the replication slots manually.</cite> Leaving the source reachable through promotion lets DMS clean up after itself.

Verify before you call it done

DMS gives you a live source and a live destination that were in sync moments ago, which is the ideal condition to validate. Compare row counts per table, spot-check recently written rows, and confirm sequence values and grants on the destination. Downstream systems that read from this database, replicas, ETL jobs, and analytics loads, need repointing too; getting those to agree with the new primary is data engineering work worth scheduling into the same window rather than discovering the morning after.

Frequently asked questions

Does Database Migration Service cost extra for PostgreSQL to Cloud SQL?

For like-to-like migrations into Cloud SQL, Google states the destination database is ready after cutover without extra steps and <cite index="5-1">at no additional charge</cite> for the migration itself. You still pay for the Cloud SQL destination instance and any networking you provision. Budget for running the source and destination in parallel through the validation window.

How much downtime does the cutover actually take?

The write freeze lasts from when you stop the source until you promote and repoint the application. If you have already reached the CDC phase with lag near zero, that window is the time to drain the last changes and fix sequences and other skipped objects, which is typically minutes for a well-prepared database. The long, variable part, the initial dump, happens while the source stays fully online.

What happens to tables without a primary key?

They migrate their initial snapshot and new inserts, but updates and deletes during the CDC phase do not replicate and must be handled by hand. The clean fix is to add a primary key or set a replica identity on those tables before the migration starts. If you cannot change the schema, plan to re-copy those specific tables during the write freeze.

Can I migrate only some tables or schemas from a database?

No. DMS migrates all tables and schemas from the selected databases, excluding the internal information_schema and pg_* schemas. If you need to leave data behind, drop or exclude it at the source before migrating, or clean it up on the destination after cutover.

Do I need pglogical if I only want a one-time copy?

Yes, for a DMS migration job. The service is built on pglogical for both the initial load and the ongoing change capture, so the extension must be present on every source database. If you genuinely only want a static one-time copy with no continuous replication, a plain pg_dump and pg_restore is simpler, but you lose the minimal-downtime cutover DMS is designed for.