Tutorial: Migrate RDS PostgreSQL to Azure Database for PostgreSQL online using DMS
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. 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:
- Migrate the sample schema by using the pg_dump utility.
- Create an instance of Azure Database Migration Service.
- Create a migration project by using Azure Database Migration Service.
- Run the migration.
- Monitor the migration.
Using the Azure Database Migration Service to perform an online migration requires creating an instance based on the Premium pricing tier. For more information, see the Azure Database Migration Service pricing page.
For an optimal migration experience, Microsoft 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.
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.
This article describes how to perform an online migration from an on-premises instance of PostgreSQL to Azure Database for PostgreSQL.
To complete this tutorial, you need to:
Download and install PostgreSQL community edition 9.5, 9.6, or 10. The source PostgreSQL Server version must be 9.5.11, 9.6.7, 10, or later. For more information, see the article Supported PostgreSQL Database Versions.
In addition, the RDS PostgreSQL version must match the Azure Database for PostgreSQL version. For example, RDS PostgreSQL 126.96.36.199 can only migrate to Azure Database for PostgreSQL 9.5.11 and not to version 9.6.7.
Create a Microsoft 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.
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. For more detail on virtual network NSG traffic filtering, see the article Filter network traffic with network security groups.
Configure your Windows Firewall for database engine access.
Open your Windows firewall to allow Azure Database Migration Service to access the source PostgreSQL server, which by default is TCP port 5432.
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.
Create a server-level firewall rule for the Azure Database for PostgreSQL server to allow Azure Database Migration Service access to the target databases. Provide the subnet range of the virtual network used for Azure Database Migration Service.
Set up AWS RDS PostgreSQL for replication
- To create a new parameter group, follow the instructions provided by AWS in the article Working with DB Parameter Groups.
- Use the master user name to connect to the source from Azure Database Migration Service. If you use an account other than the master user account, the account must have the rds_superuser role and the rds_replication role. The rds_replication role grants permissions to manage logical slots and to stream data using logical slots.
- Create a new parameter group with the following configuration: a. Set the rds.logical_replication parameter in your DB parameter group to 1. b. 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. max_replication_slots – = [number of slots], recommend set to five slots.
- Associate the parameter group you created to the RDS PostgreSQL instance.
Migrate the schema
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.
The easiest way to migrate only the schema is to use pg_dump with the -s option. 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
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
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:
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
psql -h mypgserver-20170401.postgres.database.azure.com -U postgres -d dvdrental < dvdrentalSchema.sql
If you have foreign keys in your schema, the initial load and continuous sync of the migration will fail. 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;
Run the drop foreign key (which is the second column) in the query result to drop the foreign key.
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';
Register the Microsoft.DataMigration resource provider
Sign in to the Azure portal, select All services, and then select Subscriptions.
Select the subscription in which you want to create the instance of Azure Database Migration Service, and then select Resource providers.
Search for migration, and then to the right of Microsoft.DataMigration, select Register.
Create an instance of Azure Database Migration Service
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.
On the Azure Database Migration Service screen, select Create.
On the Create Migration Service screen, specify a name for the service, the subscription, and a new or existing resource group.
Select the location in which you want to create the instance of Azure Database Migration Service.
Select an existing virtual network or create a new one.
The virtual network provides Azure Database Migration Service with access to the source PostgreSQL instance and the target Azure Database for PostgreSQL instance.
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.
Select a pricing tier; for this online migration, be sure to select the Premium: 4vCores pricing tier.
Select Create to create the service.
Create a migration project
After the service is created, locate it within the Azure portal, open it, and then create a new migration project.
In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.
On the Azure Database Migration Services screen, search for the name of the Azure Database Migration Service instance that you created, and then select the instance.
Select + New Migration Project.
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.
In the Choose type of activity section, select Online data migration.
Be sure to select Online data migration; offline migrations are not supported for this scenario.
Alternately, you can choose Create project only to create the migration project now and execute the migration later.
Select Create and run activity to create the project and run the migration activity.
Please make a note of the pre-requisites needed to set up online migration in the project creation blade.
Specify source details
On the Migration source detail screen, specify the connection details for the source PostgreSQL instance.
Specify target details
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.
Select Save, and then on the Map to target databases screen, map the source and the target database for migration.
If the target database contains the same database name as the source database, Azure Database Migration Service selects the target database by default.
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
On the migration activity screen, select Refresh to update the display until the Status of the migration shows as Running.
Under DATABASE NAME, select a specific database to get to the migration status for Full data load and Incremental data sync operations.
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.
When you're ready to complete the database migration, select Start Cutover.
Make sure to stop all the incoming transactions to the source database; wait until the Pending changes counter shows 0.
Select Confirm, and the select Apply.
When the database migration status shows Completed, connect your applications to the new target Azure Database for PostgreSQL database.
Your online migration of an on-premises instance of PostgreSQL to Azure Database for PostgreSQL is now complete.