教程:使用 DMS 将 RDS PostgreSQL 联机迁移到 Azure DB for PostgreSQLTutorial: Migrate RDS PostgreSQL to Azure DB for PostgreSQL online using DMS

可以使用 Azure 数据库迁移服务将 RDS PostgreSQL 实例中的数据库迁移到 Azure Database for PostgreSQL,在迁移期间,源数据库可保持联机状态。You can use Azure Database Migration Service to migrate databases from an RDS PostgreSQL instance to Azure Database for PostgreSQL while the source database remains online during migration. 换而言之,实现这种迁移只会对应用程序造成极短暂的停机。In other words, migration can be achieved with minimal downtime to the application. 本教程介绍如何在 Azure 数据库迁移服务中使用联机迁移活动将 DVD Rental 示例数据库从 RDS PostgreSQL 9.6 实例迁移到 Azure Database for PostgreSQL。In this tutorial, you migrate the DVD Rental sample database from an instance of RDS PostgreSQL 9.6 to Azure Database for PostgreSQL by using the online migration activity in Azure Database Migration Service.

本教程介绍如何执行下列操作:In this tutorial, you learn how to:

  • 使用 pg_dump 实用工具迁移示例架构。Migrate the sample schema by using the pg_dump utility.
  • 创建 Azure 数据库迁移服务的实例。Create an instance of Azure Database Migration Service.
  • 使用 Azure 数据库迁移服务创建迁移项目。Create a migration project by using Azure Database Migration Service.
  • 运行迁移。Run the migration.
  • 监视迁移。Monitor the migration.
  • 执行直接转换迁移。Perform migration cutover.

Note

使用 Azure 数据库迁移服务执行联机迁移需要基于“高级”定价层创建实例。Using the Azure Database Migration Service to perform an online migration requires creating an instance based on the Premium pricing tier. 有关详细信息,请参阅 Azure 数据库迁移服务定价页。For more information, see the Azure Database Migration Service pricing page. 我们对磁盘进行加密,以防止在迁移过程中数据被盗。We encrypt disk to prevent data theft during the process of migration.

Important

为获得最佳迁移体验,Azure 建议在目标数据库所在的 Azure 区域中创建 Azure 数据库迁移服务的实例。For an optimal migration experience, Azure recommends creating an instance of the Azure Database Migration Service in the same Azure region as the target database. 跨区域或地理位置移动数据可能会减慢迁移过程并引入错误。Moving data across regions or geographies can slow down the migration process and introduce errors.

Tip

使用 Azure 数据库迁移服务将数据库迁移到 Azure 时,可以进行脱机或联机迁移。 When you migrate databases to Azure by using Azure Database Migration Service, you can do an offline or an online migration. 使用脱机迁移时,应用程序停机时间从迁移开始时算起。With an offline migration, application downtime starts when the migration starts. 使用联机迁移时,停机时间仅限在迁移结束时进行转换的那段时间。With an online migration, downtime is limited to the time to cut over at the end of migration. 建议对脱机迁移进行测试,以便确定其停机时间是否可以接受;如果不能接受,请进行联机迁移。We suggest that you test an offline migration to determine whether the downtime is acceptable; if not, do an online migration.

本文介绍如何从 PostgreSQL 的本地实例联机迁移到 Azure Database for PostgreSQL。This article describes how to perform an online migration from an on-premises instance of PostgreSQL to Azure Database for PostgreSQL.

先决条件Prerequisites

要完成本教程,需要:To complete this tutorial, you need to:

  • 下载并安装 PostgreSQL 社区版 9.5、9.6 或 10。Download and install PostgreSQL community edition 9.5, 9.6, or 10. 源 PostgreSQL 服务器版本必须是 9.5.11、9.6.7、10 或更高版本。The source PostgreSQL Server version must be 9.5.11, 9.6.7, 10, or later. 有关详细信息,请参阅支持的 PostgreSQL 数据库版本一文。For more information, see the article Supported PostgreSQL Database Versions.

    另请注意,目标 Azure Database for PostgreSQL 版本必须等于或晚于 RDS PostgreSQL 版本。Also note that the target Azure Database for PostgreSQL version must be equal to or later than the RDS PostgreSQL version. 例如,RDS PostgreSQL 9.6 只能迁移到 Azure Database for PostgreSQL 9.6、10 或 11,而不能迁移到 Azure Database for PostgreSQL 9.5。For example, RDS PostgreSQL 9.6 can only migrate to Azure Database for PostgreSQL 9.6, 10, or 11, but not to Azure Database for PostgreSQL 9.5.

  • 创建 Azure Database for PostgreSQL 的实例。Create an instance of Azure Database for PostgreSQL. 有关如何使用 pgAdmin 连接到 PostgreSQL 服务器的详细信息,请参阅此文档部分Refer to this section of the document for detail on how to connect to the PostgreSQL Server using pgAdmin.

  • 使用 Azure 资源管理器部署模型创建 Azure 数据库迁移服务的 Azure 虚拟网络,它将使用 ExpressRouteVPN 为本地源服务器提供站点到站点连接。Create an Azure Virtual Network for Azure Database Migration Service by using the Azure Resource Manager deployment model, which provides site-to-site connectivity to your on-premises source servers by using either ExpressRoute or VPN. 有关创建虚拟网络的详细信息,请参阅虚拟网络文档,尤其是提供了分步详细信息的快速入门文章。For more information about creating a virtual network, see the Virtual Network Documentation, and especially the quickstart articles with step-by-step details.

  • 确保虚拟网络网络安全组规则未阻止到 Azure 数据库迁移服务的以下入站通信端口:443、53、9354、445、12000。Ensure that your virtual network Network Security Group rules don't block the following inbound communication ports to Azure Database Migration Service: 443, 53, 9354, 445, and 12000. 有关虚拟网络 NSG 流量筛选的更多详细信息,请参阅使用网络安全组筛选网络流量一文。For more detail on virtual network NSG traffic filtering, see the article Filter network traffic with network security groups.

  • 配置针对数据库引擎访问的 Windows 防火墙Configure your Windows Firewall for database engine access.

  • 打开 Windows 防火墙,使 Azure 数据库迁移服务能够访问源 PostgreSQL 服务器(默认情况下为 TCP 端口 5432)。Open your Windows firewall to allow Azure Database Migration Service to access the source PostgreSQL server, which by default is TCP port 5432.

  • 在源数据库的前面使用了防火墙设备时,可能需要添加防火墙规则以允许 Azure 数据库迁移服务访问要迁移的源数据库。When using a firewall appliance in front of your source database(s), you may need to add firewall rules to allow the Azure Database Migration Service to access the source database(s) for migration.

  • 为 Azure Database for PostgreSQL 服务器创建服务器级防火墙规则,以允许 Azure 数据库迁移服务访问目标数据库。Create a server-level firewall rule for the Azure Database for PostgreSQL server to allow Azure Database Migration Service access to the target databases. 提供用于 Azure 数据库迁移服务的虚拟网络子网范围。Provide the subnet range of the virtual network used for Azure Database Migration Service.

设置 AWS RDS PostgreSQL 用于复制Set up AWS RDS PostgreSQL for replication

  1. 若要创建新的参数组,请遵照 AWS 在使用 DB 参数组中提供的说明操作。To create a new parameter group, follow the instructions provided by AWS in the article Working with DB Parameter Groups.

  2. 在 Azure 数据库迁移服务中使用主用户名连接到源。Use the master user name to connect to the source from Azure Database Migration Service. 如果使用的帐户不是主用户帐户,该帐户必须具有 rds_superuser 角色和 rds_replication 角色。If you use an account other than the master user account, the account must have the rds_superuser role and the rds_replication role. rds_replication 角色可以授予管理逻辑槽以及使用逻辑槽流式传输数据的权限。The rds_replication role grants permissions to manage logical slots and to stream data using logical slots.

  3. 使用以下配置创建新的参数组:Create a new parameter group with the following configuration:

    a.a. 将 DB 参数组中的 rds.logical_replication 参数设置为 1。Set the rds.logical_replication parameter in your DB parameter group to 1.

    b.b. max_wal_senders =[并发任务数] - max_wal_senders 参数设置可以运行的并发任务数,建议设置为 10 个任务。max_wal_senders =[number of concurrent tasks] - The max_wal_senders parameter sets the number of concurrent tasks that can run, recommend 10 tasks.

    c.c. max_replication_slots = [槽数],建议设置为 5 个槽。max_replication_slots – = [number of slots], recommend set to five slots.

  4. 将创建的参数组关联到 RDS PostgreSQL 实例。Associate the parameter group you created to the RDS PostgreSQL instance.

迁移架构Migrate the schema

  1. 从源数据库提取架构并将其应用到目标数据库,以完成所有数据库对象(例如表架构、索引和存储过程)的迁移。Extract the schema from the source database and apply to the target database to complete migration of all database objects such as table schemas, indexes, and stored procedures.

    仅迁移架构的最简单方法是结合 -s 选项使用 pg_dump。The easiest way to migrate only the schema is to use pg_dump with the -s option. 有关详细信息,请参阅 Postgres pg_dump 教程中的示例For more information, see the examples in the Postgres pg_dump tutorial.

    pg_dump -o -h hostname -U db_username -d db_name -s > your_schema.sql
    

    例如,若要转储 dvdrental 数据库的架构文件,请使用以下命令:For example, to dump a schema file for the dvdrental database, use the following command:

    pg_dump -o -h localhost -U postgres -d dvdrental -s  > dvdrentalSchema.sql
    
  2. 在目标服务(即 Azure Database for PostgreSQL)中创建一个空数据库。Create an empty database in the target service, which is Azure Database for PostgreSQL. 若要连接和创建数据库,请参阅以下文章之一:To connect and create a database, refer to one of the following articles:

  3. 将架构导入目标服务(即 Azure Database for PostgreSQL)。Import the schema to target service, which is Azure Database for PostgreSQL. 若要还原架构转储文件,请运行以下命令:To restore the schema dump file, run the following command:

    psql -h hostname -U db_username -d db_name < your_schema.sql
    

    例如:For example:

    psql -h mypgserver-20170401.postgres.database.chinacloudapi.cn  -U postgres -d dvdrental < dvdrentalSchema.sql
    
  4. 如果架构中有外键,则迁移的初始加载和连续同步会失败。If you have foreign keys in your schema, the initial load and continuous sync of the migration will fail. 若要在目标 (Azure Database for PostgreSQL) 中提取 drop foreign key 脚本和 add foreign key 脚本,请在 PgAdmin 或 psql 中运行以下脚本:To extract the drop foreign key script and add foreign key script at the destination (Azure Database for PostgreSQL), run the following script in PgAdmin or in psql:

    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;
    
  5. 运行查询结果中的 drop foreign key(第二列),以删除外键。Run the drop foreign key (which is the second column) in the query result to drop the foreign key.

  6. 如果数据中包含触发器(insert 或 update 触发器),该触发器会在从源复制数据之前在目标中强制实施数据完整性。If you have triggers (insert or update trigger) in the data, it will enforce data integrity in the target before replicating data from the source. 建议在迁移期间禁用目标的所有表中的触发器,然后在迁移完成后再启用这些触发器 。The recommendation is to disable triggers in all the tables at the target during migration, and then enable the triggers after migration is complete.

    在目标数据库中禁用触发器:To disable triggers in target database:

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

注册 Microsoft.DataMigration 资源提供程序Register the Microsoft.DataMigration resource provider

  1. 登录到 Azure 门户,选择“所有服务” ,然后选择“订阅” 。Sign in to the Azure portal, select All services, and then select Subscriptions.

    显示门户订阅

  2. 选择要在其中创建 Azure 数据库迁移服务实例的订阅,再选择“资源提供程序” 。Select the subscription in which you want to create the instance of Azure Database Migration Service, and then select Resource providers.

    显示资源提供程序

  3. 搜索“迁移”,然后选择“注册” 。Search for migration, and then select Register.

    注册资源提供程序

创建 Azure 数据库迁移服务的实例Create an instance of Azure Database Migration Service

  1. 在 Azure 门户中,选择 + 创建资源,搜索 Azure 数据库迁移服务,然后从下拉列表选择Azure 数据库迁移服务In the Azure portal, select + Create a resource, search for Azure Database Migration Service, and then select Azure Database Migration Service from the drop-down list.

    Azure 市场

  2. 在“Azure 数据库迁移服务”屏幕上,选择“创建” 。On the Azure Database Migration Service screen, select Create.

    创建 Azure 数据库迁移服务实例

  3. 在“创建迁移服务”屏幕上,为服务、订阅以及新的或现有资源组指定名称 。On the Create Migration Service screen, specify a name for the service, the subscription, and a new or existing resource group.

  4. 选择要在其中创建 Azure 数据库迁移服务实例的位置。Select the location in which you want to create the instance of Azure Database Migration Service.

  5. 选择现有虚拟网络或新建一个。Select an existing virtual network or create a new one.

    虚拟网络为 Azure 数据库迁移服务提供对源 PostgreSQL 实例和目标 Azure Database for PostgreSQL 实例的访问权限。The virtual network provides Azure Database Migration Service with access to the source PostgreSQL instance and the target Azure Database for PostgreSQL instance.

    有关如何在 Azure 门户中创建虚拟网络的详细信息,请参阅使用 Azure 门户创建虚拟网络一文。For more information about how to create a virtual network in the Azure portal, see the article Create a virtual network using the Azure portal.

  6. 选择定价层;对于此联机迁移,请务必选择“高级:4vCores”定价层。Select a pricing tier; for this online migration, be sure to select the Premium: 4vCores pricing tier.

    配置 Azure 数据库迁移服务实例设置

  7. 选择“创建” 来创建服务。Select Create to create the service.

创建迁移项目Create a migration project

创建服务后,在 Azure 门户中找到并打开它,然后创建一个新的迁移项目。After the service is created, locate it within the Azure portal, open it, and then create a new migration project.

  1. 在 Azure 门户中,选择“所有服务” ,搜索 Azure 数据库迁移服务,然后选择“Azure 数据库迁移服务” 。In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.

    查找 Azure 数据库迁移服务的所有实例

  2. 在“Azure 数据库迁移服务”屏幕上,搜索所创建的 Azure 数据库迁移服务实例名称,选择该实例,然后选择“+ 新建迁移项目” 。On the Azure Database Migration Services screen, search for the name of the Azure Database Migration Service instance that you created, select the instance, and then select + New Migration Project.

  3. 在“新建迁移项目”屏幕上指定项目名称,在“源服务器类型”文本框中选择“AWS RDS for PostgreSQL”,在“目标服务器类型”文本框中选择“Azure Database for PostgreSQL”。 On the New migration project screen, specify a name for the project, in the Source server type text box, select AWS RDS for PostgreSQL, and then in the Target server type text box, select Azure Database for PostgreSQL.

  4. 在“选择活动类型”部分选择“联机数据迁移”。 In the Choose type of activity section, select Online data migration.

    Important

    请确保选择“联机数据迁移”;此方案不支持脱机迁移 。Be sure to select Online data migration; offline migrations are not supported for this scenario.

    创建数据库迁移服务项目

    Note

    也可以现在就选择“仅创建项目”来创建迁移项目,在以后再执行迁移。 Alternately, you can choose Create project only to create the migration project now and execute the migration later.

  5. 选择“保存” 。Select Save.

  6. 选择“创建并运行活动”,以便创建项目并运行迁移活动。 Select Create and run activity to create the project and run the migration activity.

    Note

    请在项目创建边栏选项卡中记下设置联机迁移所要满足的先决条件。Please make a note of the pre-requisites needed to set up online migration in the project creation blade.

指定源详细信息Specify source details

  • 在“添加源详细信息” 屏幕上,指定源 PostgreSQL 实例的连接详细信息。On the Add Source Details screen, specify the connection details for the source PostgreSQL instance.

    源详细信息

指定目标详细信息Specify target details

  1. 选择“保存”,然后在“目标详细信息”屏幕上指定目标 Azure Database for PostgreSQL 服务器的连接详细信息,该服务器是提前预配的,具有使用 pg_dump 部署的 DVD Rentals 架构。 Select Save, and then on the Target details screen, specify the connection details for the target Azure Database for PostgreSQL server, which is pre-provisioned and has the DVD Rentals schema deployed using pg_dump.

    目标详细信息

  2. 选择“保存”,然后在“映射到目标数据库”屏幕上,映射源和目标数据库以进行迁移。 Select Save, and then on the Map to target databases screen, map the source and the target database for migration.

    如果目标数据库包含的数据库名称与源数据库的相同,则 Azure 数据库迁移服务默认会选择目标数据库。If the target database contains the same database name as the source database, Azure Database Migration Service selects the target database by default.

    映射到目标数据库

  3. 选择“保存”,在“迁移摘要”屏幕上的“活动名称”文本框中指定迁移活动的名称,然后查看摘要,确保源和目标详细信息与此前指定的信息相符 。Select Save, on the Migration summary screen, in the Activity name text box, specify a name for the migration activity, and then review the summary to ensure that the source and target details match what you previously specified.

    迁移摘要

运行迁移Run the migration

  • 选择“运行迁移” 。Select Run migration.

    迁移活动窗口随即出现,活动的“状态”为“正在初始化” 。The migration activity window appears, and the Status of the activity is Initializing.

监视迁移Monitor the migration

  1. 在迁移活动屏幕上选择“刷新” ,以便更新显示,直到迁移的“状态” 显示为“正在运行” 。On the migration activity screen, select Refresh to update the display until the Status of the migration shows as Running.

    活动状态 - 正在运行

  2. 在“数据库名称”下选择特定数据库即可转到“完整数据加载”和“增量数据同步”操作的迁移状态。 Under DATABASE NAME, select a specific database to get to the migration status for Full data load and Incremental data sync operations.

    “完整数据加载”会显示初始加载迁移状态,而“增量数据同步”则会显示变更数据捕获 (CDC) 状态。 Full data load shows the initial load migration status, while Incremental data sync shows change data capture (CDC) status.

    库存屏幕 - 完整数据加载

    库存屏幕 - 增量数据同步

执行迁移直接转换Perform migration cutover

完成初始的完整加载后,数据库会被标记为“准备好交接”。 After the initial Full load is completed, the databases are marked Ready to Cutover.

  1. 如果准备完成数据库迁移,请选择“启动直接转换”。 When you're ready to complete the database migration, select Start Cutover.

  2. 等到“挂起的更改” 计数器显示“0” 以确保源数据库的所有传入事务都已停止,选中“确认” 复选框,然后选择“应用” 。Wait until the Pending changes counter shows 0 to ensure that all incoming transactions to the source database are stopped, select the Confirm checkbox, and then select Apply.

    “完成直接转换”屏幕

  3. 当数据库迁移状态显示“已完成”后,请将应用程序连接到新的目标 Azure Database for PostgreSQL 数据库。 When the database migration status shows Completed, connect your applications to the new target Azure Database for PostgreSQL database.

将 RDS PostgreSQL 的本地实例联机迁移到 Azure Database for PostgreSQL 现已完成。Your online migration of an on-premises instance of RDS PostgreSQL to Azure Database for PostgreSQL is now complete.

后续步骤Next steps