Known limitations

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

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. The user running this command on the source must already exist on the replica.
    • 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.

  • Object limitations:

    • Foreign tables aren't replicated by logical replication.
    • 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.
    • 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.

  • 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 must 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.

  • Only extensions and extension versions that AlloyDB for PostgreSQL supports for PostgreSQL can be migrated. Database Migration Service doesn't migrate extensions that are unsupported by AlloyDB for PostgreSQL, and if extensions exist in the source database whose versions aren't supported by AlloyDB for PostgreSQL, testing or starting the migration job might fail. To ensure a smooth migration, verify that your source extensions are supported and that your applications don't reference unsupported extensions. For the list of supported extensions, see Supported extensions in the AlloyDB for PostgreSQL documentation.

  • The pg_cron extension (or any cron jobs configured within the extension) isn't migrated by Database Migration Service, but is supported in AlloyDB for PostgreSQL destinations. If you use pg_cron in your source databases, reinstall the extension and configure your cron jobs on the destination cluster 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 AlloyDB for PostgreSQL.

  • 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 AlloyDB for PostgreSQL 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.

  • Migrating databases aren't write-protected during migration: AlloyDB for PostgreSQL doesn't run the destination instance in read-only mode during migration. Although you can connect to the destination cluster to create and manage database users or apply necessary schema changes (DDL), don't perform write operations (such as INSERT, UPDATE, or DELETE) on databases or tables that are being migrated. Writing to migrating data can cause replication conflicts or migration failures.

  • 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.

  • Custom tablespaces are mapped to the default pg_default tablespace in AlloyDB for PostgreSQL.

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

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

Security definer functions

Functions defined with SECURITY DEFINER run with the privileges of the user that owns the function rather than the user who executes it.

During continuous migration to AlloyDB for PostgreSQL, routines defined with SECURITY DEFINER become owned by the internal migration service user alloydbexternalsync on the destination primary instance. Because alloydbexternalsync possesses elevated administrative privileges, including the alloydbsuperuser and alloydbreplica roles, any user authorized to execute these functions runs them with full alloydbsuperuser rights. This creates an unintended privilege escalation risk.

To remediate this risk, audit all SECURITY DEFINER functions on your destination cluster and revoke public execution privileges immediately after migration:

REVOKE EXECUTE ON FUNCTION SCHEMA_NAME.FUNCTION_NAME(ARGUMENT_TYPES) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION SCHEMA_NAME.FUNCTION_NAME(ARGUMENT_TYPES) TO "AUTHORIZED_USER";

Collation and locale alignment

AlloyDB for PostgreSQL defaults to C.UTF-8 and en_US.utf8 locales provided by libc. All other locales in AlloyDB for PostgreSQL use International Components for Unicode (ICU) collations.

If your source database uses a default collation based on another libc locale, that default doesn't automatically carry over. To ensure consistent sorting for queries using ORDER BY, you might need to update your tables with explicit ICU collations after migration. For more information, see Supported locales for imported data in the AlloyDB for PostgreSQL documentation.

Limitations for migrations to existing destination clusters

  • You can configure only one active migration job per destination cluster.
  • Destination cluster demotion for standard migrations: For standard migrations to existing AlloyDB for PostgreSQL clusters, Database Migration Service demotes the standalone cluster to configure it as an external replica of the source database.
    • You must confirm demotion when creating the migration job in the Google Cloud console by entering the destination cluster 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 cluster demotion.
  • For standard migrations, your existing destination cluster must be empty or contain only default system configuration data. Migrating to an existing destination cluster that contains user data (such as tables) isn't supported for standard migrations.

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

  • Migrating to clusters with read pool instances is supported. For more information on AlloyDB for PostgreSQL clusters and instances, see AlloyDB for PostgreSQL overview.

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 AlloyDB for PostgreSQL cluster.

  • 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 distributes them across parallel subscriptions. Database Migration Service creates up to 2 subscriptions per database (and up to 2 total across all databases) for Optimal parallelism, and up to 4 subscriptions per database (and up to 4 total across all databases) for Maximum parallelism, with at least 1 subscription per migrated database. For example, if a database has one 1 TB table and several small tables, Database Migration Service isolates the 1 TB table in one subscription and distributes the smaller tables across the remaining subscriptions. Even with multiple subscriptions active, the overall initial dump speed is still bounded by the time required to copy the single 1 TB table.
  • 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.

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. For more details, see Quotas and limits.

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.

What's next