PostgreSQL to Aurora: 4 Ways to Migrate or Replicate Data

PostgreSQL data can be moved to Amazon Aurora PostgreSQL using a one-time dump and restore, AWS Database Migration Service (DMS), native PostgreSQL logical replication, or a managed CDC pipeline.

The right method depends mainly on how much downtime you can tolerate, whether changes must continue flowing during the move, and whether you need a full database migration or ongoing table-data replication.

• Use pg_dump + pg_restore for a one-time PostgreSQL migration when an export and restore window is acceptable.
• Use AWS DMS for an AWS-native migration workflow with full-load and change-replication options.
• Use PostgreSQL logical replication when you want to manage PostgreSQL-to-PostgreSQL replication yourself.
• Use Estuary when you want to backfill PostgreSQL table data and then continuously replicate inserts, updates, and deletes into Aurora PostgreSQL using managed CDC.

Estuary’s PostgreSQL capture connector reads changes through logical replication and, by default, backfills existing table contents before transitioning to ongoing change capture. Its PostgreSQL destination connector supports Amazon Aurora and materializes captured collections into destination tables.

PostgreSQL to Aurora Migration and Replication Methods

For keeping Aurora PostgreSQL synchronized with a live PostgreSQL source, use a replication method that continues processing changes after the initial data copy. For a one-time move where ongoing synchronization is unnecessary, pg_dump and pg_restore may be sufficient.

How PostgreSQL to Aurora Migration and Replication Work

PostgreSQL-to-Aurora moves generally follow one of two patterns: a one-time database migration or an initial load followed by ongoing replication.

For a one-time migration, the flow is typically:

PostgreSQL → export/dump → Aurora PostgreSQL → application cutover

Tools such as pg_dump and pg_restore copy PostgreSQL objects and data into Aurora. This works well when the source database can tolerate a migration window and ongoing synchronization is not required.

For continuous replication, the flow is:

PostgreSQL → WAL / logical replication → replication service → Aurora PostgreSQL

PostgreSQL records row-level changes in its write-ahead log (WAL). A replication tool can consume those changes after the initial data copy and keep Aurora updated with subsequent inserts, updates, and deletes.

This pattern is useful when the source PostgreSQL database must remain active while data is being moved. Once Aurora has caught up, the application can be cut over to the target with a shorter write-pause window.

The exact behavior depends on the method:

• Estuary backfills selected PostgreSQL table data and then continues capturing WAL changes into Aurora PostgreSQL.
• AWS DMS supports full-load and ongoing change-replication workflows.
• PostgreSQL logical replication uses native publications and subscriptions.
• pg_dump + pg_restore performs a one-time export and restore without ongoing synchronization.

Method 1: Replicate PostgreSQL Data to Aurora with Estuary

Estuary can replicate selected PostgreSQL table data into Amazon Aurora PostgreSQL using change data capture (CDC).

The pipeline looks like this:

PostgreSQL → WAL → Estuary capture → Estuary collections → Aurora PostgreSQL

Estuary’s PostgreSQL source connector uses logical replication to read changes from PostgreSQL’s write-ahead log (WAL). By default, it first backfills the current contents of the selected tables and then transitions to ongoing change capture.

Step 1: Prepare PostgreSQL for CDC

The source PostgreSQL database needs:

• logical replication enabled with wal_level=logical
• a user with replication permissions
• a replication slot
• access to the tables you want to replicate

Estuary supports PostgreSQL 10 and later across major cloud platforms and self-hosted deployments.

Step 2: Create the PostgreSQL Capture

In Estuary:

1. Create a PostgreSQL capture.
2. Enter the source database connection details.
3. Discover the available schemas and tables.
4. Select the tables you want to replicate.
5. Publish the capture.

Unless backfilling is disabled for a table, Estuary captures its existing rows first and then continues processing new change events from PostgreSQL.

Step 3: Configure Aurora PostgreSQL as the Destination

Use Estuary’s PostgreSQL destination connector to materialize the captured collections into Aurora PostgreSQL.

The destination requires:

• the Aurora PostgreSQL database address and port
• database credentials
• network connectivity from Estuary
• at least one Estuary collection to materialize

The connector creates the destination tables according to the materialization configuration; manually pre-created destination tables are not supported. Amazon Aurora is supported through the AWS PostgreSQL connection flow.

Step 4: Start Ongoing Replication

Once both connectors are published, Estuary continues capturing PostgreSQL changes and materializing the resulting collections into Aurora PostgreSQL.

This approach is useful when you need:

• existing PostgreSQL table data loaded into Aurora
• ongoing replication after the initial backfill
• inserts, updates, and deletes captured from PostgreSQL WAL
• managed replication rather than operating native logical-replication infrastructure yourself

What Estuary Does Not Replace

Estuary should be considered a table-data replication approach, not a full PostgreSQL database-cloning utility.

A broader database migration may still require separate handling for database-level objects or configuration such as roles, extensions, sequences, indexes, privileges, or other schema and operational settings that are outside the replicated table data.

Method 2: Migrate PostgreSQL to Aurora with AWS DMS

AWS Database Migration Service (AWS DMS) can move PostgreSQL data into Amazon Aurora PostgreSQL using a full load, ongoing change replication, or both.

The architecture is:

PostgreSQL → AWS DMS → Aurora PostgreSQL

For a production migration, the most relevant pattern is usually full load + CDC:

1. AWS DMS copies the existing PostgreSQL data into Aurora.
2. PostgreSQL continues serving application traffic.
3. DMS reads subsequent source changes from PostgreSQL logical replication.
4. Those changes are applied to Aurora until the target catches up.
5. The application can then be cut over to Aurora.

See the AWS DMS PostgreSQL source documentation and Aurora migration guidance for current prerequisites and supported migration patterns.

Step 1: Prepare PostgreSQL

For ongoing change replication, PostgreSQL must be configured for logical replication.

Typical requirements include:

• wal_level=logical
• sufficient replication slots
• sufficient WAL senders
• a replication user with the required permissions
• network connectivity between AWS DMS and PostgreSQL

If the migration is full-load only, CDC-specific logical replication settings may not be required.

Step 2: Configure the Source and Target Endpoints

Create:

• a PostgreSQL source endpoint
• an Aurora PostgreSQL target endpoint

Then test both connections before starting the migration task.

AWS DMS also requires the appropriate VPC, security-group, subnet, and IAM configuration so the replication resources can reach both databases.

Step 3: Choose the Migration Type

AWS DMS supports three relevant task modes:

• Full load: migrate existing data only
• Full load + CDC: migrate existing data and continue replicating source changes
• CDC only: replicate changes from a defined starting point

For a low-downtime PostgreSQL-to-Aurora move, full load + CDC is usually the most relevant option.

Step 4: Run and Monitor the Migration

During the migration, monitor:

• full-load progress
• CDC latency
• replication errors
• source WAL retention
• table statistics
• target consistency

Once the initial copy is complete and CDC lag is close to zero, you can plan the application cutover.

When AWS DMS Is a Good Fit

AWS DMS is a good fit when:

• you want to stay within AWS-native migration tooling
• the source is PostgreSQL and the target is Aurora PostgreSQL
• you need full load plus ongoing replication
• you are comfortable managing AWS networking, IAM, endpoints, and replication tasks
• minimizing application downtime is important

Things to Consider

• PostgreSQL logical replication must be configured correctly for CDC.
• Replication lag and WAL growth should be monitored during long-running tasks.
• Schema objects, extensions, roles, and other database-level objects may need separate handling depending on the migration method.
• Application cutover still needs to be planned; AWS DMS keeps data synchronized but does not automatically switch your application to Aurora.

Method 3: Replicate PostgreSQL to Aurora with Native Logical Replication

PostgreSQL’s native logical replication can replicate selected tables from a source PostgreSQL database into Amazon Aurora PostgreSQL without using a separate migration service.

The architecture is:

PostgreSQL publisher → logical replication → Aurora PostgreSQL subscriber

This method is useful when you want a PostgreSQL-native replication setup and are comfortable managing publications, subscriptions, schema preparation, replication lag, and cutover yourself.

Step 1: Prepare the Source PostgreSQL Database

Enable logical replication and make sure the source has enough replication capacity.

Typical settings include:

• wal_level=logical
• sufficient max_replication_slots
• sufficient max_wal_senders
• a replication user with access to the tables being published

You also need network connectivity from Aurora to the source PostgreSQL database.

Step 2: Prepare the Aurora Target Schema

Logical replication replicates table data, but it does not automatically reproduce the full source database environment.

Create the required destination schema and tables in Aurora before starting replication.

Pay particular attention to:

• table definitions
• primary keys
• indexes
• sequences
• extensions
• roles and privileges

These may require separate migration or synchronization steps.

Step 3: Create a Publication on PostgreSQL

Create a publication for the tables you want to replicate.

For example:

plaintext
CREATE PUBLICATION aurora_migration
FOR TABLE customers, orders, products;

You can also publish all eligible tables if that better matches your migration plan.

Step 4: Create a Subscription in Aurora

On the Aurora PostgreSQL target, create a subscription that connects to the source PostgreSQL database.

For example:

plaintext
CREATE SUBSCRIPTION aurora_migration
CONNECTION 'host=SOURCE_HOST port=5432 dbname=SOURCE_DB user=REPLICATION_USER password=PASSWORD'
PUBLICATION aurora_migration;

By default, PostgreSQL can copy the existing rows for subscribed tables and then continue applying subsequent changes.

Step 5: Monitor Replication and Cut Over

Monitor replication until Aurora has received the initial table data and the remaining replication lag is minimal.

Then:

1. briefly pause writes to the source
2. allow Aurora to catch up
3. verify row counts and critical application data
4. point the application to Aurora
5. resume writes against the new target

What Logical Replication Handles

Logical replication is designed for table-level changes such as:

• inserts
• updates
• deletes

It is not a complete database-cloning mechanism.

DDL changes, sequences, extensions, roles, and other database-level objects require separate handling.

When Native Logical Replication Is a Good Fit

Use native logical replication when:

• you want PostgreSQL-native replication without another migration service
• you need the source to remain active during most of the migration
• you are comfortable managing publications and subscriptions yourself
• the source and Aurora versions are compatible for your replication setup
• you can separately handle schema and database-level objects

Method 4: Migrate PostgreSQL to Aurora with pg_dump and pg_restore

For a one-time migration, PostgreSQL’s native pg_dump and pg_restore utilities can move database objects and data into Amazon Aurora PostgreSQL.

The workflow is:

PostgreSQL → pg_dump → dump file → pg_restore → Aurora PostgreSQL

This approach is best when you can tolerate an export-and-restore window and do not need ongoing synchronization between the source and Aurora.

Step 1: Create the PostgreSQL Dump

Create a dump in PostgreSQL’s custom format:

plaintext
pg_dump -Fc -h SOURCE_HOST -U SOURCE_USER -d SOURCE_DB -f postgres.dump

The custom format works with pg_restore and supports selective and parallel restore options.

See the PostgreSQL pg_dump documentation for available dump options.

Step 2: Prepare Aurora PostgreSQL

Create the target Aurora PostgreSQL database and make sure:

• required extensions are available
• the database user has the required permissions
• network connectivity is configured
• source and target PostgreSQL versions are compatible with your migration plan

Aurora PostgreSQL is PostgreSQL-compatible, but not every extension or database-level configuration from a self-managed PostgreSQL system is necessarily available.

Step 3: Restore the Dump into Aurora

Restore the dump:

plaintext
pg_restore \
  -h AURORA_ENDPOINT \
  -U TARGET_USER \
  -d TARGET_DB \
  postgres.dump

For larger migrations, pg_restore supports parallel jobs:

plaintext
pg_restore \
  -h AURORA_ENDPOINT \
  -U TARGET_USER \
  -d TARGET_DB \
  -j 4 \
  postgres.dump

See the PostgreSQL pg_restore documentation for current restore options.

Step 4: Validate the Migration

Before switching the application to Aurora, validate:

• table and row counts
• indexes and constraints
• sequences
• extensions
• users and permissions
• application queries
• critical data types and functions

Once validation is complete, update the application connection to point to Aurora PostgreSQL.

When pg_dump + pg_restore Is a Good Fit

Use this method when:

• the migration is one-time
• ongoing source-to-Aurora replication is not required
• an export-and-restore window is acceptable
• you want to use native PostgreSQL utilities
• the database size and restore duration fit your maintenance window

Things to Consider

• Changes made to the source after the dump starts are not continuously replicated to Aurora.
• Larger databases can require significant time to dump, transfer, and restore.
• Extensions, roles, privileges, and other database-level objects should be checked separately.
• The application cutover is your responsibility.
• For migrations that need the source to remain active with minimal data drift, use a replication-based method instead.

If Your Source Is Amazon RDS for PostgreSQL

If the source database already runs on Amazon RDS for PostgreSQL, AWS provides additional migration paths that may be simpler than setting up a separate replication pipeline.

Common options include:

• RDS snapshot migration: use an existing RDS for PostgreSQL snapshot as the basis for an Aurora PostgreSQL migration.
• Aurora read replica: for supported configurations, create an Aurora PostgreSQL read replica from the RDS PostgreSQL source, allow replication to catch up, and then promote the Aurora replica during cutover.
• AWS DMS: use full load plus ongoing change replication when you need more control over the migration process.

See the AWS documentation for migrating PostgreSQL to Aurora PostgreSQL for the current supported paths and version requirements.

If your source is already on RDS PostgreSQL, check these AWS-native options before building a separate migration workflow.

How to Minimize Downtime When Migrating PostgreSQL to Aurora

To minimize downtime, use a migration method that performs an initial data load followed by ongoing replication.

A typical low-downtime cutover looks like this:

1. Load the existing PostgreSQL data into Aurora.
2. Keep the source database online while new inserts, updates, and deletes continue replicating.
3. Monitor replication lag until Aurora is nearly caught up.
4. Pause application writes briefly.
5. Allow the final source changes to reach Aurora.
6. Validate critical tables and row counts.
7. Point the application to the Aurora endpoint.
8. Resume writes against Aurora.

This pattern can be implemented with AWS DMS, native PostgreSQL logical replication, or a managed CDC pipeline such as Estuary.

The important point is that replication reduces the amount of data that must be moved during the final cutover. It does not eliminate the need to plan the application switch, validate the target, and account for database objects that are not replicated automatically.

Conclusion

The best PostgreSQL-to-Aurora approach depends on whether you need a one-time migration or ongoing replication during the move.

Use pg_dump and pg_restore for simple one-time migrations. Use AWS DMS or native logical replication when you need Aurora to stay updated while the source remains active. Use Estuary when your main requirement is managed table-data replication with an initial backfill followed by ongoing CDC.

Need to keep PostgreSQL table data synchronized with Aurora PostgreSQL?

Start building with Estuary or review the PostgreSQL source connector and PostgreSQL destination connector documentation.