AWS Data Migration Service(DMS) setup with PostgreSQL

PostgreSQL Settings for AWS DMS Migration

• PostgreSQL Version: Ensure the PostgreSQL source database is version 9.4.x or higher.
• User Permissions for CDC Tasks: Grant the user account specified for DMS superuser permissions, as this is required to access replication-specific functions.

GRANT USER dms_user WITH PASSWORD ‘your_password’; GRANT CONNECT ON DATABASE your_database TO dms_user; GRANT USAGE ON SCHEMA ‘abc’ TO dms_user; GRANT SELECT ON ALL TABLES IN SCHEMA abc TO dms_user; ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT SELECT ON TABLES TO dms_user; ALTER USER dms_user REPLICATION;

PostgreSQL Configuration Changes:

• Edit the postgresql.conf file:
• wal_level = logical
• max_replication_slots = 4 (based on the number of tasks)
• max_wal_senders = 4 (to set the number of concurrent tasks)
• wal_sender_timeout = 60000 (timeout in milliseconds for inactive replication connections)

Replication Connections Setup:

Add the IP address of the DMS replication server in the pg_hba.conf file to allow replication and socket connections:

Replication Instance

host all all 12.3.4.56/00 md5 host replication dms 12.3.4.56/00 md5

Restart PostgreSQL:

After making changes, restart the PostgreSQL service:

sudo systemctl restart postgresql

Create a Publication:

CREATE PUBLICATION my_publication FOR ALL TABLES; — or CREATE PUBLICATION my_publication FOR ALL TABLES WITH (publish = ‘insert, update, delete’); SELECT * FROM pg_publication;

Setup Replica Identity:

Configure replica identity for tables:

ALTER TABLE table REPLICA IDENTITY value (DEFAULT, USING INDEX index_name, FULL, NOTHING);

Verify Configuration:

Check the replication slot status:

SELECT * FROM pg_replication_slots;

DMS Task Settings

For task settings, refer to the AWS documentation guide to customize the DMS task settings: AWS DMS Task Settings Guide.

Full Load Setup (When No Keys at Source)

In cases where the source doesn’t have primary keys, and you can’t use full replica identity due to performance concerns, perform a full-load with a full refresh at each run. Restart the task multiple times using these settings:

migration_type = "full-load"

“TargetTablePrepMode”: “DROP_AND_CREATE”

Automating DMS Task Restart with AWS Lambda

-- coding: utf-8 --

import boto3

import time

def lambda_handler(event, context):

client = boto3.client(‘dms’)

dms_task_name = event.get(‘dms_task_name’)

print(dms_task_name)

if not dms_task_name:

return {

‘statusCode’: 400,

‘body’: ‘dms_task_name is required’

}

Describe the DMS task to get its ARN using the task name

response = client.describe_replication_tasks(

Filters=[

{

‘Name’: ‘replication-task-id’,

‘Values’: [dms_task_name]

}

]

)

if not response[‘ReplicationTasks’]:

return {

‘statusCode’: 404,

‘body’: f’DMS task {dms_task_name} not found’

}

dms_task_arn = response[‘ReplicationTasks’][0][‘ReplicationTaskArn’]

task_status = response[‘ReplicationTasks’][0][‘Status’]

print(dms_task_arn)

Stop DMS task if it is not already stopped

if task_status != ‘stopped’:

client.stop_replication_task(

ReplicationTaskArn=dms_task_arn

)

Wait for the task to stop

while True:

response = client.describe_replication_tasks(

Filters=[

{

‘Name’: ‘replication-task-id’,

‘Values’: [dms_task_name]

}

]

)

task_status = response[‘ReplicationTasks’][0][‘Status’]

if task_status == ‘stopped’:

break

time.sleep(5) # Wait for 5 seconds before checking again

Determine the appropriate start type

start_type = ‘start-replication’ if task_status == ‘creating’ else ‘reload-target’

Start DMS task

client.start_replication_task(

ReplicationTaskArn=dms_task_arn,

StartReplicationTaskType=start_type

)

return {

‘statusCode’: 200,

‘body’: f’DMS task {dms_task_name} restarted successfully’

You can orchestrate this Lambda with Airflow for scheduling and automation:

from airflow import DAG

from airflow.operators.python_operator import PythonOperator

from airflow.utils.dates import days_ago

import boto3

import os

import logging

import json

logger = logging.getLogger()

logger.setLevel(logging.INFO)

Define default arguments for the DAG

default_args = {

‘depends_on_past’: False,

‘retries’: 1,

}

Define the DAG

dag = DAG(

dag_id=”dms-task-restart”,

default_args=default_args,

description=’A simple DAG to restart a DMS task’,

schedule_interval=’0 /6 ’, # Every 6 hours

start_date=days_ago(1),

catchup=False

)

Function to invoke the Lambda function

def invoke_lambda(**kwargs):

dms_task_name = “task_name”

if not dms_task_name:

raise ValueError(“dms_task_name is required”)

client = boto3.client(‘lambda’)

response = client.invoke(

FunctionName=”${lambda_name}”,

InvocationType=’Event’,

Payload=json.dumps({‘dms_task_name’: dms_task_name}),

)

Read the response payload

response_payload = response[‘Payload’].read().decode(‘utf-8’)

if response_payload:

response_json = json.loads(response_payload)

else:

response_json = {“message”: “No payload returned from Lambda function”}

return response_json

Define the PythonOperator

invoke_lambda_task = PythonOperator(

task_id=’invoke_lambda’,

python_callable=invoke_lambda,

provide_context=True,

dag=dag,

)

Set the task dependencies

invoke_lambda_task

Challenges Encountered

1. Out-of-Memory Errors During Full Load: This can be resolved by increasing the memory allocated to the task, using the following settings:

“ChangeProcessingTuning”: { “MemoryLimitTotal”: 3072 }

Alternatively, limit the number of tables processed simultaneously:

“FullLoadSettings”: { “MaxFullLoadSubTasks”: 1 }

No Primary Keys at Source: If the source tables lack keys, either use full replica identity (if the PostgreSQL instance can handle the extra load) or use the full-load method multiple times. Ideally, having keys on source tables is the best solution.

Limitations on using a PostgreSQL database as a DMS source

1. AWS DMS doesn’t work with Amazon RDS for PostgreSQL 10.4 or Amazon Aurora PostgreSQL 10.4 either as source or target

2. A captured table must have a primary key. If a table doesn’t have a primary key, AWS DMS ignores DELETE and UPDATE record operations for that table. We don’t recommend migrating without a Primary Key/Unique Index, otherwise additional limitations apply such as “NO” Batch apply capability, Full LOB capability, Data Validation and inability to replicate to Redshift target efficiently.

3. AWS DMS ignores an attempt to update a primary key segment. In these cases, the target identifies the update as one that didn’t update any rows. However, because the results of updating a primary key in PostgreSQL are unpredictable, no records are written to the exceptions table.

4. AWS DMS doesn’t replicate changes that result from partition or subpartition operations (ADD, DROP, or TRUNCATE).

5. Replication of multiple tables with the same name where each name has a different case (for example, table1, TABLE1, and Table1) can cause unpredictable behavior. Because of this issue, AWS DMS doesn’t support this type of replication.

6. In most cases, AWS DMS supports change processing of CREATE, ALTER, and DROP DDL statements for tables. AWS DMS doesn’t support this change processing if the tables are held in an inner function or procedure body block or in other nested constructs.

7. Currently, boolean data types in a PostgreSQL source are migrated to a SQL Server target as bit data type with inconsistent values. As a workaround, pre-create the table with a VARCHAR(1) data type for the column (or have AWS DMS create the table). Then have downstream processing treat an “F” as False and a “T” as True.

8. AWS DMS doesn’t support change processing of TRUNCATE operations.

9. AWS DMS doesn’t support change processing to set and unset column default values (using the ALTER COLUMN SET DEFAULT clause on ALTER TABLE statements).

10. A table with an ARRAY data type must have a primary key. A table with an ARRAY data type missing a primary key gets suspended during full load.

11. AWS DMS doesn’t support replication of partitioned tables. When a partitioned table is detected, the following occurs:

a. The endpoint reports a list of parent and child tables.

b. AWS DMS creates the table on the target as a regular table with the same properties as the selected tables.

c. If the parent table in the source database has the same primary key value as its child tables, a “duplicate key” error is generated.

12. AWS DMS doesn’t support replication of a table with a unique index created with a coalesce function.

13. AWS DMS doesn’t support CDC for Amazon RDS Multi-AZ database cluster for PostgreSQL as a source, since RDS for PostgreSQL Multi-AZ database clusters don’t support logical replication.

14. If your source database is also a target for another third–party replication system, DDL changes might not migrate during CDC. Because that situation can prevent the awsdms_intercept_ddl event trigger from firing. To work around the situation, modify that trigger on your source database as follows: alter event trigger awsdms_intercept_ddl enable always;

Limitations to using Amazon S3 as a target

1. Don’t enable versioning for S3. If you need S3 versioning, use lifecycle policies to actively delete old versions. Otherwise, you might encounter endpoint test connection failures because of an S3 list-object call timeout.

2. The following data definition language (DDL) commands are supported for change data capture (CDC): Truncate Table, Drop Table, Create Table, Rename Table, Add Column, Drop Column, Rename Column, and Change Column Data Type. Note that when a column is added, dropped, or renamed on the source database, no ALTER statement is recorded in the target S3 bucket, and AWS DMS does not alter previously created records to match the new structure. After the change, AWS DMS creates any new records using the new table structure

3. Full LOB mode is not supported.

4. Changes to the source table structure during full load are not supported. Changes to data are supported during full load.

5. Multiple tasks that replicate data from the same source table to the same target S3 endpoint bucket result in those tasks writing to the same file. We recommend that you specify different target endpoints (buckets) if your data source is from the same table.

6. BatchApply is not supported for an S3 endpoint. Using Batch Apply (for example, the BatchApplyEnabled target metadata task setting) for an S3 target might result in loss of data.

7. You can’t use DatePartitionEnabled or addColumnName together with PreserveTransactions or CdcPath.

8. AWS DMS doesn’t support renaming multiple source tables to the same target folder using transformation rules.

9. If you configure the task with a TargetTablePrepMode of DO_NOTHING, DMS may write duplicate records to the S3 bucket if the task stops and resumes abruptly during the full load phase.