PostgreSQL to Redshift: 3 Ways to Migrate or Replicate Data

PostgreSQL data can be moved to Amazon Redshift in three common ways: continuous CDC, AWS Database Migration Service (DMS), or a manual export-and-load workflow using Amazon S3 and Redshift COPY.

For ongoing analytics pipelines, log-based CDC is typically the better fit because inserts, updates, and deletes can be captured from PostgreSQL’s write-ahead log (WAL) and delivered to Redshift continuously. For one-time migrations or periodic batch loads, AWS DMS or an S3-based workflow may be simpler.

In this guide, we’ll compare three approaches for moving PostgreSQL data to Redshift:

• Estuary: managed PostgreSQL CDC with continuous delivery to Redshift
• AWS DMS: AWS-native migration and replication
• S3 + Redshift COPY: manual or scheduled batch loading

The right choice depends on whether you need a one-time migration, periodic batch updates, or continuous low-latency replication.

PostgreSQL to Redshift: 3 Migration and Replication Methods

Before diving into each method, it helps to understand how they differ. The three approaches vary significantly in replication latency, ongoing maintenance burden, and technical complexity. If you need data in Redshift updated continuously as changes happen in PostgreSQL, only a CDC-based approach will meet that requirement. If you are doing a one-time historical migration or occasional syncs, a batch method may be sufficient.

Use the table below to quickly identify which method fits your situation before working through the steps.

If you need ongoing Postgres to Redshift replication with minimal engineering overhead, start with Method 1. If you are already inside the AWS ecosystem and only need to migrate data periodically, Method 2 is a reasonable fit. If you are testing a concept or doing a one-time historical load on a small dataset, Method 3 gives you full control but requires the most manual work to maintain.

How Does PostgreSQL to Redshift Replication Work?

PostgreSQL-to-Redshift pipelines usually follow one of two patterns: continuous change data capture (CDC) or periodic batch loading.

For continuous replication, the flow typically looks like this:

PostgreSQL → WAL → CDC connector → transformation/staging → Amazon Redshift

1. PostgreSQL records inserts, updates, and deletes in its write-ahead log (WAL).
2. A CDC connector reads those changes using logical replication.
3. The changes are transformed into records that can be loaded into Redshift.
4. The destination pipeline applies new and updated data to Redshift tables.
5. An initial backfill is usually performed first so existing PostgreSQL data is loaded before ongoing changes continue.

For batch workflows, the architecture is different:

PostgreSQL → export → Amazon S3 → Redshift COPY

Data is exported from PostgreSQL on a schedule, stored in Amazon S3, and then loaded into Redshift using the COPY command.

The main difference is freshness. CDC continuously captures database changes as they occur, while batch pipelines only update Redshift when the next scheduled load runs.

PostgreSQL to Redshift: Migration vs Replication vs CDC

The right approach depends on whether you need to move data once, refresh it periodically, or keep Redshift continuously synchronized with PostgreSQL.

• Migration: A one-time or limited data move from PostgreSQL to Redshift. This is useful when changing systems or initially loading an analytics warehouse.
• Replication: Repeatedly copies PostgreSQL data into Redshift so the destination stays updated over time.
• Change data capture (CDC): Captures inserts, updates, and deletes from PostgreSQL’s write-ahead log (WAL) and continuously applies those changes downstream.

For a one-time move, AWS DMS full-load mode or an S3 + Redshift COPY workflow may be sufficient.

For scheduled analytics updates, batch replication can work well if data freshness of minutes or hours is acceptable.

For continuously changing production data, WAL-based CDC is generally the better fit because it captures only the changes rather than repeatedly extracting full tables.

Method 1: PostgreSQL to Redshift with Estuary

Estuary provides a managed way to move PostgreSQL data to Amazon Redshift using change data capture (CDC).

The pipeline looks like this:

PostgreSQL → WAL → Estuary capture → Estuary collection → Amazon Redshift

Estuary’s PostgreSQL connector uses logical replication to continuously capture changes from the PostgreSQL write-ahead log (WAL). Existing tables can be backfilled first, after which new inserts, updates, and deletes continue flowing through the same pipeline.

Captured data is stored in Estuary collections and then delivered to Redshift through the Amazon Redshift materialization connector. The Redshift connector uses Amazon S3 as temporary staging storage before materializing data into Redshift tables.

How to Set Up PostgreSQL to Redshift with Estuary

Step 1: Prepare PostgreSQL for CDC

Before creating the pipeline, configure PostgreSQL for logical replication.

The PostgreSQL source requires:

• wal_level=logical
• a PostgreSQL role with replication permissions
• a replication slot
• network access between Estuary and the PostgreSQL database

Estuary provides setup instructions for self-hosted PostgreSQL and managed services including Amazon RDS, Aurora, Google Cloud SQL, Azure Database for PostgreSQL, and Supabase.

Step 2: Create the PostgreSQL Capture

In Estuary:

1. Create a new PostgreSQL capture.
2. Enter the PostgreSQL database address and replication credentials.
3. Discover the available schemas and tables.
4. Select the tables you want to replicate.
5. Configure any backfill or advanced capture settings.
6. Publish the capture.

By default, the PostgreSQL connector can first backfill the current state of the selected tables and then transition to ongoing CDC.

Step 3: Prepare Amazon Redshift

To materialize the captured data into Redshift, you need:

• an accessible Amazon Redshift cluster
• credentials with permission to create tables in the target schema
• an Amazon S3 bucket for temporary staging
• AWS credentials with read and write access to that bucket

For best performance, Estuary recommends placing the staging S3 bucket in the same AWS region as the Redshift cluster.

Step 4: Create the Redshift Materialization

Once the PostgreSQL capture is running:

1. Create a new materialization.
2. Select the Amazon Redshift connector.
3. Enter the Redshift connection details.
4. Provide the S3 staging-bucket configuration and AWS credentials.
5. Select the Estuary collections created from PostgreSQL.
6. Map those collections to the desired Redshift tables.
7. Publish the materialization.

Estuary then continuously pushes changes from the captured collections into Redshift. In Estuary, this destination process is called a materialization.

When Is Estuary a Good Fit?

Estuary is a good fit when:

• Redshift needs to stay continuously updated from PostgreSQL
• you need WAL-based CDC rather than repeated full-table extracts
• you need an initial backfill followed by ongoing replication
• inserts, updates, and deletes need to be captured continuously
• you want to avoid operating Kafka, Debezium, or custom CDC infrastructure
• the same PostgreSQL data may need to feed additional destinations later

Things to Consider

• PostgreSQL must support logical replication for the CDC connector.
• Redshift materialization requires an S3 staging bucket.
• Your team still needs to configure database permissions, networking, and destination access.
• If you only need a one-time migration or occasional batch load, a simpler AWS DMS or S3 + COPY workflow may be sufficient.

If you’d like more detailed instructions, see our documentation on:

• The PostgreSQL source connector
• The Amazon Redshift materialization connector
• Detailed steps to create a Data Flow

Real-world example: Hayden AI uses Estuary to replicate PostgreSQL data to Amazon Redshift, including complex PostGIS geometry data. According to its Estuary case study, the company reduced replication lag from about 24 hours to approximately 1 hour, cut monthly data-pipeline costs by 60%, and backfilled 5 TB of historical data without disrupting production. Read the Hayden AI case study.

Method 2: PostgreSQL to Redshift with AWS DMS

AWS Database Migration Service (AWS DMS) can migrate PostgreSQL data to Amazon Redshift using either a full load, ongoing change data capture (CDC), or a combination of both.

A typical architecture looks like this:

PostgreSQL → AWS DMS replication → Amazon S3 staging → Amazon Redshift

When Redshift is the target, AWS DMS first writes migrated data to Amazon S3 and then loads it into Redshift. AWS DMS can handle the initial table load and continue applying incremental source changes afterward.

How AWS DMS Replication Works

For a PostgreSQL source, AWS DMS supports CDC using PostgreSQL logical replication.

During an initial migration, DMS performs a full load of the selected PostgreSQL tables. If ongoing replication is enabled, it then reads subsequent changes from PostgreSQL's write-ahead log (WAL) and applies them to the target.

For a Redshift target, the flow is:

1. AWS DMS reads PostgreSQL data or CDC events.
2. DMS writes the data into intermediate files in Amazon S3.
3. The files are loaded into the appropriate Redshift tables.
4. For CDC workloads, DMS continues processing net changes and periodically applies them to Redshift.

AWS DMS supports both full-load and change-processing operations for Amazon Redshift targets.

Step 1: Prepare PostgreSQL for CDC

If you only need a full-load migration, CDC-specific logical replication configuration may not be required.

For ongoing CDC, PostgreSQL must be configured for logical replication. For a self-managed PostgreSQL database, AWS documents settings including:

• wal_level = logical
• sufficient max_replication_slots
• sufficient max_wal_senders

For Amazon RDS for PostgreSQL, logical replication is enabled through the rds.logical_replication parameter.

AWS DMS uses logical replication slots to retain WAL changes until they have been consumed by the replication task. Because an inactive or lagging replication slot can retain WAL files and increase disk usage, AWS recommends monitoring source storage when CDC is enabled.

Step 2: Create the PostgreSQL Source Endpoint

In AWS DMS:

1. Create a PostgreSQL source endpoint.
2. Enter the PostgreSQL hostname, port, database name, username, and password.
3. Configure networking so the DMS replication resources can reach PostgreSQL.
4. Test the endpoint connection.

For CDC tasks, the database user also needs the required logical-replication permissions.

See the AWS DMS documentation for PostgreSQL sources for the current source requirements and limitations.

Step 3: Configure Amazon Redshift as the Target

Create an Amazon Redshift target endpoint in AWS DMS and provide:

• Redshift cluster endpoint
• database name
• username and password
• network connectivity
• required IAM permissions

AWS DMS uses Amazon S3 as intermediate storage before loading data into Redshift. When using the DMS console, AWS can create the required staging bucket and access configuration as part of the setup. A custom S3 bucket can also be configured.

The DMS replication instance and Redshift cluster must meet AWS's region and account requirements, so check the current Redshift target prerequisites before deployment.

Step 4: Create the Migration Task

Create an AWS DMS migration task and choose the migration mode that matches your use case:

• Migrate existing data: full load only
• Migrate existing data and replicate ongoing changes: full load + CDC
• Replicate data changes only: CDC only

Then select the PostgreSQL tables and schemas you want to migrate and configure any required table mappings or transformations.

For a PostgreSQL-to-Redshift analytics pipeline, full load + CDC is typically the relevant mode when you need both historical data and ongoing changes.

Step 5: Monitor the Migration

Once the task starts, monitor:

• full-load progress
• CDC latency
• table statistics
• task errors
• replication-slot health
• source WAL retention
• Redshift load performance

Monitoring is particularly important for continuous CDC because replication lag can cause PostgreSQL WAL files to accumulate if the DMS task stops consuming changes.

How Does AWS DMS Handle Inserts, Updates, and Deletes?

For CDC tasks, AWS DMS reads PostgreSQL changes through logical replication and applies those changes downstream.

Primary keys are important for reliably identifying rows during updates and deletes. AWS notes that PostgreSQL tables without primary keys may require additional replica-identity configuration for some CDC operations. Using REPLICA IDENTITY FULL can provide additional row information, but it also increases WAL generation and should be tested carefully.

When Is AWS DMS a Good Fit?

AWS DMS can be a good fit when:

• PostgreSQL and Redshift are already part of an AWS-centric architecture
• you need a one-time migration into Redshift
• you need an initial full load followed by ongoing CDC
• your team is comfortable managing AWS networking, IAM, endpoints, and replication resources
• staying within AWS-native services is an important architectural requirement

Things to Consider

• Redshift loads go through S3: DMS uses Amazon S3 as an intermediate layer rather than writing each PostgreSQL change directly into Redshift.
• CDC requires PostgreSQL logical replication: source configuration and replication-slot monitoring are required.
• Operational setup can be AWS-specific: IAM roles, networking, replication resources, endpoint configuration, and monitoring need to be managed.
• Primary-key design matters: CDC updates and deletes are easier to handle reliably when source tables have suitable primary keys.
• Replication latency can vary: freshness depends on workload, task configuration, staging, Redshift loading, and available replication capacity.

AWS DMS is therefore a strong option when you want an AWS-native migration or replication workflow, particularly if the rest of your data infrastructure already runs inside AWS.

Also Read: Critical Limitations of AWS DMS for CDC

Method 3: Move PostgreSQL Data to Redshift with S3 and COPY

A manual PostgreSQL-to-Redshift workflow can use Amazon S3 as an intermediate staging layer.

The architecture looks like this:

PostgreSQL → export files → Amazon S3 → Redshift COPY

This approach is best suited to one-time migrations or scheduled batch loads. It does not provide native PostgreSQL CDC by itself, so ongoing inserts, updates, and deletes require additional logic.

How the S3 + COPY Workflow Works

The process has two separate stages:

1. Export data from PostgreSQL into files such as CSV.
2. Upload those files to Amazon S3 and use Amazon Redshift's COPY command to load them into Redshift.

PostgreSQL can export query or table results using COPY ... TO, while Amazon Redshift uses its own COPY command to load files from S3 into an existing target table.

Step 1: Export Data from PostgreSQL

For example, PostgreSQL can export a table to CSV:

plaintext
COPY customers
TO '/path/customers.csv'
WITH (FORMAT CSV, HEADER);

For production workflows, you may instead export query results, split large datasets into multiple files, or use an external process that writes the exported data directly to object storage.

The important distinction is that PostgreSQL COPY ... TO exports the data, while Redshift COPY loads data into Redshift.

Step 2: Upload the Files to Amazon S3

Upload the exported files to an S3 location that Redshift can access.

For example:

plaintext
s3://my-data-bucket/postgres/customers/

For larger datasets, multiple files are usually preferable because Redshift can load files from S3 in parallel. AWS supports loading files by S3 prefix or through a manifest file that explicitly lists the objects to load.

Step 3: Create the Target Table in Redshift

Before running COPY, the destination table must already exist in Redshift.

For example:

plaintext
CREATE TABLE customers (
    id BIGINT,
    name VARCHAR(255),
    email VARCHAR(255),
    updated_at TIMESTAMP
);

Make sure PostgreSQL data types are mapped appropriately to Redshift types before loading the data.

Step 4: Give Redshift Access to S3

Amazon Redshift needs permission to read the S3 objects.

AWS recommends using an IAM role instead of embedding access keys directly in the COPY command. The IAM role needs permission to list the bucket and read the relevant objects.

A role can then be referenced by its ARN:

plaintext
arn:aws:iam::123456789012:role/RedshiftLoadRole

Step 5: Load the Data with Redshift COPY

Run the Redshift COPY command against the files in S3:

plaintext
COPY customers
FROM 's3://my-data-bucket/postgres/customers/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS CSV
IGNOREHEADER 1;

Redshift can load all files under an S3 prefix or use a manifest to control exactly which files are included. The COPY command appends rows to the target table, so repeat loads need to be designed carefully to avoid duplicate data.

How Do You Handle Incremental Loads?

For recurring batch replication, you need a way to export only new or changed PostgreSQL rows.

A common pattern is to use a timestamp column:

plaintext
SELECT *
FROM customers
WHERE updated_at > :last_export_time;

The resulting rows can then be exported, uploaded to a new S3 path, and loaded into Redshift.

This works for inserts and many updates, but it requires your own state-management logic to remember the last successful export.

Deletes are more difficult because deleted PostgreSQL rows no longer appear in subsequent queries. Handling them usually requires additional mechanisms such as:

• soft-delete flags
• audit tables
• delete logs
• triggers
• or a separate CDC process

For this reason, an S3 + COPY workflow is better considered a batch-loading pattern than a full CDC pipeline.

What About Automated COPY Jobs?

Amazon Redshift also supports COPY JOB, which can monitor an S3 location and automatically load newly created files using a stored COPY configuration.

For example:

plaintext
COPY public.customers
FROM 's3://my-data-bucket/postgres/customers/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS CSV
JOB CREATE postgres_customer_load
AUTO ON;

This can automate the S3-to-Redshift portion of the pipeline, but it does not solve PostgreSQL change capture by itself. You still need a separate process to export new or modified PostgreSQL records into S3.

Advantages of S3 + Redshift COPY

• Simple architecture: No dedicated CDC platform is required.
• Good for bulk loading: Redshift COPY is designed to load files from S3 efficiently and in parallel.
• Useful for one-time migrations: It works well when you primarily need to move an existing PostgreSQL dataset.
• Flexible file-based workflow: Data can be inspected or transformed before being loaded into Redshift.
• Uses native AWS services: S3 and Redshift integrate directly through IAM-based access.

Things to Consider

• It is not native CDC: PostgreSQL changes are only reflected in Redshift when your next export and load runs.
• Updates require extra logic: You need a strategy for merging or replacing changed rows.
• Deletes require separate tracking: Normal incremental SQL queries cannot detect rows that no longer exist.
• Duplicate loads are possible: Redshift COPY appends incoming rows, so repeated files or incorrect state tracking can create duplicates.
• You manage orchestration: Export scheduling, file naming, S3 uploads, load state, retries, and cleanup all become your responsibility.
• IAM configuration is required: Redshift must have permission to access the staging files in S3.

When Should You Use S3 + COPY?

This approach is a good fit when:

• you need a one-time PostgreSQL-to-Redshift migration
• hourly or daily batch loads are sufficient
• you already have an orchestration system for exports and S3 uploads
• your team wants direct control over the file-based loading process
• continuous CDC is not required

If Redshift needs to stay continuously synchronized with PostgreSQL, a WAL-based CDC approach is generally a better fit than building incremental export and delete-tracking logic around S3 and COPY.

How Are PostgreSQL Inserts, Updates, and Deletes Handled in Redshift?

How changes reach Redshift depends on the replication method.

Estuary

Estuary’s PostgreSQL connector reads inserts, updates, and deletes from PostgreSQL’s write-ahead log using logical replication.

Delete events include the record key and are marked as delete operations. For Redshift, Estuary supports two delete behaviors:

• Soft delete: the row remains in the destination and its metadata indicates that it was deleted.
• Hard delete: when enabled, the corresponding row is physically removed from Redshift.

This allows the same pipeline to handle an initial backfill and ongoing row-level changes without building separate delete-tracking logic.

AWS DMS

AWS DMS can perform a full load first and then continue processing PostgreSQL changes with CDC. For PostgreSQL sources, DMS uses logical replication and reads changes retained in the WAL.

Primary keys matter here. AWS states that if a PostgreSQL table has no primary key, DMS can ignore UPDATE and DELETE operations unless additional replica-identity configuration is used. REPLICA IDENTITY FULL can be used in some cases, but it increases WAL volume and should be tested carefully.

S3 + Redshift COPY

A file-based S3 + COPY workflow behaves differently.

New rows are easy to append, but updates and deletes require custom logic:

• Inserts: export the new rows and load them with COPY.
• Updates: load changed rows into a staging table and merge them into the destination.
• Deletes: maintain a soft-delete flag, deletion log, audit table, or another mechanism that records which source rows were removed.

A normal incremental query cannot detect a row after it has already been deleted from PostgreSQL.

That is the key architectural difference: CDC captures database changes directly, while batch loading requires you to reconstruct change behavior yourself.

PostgreSQL to Redshift: Which Method Should You Choose?

Choose the replication method based on data freshness, change handling, and how much infrastructure you want to operate.

Use Estuary when

Choose Estuary when PostgreSQL is an active production system and Redshift needs to stay continuously updated without your team operating the CDC plumbing.

This is particularly relevant when you need:

• an initial backfill followed by ongoing CDC
• reliable inserts, updates, and deletes
• one PostgreSQL capture reused across additional destinations
• managed recovery, checkpointing, and destination delivery

Estuary’s Redshift connector uses S3 as temporary staging and can materialize source deletes as either soft deletes or physical deletes depending on configuration.

Use AWS DMS when

Choose AWS DMS when your migration or replication workflow is already centered on AWS and you want an AWS-native service for:

• one-time full migrations
• full load followed by ongoing CDC
• CDC-only replication
• PostgreSQL-to-Redshift migration managed within AWS infrastructure

For PostgreSQL CDC, pay particular attention to primary keys, replication slots, WAL retention, and source-table replica identity. AWS DMS uses logical replication for PostgreSQL CDC and supports both full-load and change-processing workflows for Redshift targets.

Use S3 + COPY when

Use a manual S3 + COPY workflow when:

• the move is one-time
• hourly or daily freshness is enough
• your team already has export/orchestration tooling
• you want direct control over file generation and Redshift loading
• you do not need native CDC

It is usually less suitable when PostgreSQL changes frequently and Redshift must reflect updates and deletes continuously, because you have to build that change-tracking and merge logic yourself.

The main decision

If the requirement is simply “move this PostgreSQL dataset into Redshift once,” a batch migration may be enough.

If the requirement is “keep Redshift synchronized with a production PostgreSQL database,” use a CDC-based architecture that reads PostgreSQL WAL changes rather than repeatedly exporting tables.