Migrating Amazon RDS for PostgreSQL to Amazon Aurora using seeded logical replication

In this post, we show you how to migrate from an Amazon Relational Database Service (Amazon RDS) for PostgreSQL to Amazon Aurora PostgreSQL-Compatible Edition using seeded logical replication. For live migrations with minimal downtime, AWS provides several approaches, including Aurora read replicas, the snapshot/restore method combined with ongoing replication, and AWS Database Migration Service (AWS DMS).

When migrating production workloads, you might need to validate the Aurora environment with comprehensive load testing before cutover, and accurate performance testing requires a replicated database environment that mirrors your intended production configuration. The out-of-the-box solution with Aurora read replicas physically replicates your RDS for PostgreSQL database to an Aurora environment with a single reader node. If you need to test against a multi-reader topology, Aurora read replicas’ single-reader limitation makes accurate load testing difficult. Alternatively, seeded logical replication using AWS DMS or a native publisher-subscriber model provides a hot standby Aurora cluster that matches your production environment for accurate load testing.

However, you might face critical challenges when logically replicating RDS for PostgreSQL databases to Aurora PostgreSQL caused by LSN (log sequence number) divergence because of the fundamentally different transaction log storage architectures. When you migrate a snapshot from RDS to Aurora, the LSN conversion process produces an incompatible LSN that blocks standard logical replication.

This post covers the LSN behavior during snapshot migration, and how to overcome this LSN divergence challenge when migrating from an Amazon RDS for PostgreSQL to Amazon Aurora using seeded logical replication. To understand the LSN divergence behavior, we explore the core PostgreSQL concepts involving Write-Ahead Logs (WALs), log sequence numbers (LSNs), and the logical replication requirements when migrating between RDS and Aurora environments.

Use cases

A seeded database with ongoing data replication using AWS DMS or a native publisher-subscriber setup supports the following use cases:

Realistic load testing with zero read downtime
You get a replicated hot standby Aurora cluster with multiple reader nodes, so you can run both write and multi-reader node performance validation, and a cutover has zero read downtime.

Combined migration and upgrade
You can combine the migration and a major engine version upgrade into a single maintenance window. Run a newer PostgreSQL version on the replicated Aurora cluster during replication, reducing cumulative downtime and business impact.

Partial data extraction
To replicate only a portion of the database, you can configure schema/table mappings and data filtering with AWS DMS or publish certain tables or schemas with a publisher-subscriber model.

Background information

Write-ahead log (WAL) and log sequence number (LSN)

PostgreSQL’s logical replication uses the write-ahead log (WAL) to capture data changes and uses the log sequence number (LSN)which is a unique pointer into the WAL to track positions within the transaction log. For logical replication to function correctly between a publisher (source) and subscriber (target), an appropriate seed LSN is required. This seed LSN acts as the starting point from which the publisher (source) database begins decoding and streaming changes to the subscriber (target) database.

Choosing the seed LSN correctly for logical replication

Configuring logical replication on a restored database requires choosing a valid seed LSN to be configured on the source for continuous replication from the moment the snapshot was taken. Choosing an incorrect or discontinuous seed LSN leads to one of two outcomes (see image for reference):

1. Seed LSN is behind the snapshot point – The subscriber replays changes that already exist on the database. For tables with primary keys, replication fails due to duplicate key conflicts. For tables without primary keys, duplicate or inconsistent data silently appears on the subscriber.
2. Seed LSN is ahead of the snapshot point – The subscriber replays changes after the snapshot, resulting in missing data on the subscriber.

Selecting the correct seed LSN is therefore critical for a reliable and consistent migration.

Logical replication challenges between RDS for PostgreSQL and Aurora PostgreSQL architecture

RDS for PostgreSQL writes write-ahead logs (WALs) locally and sequentially to disk, with the database engine managing rotation, archival, and replay using standard PostgreSQL mechanisms. LSN values progress linearly and predictably.

For Aurora PostgreSQL, Aurora uses a decoupled storage architecture where transaction logs are written directly to a distributed, fault-tolerant storage service spanning multiple Availability Zones. This removes traditional WAL file management at the instance level and uses Aurora-specific log record management. Aurora maintains its own log records by generating and managing LSN sequences that don’t directly correspond to those of standard PostgreSQL.

Think of LSN as page numbers in a book. When you photocopy pages from one book (RDS snapshot) into another book (Aurora), RDS keeps the original page numbers, but Aurora creates its own numbering system. When you try to continue reading from ‘page 500’ on the source, Aurora doesn’t have a ‘page 500’ that corresponds to the same content, therefore blocking the continuity of the story (data replication).

When you restore an RDS for PostgreSQL snapshot, the following happens:

• From RDS to RDS – The restored snapshot inherits the original LSN values and continues from the snapshot point, maintaining LSN continuity. The rds_tools extension provides the function logical_seed_lsn(), which returns the valid seed LSN of the restored database, an LSN that coincides with the snapshot point. This allows for logical replication to resume replicating changes from the point when the snapshot was taken.
• From RDS to Aurora – Aurora’s distributed storage can’t adopt the LSN sequence from the traditional PostgreSQL WAL, abandons the LSN trail from the original RDS snapshot and initializes its own independent LSN sequence. Aurora does provide an equivalent function, aurora_volume_logical_start_lsn(), however, this function returns Aurora’s internally generated start LSN, which doesn’t correspond to the original RDS for PostgreSQL snapshot LSN required to resume logical replication.

The following diagram illustrates this divergence, showing the LSN the moment before the snapshot was taken, and both seed LSNs for RDS for PostgreSQL and Aurora PostgreSQL the moment after each environment was restored from the same snapshot.

The RDS for PostgreSQL seed LSN is slightly ahead of the pre-snapshot LSN, reflecting natural LSN progression and confirming continuity between the snapshot point and the post-restore state. The Aurora PostgreSQL seed LSN returns an entirely new LSN value, discontinuous from the pre-snapshot LSN.

Migration options

The following section describes the migration options.

AWS Database Migration Service (AWS DMS)

AWS Database Migration Service (AWS DMS) is a fully managed service that can handle the full load and logical replication during Change Data Capture (CDC) between RDS and Aurora environments. For more information about homogenous migration using AWS DMS, see AWS DMS homogenous migration from PostgreSQL to Amazon Aurora PostgreSQL

Snapshot/Restore seeded logical replication

By migrating and restoring a snapshot to “seed” the data into a target Aurora cluster, you can improve the initial load speed without impact on the source RDS database, then manage logical replication configuration directly between the database environments for ongoing replication. You can configure this replication manually with native publisher-subscriber setup or use AWS DMS as a CDC-only task for ongoing replication from the snapshot seed LSN, both which use logical replication and require a procedure to extract and start replication from the correct snapshot point seed LSN.

A list of pros and cons for using AWS DMS or manual logical replication are provided in the following table:

The choice between AWS DMS and the snapshot/restore method for the initial load is dependent on how managed you want the migration to be. AWS DMS provides a fully managed full load and CDC replication with built-in monitoring, but if you have a large database where full load time is prohibitive and there are workload concerns on the source, choose seeded logical replication.

After the target database is seeded by completing the snapshot/restore method, you can either choose to use AWS DMS for managed CDC replication or a publisher-subscriber model depending on your comfort level with PostgreSQL and how managed you want the CDC replication to be. For more information about ongoing replication with a CDC-only task, see Creating tasks for ongoing replication using AWS DMS, or the publisher-subscriber model, see Performing logical replication for Amazon RDS for PostgreSQL.

Solution overview

Snapshot/Restore seeded logical replication

While AWS DMS full load is a valid approach, this post will show you how to perform seeded logical replication using either a publication-subscriber model or AWS DMS CDC-only task from an RDS for PostgreSQL source to an Aurora PostgreSQL target database. This solution extends the existing information previously covered regarding RDS for PostgreSQL to Aurora PostgreSQL migrations, such as Migrate to an Amazon Aurora PostgreSQL instance from another PostgreSQL source and Best practices for migrating PostgreSQL databases to Amazon RDS and Amazon Aurora, with guidance to overcome LSN divergence that enables migration to a hot standby Aurora cluster with custom and robust reader topology.

Now that we’ve covered the logical replication challenges and requirements, the following sections show you how to complete logical replication to the Aurora PostgreSQL target cluster.

Logical replication configuration walkthrough

You can’t extract a valid seed LSN for replication from Aurora PostgreSQL environments migrated from RDS snapshots, so you must extract the LSN from a temporary RDS environment restored from the same snapshot. You will use this extracted seed LSN to initiate logical replication between the source RDS for PostgreSQL database and Aurora cluster.

Prerequisites

You must have the following prerequisites:

• An RDS for PostgreSQL instance ready for seeded replication.
• Appropriate IAM permissions for snapshot operations.
• Network connectivity between the source RDS Postgres database and target Aurora cluster.
• The rds.logical_replication static parameter set to 1 on the source database (may require reboot).
• Sufficient storage space for replication slot WAL retention on the source database.

For more information about logical replication, see Performing logical replication for Amazon RDS for PostgreSQL.

Logical replication restrictions

Before you begin, note that there are many restrictions regarding logical replication, including:

• Database schema, DDL commands, Large objects, and sequence data are not replicated.
• TRUNCATE command replication requires careful handling when truncating tables connected by foreign keys.
• Only tables (including partitioned tables) support replication. If you’re using partitions, the partition hierarchy must be the same on both sides of a logical replication setup. Attempts to replicate other types of relations, such as views, materialized views, or foreign tables, will result in an error.
• For AWS DMS, see Limitations on using a PostgreSQL database as a DMS source and Limitations on using PostgreSQL as a target for AWS Database Migration Service.

Security considerations

For a strong security posture, consider the following:

• Apply principle of least privilege with IAM permissions. For IAM permission guidance, see What are the least privileges required for a user to perform creates, deletes, modifications, backup, and recovery for an Amazon RDS DB instance?
• Configure network isolation between the two environments with private networks, security groups, and VPC peering.
• For encryption at rest, use KMS keys for storage encryption.
• For encryption in transit, use SSL/TLS encryption for the replication connection with connection string parameters, sslmode=require or sslmode=verify-full.

For more information about data protection in your RDS database environment, see Data protection in Amazon RDS.

Source database performance considerations

After the replication slot is created on the source database, it begins to retain WAL segments until consumed. Until the target Aurora cluster is provisioned and logical replication is enabled to start replaying changes, the source PostgreSQL database’s WAL storage will continue to grow and use up the instance’s storage. After the replication is enabled, similar WAL retention and storage usage occurs if replication falls behind or becomes inactive, because the WAL files will continue to accumulate and fill up storage. Both scenarios can lead to an outage on your production database due to insufficient storage. To prevent this from occurring, we recommend that you estimate WAL storage during the initial snapshot/restore migration prior to enabling replication. Additionally, set up Amazon CloudWatch alarms for OldestReplicationSlotLag, ReplicationSlotDiskUsage, and FreeStorageSpace to proactively prevent a database outage caused by insufficient storage.

Enabling logical replication on an active RDS for PostgreSQL source can add performance overhead that should be planned for. Setting the wal_level=logical writes additional information to the WAL files, increasing overall WAL size and slightly increasing write IOPS. Logical replication requires the database engine to logically decode each change into a logical representation before transmitting it. This decoding consumes additional CPU and memory resources on the source instance. High-frequency workloads with complex transactions can amplify this overhead. During replication, make sure to monitor WriteIOPS, CPUUtilization, and FreeableMemory in Amazon CloudWatch.

For PostgreSQL 16 and later, RDS supports logical replication directly from a read replica, which can significantly reduce load on the primary source instance. By pointing the source to a read replica instead of the primary, workload traffic on the primary is unaffected by the logical decoding and its associated CPU and memory overhead. For more information about read replica requirements, refer to Read replica as a source for PostgreSQL for requirements in configuring a read replica as a logical replication source.

Procedure

Complete the following steps:

1. Configure the source for replication

Confirm that the parameter, rds.logical_replication, is set to 1 on the source PostgreSQL database to allow for logical replication.

postgres=> show rds.logical_replication;
rds.logical_replication
-------------------------
on
(1 row)

Create a replication slot on the RDS for PostgreSQL source database. Do this before you take the snapshot to make sure WALs are retained for replication. When selecting a logical replication plugin, choose either the test_decoding or pglogical plugin if you are using AWS DMS. Choose pgoutput if you are using the publisher-subscriber setup.

postgres=> select pg_create_logical_replication_slot('source_repl_slot','<replication_plugin>');
  pg_create_logical_replication_slot
 --------------------------------------
  (source_repl_slot,907/20000678)

2. Create the snapshot on the source RDS for PostgreSQL instance to migrate to Aurora PostgreSQL

Create a snapshot of the source database.

aws rds create-db-snapshot \
     --db-instance-identifier source-postgresDB \
     --db-snapshot-identifier source-postgresDB-snapshot 

Retrieve the snapshot ARN to be used for migration to Aurora.

aws rds describe-db-snapshots --db-snapshot-identifier source-postgresDB-snapshot --query "DBSnapshots[0].DBSnapshotArn" --output json | jq -r
arn:aws:rds:<region>:<acct_id>:snapshot:source-postgresDB-snapshot

Restore an Aurora cluster from the source PostgreSQL database snapshot. For instructions on migrating a snapshot, refer to Migrating a snapshot of an RDS for PostgreSQL DB instance to an Aurora PostgreSQL DB cluster.

aws rds restore-db-cluster-from-snapshot \
    --db-cluster-identifier target-Aurora-cluster \
    --snapshot-identifier arn:aws:rds:<region>:<acct_id>:snapshot:source-postgresDB-snapshot \
    --engine aurora-postgresql \
    --engine-version <version>\
    --db-subnet-group-name <sg>\
    --vpc-security-group-ids <sg>\
    --kms-key-id <kms-key>

While your Aurora cluster provisions in this step, simultaneously complete step 3 to restore the temporary RDS instance and extract the seed LSN. Provisioning an Aurora cluster can take time to become available depending on the size of your database, so running these in parallel can save you significant time.

Create Aurora writer node on the cluster, target-Aurora-cluster.

aws rds create-db-instance \
     --db-cluster-identifier target-Aurora-cluster \
     --db-instance-class <instance_class> \
     --db-instance-identifier target-Aurora-cluster-1 \
     --engine aurora-postgresql

3. Extract the correct seed LSN from temporary RDS for PostgreSQL database

Restore a temporary RDS instance to extract the seed LSN. Because you can’t extract the seed LSN directly from the Aurora cluster, you must extract the seed LSN from a temporary RDS for PostgreSQL database restored from the same snapshot as the Aurora cluster.

aws rds restore-db-instance-from-db-snapshot \
     --db-instance-identifier postgresDB-temp \
     --db-snapshot-identifier source-postgresDB-snapshot \
     --db-instance-class <instance_class> \
     --db-subnet-group-name <sg> \
     --vpc-security-group-ids <sg>

On the temporary RDS for PostgreSQL database, capture the RDS for PostgreSQL seed LSN of the source snapshot. This marks the LSN position the moment the snapshot was taken, which you will use to initiate replication on the target Aurora cluster. Save this LSN. After you save it, this temporary RDS instance can be deleted immediately to save cost.

postgres=> create extension rds_tools;
 CREATE EXTENSION
postgres=> SELECT rds_tools.logical_seed_lsn();
  logical_seed_lsn
 ------------------
  8E4/50000090
 (1 row)

→ If you choose AWS DMS for ongoing replication, skip to Option B – AWS DMS CDC-only task.

→ If you choose the publisher-subscriber model, continue with steps #4-7 in Option A – Publisher-subscriber setup.

Option A – Publisher-subscriber setup

4. Create a publication on the source database for the required tables or a subset of the tables

postgres=> CREATE PUBLICATION source_publication FOR ALL TABLES;

5. Configure the replication on the target Aurora PostgreSQL instance using the extracted seed LSN

Connect to the Aurora PostgreSQL writer instance and create a subscription to the RDS for PostgreSQL database, replacing source hostname, port, dbname, user, and password:

postgres=> CREATE SUBSCRIPTION target_subscription CONNECTION 'host=<source hostname> port=<port> dbname=<dbname> user=<user> password=<password> sslmode=require' PUBLICATION source_publication with (copy_data = false, create_slot = false, enabled = false, connect = true, slot_name = source_repl_slot);

Construct the external_id used to identify the replication origin name required to advance the subscription to the extracted seed LSN.

postgres=> SELECT 'pg_'||oid::text AS "external_id"
FROM pg_subscription
WHERE subname = 'target_subscription';
 external_id
 -------------
 pg_94378
 (1 row)

Use the external_id from the previous query to advance the replication origin to the extracted seed LSN from the temporary RDS database from step 3.

postgres=> SELECT pg_replication_origin_advance('pg_94378', '8E4/50000090'); 
 pg_replication_origin_advance
 -------------------------------
 (1 row)

Check the test_table count on the target instance prior to enabling replication. In this example, the row count is 5,882:

postgres=> select count (*) from test_table;
 count
 -------
   5882
 (1 row)

6. Initiate replication from the target Aurora PostgreSQL database

From the target Aurora writer node, enable the subscription to begin replication:

postgres=> ALTER SUBSCRIPTION target_subscription ENABLE;

7. Verify replication is active and changes are replicating

On the source PostgreSQL database, check that the replication slot is active and replication lag is catching up, as shown by the confirmed_flush_lsn_lag values decrease using the following command:

postgres=> SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS confirmed_flush_lsn_lag, restart_lsn, confirmed_flush_lsn, active FROM pg_replication_slots;
        slot_name           | confirmed_flush_lsn_lag |  restart_lsn  | confirmed_flush_lsn | active
 ------------------------+-------------------------+---------------+---------------------+--------
  source_repl_slot  |                       0 | 907/38000750 | 907/38000788        | t

Verify the record count in both the source RDS and target Aurora cluster. In this example, the previously checked row count of 5,882 has increased to the source row count of 7,717.

-- Source
postgres=> select count(*) from test_table;
  count
 -------
   7717
 (1 row)

-- Target
postgres=> select count (*) from test_table;
  count
 -------
   7717
 (1 row)

After replication has caught up, continue to validate data by running sample DML operations on a test_table to show replicated values.

-- Source
postgres=> delete from test_table where id=1;
DELETE 1
postgres=> select count(*) from test_table;
   count
  -------
    7716
  (1 row)

-- Target
postgres=> select count (*) from test_table;
   count
  -------
    7716
  (1 row)

Option B – AWS DMS CDC-only task

4. Create AWS DMS resources

Create a replication instance that can handle the workload. For more information, see Choosing the right AWS DMS replication instance for your migration.

Create AWS DMS endpoints for source and target. Modify the source Endpoint settings for SlotName and PluginName, matching the logical replication slot name and plugin you used in step (1). No endpoint settings are needed for the target endpoint.

5. Create the AWS DMS task

Create the AWS DMS task with the setting Enable custom CDC start mode and set the native CDC start point to the extracted seed LSN from the temporary RDS database from step 3. Consider using data validation which will validate existing data and replicated data. For more information, see AWS DMS data validation.

6. Start the replication task

Start the replication task to begin change data capture from the selected snapshot seed LSN.

7. Verify replication is active and changes are replicating

After the task shows as ongoing replication, check the AWS DMS task dashboard to confirm DML operations are being replicated.

Monitor the Amazon CloudWatch metrics, CDCLatencySource and CDCLatencyTarget, to observe the decrease in CDC latency. For more information about monitoring AWS DMS tasks, see Monitoring AWS DMS tasks. For added confirmation, verify that the changes have been replicated directly on the target database.

Both option A and option B

8. Monitor replication and prepare for cutover

Continue to monitor replication as needed until cutover. Pay attention to the Amazon CloudWatch metrics for OldestReplicationSlotLag, ReplicationSlotDiskUsage, and FreeStorageSpace on the source database.

After you’re ready for cutover, verify that replication is current

1. Check that the CDC replication lag is zero.
2. Confirm that row counts match. If using AWS DMS, consider AWS DMS data validation.
3. Test the application on the target Aurora cluster.

When ready, cutover the application to Aurora cluster, managing endpoint changes appropriately.

Rollback options

For support in troubleshooting AWS DMS tasks, see Troubleshooting migration tasks in AWS Database Migration Service. Troubleshooting native replication lag is outside the scope of this post. If issues are discovered during initiating replication, load testing, or validation on the Aurora cluster, the rollback path is straightforward because the source RDS instance remains fully operational throughout the process. No changes are made to the source that can’t be reversed.

To roll back:

Option A – Publisher-subscriber setup

1. Disable and drop the subscription on the Aurora writer. This will stop new change data capture:

   postgres=> ALTER SUBSCRIPTION target_subscription DISABLE; 
   postgres=> DROP SUBSCRIPTION target_subscription;

2. Verify that the replication slot is dropped on the source RDS instance:

   postgres=> SELECT pg_drop_replication_slot('source_repl_slot');

3. Drop the publication if no longer needed:

   postgres=> DROP PUBLICATION source_publication;

4. Delete the Aurora cluster and snapshots created during the process

Because the application never cut over to Aurora, the source remained in normal operation and is unaffected. If the decision is made to retry the migration after a rollback, recreate the replication slot and publication on the source before taking a new snapshot, as the previous slot was dropped.

Option B – AWS DMS CDC-only task

1. Stop and delete the AWS DMS task
2. Verify that the replication slot is dropped on the source RDS instance (See rollback step 2 in the preceding section)

Clean up

Clean up resources to avoid charges.

• Delete the temporary RDS instance after extracting the seed LSN. You can do this immediately after retrieving the seed LSN to avoid RDS instance charges.
• Delete the snapshots created during this process.
• Delete the replication instance if you used AWS DMS.
• Disable and drop the subscription on the target Aurora cluster if you used the publisher-subscriber model.

Check that the replication slot is dropped from the RDS source. If the replication slot isn’t dropped and remains inactive, it will continue to retain WALs, taking up storage, which can eventually lead to insufficient storage issues. Check for inactive slots by running the following command:

postgres=> select slot_name, active from pg_replication_slots where active = 'f';

Conclusion

In this post, we showed you how to overcome LSN divergence when migrating from RDS for PostgreSQL to Aurora PostgreSQL. You learned the underlying behavior of LSNs during snapshot migration, how to extract the correct seed LSN using a temporary RDS instance, how to configure logical replication between the two environments and create a hot standby Aurora cluster, all without disrupting your production workload. The result is a fully replicated, hot standby Aurora cluster with a multi-reader topology ready for realistic load testing, giving you the confidence to cut over on your own schedule. Your goal might be a same-version migration, a major version upgrade, or validating Aurora’s performance characteristics against your production traffic patterns, and seeded logical replication provides a repeatable, low-risk path to get there. When you’re ready to move forward, start by reviewing the prerequisites and considerations in the preceding sections and validate the approach against your specific PostgreSQL version and configuration in a non-prod environment.

About the authors