RDS PostgreSQL to Aurora PostgreSQL: Multi-Tenant to Single-Tenant Data Sync Using AWS DMS

Using an RDS PostgreSQL Read Replica as the CDC source to minimize production impact

Reference architecture used in the tested migration approach.

Item	Details
Source	RDS PostgreSQL 18.4
Migration source	RDS PostgreSQL Read Replica
Migration engine	AWS Database Migration Service (AWS DMS)
Target	Aurora PostgreSQL
Migration pattern	Multi-Tenant → Single-Tenant
DMS mode	Full Load + CDC
Large-table approach	Controlled concurrency; two large tables at a time during the test approach

Abstract. Large PostgreSQL migrations become difficult when the source is a production multi-tenant system, the destination is a single-tenant Aurora PostgreSQL environment, and a relatively small number of tables account for terabytes of data. This case study presents a practical architecture that uses an RDS PostgreSQL Read Replica as the AWS DMS source for CDC. It walks through the PostgreSQL 18.4 replication configuration, logical replication slot setup, DMS endpoints, Full Load + CDC validation, large-table concurrency, and the troubleshooting lessons discovered during testing.

1. The Problem: Moving Large PostgreSQL Data Without Turning Migration Into a Production Incident

The migration objective was to synchronize data from a multi-tenant RDS PostgreSQL environment into an Aurora PostgreSQL single-tenant environment. The challenge was not only the volume of data. The source contains a number of very large tables, with several individual tables reaching hundreds of gigabytes and with indexes representing a substantial part of the total footprint. A migration that simply maximizes parallelism can therefore become a source, DMS, or target capacity problem.

At the same time, the production database must continue serving application traffic. Directly adding a heavy migration workload to the writer is undesirable, particularly when Full Load is reading large relations and CDC must remain active for an extended period.

The design question was therefore: how can the migration copy the initial data and continuously capture changes while moving the DMS source-capture connection away from the production writer? The answer tested here is to introduce an RDS PostgreSQL Read Replica between the production Primary and AWS DMS.

2. Why the Read Replica Architecture?

AWS RDS/Aurora database topology from the test environment, showing the Primary, Read Replica, and Aurora target.

The Read Replica provides a separate database endpoint for the DMS source connection. The production Primary remains the application-facing source of truth, while physical replication keeps the Read Replica synchronized. DMS then uses the Read Replica as its source endpoint and performs Full Load + CDC toward Aurora PostgreSQL.

Component	Primary responsibility
RDS PostgreSQL Primary	Application workload and source of truth
RDS PostgreSQL Read Replica	Migration source / CDC read path
AWS DMS	Initial Full Load and ongoing CDC processing
Aurora PostgreSQL	Single-tenant target database

This architecture is designed to minimize direct DMS CDC workload on the production writer. It does not mean that the migration is completely impact-free: physical replication, WAL generation, replica processing, network traffic, and target writes still consume resources. The important difference is that the DMS source connection is separated from the production writer, giving the migration a dedicated source endpoint and an operational control point.

3. Architecture at a Glance

The flow is intentionally simple: the Primary sends changes through physical replication to the Read Replica; the Read Replica is used as the DMS CDC source; DMS performs Full Load + CDC; and Aurora PostgreSQL receives the data in the single-tenant target.

The architecture also separates tuning decisions:

·       Source protection is primarily controlled by monitoring the Primary and Read Replica relationship.

·       CDC source pressure is controlled through Read Replica capacity and DMS source configuration.

·       Migration throughput is controlled through DMS instance size and table concurrency.

·       Target pressure is controlled through Aurora capacity, indexing strategy, and load concurrency.

4. Preparing the PostgreSQL 18.4 Source

The test environment used PostgreSQL 18.4 on both the Primary and the Read Replica. Before changing replication parameters, establish the exact database name and confirm the replica role.

SELECT current_database();

SELECT pg_is_in_recovery();

SELECT version();

The Read Replica should return true for pg_is_in_recovery(). The database name returned by current_database() becomes important later because rds.logical_slot_sync_dbname must correspond to the database used by the logical-slot workflow.

5. Configure the Primary: Logical Replication and the Physical Replica Slot

On the Primary, enable logical replication and configure synchronized_standby_slots with the actual physical slot name associated with the Read Replica. The physical slot name should be discovered from the environment rather than copied from another server.

Parameter	Tested configuration
rds.logical_replication	on
synchronized_standby_slots	Actual physical Read Replica slot name

SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn
FROM pg_replication_slots
WHERE slot_type = 'physical';

In the supplied test environment, the physical slot was:

rds_ap_south_1_db_4jxstomc3p4fynocelip3vc4dy

Primary parameter configuration showing logical replication and synchronized_standby_slots.

The important point is that synchronized_standby_slots is not simply a Boolean '1' setting in this configuration. It identifies the physical standby slot that participates in the synchronization design.

6. Configure the Read Replica: Logical Slot Synchronization

The Read Replica requires its own parameter configuration. The tested configuration enabled logical replication, enabled replication-slot synchronization, set the logical-slot synchronization database, and enabled hot-standby feedback.

Parameter	Read Replica value
rds.logical_replication	on
sync_replication_slots	on
rds.logical_slot_sync_dbname	Exact source database name
hot_standby_feedback	on

      

Verification of rds.logical_slot_sync_dbname on the Read Replica.

In the test environment, rds.logical_slot_sync_dbname returned datablogs. The key lesson is not the literal name 'datablogs'; it is that the value must match the actual database being used. A different environment must use its own exact database name.

hot_standby_feedback is enabled in the tested configuration because the Read Replica is serving the logical CDC workload. This setting should be monitored together with WAL retention and replica health; it should not be enabled without operational monitoring.

Read Replica configuration after the replication settings were changed.

7. Verify Effective Parameter Values Before Proceeding

Use the same verification query on both instances:

SELECT
    current_database() AS database_name,
    inet_server_addr() AS server_ip,
    version() AS postgres_version,
    name AS parameter,
    setting AS value,
    pending_restart
FROM pg_settings
WHERE name IN (
    'rds.logical_replication',
    'synchronized_standby_slots',
    'sync_replication_slots',
    'rds.logical_slot_sync_dbname',
    'hot_standby_feedback'
)
ORDER BY name;

The effective PostgreSQL setting is more useful than simply checking what was entered into a parameter group. The pending_restart column also shows whether a restart is still required before the intended value becomes active.

·       Primary: verify logical replication is on.

·       Primary: verify synchronized_standby_slots contains the actual physical replica slot.

·       Read Replica: verify logical replication is on.

·       Read Replica: verify sync_replication_slots is on.

·       Read Replica: verify rds.logical_slot_sync_dbname is the exact source database.

·       Read Replica: verify hot_standby_feedback is on.

8. Create and Monitor the Logical Replication Slot on Replica

The DMS source endpoint uses a logical replication slot named dms_read_replica_slot in the tested design. The slot must be created in the intended database and then verified before the DMS task is started.

SELECT *
FROM pg_create_logical_replication_slot(
    'dms_read_replica_slot',
    'test_decoding'
);

Creation of the dms_read_replica_slot logical replication slot on the Read Replica.

Monitor the slot with:

SELECT
    slot_name,
    slot_type,
    plugin,
    database,
    active,
    active_pid,
    restart_lsn,
    confirmed_flush_lsn,
    wal_status,
    failover,
    synced
FROM pg_replication_slots
WHERE slot_name = 'dms_read_replica_slot';

This query is particularly valuable during DMS troubleshooting. If the DMS task reports that a slot is already active, unavailable, or cannot be used for logical decoding, the slot state and active process should be checked before repeatedly restarting the task.

9. Create the AWS DMS Replication Instance

The migration used a provisioned AWS DMS replication instance. The test environment demonstrated the end-to-end workflow, while the production migration should size the instance based on measured Full Load throughput, CDC rate, table concurrency, and target capacity.

AWS DMS replication instance used for the migration test.

For the large-table workload, the practical approach is controlled concurrency. Two large tables at a time was the working approach discussed and tested. This avoids assuming that maximum parallelism automatically produces maximum migration speed.

If this metric is high	Likely action
DMS CPU / memory	Consider scaling the DMS replication instance or reducing concurrency.
Read Replica CPU / I/O	Reduce migration pressure and verify replica health.
Replica lag	Investigate source/replica pressure before increasing DMS load.
Aurora CPU / I/O	Increase target capacity or reduce concurrent load.
CDC latency	Identify whether DMS, source replica, network, or target is the bottleneck.

10. Configure the DMS Source Endpoint on the Read Replica

The DMS PostgreSQL source endpoint is configured against the Read Replica. This is the central architectural decision that separates the DMS CDC source connection from the production writer.

Setting	Test configuration
Endpoint role	Source
Database engine	PostgreSQL
Database name	datablogs
SSL mode	require
SlotName	dms_read_replica_slot

AWS DMS PostgreSQL source endpoint configured against the Read Replica with SlotName=dms_read_replica_slot.

SlotName=dms_read_replica_slot

SlotName belongs on the DMS PostgreSQL source endpoint. It tells DMS which logical replication slot to use for CDC. It is not a target-endpoint setting.

11. Configure the Aurora PostgreSQL Target Endpoint

The target endpoint connects AWS DMS to the Aurora PostgreSQL writer. The supplied test configuration used MaxFileSize=1048576 and loadUsingCSV=true.

Setting	Supplied test value
Endpoint role	Target
Database name	datablogs
SSL mode	require
MaxFileSize	1048576
Extra connection attributes	loadUsingCSV=true;

AWS DMS Aurora PostgreSQL target endpoint configuration.

These settings should be retained for production only after validation against the exact DMS version and target workload. Target-side write capacity can become the limiting factor even when the DMS instance has spare CPU.

12. Create Full Load + CDC

With the source and target endpoints validated, create the DMS task using Full Load + CDC. This provides the initial copy and then continues with change data capture.

1.       Validate the Read Replica source endpoint.

2.       Validate the Aurora target endpoint.

3.       Select Full Load + CDC.

4.       Configure table mappings for the approved migration scope.

5.       Start the task.

6.       Monitor Full Load progress and CDC latency continuously.

For a multi-tenant to single-tenant migration, table mappings should be reviewed carefully. The migration should explicitly define which schemas and tables are moving and whether any tenant filtering, consolidation, or transformation is required.

13. Proving CDC With Continuous Source Changes

A controlled test table was used to generate continuous inserts while the DMS task was running. This validates the CDC path rather than only the initial Full Load.

DO $$
BEGIN
    LOOP
        INSERT INTO public.dms_cdc_test (message)
        VALUES ('CDC test - ' || clock_timestamp());

        COMMIT;

        PERFORM pg_sleep(1);
    END LOOP;
END $$;

Controlled source-side continuous insert workload used to validate CDC.

This type of continuous test is appropriate for a non-production validation environment. In production, use a controlled test procedure with a defined stop condition and business approval.

14. End-to-End Result: Full Load Completed, CDC Ongoing

The supplied DMS screenshot shows the desired migration state: Full Load progress at 100%, with the task status indicating that loading completed and replication is ongoing.

Validation	Observed/tested result
DMS task mode	Full Load + CDC
Full Load progress	100%
Task status	Load completed, replication ongoing
CDC validation	Continuous source inserts used

AWS DMS final task state showing Full Load completed and replication continuing.

This is an important milestone, but it is not by itself the final cutover criterion. The team should continue monitoring CDC latency and validate the target data before switching application traffic or declaring the migration complete.

15. The Large-Table Problem: Why Two Tables at a Time?

The source contains roughly fifteen large tables, with the largest tables measured in hundreds of gigabytes and with indexes adding substantial storage. When tables of this size are loaded in parallel, the bottleneck can move quickly between DMS compute, source I/O, network throughput, Aurora write capacity, and index maintenance.

For that reason, two large tables at a time is a sensible starting point for this workload. It is not a universal DMS limit or a magic number. It is a controlled concurrency value that can be increased only after observing the system under load.

·       Start with two large tables.

·       Measure rows/second or GB/hour rather than judging speed by CPU alone.

·       Check DMS CPU and memory.

·       Check Read Replica CPU, I/O, and replication lag.

·       Check Aurora CPU, I/O, write latency, and storage behavior.

·       Increase concurrency only when all critical components have headroom.

·       Reduce concurrency if replica lag or target pressure increases.

16. Indexes: Fast Load Versus Long Index Creation

A common migration optimization is to load table data before building secondary indexes. This can improve bulk-load throughput because the migration is not maintaining every index entry during the initial data load. However, that approach is not automatically better for every migration.

For very large tables, rebuilding indexes afterward can take a long time and can consume significant target CPU, I/O, and storage bandwidth. If the target must become production-ready immediately after cutover, deferred index creation can simply move the time cost from the load phase to the post-load phase.

The right approach should therefore be decided table by table. Compare index size, index build duration, target maintenance window, application readiness requirements, and whether the DMS task can safely operate with the selected target object strategy.

17. How the Design Minimizes Production Impact

The main control is to use the Read Replica as the DMS source rather than connecting DMS directly to the production writer. This reduces the need for DMS CDC reads to compete directly with application transactions on the Primary.

Production concern	Control in this design
DMS source read workload	Move DMS source connection to the Read Replica.
Replica lag	Monitor continuously and reduce migration pressure if it rises.
DMS bottleneck	Scale the replication instance when DMS compute is the limiting factor.
Target bottleneck	Tune Aurora capacity and load concurrency.
Large table pressure	Use controlled concurrency, starting with two large tables.
Long CDC window	Monitor slot state, WAL retention, and CDC latency.
Cutover risk	Reconcile data and wait for CDC to catch up before cutover.

The phrase 'no impact on production' should be avoided in technical documentation because no migration can guarantee that. A more accurate statement is that the architecture is designed to minimize direct DMS CDC workload on the production writer while providing monitoring and throttling points.

18. Troubleshooting Lessons From the Test Environment

18.1 DMS reported that the slot was already active

Slot 'dms_read_replica_slot' state found as 'already active' while expected as 'inactive'.

This indicates that another process or DMS session was consuming the logical slot. Before resuming the task, identify the active session/process, stop the conflicting consumer where appropriate, confirm the slot is inactive, and then resume the DMS task.

18.2 DMS attempted CREATE TABLE on a read-only source

ERROR: cannot execute CREATE TABLE in a read-only transaction

The test showed DMS attempting to create an internal DDL artifact on the read-only source. This is an important compatibility/configuration consideration when using a Read Replica as the DMS source. The issue should be reproduced and validated in the exact DMS version and task configuration intended for production rather than assuming that every task mode behaves identically.

18.3 A synchronized slot could not be used for logical decoding

ERROR: cannot use replication slot "dms_read_replica_slot" for logical decoding
DETAIL: This replication slot is being synchronized from the primary server.

This was observed during an earlier configuration experiment. The lesson is to avoid mixing different slot-synchronization approaches without testing the exact intended design. The working test configuration documented here should be treated as the baseline, and any failover/slot-synchronization variation should be validated separately.

18.4 The logical slot did not appear where expected

When a logical slot is missing from the Read Replica, verify the instance role, effective parameter values, exact database name, slot synchronization settings, and the physical replica relationship before recreating slots repeatedly. The test cycle demonstrated why checking all layers in order is faster than troubleshooting DMS alone.

18.5 DMS Full Load – Recovery Conflict

During the Full Load phase, DMS reported SOURCE_UNLOAD errors because long-running queries on the PostgreSQL Read Replica conflicted with WAL recovery. The observed errors were:

FATAL: terminating connection due to conflict with recovery – User was holding shared buffer pin for too long.

ERROR: cannot assign TransactionIds during recovery.

Initial action: max_standby_streaming_delay was increased from 30 seconds to 300 seconds (300000 ms).

Final tuning applied for the migration test:

max_standby_archive_delay = 1800000    -- 30 minutes

max_standby_streaming_delay = 3600000  -- 1 hour

TransactionConsistencyTimeout = 900     -- 15 minutes

These settings provide additional time for long-running DMS Full Load operations before recovery conflicts or DMS transaction-consistency handling terminate the connection. Replica lag, WAL retention, query duration, and application read performance should be monitored closely during the migration.

Lesson learned: when DMS Full Load runs against a PostgreSQL Read Replica, recovery-conflict settings must be tuned together with replica health and workload characteristics. Increasing the delay values is a mitigation, not a substitute for monitoring replica lag, WAL retention, long-running queries, and the impact on application read performance.

19. Monitoring Queries

Effective parameter values:

SELECT
    name,
    setting,
    unit,
    context,
    pending_restart
FROM pg_settings
WHERE name IN (
    'rds.logical_replication',
    'synchronized_standby_slots',
    'sync_replication_slots',
    'rds.logical_slot_sync_dbname',
    'hot_standby_feedback'
)
ORDER BY name;

Logical and physical slot state:

SELECT
    slot_name,
    slot_type,
    plugin,
    database,
    active,
    active_pid,
    restart_lsn,
    confirmed_flush_lsn,
    wal_status,
    failover,
    synced
FROM pg_replication_slots
ORDER BY slot_name;

Replica role:

SELECT pg_is_in_recovery();

20. Production Readiness Checklist

·       Read Replica is healthy and synchronized.

·       PostgreSQL version and parameter-group changes are validated.

·       Primary rds.logical_replication is enabled.

·       synchronized_standby_slots references the actual physical Read Replica slot.

·       Read Replica sync_replication_slots is enabled.

·       Read Replica rds.logical_slot_sync_dbname matches the exact source database.

·       hot_standby_feedback is enabled as validated and its operational effects are monitored.

·       Logical slot dms_read_replica_slot exists and its state is understood.

·       DMS source endpoint points to the Read Replica.

·       Source endpoint SlotName is dms_read_replica_slot.

·       Aurora target endpoint connectivity is validated.

·       DMS Full Load + CDC task has been tested.

·       Large-table concurrency begins conservatively.

·       DMS, source replica, and Aurora monitoring is active.

·       CDC latency and WAL retention are within the approved limits.

·       Data reconciliation and business validation criteria are defined.

·       Final cutover and rollback procedures are approved.

21. Conclusion

A large PostgreSQL migration is as much a capacity-management exercise as it is a replication exercise. In this case, the goal was to move from a multi-tenant RDS PostgreSQL source to a single-tenant Aurora PostgreSQL target without turning the migration into a production performance problem.

The tested architecture uses an RDS PostgreSQL Read Replica as the AWS DMS source, allowing the production Primary to remain focused on application transactions while the migration pipeline consumes changes from the replica. PostgreSQL 18.4 logical-replication configuration, a dedicated logical slot, AWS DMS Full Load + CDC, and controlled table concurrency form the core of the approach.

The most valuable part of the exercise was the test environment. It exposed practical issues around slot state, read-only DDL behavior, database-specific slot synchronization, and the interaction between PostgreSQL replication and DMS. These findings can be incorporated into the production runbook before the migration window.

For environments containing multiple very large tables, starting with two large tables at a time provides a controlled baseline. From there, concurrency should be increased only when measurements show that DMS, the Read Replica, and Aurora all have sufficient capacity. The objective is sustainable throughput, not maximum parallelism.

Key takeaway: separate the DMS CDC source from the production writer, validate the PostgreSQL replication chain in a test environment, and tune migration concurrency based on the actual bottleneck.