Introduction

DMS cloud service is used to migrate relational databases, data warehouses, NoSQL databases, and other sorts of data stores. It can also capture ongoing changes from the source. DMS Change data capture (CDC) is the process that captures ongoing changes after the completion of initial migration to a supported target data store. This keeps source and target in sync.

AWS DMS will read the transaction logs by using API operations internally to capture the database changes. To support CDC, tables should have primary key enabled. If a table doesn’t have a primary key, the write-ahead logs (WAL) don’t include a before row of the database and AWS DMS cannot update the table.

Prerequisites for using Azure PostgreSQL as source

Source engines require some additional configurations to make it possible to capture the change data. For Azure PostgreSQL as source, do the following:

• Use Azure PostgreSQL server that is version 11 or above.

• Need to add the required IP address to allow in the firewall rules.

Note: In the firewall rules, update connection security 0.0.0.0 to 255.255.255.255 to allow all IP’s(allow specific IP’S to provide the restricted access).

• Set required server parameters as following:
• Set max_replication_slots to greater than 1.

This value should be set according to the number of tasks that you want to run.

• Set max_wal_senders to greater than 1.

This parameter sets the number of concurrent tasks that can run.

• In Replication settings, modify replication support to Logical and restart the server so that it supports CDC.
• Set wal_level = logical

Create a Replication Instance

AWS DMS uses a replication instance to connect to your data source

• Go to DMS and Go to replication instance
• Create replication instance and save it

Note:

• Instance class: If you have a large database (many tables) or use several LOB data types, setting up a larger instance is preferable.
• Multi-AZ: If you select Multi-AZ, AWS DMS launches a primary and secondary replication instance in separate Availability Zones.

Create source Endpoint:

• Go to DMS and Go to Endpoints
• Select on create endpoint and click source endpoint
• In our use case, source endpoint is Azure PostgreSQL

• Choose SSL mode as require. Use SSL to encrypt connections between your source endpoint and the replication instance.
• Username should be entered in a format as username@hostname
• Now test connection.

Create target Endpoint:

• Go to DMS and Go to Endpoints
• Select on create endpoint and click target endpoint
• In this use case the target is RDS Postgres. The server details can be directly fetched by selecting the RDS DB instance and selecting the instance while creating the endpoint.

• Now test connection.

Creating a DMS task for Full load and CDC

• On the navigation pane, choose Tasks, then choose Create task.
• On the Create Task page, specify the options. Select the migrating type as Migrate existing data and replicate ongoing changes.

You can choose either of the below options as per the requirement.

Table mappings:

• Specify the tables that are to be migrated from source endpoint. For multiple tables with different schema, you can add new selection rule and specify the table name.

• Click on create task.

OUTPUT:

After the successful completion of task, summary looks like the following:

Table statistics will get updated depending on the transactions that happened on the source as shown in below image.

• Purpose of the article – To migrate data from Azure PostgreSQL to AWS RDS PostgreSQL using DMS
• Intended Audience – Useful for the people working on AWS DMS service
• References / Sources of the information referred –
  https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html 

LINKS:-

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html

Contact for further details:

Sai Laharika Pothina
Associate Trainee-Data Lakes & DWH
MOURI Tech