Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/database-migration/docs/postgres/migration-fidelity below:

Migration fidelity | Database Migration Service

Stay organized with collections Save and categorize content based on your preferences.

When you're migrating your schema, data, and metadata from a source database to a destination database, you want to ensure that all of this information is migrated accurately. Database Migration Service provides a high-fidelity way to migrate database objects (including the schema, data, and metadata) from one database to another.

During the migration process, data and constraints are migrated separately. Data is migrated first, and constraints such as primary keys, foreign keys, and indexes are recreated on the instance after the initial full dump and load.

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

• All tables from all databases and schemas, excluding the following schemas:

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

  For more information about these schemas, see Known limitations.

• Naming

• Primary key

• Data type

• Ordinal position

• Default value

• Nullability

• Auto-increment attributes

• Secondary indexes

• Stored procedures

• Functions

• Triggers

• Views

• Foreign key constraints

Only data manipulation language (DML) changes are updated automatically during continuous migrations. Managing data definition language (DDL) changes so that the source and destination databases remain compatible is the responsibility of the user, and can be achieved in two ways:

1. Stopping writes to the source and running the DDL commands in both source and destination. Before running DDL commands on the destination, grant cloudsqlexternalsync to the Cloud SQL user applying the DDL changes. To enable querying or changing the data, grant the cloudsqlexternalsync role to the relevant Cloud SQL users.
2. Using the pglogical.replicate_ddl_command to allow DDL to be run on the source and destination at a consistent point. 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 (for example, the table, sequence, view, or database).

   Here are a few examples of using the pglogical.replicate_ddl_command.

   Replace:

   • [SCHEMA] with the name of the table schema you want to use
   • [TABLE_NAME] with the table name
   • [NEW_NAME_FOR_TABLE] with the new name for the table when when performing the rename operation
   Add a column to a database table with a primary key

   select pglogical.replicate_ddl_command(
     'ALTER TABLE [SCHEMA].[TABLE_NAME] add column surname varchar(20)',
     '{default}'
   );

   Add a column to a database 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 database table with a primary key

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

   Change the name of a database 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 database table with a primary key

   Run the following commands:

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

   2. select pglogical.replication_set_add_table('default', '[SCHEMA].[TABLE_NAME]');

   Create a database table without a primary key

   Run the following commands:

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

   2. select pglogical.replication_set_add_table(
        'default_insert_only', '[SCHEMA].[TABLE_NAME]'
      );

• To add users to a Cloud SQL destination instance, navigate to the instance and add users from the Users tab, or add them from a PostgreSQL client. Learn more about creating and managing PostgreSQL users.

• Database Migration Service doesn't migrate extensions that are unsupported by Cloud SQL. The presence of these extensions doesn't block the migration, but to ensure a smooth migration process verify that your objects or applications don't reference any unsupported extensions. We recommend removing these extensions and references from your source database before you proceed.

• Large objects can't be replicated, as PostgreSQL's logical decoding facility does not support decoding changes to large objects. For tables that have column type oid referencing large objects, the rows are synced, and new rows are replicated. However, trying to access the large object on the destination database (read using lo_get, export using lo_export, or check the catalog pg_largeobject for the given oid), fails with a message saying that the large object does not exist.

• 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. You should migrate UPDATE and DELETE statements manually.

• Database Migration Service doesn't migrate data from materialized views, just the view schema. To populate the views, run the following command: REFRESH MATERIALIZED VIEW view_name.

• The SEQUENCE states (for example, last_value) on the new destination might vary from the source SEQUENCE states.

• Customized tablespaces aren't supported in the destination Cloud SQL instance. All the data inside customized tablespaces is migrated to the default pg_default tablespace in Cloud SQL.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-07-09 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-07-09 UTC."],[[["Database Migration Service ensures high-fidelity migration of database objects, including schema, data, and metadata, from source to destination databases."],["During migration, data is migrated first, followed by the recreation of constraints such as primary keys, foreign keys, and indexes on the destination instance."],["Continuous migration automatically updates data manipulation language (DML) changes, while data definition language (DDL) changes require manual management or the use of `pglogical.replicate_ddl_command` to maintain compatibility."],["Certain components are not migrated, such as unsupported Cloud SQL extensions, large objects, `UPDATE` and `DELETE` statements for tables without primary keys, and data from materialized views, where users will have to refresh it on the destination end."],["All data in customized tablespaces will migrate to the default `pg_default` tablespace in Cloud SQL, and `SEQUENCE` states may differ from the source to the destination."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4