This guide walks through the steps taken to successfully set up AWS Database Migration Service (DMS) to migrate a PostgreSQL database and keep it in sync using Change Data Capture (CDC).

---

Why Use AWS DMS?

AWS DMS allows you to:

• Migrate existing PostgreSQL data to a new database

• Continue replicating changes (CDC) with minimal downtime

• Avoid complex custom scripting

---

Step-by-Step Setup for Self-Managed PostgreSQL

1. Enable Logical Replication on Source PostgreSQL

PostgreSQL Config Changes (postgresql.conf):

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
wal_sender_timeout = 10000
idle_in_transaction_session_timeout = 0
shared_preload_libraries = 'pglogical,pg_stat_statements'

Why?

• wal_level = logical: Required to decode changes for logical replication. By default, it's usually set to replica or minimal, which only supports physical replication. Changing it to logical allows tools like DMS to stream individual row-level changes.

• max_replication_slots: Controls how many replication slots can be created (DMS uses one).

• max_wal_senders: Controls how many concurrent replication connections.

• wal_sender_timeout: Prevents idle replication connections from being killed too early.

• idle_in_transaction_session_timeout = 0: Prevents DMS idle connections from being closed.

• shared_preload_libraries: Needed for extensions like pglogical and pg_stat_statements that hook into Postgres internals.

Note: Restart the PostgreSQL server after applying these settings.

---

2. Install Required Extensions

In the database:

CREATE EXTENSION pglogical;
CREATE EXTENSION pg_stat_statements;

Why?

• pglogical: Enables logical replication with features beyond the native publication/subscription.

• pg_stat_statements: Optional but useful for monitoring query stats.

---

3. Configure pg_hba.conf

Allow the DMS replication instance to connect:

host replication dms_user <dms_ip>/32 md5
host all all <dms_ip>/32 md5

---

4. Create and Grant a DMS Replication User

CREATE USER dms_user WITH REPLICATION PASSWORD 'securepass';
GRANT CONNECT ON DATABASE yourdb TO dms_user;
GRANT USAGE ON SCHEMA public TO dms_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dms_user;

---

5. Set Up the DMS Replication Instance and Task

In AWS Console:

• Create a DMS Replication Instance

• Create Source and Target Endpoints

• In the source endpoint use extra connection attributes:

    pluginName=pgoutput;heartbeatFrequency=1;mapBooleanAsBoolean=true
  

• Create a migration task:

  • Type: Migrate existing data and replicate ongoing changes

  • Start the task

---

💻 Additional Setup for Amazon RDS / Aurora PostgreSQL

1. Modify the RDS Parameter Group

• Set the following parameters in a custom parameter group:

    wal_level = logical
    max_replication_slots = 10
    max_wal_senders = 10
    wal_sender_timeout = 10000
    idle_in_transaction_session_timeout = 0
    shared_preload_libraries = pglogical,pg_stat_statements
    rds.logical_replication = 1
  

After updating the parameter group, reboot the RDS instance for changes to take effect.

2. Create Extensions (if supported)

Use the master user to create extensions:

CREATE EXTENSION pglogical;
CREATE EXTENSION pg_stat_statements;

3. Create a Replication User

You can't grant the REPLICATION role on RDS manually, but DMS can use a user created by the master with the right privileges:

CREATE USER dms_user WITH PASSWORD 'securepass';
GRANT rds_replication TO dms_user;
GRANT CONNECT ON DATABASE yourdb TO dms_user;
GRANT USAGE ON SCHEMA public TO dms_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dms_user;

4. Continue with DMS Setup as Above

Once the RDS instance is configured and the user is created, proceed with DMS task and endpoint creation like in the self-managed setup.

---

🧠 Understanding WAL and Replication Slots

🔹 What is WAL (Write-Ahead Logging)?

PostgreSQL writes all changes to a special log file called the WAL before it touches the actual data files. This ensures data safety and enables crash recovery.

WAL is also what powers replication. Logical replication tools (like DMS or pglogical) read WAL logs and turn them into logical operations (INSERT/UPDATE/DELETE).

WAL Levels:

• minimal: For performance; no replication

• replica: Default; allows streaming replication

• logical: Enables logical decoding for tools like DMS and pglogical

Changing wal_level to logical is essential to stream row-level changes.

---

🔹 What Are Replication Slots?

A replication slot is like a pointer that tracks how far a replication client (like DMS) has read from the WAL.

• It tells Postgres: “Don’t delete old WAL files until I’ve read them.”

• If the client is active, WAL gets read and cleared.

• If the client is inactive (e.g., DMS is stopped), WAL files pile up and can fill the disk.

You can check replication slots:

SELECT * FROM pg_replication_slots;

If a slot is inactive:

SELECT pg_drop_replication_slot('slot_name');

---

you need also to add 2 iam roles for dms to work

dms-vpc-role

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ec2:CreateNetworkInterface",
                "ec2:DescribeAvailabilityZones",
                "ec2:DescribeInternetGateways",
                "ec2:DescribeSecurityGroups",
                "ec2:DescribeSubnets",
                "ec2:DescribeVpcs",
                "ec2:DeleteNetworkInterface",
                "ec2:ModifyNetworkInterfaceAttribute"
            ],
            "Resource": "*"
        }
    ]
}

dms-cloudwatch-logs-role

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "CloudWatchLogsAccess",
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogGroup",
                "logs:CreateLogStream",
                "logs:DescribeLogGroups",
                "logs:DescribeLogStreams",
                "logs:PutLogEvents"
            ],
            "Resource": "*"
        }
    ]
}

✅ Final Tips

• Always monitor WAL size and replication slot activity

• Be careful when stopping DMS tasks — slots may stay behind

• Always drop unused replication slots to avoid disk issues

---

That’s the full flow. You now understand not just how to make DMS work with PostgreSQL — but why each step matters under the hood, whether on a self-managed instance or Amazon RDS.