教程:通过 Azure 门户使用 DMS 将 PostgreSQL 联机迁移到 Azure DB for PostgreSQLTutorial: Migrate PostgreSQL to Azure DB for PostgreSQL online using DMS via the Azure portal

可以使用 Azure 数据库迁移服务在尽量缩短应用程序停机时间的情况下,将数据库从本地 PostgreSQL 实例迁移到 Azure Database for PostgreSQLYou can use Azure Database Migration Service to migrate the databases from an on-premises PostgreSQL instance to Azure Database for PostgreSQL with minimal downtime to the application. 本教程介绍如何在 Azure 数据库迁移服务中使用联机迁移活动将 DVD Rental 示例数据库从 PostgreSQL 9.6 的本地实例迁移到 Azure Database for PostgreSQL。In this tutorial, you migrate the DVD Rental sample database from an on-premises instance of 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 using the pg_dump utility.
  • 创建 Azure 数据库迁移服务的实例。Create an instance of Azure Database Migration Service.
  • 在 Azure 数据库迁移服务中创建迁移项目。Create a migration project in Azure Database Migration Service.
  • 运行迁移。Run the migration.
  • 监视迁移。Monitor the migration.
  • 执行直接转换迁移。Perform migration cutover.

备注

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

重要

为获得最佳迁移体验,Azure 建议在目标数据库所在的 Azure 区域中创建 Azure 数据库迁移服务的实例。For an optimal migration experience, Azure recommends creating an instance of 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.

先决条件Prerequisites

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

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

    另请注意,目标 Azure Database for PostgreSQL 版本必须等于或晚于本地 PostgreSQL 版本。Also note that the target Azure Database for PostgreSQL version must be equal to or later than the on-premises PostgreSQL version. 例如,PostgreSQL 9.6 可以迁移到 Azure Database for PostgreSQL 9.6、10 或 11,而不能迁移到 Azure Database for PostgreSQL 9.5。For example, PostgreSQL 9.6 can 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 Azure Database for PostgreSQL server.

  • 使用 Azure 资源管理器部署模型创建 Azure 数据库迁移服务的 Azure 虚拟网络,它将使用 ExpressRouteVPN 为本地源服务器提供站点到站点连接。Create a 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.

    备注

    在设置虚拟网络期间,如果将 ExpressRoute 与 Azure 的网络对等互连一起使用,请将以下服务终结点添加到将在其中预配服务的子网:During virtual network setup, if you use ExpressRoute with network peering to Azure, add the following service endpoints to the subnet in which the service will be provisioned:

    • 目标数据库终结点(例如,SQL 终结点、Cosmos DB 终结点等)Target database endpoint (for example, SQL endpoint, Cosmos DB endpoint, and so on)
    • 存储终结点Storage endpoint
    • 服务总线终结点Service bus endpoint

    Azure 数据库迁移服务缺少 Internet 连接,因此必须提供此配置。This configuration is necessary because Azure Database Migration Service lacks internet connectivity.

  • 确保虚拟网络的网络安全组 (NSG) 规则未阻止到 Azure 数据库迁移服务的以下入站通信端口:443、53、9354、445、12000。Ensure that the Network Security Group (NSG) rules for your virtual network don't block the following inbound communication ports to Azure Database Migration Service: 443, 53, 9354, 445, 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 Azure Database for PostgreSQL to allow Azure Database Migration Service to access to the target databases. 提供用于 Azure 数据库迁移服务的虚拟网络子网范围。Provide the subnet range of the virtual network used for Azure Database Migration Service.

  • 在 postgresql.config 文件中启用逻辑复制,并设置以下参数:Enable logical replication in the postgresql.config file, and set the following parameters:

    • wal_level = logicalwal_level = logical
    • max_replication_slots = [槽数],建议设置为“5 个槽” max_replication_slots = [number of slots], recommend setting to five slots
    • 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 setting to 10 tasks

重要

现有数据库中的所有表都需要主键,以确保可以将更改同步到目标数据库。All tables in your existing database need a primary key to ensure that changes can be synced to the target database.

迁移示例架构Migrate the sample schema

若要完成所有数据库对象(例如表架构、索引和存储过程),需从源数据库提取架构并将其应用到此数据库。To complete all the database objects like table schemas, indexes and stored procedures, we need to extract schema from the source database and apply to the database.

  1. 使用 pg_dump -s 命令为数据库创建架构转储文件。Use pg_dump -s command to create a schema dump file for a database.

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

    例如,若要为 dvdrental 数据库创建架构转储文件:For example, to create a schema dump file for the dvdrental database:

    pg_dump -o -h localhost -U postgres -d dvdrental -s -O -x > dvdrentalSchema.sql
    

    若要详细了解如何使用 pg_dump 实用程序,请参阅 pg-dump 教程中的示例。For more information about using the pg_dump utility, see the examples in the pg-dump tutorial.

  2. 在目标环境中创建一个空数据库,即 Azure Database for PostgreSQL。Create an empty database in your target environment, which is Azure Database for PostgreSQL.

    有关如何连接和创建数据库的详细信息,请参阅在 Azure 门户中创建 Azure Database for PostgreSQL 服务器一文。For details on how to connect and create a database, see the article Create an Azure Database for PostgreSQL server in the Azure portal.

  3. 通过还原架构转储文件,将架构导入已创建的目标数据库。Import the schema into the target database you created by restoring the schema dump file.

    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 citus < dvdrentalSchema.sql
    
  4. 若要提取 drop foreign key 脚本并将其添加到目标 (Azure Database for PostgreSQL) 中,请在 PgAdmin 或 psql 中运行以下脚本。To extract the drop foreign key script and add it at the destination (Azure Database for PostgreSQL), in PgAdmin or in psql, run the following script.

    重要

    如果架构中有外键,则迁移的初始加载和连续同步将失败。Foreign keys in your schema will cause the initial load and continuous sync of the migration to fail.

    SELECT Q.table_name
        ,CONCAT('ALTER TABLE ', table_schema, '.', table_name, STRING_AGG(DISTINCT CONCAT(' DROP CONSTRAINT ', foreignkey), ','), ';') as DropQuery
            ,CONCAT('ALTER TABLE ', table_schema, '.', table_name, STRING_AGG(DISTINCT CONCAT(' ADD CONSTRAINT ', foreignkey, ' FOREIGN KEY (', column_name, ')', ' REFERENCES ', foreign_table_schema, '.', foreign_table_name, '(', foreign_column_name, ')' ), ','), ';') as AddQuery
    FROM
        (SELECT
        S.table_schema,
        S.foreignkey,
        S.table_name,
        STRING_AGG(DISTINCT S.column_name, ',') AS column_name,
        S.foreign_table_schema,
        S.foreign_table_name,
        STRING_AGG(DISTINCT S.foreign_column_name, ',') AS foreign_column_name
    FROM
        (SELECT DISTINCT
        tc.table_schema,
        tc.constraint_name AS foreignkey,
        tc.table_name,
        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'
        ) S
        GROUP BY S.table_schema, S.foreignkey, S.table_name, S.foreign_table_schema, S.foreign_table_name
        ) Q
        GROUP BY Q.table_schema, Q.table_name;
    
  5. 运行查询结果中的 drop foreign key(第二列)。Run the drop foreign key (which is the second column) in the query result.

  6. 若要在目标数据库中禁用触发器,请运行以下脚本。To disable triggers in target database, run the script below.

    重要

    数据中的触发器(插入或更新触发器)会赶在从源中复制数据之前在目标中强制实施数据完整性。Triggers (insert or update) in the data enforce data integrity in the target ahead of the data being replicated from the source. 因此,建议在迁移期间禁用目标的所有表中的触发器,然后在迁移完成后重新启用这些触发器。As a result, it's recommended that you disable triggers in all the tables at the target during migration, and then re-enable the triggers after migration is complete.

    SELECT DISTINCT CONCAT('ALTER TABLE ', event_object_schema, '.', event_object_table, ' DISABLE TRIGGER ', trigger_name, ';')
    FROM information_schema.triggers
    

注册 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.

    注册资源提供程序

创建 DMS 实例Create a DMS instance

  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, the subscription, a new or existing resource group, and the location for the service.

  4. 选择现有虚拟网络或新建一个。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 server 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.

  5. 选择定价层。Select a pricing tier.

    有关成本和定价层的详细信息,请参阅价格页For more information on costs and pricing tiers, see the pricing page.

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

  6. 选择“查看 + 创建”以创建服务。Select Review + create to create the service.

    服务创建将在约 10 到 15 分钟内完成。Service creation will complete within about 10 to 15 minutes.

创建迁移项目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 Azure Database Migration Service instance that you created, select the instance, and then select + New Migration Project.

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

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

    创建 Azure 数据库迁移服务项目

    备注

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

  5. 选择“保存”,注意成功使用 Azure 数据库迁移服务迁移数据所要满足的要求,然后选择“创建并运行活动” 。Select Save, note the requirements to successfully use Azure Database Migration Service to migrate data, and then select Create and run activity.

指定源详细信息Specify source details

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

    “添加源详细信息”屏幕

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

指定目标详细信息Specify target details

  1. 在“目标详细信息”屏幕上指定目标服务器的连接详细信息,该服务器是使用 pg_dump 将 DVD Rentals 架构部署到的 PostgreSQL 的预配实例 。On the Target details screen, specify the connection details for the target server, which is the pre-provisioned instance of PostgreSQL to which the DVD Rentals schema was deployed by 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, and then on the Migration settings screen, accept the default values.

    “迁移设置”屏幕

  4. 选择“保存”,在“迁移摘要”屏幕上的“活动名称”文本框中指定迁移活动的名称,然后查看摘要,确保源和目标详细信息与此前指定的信息相符 。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 should update to show as Backup in Progress.

监视迁移Monitor the migration

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

    监视迁移过程

  2. 完成迁移后,请在“数据库名称”下选择特定数据库即可转到“完整数据加载”和“增量数据同步”操作的迁移状态 。When the migration is complete, 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 instance of Azure Database for PostgreSQL.

后续步骤Next steps