DynamoDB to PostgreSQL: Batch Migration and Real-Time CDC

You can move data from Amazon DynamoDB to PostgreSQL in two main ways: use DynamoDB Streams and a managed change data capture (CDC) pipeline for continuous replication, or export a point-in-time snapshot to Amazon S3 and transform it for a one-time PostgreSQL load.

Use CDC when PostgreSQL must remain current as DynamoDB items are inserted, updated, or deleted. Use an S3 export for a one-time migration, archival copy, or scheduled batch refresh.

Because DynamoDB and PostgreSQL use different data models, moving the data is only part of the migration. You must also decide how to map partition and sort keys, nested attributes, secondary indexes, TTL records, and application access patterns into a relational schema.

This guide compares both approaches and explains how to build a managed DynamoDB-to-PostgreSQL CDC pipeline with Estuary.

DynamoDB to PostgreSQL methods compared

What changes when moving from DynamoDB to PostgreSQL?

DynamoDB and PostgreSQL use different data models, so a migration is not always a one-to-one table copy. Plan the following mappings before creating the pipeline.

Replicating data does not automatically migrate an application. Code that uses DynamoDB APIs, expressions, indexes, or consistency behavior must be reviewed separately.

Method 1: Continuously replicate DynamoDB to PostgreSQL with Estuary

Estuary is a managed real-time data movement platform for CDC, streaming, and batch pipelines. Its DynamoDB capture connector first scans selected tables to backfill their existing items. It then reads inserts, updates, and deletes from DynamoDB Streams and stores those events in reusable Estuary collections.

A PostgreSQL materialization writes the collections into connector-managed destination tables. This allows the initial snapshot and subsequent changes to move through the same pipeline.

For compatible streaming pipelines, Estuary can provide sub-100ms latency. However, this should not be presented as a fixed per-record guarantee. Actual end-to-end freshness depends on AWS region, network conditions, transformations, capture activity, and PostgreSQL write performance.

Prerequisites for DynamoDB capture

Before configuring the DynamoDB source:

• Enable DynamoDB Streams on every table you want to capture.
• Set the stream view type to New and old images (NEW_AND_OLD_IMAGES).
• Authenticate using an AWS access key and secret key or an AWS IAM role.
• When using an IAM role, set its maximum session duration to 12 hours.
• Grant the capture identity permission to scan the selected tables and read their streams.
• Plan for read-capacity usage during the initial backfill. Estuary allows backfill concurrency and read-capacity allocation to be configured.

The IAM policy should include the permissions required for table discovery, initial scans, and stream consumption, including:

• dynamodb:ListTables
• dynamodb:DescribeTable
• dynamodb:Scan
• dynamodb:Query
• dynamodb:ListStreams
• dynamodb:DescribeStream
• dynamodb:GetShardIterator
• dynamodb:GetRecords

DynamoDB Streams retains change records for up to 24 hours. Monitor pipeline interruptions because an outage that exceeds this window may require recovery or another backfill.

Prerequisites for PostgreSQL

Before configuring PostgreSQL as the destination:

• Provide a PostgreSQL database that Estuary can reach directly or through an SSH tunnel.
• Authenticate with a database user and password or a supported cloud IAM authentication method.
• Select the destination database and schema. The default PostgreSQL port is 5432, and the default schema is commonly public.
• Allow the connector to create destination tables. Manually pre-created destination tables are not supported by default.
• Decide whether each binding should use standard updates or delta updates.
• Decide whether source deletes should remain as soft deletes or be propagated as hard deletes.
• Create required PostgreSQL indexes during table creation when query performance depends on them.

How updates and deletes are handled

PostgreSQL materializations support two update modes:

• Standard updates: Maintain a reduced destination table based on the collection key. This is the default and is usually appropriate when PostgreSQL should contain the latest version of each DynamoDB item.
• Delta updates: Append incoming events instead of reducing them to one current row. This can be useful for high-volume, event-oriented, or audit workloads.

Source deletes are represented as soft deletes by default, allowing downstream systems to inspect the operation through Estuary metadata. Enable hard deletes only when rows must be physically removed from PostgreSQL.

Step 1: Connect to DynamoDB Source

• Log in to your Estuary account or register for a new account if you don’t already have one.
  
• On the Estuary dashboard, click on the Sources present on the left side of the page.

• Within the Sources page, locate and click on the + NEW CAPTURE button.

• On the Create Capture page, use the Search connectors box to find the DynamoDB connector. When you see the connector in the search results, click on the Capture button.

• On the DynamoDB Create Capture page, provide a unique Name for your connector. Specify the mandatory Endpoint Config details, including Access Key ID, Secret Access Key, and Region.

• Once you fill in all the required fields, click on Next > Save and Publish.

Step 2: Connect to PostgreSQL Destination

• Navigate back to the Estuary dashboard and click on Destinations to configure PostgreSQL.
• Within the Destinations page, click on the + NEW MATERIALIZATION button.

• On the Create Materialization page, use the Search connectors box to locate the PostgreSQL connector. Once you locate it, click on the Materialization button within the same tile.

• On the PostgreSQL Create Materialization page, provide a distinct Name for the connector. Provide all the necessary information, including Database Username, Password, and Address.

• In case the data from DynamoDB hasn’t been filled in automatically, you can manually add it from the Source Collections section.

• After filling in all the details, click on the NEXT button. Then click on SAVE AND PUBLISH.

After completing these two simple steps, Estuary will continuously migrate your DynamoDB data to Postgres in real-time, ensuring that your database is always up-to-date.

Get a complete understanding of the above flow from Estaury’s documentation:

• Set up a Data Flow
• DynamoDB Source Connector
• PostgreSQL Destination Connector

Benefits of Using Estuary

• No-Code: Estuary eliminates the need for coding during extraction and loading, allowing you to perform data transfers without requiring advanced programming skills. This enables technical as well as non-technical users to effectively manage the migration process.
• Pre-built Connectors: With a wide range of 200+ inbuilt connectors, Estuary facilitates effortless integration between multiple sources and destinations. This reduces the overall time and complexity required to set up the migration process.
• Change Data Capture with sub-100ms latency: Estuary leverages CDC technology to detect and replicate only the modified data, applying changes to PostgreSQL in under 100ms. This minimizes transfer volume and keeps the target database perfectly up-to-date in real time.

Method 2: Export DynamoDB to Amazon S3 and load PostgreSQL

For a one-time migration or scheduled batch process, DynamoDB can export table data directly to Amazon S3 without consuming read-capacity units or affecting table performance.

DynamoDB supports full and incremental exports. Point-in-time recovery must be enabled for the source table before using this feature.

Step 1: Export the DynamoDB table to Amazon S3

In the AWS Management Console:

1. Open DynamoDB and select the source table.
2. Open Exports and streams.
3. Select Export to S3.
4. Choose a full or incremental export.
5. Select the point in time and destination S3 bucket.
6. Choose DynamoDB JSON or Amazon Ion as the export format.
7. Start the export and wait for it to complete.

The native export does not produce relational CSV files. It produces DynamoDB JSON or Amazon Ion, so the data must be transformed before it can be loaded into relational PostgreSQL tables.

Step 2: Transform the exported records

Use AWS Glue, Python, Spark, or another transformation engine to:

• Extract DynamoDB type annotations.
• Map partition and sort keys to PostgreSQL keys.
• Convert numbers, strings, binary values, booleans, lists, maps, and null values.
• Flatten nested attributes or preserve them in JSONB columns.
• Apply a stable PostgreSQL schema.
• Write the transformed data as CSV, Parquet, or another suitable intermediate format.

Step 3: Load the transformed data into PostgreSQL

For CSV data, PostgreSQL's COPY command is generally faster than inserting rows individually:

sql
COPY target_schema.target_table
FROM '/path/to/transformed-file.csv'
WITH (
    FORMAT CSV,
    HEADER TRUE
);

For managed or remote PostgreSQL services where server-side file access is unavailable, use \copy, a JDBC-based loader, AWS Glue, or the PostgreSQL bulk-loading method supported by your provider.

Limitations of the batch export method

• It does not continuously replicate new changes.
• DynamoDB's document structure must be converted into a relational schema.
• Incremental exports still require orchestration, transformation, and deduplication.
• Secondary indexes, DynamoDB APIs, and application access patterns are not migrated.
• Cutover requires a strategy for changes made after the selected export point.

2026 note: DynamoDB migration vs. ExtendDB

AWS engineers introduced ExtendDB, an early-stage open-source DynamoDB-compatible API with PostgreSQL as its first storage backend. It is intended for development, testing, and experimentation. It is not a managed service for copying an existing DynamoDB table into PostgreSQL, and it does not reproduce DynamoDB's managed scaling and availability guarantees.

Validate the DynamoDB-to-PostgreSQL pipeline

Before using the PostgreSQL tables in production:

1. Compare source item counts with destination row counts, accounting for filtered records and soft deletes.
2. Validate a sample of partition keys, sort keys, nested attributes, numbers, timestamps, and null values.
3. Test an insert, update, and delete in DynamoDB and confirm each operation reaches PostgreSQL.
4. Measure end-to-end replication lag under normal and peak workloads.
5. Confirm that destination indexes support the application's PostgreSQL queries.
6. Test recovery after temporarily interrupting the capture.
7. Update and test application code that previously depended on DynamoDB APIs or indexes.
8. Define a cutover window and a rollback plan before directing production traffic to PostgreSQL.

Conclusion

The right DynamoDB to PostgreSQL method depends on whether you need a one-time snapshot or a continuously updated destination.

Native DynamoDB exports to Amazon S3 are suitable for one-time migrations and scheduled batch processes, but they require data-model transformation and a separate PostgreSQL loading workflow. For continuously changing tables, a CDC pipeline can backfill existing items and then replicate inserts, updates, and deletes through DynamoDB Streams.

Whichever approach you choose, plan the schema mapping, delete behavior, indexes, validation, and application cutover before treating PostgreSQL as production-ready.

Want to keep PostgreSQL continuously updated from DynamoDB without managing a custom Streams pipeline? Start building your DynamoDB-to-PostgreSQL pipeline with Estuary or explore the DynamoDB connector documentation.