Postgres to Redshift: A CDC Walkthrough That Ends in One External Schema

Redshift will answer almost any question about your data, as long as the data is already there. Getting it from Postgres to Redshift is your job, and the first version is usually a nightly dump to Amazon S3 followed by COPY. That holds until an order is cancelled at noon and the evening dashboard still counts it.

The common ways to move Postgres data into Redshift:

• COPY from S3 exports on a schedule. A script, a scheduler and a staging bucket. Freshness is hours to daily, and updates and deletes need full reloads or your own merge.
• AWS Database Migration Service (DMS). Change data capture (CDC) through logical replication into native Redshift tables, updates and deletes included. You run a replication instance and a task, plus an S3 staging bucket.
• Aurora PostgreSQL zero-ETL integration. ETL is extract, transform, load, and here there is none to run: changes reach native Redshift tables within seconds. The source is Aurora PostgreSQL, and Redshift is the only reader.
• Managed extract, load, transform (ELT) tools such as Fivetran, Estuary or Airbyte Cloud. Nothing to host, and freshness in minutes by plan. You pay per row, per GB or per connector for a second copy inside Redshift.

This tutorial takes a fifth route. OLake Go, an open source (Apache 2.0) replication tool, reads the Postgres write-ahead log (WAL) and commits the changes as Apache Iceberg tables into the AWS Glue Data Catalog. The files stay in an S3 bucket you own.

Redshift maps that Glue database onto an external schema with one statement and queries the tables in place. Athena and DuckDB read the same tables through the same catalog.

I ran this against Postgres 16 and a Redshift Serverless workgroup in ap-southeast-1, with OLake Go v1.1.0. The first sync copied 48 records in 19 seconds, and a second sync replayed 12 changes in 18 seconds. Every number below is from that run.

What you need

Postgres 12 or later, Docker, an AWS account, Redshift Serverless or an RA3 cluster, and about 40 minutes. My workgroup ran at 8 Redshift Processing Units (RPU).

The pipeline is OLake UI from its Docker Compose install, with one job that copies two tables, customers and orders, and then replays the WAL.

Step 1: Prepare Postgres

Skip the container if you already have a database. For a local test, this starts Postgres 16 with logical replication switched on:

docker run -d --name pg_cdc -p 5432:5432 \
  -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=shop \
  postgres:16 \
  -c wal_level=logical -c max_replication_slots=10 \
  -c max_wal_senders=10 -c wal_sender_timeout=0

On a server you manage, those four settings are ALTER SYSTEM SET statements, and wal_level needs a restart. On Amazon Relational Database Service (RDS), Aurora, Cloud SQL, Azure, Neon or Supabase, the same settings sit behind a parameter group or a dashboard toggle.

Connect with psql -h localhost -U postgres -d shop, then create the tables and the starting rows:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100), email VARCHAR(200), city VARCHAR(100), tier VARCHAR(20),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW());

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id), product VARCHAR(100), amount NUMERIC(10,2), status VARCHAR(20),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW());

-- 8 customers and 40 orders
INSERT INTO customers (name, email, city, tier) VALUES
  ('Aarav Mehta','[e-mail]','Mumbai','gold'),   ('Priya Sharma','[e-mail]','Bengaluru','silver'),
  ('Liam Carter','[e-mail]','Austin','bronze'),  ('Sofia Rossi','[e-mail]','Milan','gold'),
  ('Kenji Tanaka','[e-mail]','Tokyo','silver'), ('Emma Schulz','[e-mail]','Berlin','bronze'),
  ('Noah Kim','[e-mail]','Seoul','gold'),        ('Zara Ali','[e-mail]','Dubai','silver');

INSERT INTO orders (customer_id, product, amount, status)
SELECT 1 + (n % 8), (ARRAY['Laptop','Headphones','Monitor','Keyboard','Mouse','Webcam'])[1 + n % 6],
       ROUND(20 + ((n * 7919) % 120000) / 100.0, 2), (ARRAY['placed','shipped','delivered'])[1 + n % 3]
FROM generate_series(1, 40) AS n;

OLake Go connects as its own role and reads changes through a publication and a pgoutput replication slot:

CREATE ROLE olake WITH LOGIN REPLICATION PASSWORD '<password>';
GRANT USAGE ON SCHEMA public TO olake;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO olake;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO olake;

CREATE PUBLICATION olake_pub FOR TABLE customers, orders;
SELECT pg_create_logical_replication_slot('olake_slot', 'pgoutput');

-- check
SHOW wal_level;                                        -- logical
SELECT slot_name, plugin, active FROM pg_replication_slots;
SELECT pubname, puballtables FROM pg_publication;

💡 Give every job its own slot and publication. Two jobs on one slot read each other's changes, and both lose data.

Two notes for a production database:

• Primary keys. A table without one needs ALTER TABLE <table> REPLICA IDENTITY FULL; so that its updates and deletes carry the old row.
• The slot pins WAL. Postgres keeps the log until OLake Go has read it. Keep the job on a schedule, alert on pg_replication_slots lag, and drop the slot when you retire a job.

Step 2: A bucket, a writer and a role for Redshift

Three things have to exist in AWS before OLake Go writes anything.

1. An S3 bucket for the table files, in the region where your engines run. Mine was in ap-southeast-1.
2. An Identity and Access Management (IAM) user for OLake Go with Glue and S3 permissions. I used a scoped user with the inline policy below and created an access key pair for it.
3. An IAM role for Redshift, attached to the Serverless namespace or the cluster, that can read Glue and the bucket. Mine had AmazonS3ReadOnlyAccess plus read access to Glue.

{
  "Version": "2012-10-17",
  "Statement": [
    { "Effect": "Allow", "Action": ["s3:ListBucket", "s3:GetBucketLocation"], "Resource": "arn:aws:s3:::<bucket>" },
    { "Effect": "Allow", "Action": ["s3:GetObject", "s3:PutObject", "s3:DeleteObject", "s3:AbortMultipartUpload", "s3:ListMultipartUploadParts"], "Resource": "arn:aws:s3:::<bucket>/*" },
    { "Effect": "Allow",
      "Action": ["glue:GetDatabase", "glue:GetDatabases", "glue:CreateDatabase", "glue:GetTable", "glue:GetTables", "glue:CreateTable", "glue:UpdateTable", "glue:DeleteTable"],
      "Resource": ["arn:aws:glue:<region>:<account>:catalog", "arn:aws:glue:<region>:<account>:database/*", "arn:aws:glue:<region>:<account>:table/*/*"] }
  ]
}

You do not create a Glue database. OLake Go makes one per job on the first sync and registers each table in it.

Step 3: Start OLake UI and connect Postgres

curl -sSL https://raw.githubusercontent.com/datazip-inc/olake-ui/master/docker-compose-v1.yml | docker compose -f - up -d

Open http://localhost:8000 and sign in with admin / password.

Go to Sources, click Create Source and pick Postgres as the connector. Under Change data capture (CDC), leave Enable CDC on and enter olake_slot as the Replication Slot and olake_pub as the Publication. Under Connection details I used host host.docker.internal, because Postgres ran in another container, with database shop and user olake.

Click Set up source. OLake Go tests the connection and then checks wal_level, the replication privilege, the slot and the publication before anything is saved. When one of them fails, the dialog names the setting and its recommended value.

Step 4: Add the Glue destination

Go to Destinations and click Create Destination. Pick Apache Iceberg as the connector and AWS Glue as the catalog type, then fill in the form:

Field	Value
S3 Path	s3://<bucket>/lakehouse/, the path OLake Go writes under
AWS Region	the region of the bucket and the Glue catalog, ap-southeast-1 in my run
AWS Access Key and AWS Secret Key	the key pair of the IAM user from step 2

Click Create. If OLake Go runs on an instance that has a role, leave the two key fields empty and it uses the instance role.

Step 5: Create the job and run the first sync

One detail is specific to Redshift. It is not in OLake Go's target engine picker yet, so on this job you set the delete format yourself.

1. Open Jobs and click Create Job. Name it postgres_to_redshift and give it a schedule.
2. Select the source and the destination from steps 3 and 4.
3. Under Advanced Settings, set Target Query Engines to AWS Athena and DuckDB.
4. Click Next. On the Streams page, tick customers and orders.
5. For each table, set the sync mode to Full Refresh + CDC, the ingestion mode to Upsert and the Upsert Type to Positional Deletes.
6. Click Create Job, then open the job's menu and choose Sync Now.

Positional deletes are the format Redshift reads, and the two listed engines read it too.

In my run the first sync copied 8 customers and 40 orders (48 records, 2.23 KB) and finished in 19 seconds, container start included. It records the WAL position through the slot first and then reads each table in parallel chunks, so nothing written during the copy is lost.

OLake Go names the Glue database after the job, the source database and the schema. Mine became postgres_to_redshift_shop_public, and the Iceberg DB field at the top of the Streams page changes that name before the first sync.

Step 6: Query the tables in Redshift

Open Redshift query editor v2 and connect to the workgroup or cluster. The first statement maps the Glue database onto a schema, and you run it once:

CREATE EXTERNAL SCHEMA lakehouse
FROM DATA CATALOG
DATABASE 'postgres_to_redshift_shop_public'
IAM_ROLE default;

SELECT id, name, city, tier FROM lakehouse.customers ORDER BY id;
SELECT status, count(*) AS n, round(sum(amount), 2) AS total
FROM lakehouse.orders GROUP BY status ORDER BY status;

IAM_ROLE default uses the default role of the namespace or cluster. The schema is read-only: OLake Go is the writer, and an INSERT or DELETE on these tables fails.

⚠️ Create the external schema after the first sync, in the workgroup's region. Before that the Glue database is not there, and Redshift answers ERROR: Database 'postgres_to_redshift_shop_public' does not exist in the Glue Data Catalog.

Step 7: Change rows and sync again

A nightly COPY would miss everything in this step until its next full reload. Back in psql, make 12 changes, 7 inserts and 5 updates:

INSERT INTO customers (name, email, city, tier) VALUES
  ('Maya Patel', '[e-mail]', 'Pune', 'silver'),
  ('Lucas Silva', '[e-mail]', 'São Paulo', 'bronze');
INSERT INTO orders (customer_id, product, amount, status) VALUES
  (9, 'Laptop', 1299.00, 'placed'), (9, 'Mouse', 25.50, 'placed'),
  (10, 'Monitor', 349.99, 'placed'), (3, 'Keyboard', 89.00, 'placed'), (7, 'Webcam', 79.90, 'placed');
UPDATE customers SET tier = 'gold', updated_at = NOW()      WHERE name = 'Liam Carter';
UPDATE customers SET city = 'Abu Dhabi', updated_at = NOW() WHERE name = 'Zara Ali';
UPDATE orders    SET status = 'delivered', updated_at = NOW() WHERE id IN (1, 4, 7);

Choose Sync Now again. The second sync read 12 records (594 bytes) from the slot and finished in 18 seconds end to end.

For customers it wrote one new data file with 4 rows: the 2 inserts plus the new versions of the 2 updated rows. One small positional delete file retires the 2 old versions, and orders has the same shape with 8 rows and 3 deletes.

Now re-run the first SELECT in Redshift, with no refresh and no ALTER TABLE before it. Liam Carter is gold, Zara Ali has moved to Abu Dhabi, and the two new customers are there.

Redshift returns 10 rows, one per key, because it applies the positional deletes while reading. The orders roll-up matched Postgres to the cent: 16 delivered orders for 8659.68, 18 placed for 10522.26 and 11 shipped for 6397.25.

OLake Go adds an _op_type column to every table. It is r for a row from the first snapshot, c for an insert, u for an update and d for a delete.

Read the same tables from Athena and DuckDB

Both engines open the files OLake Go already wrote, so no export job runs.

Athena. The Glue database appears in the query editor's database list with no registration. Pick a workgroup that has a query result location in S3, select the database and run:

SELECT id, name, city, tier FROM postgres_to_redshift_shop_public.customers ORDER BY id;
SELECT status, count(*) AS n, round(sum(amount), 2) AS total
FROM postgres_to_redshift_shop_public.orders GROUP BY status ORDER BY status;

DuckDB. It reads a table from its current metadata file, which Glue stores as the table's metadata_location. Ask Glue for the location, then scan it:

aws glue get-table --database-name <glue database> --name customers \
  --query 'Table.Parameters.metadata_location' --output text

INSTALL iceberg; LOAD iceberg;
INSTALL httpfs;  LOAD httpfs;

CREATE SECRET lake (TYPE s3, KEY_ID '<access key>', SECRET '<secret key>', REGION '<region>');

SELECT id, name, city, tier
FROM iceberg_scan('s3://<bucket>/lakehouse/postgres_to_redshift_shop_public.db/customers/metadata/<latest>.metadata.json')
ORDER BY id;

DuckDB 1.5.5 returned the same 10 customers and the same roll-up as Redshift and Athena. Each sync writes a new metadata file, so fetch the location again before a fresh read.

Tips from the run

• Three type changes to expect. numeric arrives as DOUBLE PRECISION, so round at query time when you need exact decimal arithmetic. timestamptz becomes TIMESTAMP in Coordinated Universal Time (UTC), and json and jsonb arrive as VARCHAR for JSON_PARSE.
• A stale probe table can fail the destination test. The test opens a table called test_olake.test_olake in Glue. Mine failed with is not authorized to perform: s3:GetObject because an earlier run in that account and region had left the table pointing at another bucket. Delete the stale table, or use a region whose catalog does not have it, which is what I did.
• Give Athena its own workgroup. Athena needs a query result location in S3 before it runs anything. I used a workgroup that writes to s3://<bucket>/athena-results/ and left the shared primary one alone.
• Plan for compaction. Each sync adds a small data file and a delete file per table, and Glue has no built-in compaction for tables written by external engines. Spark on Amazon Elastic MapReduce (EMR) can run rewrite_data_files through the same catalog.

How this compares with other Postgres to Redshift methods

Method	Freshness	Updates and deletes	Where the data lands	What you run
COPY from S3 exports	hours to daily	no; full reloads or your own merge	native Redshift tables	a script, a scheduler and a staging bucket
AWS DMS	CDC through logical replication	yes	native Redshift tables	a replication instance and task, plus a staging bucket
Aurora PostgreSQL zero-ETL	seconds	yes	native Redshift tables; Redshift is the only reader	nothing; the source is Aurora PostgreSQL
Fivetran, Estuary, Airbyte Cloud	minutes, by plan	varies by tool	Redshift tables, a second copy	nothing; per-row, per-GB or per-connector pricing
OLake Go to Glue (this post)	CDC on a schedule down to one minute	yes, as Iceberg row-level deletes	Iceberg tables that Redshift, Athena, EMR, Spark and DuckDB all read	one Docker Compose stack, open source

If your source is Aurora PostgreSQL and Redshift is the only reader, the zero-ETL integration is the simpler choice. The route in this post earns its setup when Athena, EMR or a notebook needs the same rows, or when you want to own the files.

Its limits, stated plainly:

• It is not native Redshift speed. An external schema is read through Redshift Spectrum, which scans Parquet in S3 on every query. For a hot dashboard, load a slice into a native table on a schedule.
• Tables need a primary key. Without one, inserts replicate but updates and deletes are not applied, unless you set REPLICA IDENTITY FULL.
• Stay on positional deletes. Redshift reads deletion vectors only on Serverless above 4 RPU or on RG-type clusters, and Athena cannot read Iceberg v3 tables at all.

FAQ

What happens when I add a column in Postgres?

OLake Go adds it to the Iceberg table on the next sync and updates the Glue table definition in the same commit. Redshift shows the new column on the next query of the external schema.

How often can the sync run?

The job runs on a schedule, and the interval goes down to one minute. Each run replays only what changed since the previous one, and my 12-change sync took 18 seconds.

Does this work on a provisioned Redshift cluster?

Yes. Redshift Serverless and RA3 clusters both read the Glue Data Catalog through an external schema. Attach the IAM role to the cluster instead of the namespace, and IAM_ROLE default then uses the cluster's default role.

What does this pipeline cost to run?

Storage is your own bucket at S3 rates, and Glue adds only its per-object pricing for the table entries. Redshift Serverless bills RPU-hours only while queries run, and Athena bills per terabyte scanned. OLake Go is open source and runs wherever you run Docker.

Where to go from here

• Point the source at your real database. Managed Postgres sets wal_level through a parameter group or a server setting, not ALTER SYSTEM.
• Once the change volume grows, put compaction on a schedule, with Spark's rewrite_data_files on EMR or with OLake Fusion.
• The full guide, with the command line configuration, the data type table, schema change rules and troubleshooting, is on olake.io.

If your Redshift or Glue setup returns something different at one of these steps, post the exact message in the comments and I'll look into it.