Postgres to DuckDB with CDC: One ATTACH Instead of a Nightly Export

DuckDB makes analytics on a laptop feel free, right up to the point where the data has to come from somewhere. For a lot of people that somewhere is Postgres. The first Postgres to DuckDB setup is usually a pg_dump or a CSV export loaded into a DuckDB file, and that file is stale by the time anyone opens it.

The usual ways to put Postgres data in front of DuckDB:

• The DuckDB postgres extension. DuckDB logs in to the live database and reads the current rows. Nothing is copied and there is nothing to operate, but every query lands on Postgres.
• pg_dump or COPY TO, then COPY into DuckDB. A script and a scheduler rebuild a DuckDB file. Freshness is hours to daily, and it does full reloads only, so updates and deletes never arrive as changes.
• Debezium and Kafka, with a consumer that writes Parquet. Changes show up within seconds. You operate Kafka, Kafka Connect, Debezium and the consumer, and the merge logic for updates and deletes is yours to write.
• Managed extract, load, transform (ELT) into a warehouse, with DuckDB reading the warehouse. Nothing to host, and freshness in minutes by plan. Pricing is per row, per GB or per connector, and the warehouse tables are a second copy you pay to move and store.

This walkthrough takes a fifth route. OLake Go, an open source (Apache 2.0) replication tool, does change data capture (CDC) on the Postgres write-ahead log (WAL) and commits the rows as Apache Iceberg tables into Apache Polaris. Polaris is an open source Iceberg Representational State Transfer (REST) catalog that you host next to a bucket you own.

DuckDB then attaches the whole catalog with one ATTACH and queries the tables where they sit. There is no DuckDB file to rebuild or pass around, and ClickHouse and Trino can read the same tables.

I ran this on one laptop: Postgres 16, Polaris 1.8.0 and MinIO in Docker, OLake Go v1.1.0, and DuckDB 1.5.5 in a local shell. The first sync copied 48 records in 22 seconds, and the second replayed 12 changes in 12 seconds. All the figures below come from that run.

What you need

• Docker, plus curl, jq and the AWS command line tool for the catalog script in step 2.
• Postgres 12 or later. Mine was Postgres 16, in a container.
• DuckDB 1.4 or later. It installs the iceberg and httpfs extensions the first time you ask for them.
• About 40 minutes.

Step 1: Prepare Postgres for CDC

Pointing at tables you already have? Skip the sample rows and go straight to the settings. To follow along with my exact data, connect with psql to a database named shop and create two tables:

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()); INSERT INTO customers (name, email, city, tier) VALUES ('Aarav Mehta','aarav@example.com','Mumbai','gold'), ('Priya Sharma','priya@example.com','Bengaluru','silver'), ('Liam Carter','liam@example.com','Austin','bronze'), ('Sofia Rossi','sofia@example.com','Milan','gold'), ('Kenji Tanaka','kenji@example.com','Tokyo','silver'), ('Emma Schulz','emma@example.com','Berlin','bronze'), ('Noah Kim','noah@example.com','Seoul','gold'), ('Zara Ali','zara@example.com','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; 

That is 8 customers and 40 orders. Next come the replication settings. wal_level only takes effect after a restart, and on managed Postgres it is a parameter group or server setting instead of ALTER SYSTEM.

ALTER SYSTEM SET wal_level = 'logical'; ALTER SYSTEM SET max_replication_slots = 10; ALTER SYSTEM SET max_wal_senders = 10; ALTER SYSTEM SET wal_sender_timeout = 0; -- so a long first snapshot is not cut off 

After the restart, create a role for OLake Go, a publication for the two tables and a replication slot on the built-in pgoutput plugin:

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; 

⚠️ One slot and one publication per job. Two jobs on the same slot read each other's changes, and both lose data.

Both sample tables have a primary key, so they need nothing more. A table without one needs ALTER TABLE <table> REPLICA IDENTITY FULL; so that its updates and deletes carry the old row.

Step 2: Run Polaris and MinIO, then create the catalog

Three things must exist before OLake Go can write: a Polaris server, a catalog in it that points at a bucket, and a principal allowed to manage that catalog. A principal is a client id plus a secret. Save this as docker-compose.yml, pick a root secret and run docker compose up -d:

services: minio: image: minio/minio hostname: lake-minio command: server /data --console-address ":9001" environment: { MINIO_ROOT_USER: minioadmin, MINIO_ROOT_PASSWORD: minioadmin } ports: ["9000:9000", "9001:9001"] polaris: image: apache/polaris:latest depends_on: [minio] ports: ["8181:8181", "8182:8182"] environment: AWS_REGION: us-east-1 AWS_ACCESS_KEY_ID: minioadmin AWS_SECRET_ACCESS_KEY: minioadmin POLARIS_BOOTSTRAP_CREDENTIALS: POLARIS,root,<root-secret> polaris.realm-context.realms: POLARIS 

In this form Polaris holds its own state in memory, so it is empty after every restart. A production deployment points it at Postgres.

With both containers up, the management API creates everything else. Give the aws command the MinIO keys (minioadmin above) as its credentials first, so it can make the bucket.

P=http://localhost:8181 # 1. A bucket for the tables aws --endpoint-url http://localhost:9000 s3 mb s3://lakehouse # 2. A root token TOKEN=$(curl -s $P/api/catalog/v1/oauth/tokens -H 'Polaris-Realm: POLARIS' \ -d grant_type=client_credentials -d client_id=root -d client_secret=<root-secret> \ -d scope=PRINCIPAL_ROLE:ALL | jq -r .access_token) H=(-H "Authorization: Bearer $TOKEN" -H 'Polaris-Realm: POLARIS' -H 'Content-Type: application/json') # 3. The catalog: where the files go, and how Polaris and the engines reach the store curl -s "${H[@]}" $P/api/management/v1/catalogs -d '{"catalog": { "name": "olake", "type": "INTERNAL", "readOnly": false, "properties": {"default-base-location": "s3://lakehouse/olake"}, "storageConfigInfo": {"storageType": "S3", "allowedLocations": ["s3://lakehouse/olake"], "endpoint": "http://<minio-host>:9000/", "endpointInternal": "http://lake-minio:9000", "pathStyleAccess": true, "region": "us-east-1"}}}' # 4. A principal for OLake Go and the engines. The response holds clientId and clientSecret: keep them. curl -s "${H[@]}" $P/api/management/v1/principals -d '{"principal": {"name": "olake"}}' # 5. Roles and the grant that lets the principal create namespaces and tables in the catalog curl -s "${H[@]}" $P/api/management/v1/principal-roles -d '{"principalRole": {"name": "olake_role"}}' curl -s "${H[@]}" $P/api/management/v1/catalogs/olake/catalog-roles -d '{"catalogRole": {"name": "olake_catalog_role"}}' curl -s -X PUT "${H[@]}" $P/api/management/v1/principals/olake/principal-roles -d '{"principalRole": {"name": "olake_role"}}' curl -s -X PUT "${H[@]}" $P/api/management/v1/principal-roles/olake_role/catalog-roles/olake -d '{"catalogRole": {"name": "olake_catalog_role"}}' curl -s -X PUT "${H[@]}" $P/api/management/v1/catalogs/olake/catalog-roles/olake_catalog_role/grants -d '{"type": "catalog", "privilege": "CATALOG_MANAGE_CONTENT"}' 

Three details in that script matter later:

• There are two store addresses. endpoint is what Polaris hands to engines together with short-lived credentials, and endpointInternal is what Polaris uses itself. MinIO needs both, and on AWS S3 you leave both out. In my run <minio-host> was host.docker.internal.
• The secret appears once. The response to call 4 holds clientId and clientSecret. OLake Go and DuckDB both log in with that pair.
• Hostnames must not contain underscores. Polaris is a Java service, and a store host such as lab_minio makes every table creation fail. That is why the MinIO container is called lake-minio.

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 log in with admin / password. Under Sources, click Create Source and choose Postgres.

1. Under Change data capture (CDC), keep Enable CDC on. Enter olake_slot as the Replication Slot and olake_pub as the Publication.
2. Under Connection details, fill in the host, port, database name, username and password. My Postgres ran in another container, so the host was host.docker.internal, with database shop and user olake.
3. Click Set up source.

The test runs before anything is saved. OLake Go checks the connection, then wal_level, the replication privilege, the slot and the publication, and shows the current and recommended value for anything that fails.

Step 4: Add the Polaris destination

Under Destinations, click Create Destination. Choose Apache Iceberg as the connector, Polaris as the catalog type and OAuth2 as the authentication type. Polaris and MinIO were containers too, so both addresses below go through host.docker.internal.

Click Create. OLake Go trades the credentials for a token, creates a namespace and a test table through Polaris, writes to the bucket and then drops the test table.

Step 5: Create the job and run the first sync

1. Open Jobs and click Create Job. Name it postgres_to_duckdb and set a schedule.
2. Pick the source and the destination from steps 3 and 4.
3. Under Advanced Settings, set Target Query Engines to DuckDB, and add Trino if it will read the tables too.
4. Click Next. On the Streams page, tick customers and orders.
5. For each table, set the sync mode to Full Refresh + CDC and the ingestion mode to Upsert.
6. Click Create Job, open the job's menu and choose Sync Now.

The schedule is how often the change stream is drained, not how often a full copy runs. With DuckDB and Trino as the target engines, OLake Go offers the delete formats both can read. My job ran with Positional Deletes, the format DuckDB documents, and OLake UI stores the small index that format keeps between runs.

The first sync copied 8 customers and 40 orders, 48 records and 2.23 KB, in 22 seconds with container start included. It notes the WAL position through the slot and then reads each table in parallel chunks. Nothing written during the copy is lost, and there is no separate backfill job.

Step 6: Query the tables in DuckDB

There is no registration step and no CREATE TABLE. Open a DuckDB shell and run this with the client id and secret from step 2:

INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs; CREATE SECRET polaris_secret ( TYPE iceberg, CLIENT_ID '<client_id>', CLIENT_SECRET '<client_secret>', OAUTH2_SERVER_URI 'http://<polaris-host>:8181/api/catalog/v1/oauth/tokens', OAUTH2_SCOPE 'PRINCIPAL_ROLE:ALL' ); -- MinIO, or any store whose address differs from the one Polaris vends CREATE SECRET store_secret ( TYPE s3, KEY_ID '<access key>', SECRET '<secret key>', ENDPOINT '<minio-host>:9000', URL_STYLE 'path', USE_SSL false, REGION 'us-east-1' ); ATTACH 'olake' AS polaris ( TYPE iceberg, ENDPOINT 'http://<polaris-host>:8181/api/catalog', SECRET polaris_secret, ACCESS_DELEGATION_MODE 'none' ); SHOW ALL TABLES; SELECT id, name, city, tier FROM polaris.postgres_to_duckdb_shop_public.customers ORDER BY id; 

Here <polaris-host> and <minio-host> are the addresses as DuckDB sees them. From the laptop that runs the containers, both are localhost, the same address the step 2 script used.

Two notes on that block:

• store_secret and ACCESS_DELEGATION_MODE 'none' are there for the laptop. Polaris vends the store address from the catalog, and that address may not resolve outside Docker. With Polaris on AWS S3 you drop both, and DuckDB reads with the credentials Polaris vends.
• The schema name is built for you. OLake Go joins the job name, the source database and the schema, which gave postgres_to_duckdb_shop_public. The Iceberg DB field at the top of the Streams page changes it before the first sync.

DuckDB fetches the OAuth2 token itself, and the attached catalog is read-only from its side. OLake Go is the writer.

Step 7: Change rows in Postgres and sync again

A nightly export has no answer for this part. Back in psql, I made 12 changes, 7 inserts and 5 updates:

INSERT INTO customers (name, email, city, tier) VALUES ('Maya Patel', 'maya@example.com', 'Pune', 'silver'), ('Lucas Silva', 'lucas@example.com', '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 12 seconds.

For customers, OLake Go wrote one data file holding 4 rows: the 2 inserts and the new versions of the 2 updated rows. One small positional delete file retires the 2 old versions. orders has the same shape with 8 rows and 3 deletes, and the commit report in the sync log spells it out.

Run the SELECT again. DuckDB shows the result on its next query, with no refresh: Liam Carter is gold, Zara Ali is in Abu Dhabi, and Maya Patel and Lucas Silva have arrived.

DuckDB returns 10 rows, one per key, because it applies the positional deletes while reading. Add _op_type to the column list to see how each row got there: r for the snapshot, c for an insert and u for an update.

Read the same tables from ClickHouse and Trino

This is what open tables buy you over an exported DuckDB file. Both engines read the files OLake Go wrote, through the same Polaris endpoint, and nothing is exported for them.

ClickHouse attaches the catalog as one database:

SET allow_database_iceberg = 1; CREATE DATABASE polaris ENGINE = DataLakeCatalog('http://<polaris-host>:8181/api/catalog') SETTINGS catalog_type = 'rest', warehouse = 'olake', catalog_credential = '<client_id>:<client_secret>', oauth_server_uri = 'http://<polaris-host>:8181/api/catalog/v1/oauth/tokens', auth_scope = 'PRINCIPAL_ROLE:ALL', vended_credentials = true, region = 'us-east-1'; SELECT id, name, city, tier FROM polaris.`postgres_to_duckdb_shop_public.customers` ORDER BY id; 

ClickHouse has one level of namespace, so the Iceberg namespace and the table name go inside one pair of backticks. Trino needs a single file, etc/catalog/polaris.properties:

connector.name=iceberg iceberg.catalog.type=rest iceberg.rest-catalog.uri=http://<polaris-host>:8181/api/catalog iceberg.rest-catalog.warehouse=olake iceberg.rest-catalog.security=OAUTH2 iceberg.rest-catalog.oauth2.credential=<client_id>:<client_secret> iceberg.rest-catalog.oauth2.scope=PRINCIPAL_ROLE:ALL iceberg.rest-catalog.oauth2.server-uri=http://<polaris-host>:8181/api/catalog/v1/oauth/tokens iceberg.rest-catalog.vended-credentials-enabled=true fs.native-s3.enabled=true s3.region=us-east-1 # MinIO or another S3-compatible store only: s3.endpoint=http://<minio-host>:9000 s3.path-style-access=true 

ClickHouse 26.8.36, a long-term support (LTS) release, and Trino 484 returned the same 10 rows as DuckDB. A roll-up of orders by status matched Postgres to the cent in all three: 16 delivered for 8659.68, 18 placed for 10522.26 and 11 shipped for 6397.25.

Tips from the run

• Expect three type changes. numeric shows up in DuckDB as DOUBLE, so round at query time when you need exact decimal arithmetic. timestamptz becomes TIMESTAMP WITH TIME ZONE in Coordinated Universal Time (UTC), and json and jsonb arrive as VARCHAR for DuckDB's json extension.
• Copy a hot table locally. DuckDB reads Parquet over the network on every query. For a table you hit all day, run CREATE TABLE local AS FROM polaris... once and query the local copy.
• New columns arrive on their own. Add a column in Postgres and OLake Go adds it to the Iceberg table on the next sync. Widening changes such as int to bigint are applied, and narrowing ones fail the sync instead of corrupting the table.
• Watch the slot. It pins WAL on the Postgres server 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.
• Plan for compaction. Nothing compacts the tables for you, and a CDC table gets a new small data file and delete file on each sync. Spark's rewrite_data_files works through the same Polaris endpoint.

How the Postgres to DuckDB methods compare

The postgres extension is the simpler choice for a one-off query against a small table you can reach directly. The route in this post is worth its setup when you want production Postgres out of the query path, or when more than one engine needs the same rows.

Where it is the wrong tool:

• One analyst, one laptop, one small table. The postgres extension or a nightly COPY is less to run than a catalog and OLake Go.
• Tables with no primary key. Inserts replicate, but updates and deletes cannot be matched to a row and are not applied. Add a key or set REPLICA IDENTITY FULL first.
• A reader that depends on equality deletes in DuckDB. Keep the job on positional deletes or deletion vectors.

FAQ

Can DuckDB write to the tables it attached?

No. The attached Polaris catalog is read-only from DuckDB, and OLake Go is the writer. For scratch work, copy a table into a local DuckDB table and change that copy.

How often can the sync run?

The schedule goes down to one minute. It sets how often the change stream is drained, so each run moves only the rows that changed. Every sync ends with one commit to Polaris, and DuckDB sees it on the next query.

Can I use Amazon S3 in place of MinIO?

Yes. Use an S3 bucket and an Identity and Access Management (IAM) role, and leave endpoint and endpointInternal out of the catalog. In DuckDB, drop store_secret and the ACCESS_DELEGATION_MODE line, because on AWS S3 the address Polaris vends resolves everywhere.

Do I have to attach the catalog again after every sync?

No. New and changed rows show up on the next query against the att...