Migration fidelity

When you migrate schema, data, and metadata from a source database to a destination database, you want to ensure that all information is migrated accurately. Database Migration Service provides a high-fidelity mechanism to migrate database objects (including schema, data, and metadata) from one PostgreSQL database to another.

During the migration process, data and constraints are migrated in structured phases. Data is migrated first during the initial snapshot load, and constraints such as primary keys, foreign keys, and indexes are recreated on the destination instance after the initial load completes.

The following data, schema, and metadata components are migrated as part of the database migration:

Data

  • All tables and schemas from databases selected for migration, excluding the following system schemas:

    • The information schema (information_schema)
    • Any schemas beginning with pg_ (for example, pg_catalog, pg_toast)

    For more information about these system schemas, see Known limitations.

    You can add or remove databases from a migration job during an ongoing migration. Removing a database stops replication for that database without deleting the already-migrated database on the destination instance. For more information, see Edit a migration job.

Schema

  • Object naming

  • Primary keys

  • Data types

  • Ordinal column positions

  • Default values

  • Nullability

  • Identity and serial attributes

  • Secondary indexes

Metadata

  • Stored procedures

  • Functions

  • Triggers

  • Views

  • Foreign key constraints

Continuous migration

Data manipulation language (DML) changes (such as INSERT, UPDATE, and DELETE operations on tables with a primary key or replica identity) are automatically replicated during continuous migrations.

If there are any data definition language (DDL) changes on your source database during the migration process, you have to update your destination instance so that your databases remain compatible. You can perform the DDL changes in the following ways:

  1. Manual DDL execution: Stop writes to the source database, run the DDL commands on the source database, and run matching DDL commands on the destination database. Before running DDL commands on the destination, grant the cloudsqlexternalsync role to the Cloud SQL user applying the changes.

  2. Replicated DDL commands with pglogical: You can use pglogical.replicate_ddl_command to run DDL on both the source and destination databases at a consistent point in the replication stream. The user executing this command must have matching credentials on both source and destination instances, and must be a superuser or the owner of the modified object.

The following tabs provide sample SQL commands for common DDL operations using pglogical.replicate_ddl_command:

Tables with primary keys

Add a column to a table with a primary key

SELECT pglogical.replicate_ddl_command(
  'ALTER TABLE [SCHEMA].[TABLE_NAME] ADD COLUMN surname varchar(20)',
  '{default}'
);

Change the name of a table with a primary key

SELECT pglogical.replicate_ddl_command(
  'ALTER TABLE [SCHEMA].[TABLE_NAME] RENAME TO [NEW_NAME_FOR_TABLE]',
  '{default}'
);

Create a table with a primary key

Run the following commands on the source database:

SELECT pglogical.replicate_ddl_command(
  command := 'CREATE TABLE [SCHEMA].[TABLE_NAME] (id INTEGER PRIMARY KEY, name VARCHAR);',
  replication_sets := ARRAY['default']
);
SELECT pglogical.replication_set_add_table('default', '[SCHEMA].[TABLE_NAME]');

Tables without primary keys

Add a column to a table without a primary key

SELECT pglogical.replicate_ddl_command(
  'ALTER TABLE [SCHEMA].[TABLE_NAME] ADD COLUMN surname varchar(20)',
  '{default_insert_only}'
);

Change the name of a table without a primary key

SELECT pglogical.replicate_ddl_command(
  'ALTER TABLE [SCHEMA].[TABLE_NAME] RENAME TO [NEW_NAME_FOR_TABLE]',
  '{default_insert_only}'
);

Create a table without a primary key

Run the following commands on the source database:

SELECT pglogical.replicate_ddl_command(
  command := 'CREATE TABLE [SCHEMA].[TABLE_NAME] (id INTEGER PRIMARY KEY, name VARCHAR);',
  replication_sets := ARRAY['default_insert_only']
);
SELECT pglogical.replication_set_add_table('default_insert_only', '[SCHEMA].[TABLE_NAME]');

What isn't migrated

  • Users and privileges: User login credentials and roles aren't migrated. To configure users on the destination instance, use the Users tab in the Google Cloud console or manage them using a SQL client. For more information, see Create and manage users in the Cloud SQL documentation.

  • Unsupported extensions: Database Migration Service doesn't migrate extensions that are unsupported by Cloud SQL. The presence of these extensions doesn't block the migration job, but applications referencing unsupported extensions might experience errors. For the list of supported extensions, see PostgreSQL extensions in the Cloud SQL documentation.

  • Large objects: PostgreSQL large objects (data type oid referencing pg_largeobject) aren't replicated by logical decoding.

  • Tables without primary keys: Database Migration Service migrates the initial snapshot and replicates INSERT statements during the CDC phase. For UPDATE and DELETE statements:

    • Standard migrations: Standard migrations require that your tables have primary keys to replicate UPDATE and DELETE statements.

    • Quick-start migrations: With quick-start migrations, primary keys aren't required to replicate UPDATE and DELETE operations if you set the replica identity to INDEX or FULL. Configuring REPLICA IDENTITY FULL increases write-ahead log (WAL) volume and resource overhead. Adding a primary key is recommended where feasible. For more information, see the PostgreSQL Replica Identity documentation and Quick-start migrations guide.

  • Materialized views: Database Migration Service migrates materialized view definitions, but doesn't migrate the underlying cached data. To populate materialized views on the destination, run REFRESH MATERIALIZED VIEW view_name after migration.

  • Sequence states: The current values of sequences (such as last_value) on the destination instance might differ from the source during ongoing CDC. Sequence values are synchronized during the final promotion step.

  • Custom tablespaces: Cloud SQL doesn't support custom tablespaces. Objects defined in custom tablespaces are automatically remapped to the default pg_default tablespace in Cloud SQL.

What's next