从 PostgreSQL 联机迁移到 Azure DB for PostgreSQL 时的已知问题/迁移限制Known issues/migration limitations with online migrations from PostgreSQL to Azure DB for PostgreSQL

以下部分描述了在从 PostgreSQL 联机迁移到 Azure Database for PostgreSQL 时存在的已知问题和限制。Known issues and limitations associated with online migrations from PostgreSQL to Azure Database for PostgreSQL are described in the following sections.

联机迁移配置Online migration configuration

  • 源 PostgreSQL 服务器必须运行版本 9.4、9.5、9.6、10 或 11。The source PostgreSQL server must be running version 9.4, 9.5, 9.6, 10, or 11. 有关详细信息,请参阅支持的 PostgreSQL 数据库版本一文。For more information, see the article Supported PostgreSQL Database Versions.

  • 仅支持迁移到同一版本或更高版本。Only migrations to the same or a higher version are supported. 例如,支持将 PostgreSQL 9.5 迁移到 Azure Database for PostgreSQL 9.6 或 10,但不支持从 PostgreSQL 11 迁移到 PostgreSQL 9.6。For example, migrating PostgreSQL 9.5 to Azure Database for PostgreSQL 9.6 or 10 is supported, but migrating from PostgreSQL 11 to PostgreSQL 9.6 isn't supported.

  • 若要在源 PostgreSQL postgresql.config 文件中启用逻辑复制,请设置以下参数 :To enable logical replication in the source PostgreSQL postgresql.conf file, set the following parameters:

    • wal_level = logical wal_level = logical
    • max_replication_slots = [要迁移的数据库最大数目的下限];如果要迁移四个数据库,请将该值设置为至少为 4。max_replication_slots = [at least max number of databases for migration]; if you want to migrate four databases, set the value to at least 4.
    • max_wal_senders = [并发运行的数据库数];建议值为 10 max_wal_senders = [number of databases running concurrently]; the recommended value is 10
  • 向源 PostgreSQL pg_hba.conf 添加 DMS 代理 IPAdd DMS agent IP to the source PostgreSQL pg_hba.conf

    1. 预配 Azure 数据库迁移服务后,记下 DMS IP 地址。Make a note of the DMS IP address after you finish provisioning an instance of Azure Database Migration Service.

    2. 向 pg_hba.conf 文件添加 IP 地址,如下所示:Add the IP address to the pg_hba.conf file as shown:

          host  all     172.16.136.18/10    md5
          host  replication postgres    172.16.136.18/10    md5
      
  • 用户必须在托管源数据库的服务器上具有“复制”角色。The user must have the REPLICATION role on the server hosting the source database.

  • 源数据库架构和目标数据库架构必须匹配。The source and target database schemas must match.

  • 目标 Azure Database for PostgreSQL 单一服务器中的架构不能包含外键。The schema in the target Azure Database for PostgreSQL-Single server must not have foreign keys. 使用以下查询来删除外键:Use the following query to drop foreign keys:

                                SELECT Queries.tablename
           ,concat('alter table ', Queries.tablename, ' ', STRING_AGG(concat('DROP CONSTRAINT ', Queries.foreignkey), ',')) as DropQuery
                ,concat('alter table ', Queries.tablename, ' ', 
                                                STRING_AGG(concat('ADD CONSTRAINT ', Queries.foreignkey, ' FOREIGN KEY (', column_name, ')', 'REFERENCES ', foreign_table_name, '(', foreign_column_name, ')' ), ',')) as AddQuery
        FROM
        (SELECT
        tc.table_schema, 
        tc.constraint_name as foreignkey, 
        tc.table_name as tableName, 
        kcu.column_name, 
        ccu.table_schema AS foreign_table_schema,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name 
    FROM 
        information_schema.table_constraints AS tc 
        JOIN information_schema.key_column_usage AS kcu
          ON tc.constraint_name = kcu.constraint_name
          AND tc.table_schema = kcu.table_schema
        JOIN information_schema.constraint_column_usage AS ccu
          ON ccu.constraint_name = tc.constraint_name
          AND ccu.table_schema = tc.table_schema
    WHERE constraint_type = 'FOREIGN KEY') Queries
      GROUP BY Queries.tablename;
    
    

    运行查询结果中的 drop foreign key(第二列)。Run the drop foreign key (which is the second column) in the query result.

  • 目标 Azure Database for PostgreSQL 单一服务器中的架构不能包含任何触发器。The schema in target Azure Database for PostgreSQL-Single server must not have any triggers. 若要禁用目标数据库中的触发器,请使用以下设置:Use the following to disable triggers in target database:

    SELECT Concat('DROP TRIGGER ', Trigger_Name, ';') FROM  information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_schema';
    

数据类型限制Datatype limitations

限制:如果表中没有主键,则所做的更改可能不会同步到目标数据库。Limitation: If there's no primary key on tables, changes may not be synced to the target database.

解决方法:暂时为表设置一个主键,以便迁移能够继续。Workaround: Temporarily set a primary key for the table for migration to continue. 数据迁移完成后,可以删除该主键。You can remove the primary key after data migration is complete.

从 AWS RDS PostgreSQL 联机迁移时的限制Limitations when migrating online from AWS RDS PostgreSQL

尝试从 AWS RDS PostgreSQL 联机迁移到 Azure Database for PostgreSQL 时,你可能会遇到以下错误。When you try to perform an online migration from AWS RDS PostgreSQL to Azure Database for PostgreSQL, you may encounter the following errors.

  • 错误:数据库“{database}”的表“{table}”中的列“{column}”的默认值在源服务器和目标服务器上有所不同。Error: The Default value of column '{column}' in table '{table}' in database '{database}' is different on source and target servers. 在源服务器上,值为“{value on source}”,而在目标服务器上,值则为“{value on target}”。It's '{value on source}' on source and '{value on target}' on target.

    限制:如果列架构上的默认值在源数据库与目标数据库之间有所不同,则会出现此错误。Limitation: This error occurs when the default value on a column schema is different between the source and target databases. 解决方法:确保目标上的架构与源上的架构匹配。Workaround: Ensure that the schema on the target matches schema on the source. 有关迁移架构的详细信息,请参阅 Azure PostgreSQL 联机迁移文档For detail on migrating schema, refer to the Azure PostgreSQL online migration documentation.

  • 错误:目标数据库“{database}”包含“{number of tables}”个表,而源数据库“{database}”包含“{number of tables}”个表。Error: Target database '{database}' has '{number of tables}' tables where as source database '{database}' has '{number of tables}' tables. 源数据库和目标数据库中表的数目应当匹配。The number of tables on source and target databases should match.

    限制:当源数据库与目标数据库的表数不同时,将出现此错误。Limitation: This error occurs when the number of tables is different between the source and target databases.

    解决方法:确保目标上的架构与源上的架构匹配。Workaround: Ensure that the schema on the target matches schema on the source. 有关迁移架构的详细信息,请参阅 Azure PostgreSQL 联机迁移文档For detail on migrating schema, refer to the Azure PostgreSQL online migration documentation.

  • 错误: 源数据库 {database} 为空Error: The source database {database} is empty.

    限制:当源数据库为空时,会出现此错误。Limitation: This error occurs when the source database is empty. 这最有可能是因为你选择了错误的数据库作为源数据库。It is most likely because you have selected the wrong database as source.

    解决方法:反复检查选择迁移的源数据库,然后重试。Workaround: Double-check the source database you selected for migration, and then try again.

  • 错误: 目标数据库 {database} 为空。Error: The target database {database} is empty. 请迁移架构。Please migrate the schema.

    限制:当目标数据库上没有架构时,会出现此错误。Limitation: This error occurs when there's no schema on the target database. 确保目标上的架构与源上的架构匹配。Make sure schema on the target matches schema on the source. 解决方法:确保目标上的架构与源上的架构匹配。Workaround: Ensure that the schema on the target matches schema on the source. 有关迁移架构的详细信息,请参阅 Azure PostgreSQL 联机迁移文档For detail on migrating schema, refer to the Azure PostgreSQL online migration documentation.

其他限制Other limitations

  • 数据库名称不能包含分号 (;)。The database name can't include a semi-colon (;).
  • 已捕获的表必须包含主键。A captured table must have a Primary Key. 如果某个表没有主键,则删除和更新记录操作的结果将不可预测。If a table doesn't have a primary key, the result of DELETE and UPDATE record operations will be unpredictable.
  • 忽略更新主键段。Updating a Primary Key segment is ignored. 在这种情况下,应用此类更新将被目标标识为未更新任何行的更新,并且将导致向异常表写入记录。In such cases, applying such an update will be identified by the target as an update that didn't update any rows and will result in a record written to the exceptions table.
  • 迁移名称相同但包含不同案例(例如,table1、TABLE1 和 Table1)的多个表可能导致不可预测的行为,因此不支持。Migration of multiple tables with the same name but a different case (e.g. table1, TABLE1, and Table1) may cause unpredictable behavior and is therefore not supported.
  • 不支持更改 [CREATE | ALTER | DROP | TRUNCATE] table DDL 的处理。Change processing of [CREATE | ALTER | DROP | TRUNCATE] table DDLs isn't supported.
  • 在 Azure 数据库迁移服务中,单个迁移活动最多只能容纳四个数据库。In Azure Database Migration Service, a single migration activity can only accommodate up to four databases.