Known limitations

Before you choose to migrate your databases to Cloud SQL, consider known limitations for this migration scenario.

Note: You can also use Google Cloud Migration Center to discover gaps in feature support. See Discover and import databases in the Migration Center documentation.

Known limitations for using a PostgreSQL database as a source include:

• The pglogical extension doesn't support the replication of generated columns for PostgreSQL 12+.

• Changes to table structures (DDL) aren't replicated through standard DDL commands, but only with commands executed using the pglogical extension used for replication. This includes changes to enum types.

  • For example, pglogical provides the pglogical.replicate_ddl_command function that allows DDL to be run on both the source database and replica at a consistent point.
  • In order to replicate data for newly created tables, you must use the pglogical.replication_set_add_table command to add new tables to existing replication sets.
  • To learn more about DDL replication while the migration is in progress, see Migration fidelity.

• 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. Migrating UPDATE and DELETE statements is different for:

  • Standard migrations: UPDATE and DELETE statements aren't migrated for tables without primary keys. You have to add primary keys for your columns before you migrate, or migrate UPDATE and DELETE statements manually.
  • 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.

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

• The SEQUENCE states (for example, last_value) on the destination might vary from the source during ongoing CDC. Sequence values are synchronized during the final promotion step.

• UNLOGGED and TEMPORARY tables aren't and can't be replicated.

• Large Object data type (oid referencing pg_largeobject) isn't supported by PostgreSQL logical decoding.

• Only extensions that Cloud SQL supports for PostgreSQL can be migrated. 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 verify that your applications don't reference unsupported extensions. For the list of supported extensions, see PostgreSQL extensions in the Cloud SQL documentation.

• The pg_cron extension (or any cron jobs configured within the extension) isn't migrated by Database Migration Service, but is supported in Cloud SQL for PostgreSQL destinations. If you use pg_cron in your source databases, reinstall the extension and configure your cron jobs on the destination instance after the migration is complete.

• Database Migration Service doesn't support migrating from read replicas that are in recovery mode.

• Database Migration Service doesn't support Amazon RDS sources where the AWS SCT extension pack is applied.

• User-defined functions written in C can't be migrated, except for functions that are installed when creating extensions supported by Cloud SQL.

• If other extensions exist in the source database and their versions aren't supported by Cloud SQL, testing or starting the migration job will fail.

• Databases that are added to the source instance after the migration job has started aren't migrated.

• You can select specific databases when you migrate by using Database Migration Service. All tables and schemas from the selected databases are migrated, excluding:

  • The information schema (information_schema).
  • Any schemas beginning with pg_ (for example, pg_catalog, pg_toast, pgtemp*). For the full list of PostgreSQL catalogs that begin with pg_, see PostgreSQL system catalogs in the PostgreSQL documentation.
  • User login credentials and roles.

• If encrypted databases require customer-managed encryption keys (CMEK) to decrypt data, and Database Migration Service doesn't have access to the keys, the databases can't be migrated. However, data encrypted with the pgcrypto extension can be migrated because Cloud SQL supports pgcrypto. Database Migration Service also supports migrating from encrypted Amazon Aurora or Amazon RDS databases because AWS handles decryption transparently. For more information, see Encrypting Amazon Aurora resources and Encrypting Amazon RDS resources.

• Destination database operational behavior during migration:

  • Writable destination database: Cloud SQL for PostgreSQL doesn't run the destination database in read-only mode during migration. The destination instance isn't demoted when created by Database Migration Service.

  • Avoid write operations on migrating data: Don't perform write operations (such as INSERT, UPDATE, DELETE, or DDL schema changes) on tables or databases participating in the migration. Executing writes on migrating data can result in replication conflicts with the incoming change data capture (CDC) stream.

  • Manage database users: You can create and manage database users at any time using standard psql commands or through Cloud SQL in the Google Cloud console. For more information, see Manage users in the Cloud SQL documentation.

  • Suspended features: Automated backups and point-in-time recovery (PITR) are suspended during continuous replication. In addition, you can't create Cloud SQL read replicas from the destination instance until the migration job is promoted.

• Trigger behavior depends on trigger configuration. By default, triggers don't fire on the replica during replication unless configured with ALTER EVENT TRIGGER or ALTER TABLE ... ENABLE ALWAYS TRIGGER.

• Functions defined with SECURITY DEFINER are created by cloudsqlexternalsync on the destination Cloud SQL instance, which holds elevated privileges. Restrict execution privileges on sensitive SECURITY DEFINER functions by revoking public permissions and granting access selectively.

• Cloud SQL doesn't support custom tablespaces. All objects inside custom tablespaces are migrated to the default pg_default tablespace in Cloud SQL.

• The Private Service Connect interfaces connectivity method is only supported for migrating to existing destination instances. If you want to use private IP connectivity and migrate to a new destination instance, use VPC peering.

• Identity and Access Management database authentication for destination instances or clusters isn't supported. Database Migration Service connects to the destination database using database user authentication. For more information about IAM, see IAM authentication in the Cloud SQL documentation.

Limitations for migrations to existing destination instances

• Destination instance demotion for standard migrations: For standard migrations to existing Cloud SQL instances, Database Migration Service demotes the standalone instance to configure it as an external replica of the source database.
  • Demoting the instance doesn't place the PostgreSQL engine into read-only mode, but you must avoid write operations on migrating tables to prevent replication conflicts.
  • Demoting the instance suspends automated backups and point-in-time recovery (PITR) and prevents adding Cloud SQL read replicas until promotion.
  • You must confirm demotion when creating the migration job in the Google Cloud console by entering the destination instance ID (or by running gcloud database-migration migration-jobs demote-destination).
  • Quick-start migrations use PostgreSQL pgnative logical replication and don't require or perform destination instance demotion.
• Your existing destination instance can only contain default system databases that are set up automatically when you create the destination instance. Migrating to existing destination instances that contain user data isn't supported.

  If you encounter issues due to extra data in your existing destination instance, clear the databases in your destination instance and retry the migration job. See Clear extra data from your existing destination instance.

• You can configure only one migration job per destination instance.
• You can only migrate to standalone Cloud SQL instances. Migrating to external server replicas isn't supported.
• After you promote an instance, you must turn on point-in-time recovery.
• If your instance has customized backup settings (for example, a custom backup location), you have to restore your backup settings again after promotion. During the promotion process, Cloud SQL resets backup settings to their default values.
• For Terraform users: Database Migration Service modifies the backup and recovery settings of your destination instance. This might cause the destination instance settings to differ from the Terraform configuration used for provisioning. If you experience this issue, see the guidance in Diagnose issues.

Quotas

• Up to 2,000 connection profiles and 1,000 migration jobs can exist at any given time. To create space for more, delete unused migration jobs and connection profiles.

Data dump parallelism considerations

When you create a migration job, you can increase the number of parallel subscriptions to accelerate the initial data dump and ongoing change data capture (CDC) replication. Using higher data dump parallelism settings also increases connection and resource utilization on both your source database and your destination Cloud SQL instance.

• Ensure that your source database has sufficient CPU, memory, and I/O headroom before you use Maximum parallelism.
• When using adjusted data dump parallelism, you must configure higher values for max_replication_slots, max_wal_senders, and max_worker_processes on your source database. For detailed parameter calculations, see Configure your source database.
• The pglogical extension replicates data at the table level during the initial full dump. A single non-partitioned table is assigned to one subscription and isn't split across multiple parallel subscriptions or worker threads.
• The pglogical extension replicates individual leaf partitions as separate tables and can distribute multiple leaf partitions across parallel subscriptions during the initial full dump. A single large leaf partition can't be split across multiple subscriptions and is copied by a single thread.
• Database Migration Service sorts tables by size and only adds parallel subscriptions when it's possible to reduce the total data volume of the largest subscription. Database Migration Service creates up to 2 subscriptions per database for Optimal parallelism and up to 4 subscriptions per database for Maximum parallelism, apportioned by database size from a target pool of 10 subscriptions across all databases (with at least 1 subscription per migrated database). If a database has heavily skewed table sizes, Database Migration Service allocates fewer subscriptions than the maximum cap. For example, if a database contains one 1 TB table and several small tables, Database Migration Service isolates the 1 TB table in one subscription and groups the smaller tables into a second subscription. In this scenario, Database Migration Service uses only 2 subscriptions even when parallelism is set to Maximum.
• Database Migration Service assigns tables without primary keys to dedicated insert-only replication sets (one per parallel subscription). Multiple tables without primary keys can be distributed across parallel subscriptions, but during the initial full dump each individual table is copied as a single table stream within one subscription and isn't split across multiple parallel threads.

PostgreSQL extension version compatibility during major version upgrades

When you migrate to a higher PostgreSQL major version, Database Migration Service uses the pg_restore utility, which installs the default extension version of the target PostgreSQL major version. You can't specify an exact extension version for the pg_restore utility.

If your source database uses an extension version that is schema-incompatible with the default version installed on the target PostgreSQL major version, the migration might fail due to a schema mismatch or internal errors.

To avoid migration failures, verify schema compatibility. Before you migrate, upgrade the extensions on your source database to a version compatible with the target PostgreSQL major version.