Amazon Redshift to PostgreSQL: How to Move Your Data

Moving data from Amazon Redshift to PostgreSQL runs against the usual direction of travel, and the size mismatch is the first thing to confront. Redshift scales from a few hundred gigabytes to more than a petabyte. Airbyte's own documentation recommends the Postgres destination for volumes of roughly 10GB or less, or for testing.

That is not a reason to abandon the pipeline. It is a reason to be precise about what you move. This guide covers the managed path with Airbyte, and the whole of it rests on one principle: you are moving a modelled result that an application needs to read, not a warehouse table.

Amazon Redshift to PostgreSQL at a glance:

Why move data from Redshift to PostgreSQL?

Two situations account for most of these pipelines, and both involve a small slice of a large warehouse.

The first is serving computed results to an application. Redshift calculates something expensive overnight, a segment membership, a propensity score, an aggregated balance, and a product needs to read it per request with low latency. Redshift is not built for that access pattern and Postgres is, so the warehouse computes and the database serves.

The second is giving something a connection it can actually use. Internal tools, admin panels and older reporting systems often speak Postgres and nothing else, and rewriting them is not on anyone's roadmap. Landing the specific tables they need is cheaper than the alternative. In both cases the operative word is specific.

What do you need before you start?

The credentials are ordinary. Two of these are decisions that determine whether this pipeline behaves:

A read-only Redshift user. Scoped to the schema you are reading rather than the whole cluster. The connector is built on the JDBC code base and does not alter the schema present in your warehouse, so it is a genuinely read-only operation on the source side.

A modelled table or view, not a raw one. Given the volume guidance on the destination, point this at a result set sized for an operational database. A wide analytical table that is cheap to scan in a columnar warehouse is a poor fit for a row-oriented one, so narrow it in Redshift first.

A cursor column. Incremental sync needs a user-defined cursor field such as an updated_at column, and for incremental dedupe you also nominate primary key columns. Without a cursor you are on full refresh, which on a warehouse table means re-reading everything on every run.

A Postgres user with write permission. On the target schema. If you already run a Postgres destination on an older connector version, read the migration guide before upgrading, because downstream models referencing raw tables will need updating.

If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on both the Redshift cluster and the Postgres instance before you begin.

How do you build a Redshift to PostgreSQL pipeline in Airbyte?

Step 1: Model the result in Redshift

Create a table or view in Redshift containing exactly the rows and columns the consuming application reads, with an updated_at column maintained on every write so there is something to use as a cursor. This is the step that makes the volume guidance on the destination irrelevant, because you are no longer moving warehouse-scale data, you are moving a result.

Step 2: Configure the Redshift source

Click Sources in the left navigation, then New Source, and select Redshift, following adding a source. Supply the host, port, database, schema and credentials. The easiest way to confirm network access is the check connection tool in the interface, which will tell you quickly whether your security group and IP rules are right. Full field details are in the Redshift source documentation.

Step 3: Configure the PostgreSQL destination

Click Destinations, then New Destination, and select Postgres, following adding a destination. From version 3.0.0 the Postgres destination uses Direct Load architecture, writing records straight to final tables with no intermediate raw tables. Each table carries your columns plus Airbyte metadata columns, including a JSONB column recording per-row typing changes.

Step 4: Create the connection and set a cursor

Click Connections, then New connection, select your tables, and nominate a cursor field so you can use an incremental sync mode. For incremental dedupe, also tell the connector which columns form the primary key. Set a frequency that matches how often the underlying model actually recomputes, because syncing hourly from something rebuilt nightly is work for nothing.

Schedule with your cluster in mind. Redshift concurrency is finite, and a sync reading a large table competes with the analytical queries the warehouse exists to serve. Running the sync shortly after your overnight models finish, rather than during the working day, avoids that contention entirely.

What does the cursor miss?

Incremental sync here is cursor-based, and the connector extracts changes with a query that amounts to selecting everything where the cursor is greater than its value at the last sync. That is simple and reliable, and it has two blind spots worth stating explicitly.

A row modified without its cursor column being updated will not be picked up. On a table your own models rebuild this is entirely within your control, which is why step 1 says to maintain the updated_at column deliberately rather than hoping one already behaves. If your transformation rewrites rows without touching a timestamp, the pipeline will quietly serve stale values.

And deletions are invisible, because a query filtered on a cursor cannot observe an absence. If rows leave your Redshift result set, for instance when a customer drops out of a segment, they will persist in Postgres indefinitely. A soft-delete flag in the modelled table, filtered downstream, is the clean fix. A periodic full refresh on that table is the blunt one.

When is PostgreSQL the wrong destination?

Airbyte's documentation is direct about this: Postgres is an excellent relational database but it is not a data warehouse, and Postgres destinations are recommended for small volumes of roughly 10GB or less, or for testing. Beyond that, expect slowdowns on large writes, and watch memory, CPU and IOPS during syncs. Postgres-compatible services such as Aurora are not exempt.

That guidance is unusually relevant here, because the source is a system designed for petabytes. If someone has asked you to replicate a Redshift schema into Postgres wholesale, the honest answer is that this is the wrong shape of project, and the conversation to have is about which specific result the consuming system needs.

There is also a structural mismatch behind the volume one. Redshift is columnar, so a table with two hundred columns is cheap to scan when you only touch three of them. Postgres is row-oriented and reads the whole row regardless. A wide analytical table that performs well in the warehouse can perform badly in the destination even at modest row counts, which is another argument for narrowing before you move.

Frequently asked questions

How much data can I move into PostgreSQL?

Airbyte recommends Postgres destinations for roughly 10GB or less, or for testing. Beyond that, watch memory, CPU and IOPS during syncs, and consider whether a warehouse destination is the better fit.

Does the Redshift source support CDC?

No. Incremental sync is cursor-based and needs a user-defined cursor field such as an updated_at column. Deletes are not captured.

Why are some updated rows not appearing in Postgres?

The cursor column was not updated when those rows changed. The connector selects records where the cursor exceeds its last value, so a modification that leaves the cursor untouched is invisible.

Will this pipeline slow down my Redshift cluster?

It consumes concurrency like any other query. Schedule syncs outside your busy analytical window, ideally after your overnight models complete, and read a narrow modelled table rather than a wide raw one.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. You will want to write the Redshift view or model in SQL, which is where the design work on this pipeline actually sits.

Get your Redshift data into PostgreSQL

Model a narrow result in Redshift with a reliable updated_at column, nominate it as your cursor, decide what a removed row should mean, and schedule the sync outside your busy analytical window. Do that and the volume guidance on the destination stops being a constraint, because you are moving a result rather than a warehouse.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same pattern applies to other warehouse and operational database pairings. For the equivalent pipeline out of a different warehouse, see BigQuery to MySQL, and for Redshift as a destination rather than a source, BigQuery to Amazon Redshift.

Start syncing now →