Skip to main content

Postgres to DuckDB: CDC via Apache Iceberg

Postgres to DuckDB usually means pg_dump, a CSV or Parquet export, and a DuckDB file somebody rebuilds, or DuckDB's postgres extension hitting the live database. This guide takes a different route.

OLake Go reads the Postgres write-ahead log (WAL) and commits the changes as Apache Iceberg tables into an Apache Polaris catalog. DuckDB attaches that catalog and queries the tables in place.

Polaris is an open source Iceberg REST (Representational State Transfer) catalog you run yourself, with the files in your own bucket. Nothing else is in the path: no Kafka, no Debezium, no DuckDB file to copy around. ClickHouse and Trino read the same tables through the same endpoint.

By Shubham Satish BaldavaUpdated October 29, 2026

Key takeaways

  • OLake Go replicates Postgres into Iceberg tables in Polaris: a full historical snapshot first, then change data capture (CDC) from the WAL, with no Kafka or Debezium in between.
  • DuckDB attaches the whole Polaris catalog with one ATTACH, lists every table OLake Go committed, and sees every change the moment a sync commits. Every session reads the same tables, on a laptop or in a job.
  • The same tables, written once by OLake Go, are also queryable from ClickHouse and Trino through the same catalog. In the run on this page all three engines returned identical rows.

The short answer. To replicate Postgres to DuckDB with change data capture (CDC), run OLake Go with a Postgres replication slot as the source. Point it at an Apache Polaris catalog as the Iceberg destination. The first sync copies each table in full (the historical snapshot); every later sync replays inserts, updates and deletes from the WAL. DuckDB, ClickHouse and Trino then query the same Iceberg tables, with no second copy.

How Postgres CDC reaches DuckDB here​

Two things happen in order, and OLake Go does both on its own. The first sync records the current WAL position through the replication slot. Then it reads each table in parallel chunks and writes Parquet data files: that is the historical load.

Every later sync opens the slot at the saved position and replays the inserts, updates and deletes since. Nothing written during the snapshot is lost, and there is no separate backfill job.

Each sync ends with one commit to Polaris. OLake Go asks Polaris for short-lived storage credentials, uploads the data and delete files, then posts the new snapshot to the catalog. DuckDB, ClickHouse and Trino see it on their next query; none of them keeps a copy.

One thing to keep an eye on: the replication slot pins WAL on the Postgres server until OLake Go has read it. A job that is paused for days makes the WAL grow by everything written since. Keep the job running on a schedule, and alert on pg_replication_slots lag if disk is tight.

What you need​

  • Postgres 12 or later with wal_level = logical and a user that can read the WAL. The tables on this page ran on Postgres 16. Hosted Postgres on Amazon Relational Database Service (RDS), Aurora, Google Cloud SQL, Azure, Neon or Supabase works the same way; step 1 links the setup guides.
  • An Apache Polaris server and a bucket, both reachable from OLake Go and from wherever DuckDB runs. The run on this page used Polaris 1.8.0 and MinIO in Docker; on AWS, use an S3 bucket and an IAM role instead.
  • DuckDB 1.4 or later with the iceberg and httpfs extensions, which DuckDB installs on first use. The run on this page used DuckDB 1.5.5.
  • OLake UI from the Docker Compose install. The run on this page used OLake Go v1.1.0.
  • Docker, and about 40 minutes.

Step 1: Prepare Postgres for CDC​

Most readers point OLake Go at tables they already have. If you want to follow along with the exact data from this page, create these two tables first:

The two sample tables used on this page
Sample schema
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());

-- Then insert whatever you like. This page started with 8 customers and 40 orders.

OLake Go reads changes through logical replication with the built-in pgoutput plugin (Postgres 10 and later). Four things must be in place on the database before the first sync.

SettingValueWhy it matters
wal_levellogicalWithout it Postgres writes no row images to the WAL (write-ahead log), so there is nothing to replicate. Changing it needs a restart.
Replication roleLOGIN REPLICATION plus SELECT on the tablesThe first snapshot reads the tables; the change stream opens a replication connection.
Publicationone per OLake Go job, covering the synced tablesTells Postgres which tables' changes to publish.
Replication slotone per OLake Go job, plugin pgoutputKeeps WAL segments until OLake Go has read them.
On the source database
-- 1. Logical replication. wal_level needs a restart (a parameter-group change on managed Postgres).
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

-- 2. A role for OLake Go
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;

-- 3. A publication for the tables you sync, and a pgoutput slot
CREATE PUBLICATION olake_pub FOR TABLE customers, orders;
SELECT pg_create_logical_replication_slot('olake_slot', 'pgoutput');

Tables with a primary key need nothing more. A table without one needs ALTER TABLE <table> REPLICA IDENTITY FULL; so that its updates and deletes carry the old row.

Check the result before moving on:

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

Managed Postgres sets wal_level through a parameter group or server setting rather than ALTER SYSTEM. Each flavour has its own guide:

Where Postgres runsGuide
Amazon RDSRDS Postgres CDC setup
Amazon AuroraAurora Postgres CDC setup
Azure Database for PostgreSQLAzure Postgres CDC setup
Google Cloud SQLCloud SQL Postgres CDC setup
Your own server or VMGeneric Postgres CDC setup
Docker, for a local testLocal Postgres with Docker Compose
One slot and one publication per job

Two OLake Go jobs sharing a replication slot read each other's changes and both lose data. Create a separate slot and publication for every job.

Step 2: Set up the Polaris catalog​

DuckDB reads Iceberg tables through a catalog. Apache Polaris is the one this page uses: an open source REST catalog that you run next to your bucket.

OLake Go writes into it, and DuckDB, ClickHouse and Trino read from it with the credentials Polaris hands out. The Iceberg files sit in your own S3 or MinIO bucket; Polaris holds only the pointers and the permissions.

CatalogOLake Go writesDuckDB readsVerified on this page
Apache PolarisYesYes: ATTACH to the REST endpointYes, end to end

Apache Polaris is an open source Iceberg REST catalog. It keeps the table metadata pointers, hands out short-lived storage credentials to engines, and applies its own role-based access control. OLake Go writes to it through the standard Iceberg REST API, and ClickHouse, Trino, DuckDB and Spark read from the same endpoint. The files live in an S3 bucket or an S3-compatible store that you own.

Three things need to exist before OLake Go can write: a Polaris server, a catalog inside it that points at your bucket, and a principal (a client id and secret) with the right to manage that catalog. The run on this page used Polaris 1.8.0 and MinIO in Docker; on AWS, swap the MinIO endpoint for a plain S3 bucket and an IAM role.

docker-compose.yml (Polaris and MinIO, for a local test)
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

Polaris stores its own state in memory with this setup, so it starts empty every time; a production deployment points it at Postgres. Once it is up, create the bucket, the catalog and the principal through the management API. root is the bootstrap principal; the olake principal is the one OLake Go and the engines use.

Polaris, once
P=http://localhost:8181

# 1. A bucket for the tables (MinIO; on AWS create it in the console or with aws s3 mb)
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"}'

What each block is for:

  • endpoint is the store address Polaris hands to engines along with vended credentials; endpointInternal is the address Polaris itself uses. On AWS S3 leave both out. On MinIO both are required, or Polaris tries to reach AWS and fails. Keep the trailing slash on endpoint: ClickHouse 26.8 and 26.10 build the object path from it, and without the slash every read fails with a 404 (the fix is explained under troubleshooting on this page).
  • Polaris vends credentials through the store's STS endpoint. MinIO exposes one, so nothing else is needed. For a store without STS, add "stsUnavailable": true and give Polaris static keys to hand out.
  • CATALOG_MANAGE_CONTENT lets the principal create namespaces and tables and read and write their data. Scope it to a namespace instead when several teams share the catalog.
  • The principal's secret is shown once, in the response to step 4. It is what goes into OLake Go as <client_id>:<client_secret>.
Hostnames with underscores break Polaris

Polaris is a Java service, and Java's URI parser rejects hostnames that contain an underscore. A MinIO container named lab_minio makes every table creation fail with host must not be null or Expected scheme-specific part at index 5: http:. Use a hyphen in the hostname (lake-minio), as above.

You can check the principal outside OLake Go by exchanging its credentials for a token, the same call OLake Go makes on every sync:

Token check
curl -s http://localhost:8181/api/catalog/v1/oauth/tokens -H 'Polaris-Realm: POLARIS' \
  -d grant_type=client_credentials -d client_id=<client_id> -d client_secret=<client_secret> \
  -d scope=PRINCIPAL_ROLE:ALL

A good principal returns a JSON object with an access_token. Running Polaris on Kubernetes, with Postgres behind it, or with an external identity provider is covered on the Polaris catalog page.

Step 3: Connect Postgres to OLake Go​

  1. In the OLake UI, open Sources and click Create Source. Pick Postgres as the connector, give the source a name and keep the OLake Go version on the latest release.
  2. Under Change data capture (CDC), leave Enable CDC on and enter the Replication Slot and Publication you created in step 1. The default Initial Wait Time is fine.
  3. Under Connection details, fill in the host, port, database name, username and password. Leave Schemas empty to discover every non-system schema, or list the ones you want.
  4. Click Set up source. OLake Go tests the connection, then checks the four CDC prerequisites (wal_level, the replication privilege, the slot and the publication) and shows the current and recommended value for anything that fails.

Here is the source form from the run on this page. Postgres ran in another container, so the host is host.docker.internal; the user, slot and publication are the ones from step 1.

OLake UI Edit source form for Postgres on OLake Go v1.1.0 with Enable CDC on, replication slot olake_slot, publication olake_pub, host host.docker.internal, database shop, user olake and SSL disabled
The Postgres source in OLake UI: Enable CDC on, with the slot, publication and replication user from step 1.

Set up source runs the connection test and the CDC checks (wal_level, the slot, the publication) before anything is saved. When one fails, the dialog names the setting and its recommended value.

The full field reference, including SSL modes and SSH tunnels, is on the Postgres connector page.

Step 4: Configure the Iceberg destination​

  1. Open Destinations and click Create Destination.
  2. Pick Apache Iceberg as the connector and Polaris as the catalog type.
  3. Choose OAuth2 as the authentication type. REST Catalog URI is the Polaris catalog endpoint (http://<polaris-host>:8181/api/catalog), Polaris Catalog Name the catalog from the setup (olake), REST Auth URI the token endpoint, and REST Credential the principal's <client_id>:<client_secret>.
  4. Fill in the store: AWS Region, and for MinIO the S3 Endpoint with the AWS Access Key and AWS Secret Key. On AWS S3 leave the endpoint empty.
  5. Click Create. OLake Go exchanges the credentials for a token, creates a namespace and a test table through Polaris, writes to the bucket and drops the test table again.

Here is the destination form from the run on this page. Polaris and MinIO ran in other containers, so both addresses use host.docker.internal; the credential is the olake principal from step 2.

OLake UI Edit destination form for Apache Iceberg with catalog type Polaris, authentication type OAuth2, REST catalog URI http://host.docker.internal:8181/api/catalog, Polaris catalog name olake, S3 endpoint http://host.docker.internal:9000, REST scope PRINCIPAL_ROLE:ALL and region us-east-1
The Polaris destination in OLake UI: OAuth2 with the principal from step 2, and the MinIO endpoint for the files.

Polaris Configuration Parameters

ParameterSample ValueDescription
REST Catalog URI
required		http://<REST_ENDPOINT>:8181/api/catalogEndpoint URL for the Polaris REST catalog service.
Authentication Type
required		oauth2Authentication method OLake Go uses for Polaris REST catalog requests. Set to match your catalog's authentication configuration.
Polaris Catalog Name
required		<POLARIS_CATALOG_NAME>Name of the Polaris catalog to use, OLake Go passes this to the Polaris REST catalog as the warehouse identifier, so catalog operations are routed to that catalog and its pre-configured S3 storage and IAM settings.
REST Auth URI
required		http://<REST_ENDPOINT>:8181/api/catalog/v1/oauth/tokensURL of the REST catalog’s OAuth2 token endpoint. OLake Go uses this with REST Credential to request an access token during OAuth2 client-credentials authentication.
REST Credential
required		<olake_user_client_id>:<olake_user_client_secret>Specifies the client ID and secret for OAuth2, formatted as client_id:client_secret. Used with REST Auth URI when OLake Go requests an access token from the catalog’s auth service. Not needed when using REST Token or other auth types.
AWS Region
required		ap-south-1AWS Region where your S3 bucket exists. Required so OLake Go calls the correct regional S3 endpoint.
REST ScopePRINCIPAL_ROLE:ALLRequired for OAuth2 client-credentials auth. Scopes are space-separated. Used with REST Auth URI and REST Credential for OAuth2 client-credentials auth. Values are catalog-specific. Not needed when using REST Token or other auth types.
S3 Endpointhttp://<S3_ENDPOINT>:9000S3 API endpoint for writing Iceberg data files. Leave empty for AWS S3 but required for S3-compatible storage like MinIO.
AWS Access KeyXXXAWS access key ID for authentication. Optional if using IAM roles or instance profiles.

If using IAM:
Docker Compose: add the required IAM environment variables under x-envs in your compose file. See Service Environment Variables for setup details.
Kubernetes: set up pod IAM in Cloud Integration.
AWS Secret KeyXXXAWS secret access key for authentication. Optional if using IAM roles or instance profiles.

If using IAM:
Docker Compose: add the required IAM environment variables under x-envs in your compose file. See Service Environment Variables for setup details.
Kubernetes: set up pod IAM in Cloud Integration.
Enable Arrow WritesfalseWrites data and delete files using Apache Arrow based writer and registers them in Iceberg.
Catalog Nameolake_icebergName of the Iceberg catalog OLake Go registers tables under. Defaults to olake_iceberg if left empty.
Catalog Name Supported for v0.3.5 and above
For the catalog name, OLake Go only supports lowercase letters and underscores. Spaces and special characters are not supported.

Step 5: Create the job and run the first sync​

  1. Open Jobs and click Create Job. Give it a name and a schedule. Every Day at a fixed hour, or a cron expression, both work; the schedule is how often the change stream is drained, not how often a full copy runs.
  2. Select the source and the destination you created.
  3. Under Advanced Settings, set Target Query Engines to DuckDB, plus Trino if it will read the tables too. OLake Go then resolves an Iceberg delete format every one of those engines can read, so you never pick one by hand.
  4. Click Next. OLake Go discovers the tables and opens the Streams page.
  5. Tick the tables to replicate. For each one, set the sync mode to Full Refresh + CDC and the ingestion mode to Upsert. Leave the Upsert Type as OLake Go resolved it (Positional Deletes in the run on this page).
  6. Click Create Job, then open the job's menu and choose Sync Now for the first run.

Two target query engines were set on this job, DuckDB and Trino. OLake Go offers every delete format both can read; the run on this page picked Positional Deletes, the format DuckDB documents, from that list. Positional deletes keep a small index between runs, which OLake UI stores for you.

OLake UI Streams Selection page with the public schema, customers and orders selected, sync mode Full Refresh + CDC, ingestion mode Upsert and upsert type Positional Deletes on Iceberg format version V2
Each stream: Full Refresh + CDC, Upsert, and Positional Deletes as the upsert type. The Iceberg DB name at the top is editable.
OLake UI Job Logs and History for postgres_to_duckdb showing two completed syncs, the first in 22 seconds and the second in 12 seconds
Job history: the 22 s historical snapshot, then the 12 s change-only sync.

On this page the first run copied 8 customers and 40 orders (48 records, 2.23 KB) and finished in 22 seconds, container start included. OLake Go names the destination namespace after the job, the source database and the schema, so postgres_to_duckdb on database shop produced postgres_to_duckdb_shop_public. The Iceberg DB field at the top of the Streams page changes it before the first sync.

Step 6: Query the tables in DuckDB​

There is no registration step. DuckDB attaches the whole Polaris catalog with ATTACH, lists every table OLake Go committed, and reads the files straight from the bucket. DuckDB fetches the OAuth2 token itself from the principal's client id and secret. On a laptop, where the store address Polaris hands out may not resolve, add an S3 secret for the store and turn credential vending off:

DuckDB 1.5.5
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;

-- What OLake Go created (schema = <job name>_<source database>)
SELECT id, name, city, tier FROM polaris.postgres_to_duckdb_shop_public.customers ORDER BY id;

With Polaris on AWS S3, drop the S3 secret and the ACCESS_DELEGATION_MODE line: DuckDB then reads with the credentials Polaris vends. The attached catalog is read-only from DuckDB; OLake Go is the writer.

OLake Go adds four columns to every table: _olake_id (a hash of the primary key), _op_type, _olake_timestamp and _cdc_timestamp. _op_type is r for a row from the first snapshot, c for an insert, u for an update and d for a delete. Filter them out of dashboards, or use them to see when a row last changed.

DuckDB 1.5.5 shell querying polaris.postgres_to_duckdb_shop_public.customers through the attached Polaris catalog and returning 10 rows with id, name, city and tier
DuckDB 1.5.5 reading the customers table through Polaris after the change sync: 10 rows, one per key.

Step 7: Change rows in Postgres and sync again​

To see the change path work, the run on this page made 12 changes in Postgres after the first sync:

Changes made in Postgres between the two syncs
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);

The second sync read 12 records (594 bytes) from the slot and finished in 12 seconds end to end. For customers that meant one new data file with 4 rows: 2 inserts plus the new versions of 2 updated rows.

One small positional delete file retires the 2 old versions. OLake Go's commit report in the sync log shows it directly: addedDataFiles=1, addedRecords=4, addedPositionalDeleteFiles=1, addedPositionalDeletes=2. The orders table has the same shape with 8 rows and 3 deletes.

OLake UI sync log filtered to the Iceberg commit report for orders, showing addedDataFiles 1, addedPositionalDeleteFiles 1, addedRecords 8, totalRecords 48 and addedPositionalDeletes 3
The change-only sync in the OLake UI log: one data file and one positional delete file per table.

DuckDB shows the result on the next query, with no refresh. Liam Carter is gold, Zara Ali moved to Abu Dhabi, and the two new customers are there.

The two retired versions are gone, because DuckDB applies the positional deletes while reading. The _op_type column shows how each row arrived: r for the snapshot, c for the inserts and u for the updates.

Schema changes. When a column is added in Postgres, OLake Go adds it to the Iceberg table on the next sync. DuckDB shows it on the next ATTACH.

Widening changes (int to bigint, real to double precision) are applied. Narrowing or incompatible type changes fail the sync rather than corrupt the table. Full rules are on the schema evolution page.

Query the same tables from ClickHouse and Trino​

This is the point of writing Iceberg instead of exporting a DuckDB file. Nothing is exported, copied or refreshed: both engines read the files OLake Go wrote, through the same Polaris endpoint.

There is no registration step. ClickHouse attaches the whole Polaris catalog as one database with the DataLakeCatalog engine, lists every table OLake Go committed, and reads the files with the credentials Polaris vends. One thing to know before the first query: ClickHouse has one level of namespace, so the Iceberg namespace and table name go together inside backticks.

clickhouse-client
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';

SHOW TABLES FROM polaris;

-- What OLake Go created (namespace = <job name>_<source database>_<source schema>)
SELECT id, name, city, tier
FROM polaris.`postgres_to_duckdb_shop_public.customers`
ORDER BY id;

vended_credentials = true makes ClickHouse read the files with the short-lived keys Polaris hands out per table, so no store keys sit in ClickHouse. To use static keys instead, replace it with storage_endpoint, aws_access_key_id and aws_secret_access_key. The database is read-only: INSERT, ALTER and RENAME DATABASE return NOT_IMPLEMENTED.

OLake Go adds four columns to every table: _olake_id (a hash of the primary key), _op_type, _olake_timestamp and _cdc_timestamp. _op_type is r for a row from the first snapshot, c for an insert, u for an update and d for a delete. Filter them out of dashboards, or use them to see when a row last changed.

clickhouse-client querying polaris.postgres_to_duckdb_shop_public.customers through the DataLakeCatalog engine and returning the same 10 rows as DuckDB
ClickHouse 26.8 reading the same table through Polaris: the same 10 rows.

An orders roll-up matched Postgres to the cent in all three engines. It gave 16 delivered orders for 8659.68, 18 placed for 10522.26, 11 shipped for 6397.25.

Data types: Postgres to Iceberg to DuckDB​

OLake Go maps Postgres types to Iceberg types, and DuckDB maps Iceberg types to its own. Most columns pass through unchanged; three groups are worth knowing about.

PostgresIceberg (written by OLake Go)DuckDB showsNotes
integer, serialintINTEGER
bigint, bigseriallongBIGINT
numeric(p,s), real, double precisiondoubleDOUBLEOLake Go stores decimals as doubles. The orders.amount sums on this page matched Postgres to the cent in every engine; round at query time when you need exact decimal arithmetic.
timestamptz, timestamptimestamptz (UTC)TIMESTAMP WITH TIME ZONEtimestamp has no zone in Postgres; it is read as UTC.
varchar, text, uuid, json, jsonbstringVARCHARParse JSON with DuckDB's json extension.
booleanbooleanBOOLEAN
byteabinaryBLOB

Postgres types in the table come from the Postgres connector reference. The DuckDB side comes from the DuckDB Iceberg extension page (checked 2026-10-29) and from DESCRIBE on the tables of this run. OLake Go adds _cdc_lsn on Postgres tables, the WAL position each change came from.

Cost and maintenance​

  • Storage is your bucket: you pay S3 or MinIO rates for the Parquet and delete files, and nothing to Polaris or DuckDB for holding them.
  • Compute. OLake Go runs wherever you run Docker. DuckDB reads Parquet over the network on every query; for a table you hit all day, CREATE TABLE local AS FROM polaris... once and query the local copy.
  • Maintenance. Nothing compacts the tables for you. A CDC table that gets a new small file and delete file per sync wants compaction; OLake Fusion does this for the catalogs OLake Go writes to, and Spark's rewrite_data_files works through the same Polaris endpoint.
  • WAL disk. The replication slot keeps WAL until OLake Go reads it. A job paused for a weekend on a busy server can hold tens of gigabytes. Drop the slot if you retire a job.
  • Credential rotation. Polaris principals do not expire by default. Rotate the olake principal's secret on your own schedule with the management API; a rotated secret fails the next sync at the token exchange, before any data moves.

Troubleshooting​

1. DuckDB cannot resolve the store address​

IO Error: Could not resolve hostname error for HTTP GET to 'http://host.docker.internal:9000/lakehouse/...'

DuckDB used the store address Polaris vends, which resolves inside Docker but not on the laptop. Add an S3 secret with the address DuckDB can reach and attach with ACCESS_DELEGATION_MODE 'none', as in step 6. On AWS S3 the vended address resolves everywhere and the secret is not needed.

2. Polaris cannot create a table​

{"error":{"message":"host must not be null.","type":"NullPointerException","code":500}}
{"error":{"message":"java.net.URISyntaxException: Expected scheme-specific part at index 5: http:","type":"RuntimeException","code":500}}

The store hostname contains an underscore (lab_minio), which Java's Uniform Resource Identifier (URI) parser rejects. Rename the container or host with a hyphen and update endpoint and endpointInternal on the catalog.

3. Polaris refuses to vend credentials​

{"error":{"message":"Credential vending was requested for table probe.t1, but no credentials are available","type":"IllegalArgumentException","code":400}}

The catalog has stsUnavailable: true but Polaris was given no static keys to hand out. Either remove stsUnavailable (MinIO and S3 both expose a Security Token Service (STS) endpoint), or configure the keys on the Polaris server.

4. The next sync waits on the slot​

If a sync hangs after the snapshot, another process holds the replication slot, or wal_sender_timeout cut the connection during a quiet period. Check pg_stat_replication for a second consumer of olake_slot, and set wal_sender_timeout = 0 for the OLake Go user as the prerequisites block shows.

5. The destination test fails at the token exchange​

OLake Go's destination test exchanges the principal's credentials at /api/catalog/v1/oauth/tokens before anything else. Three causes cover nearly every failure.

A wrong client id or secret, a principal without a role that carries CATALOG_MANAGE_CONTENT on the catalog, or a wrong Polaris Catalog Name. Run the token check from the catalog setup; it returns the same error Polaris gives OLake Go.

How this compares with other ways to get Postgres data into DuckDB
MethodFreshnessUpdates and deletesWhere the data landsWhat you run
DuckDB postgres extensionLive, per queryReads the current rowNothing is copied; every query hits PostgresA Postgres user DuckDB can log in with
pg_dump or COPY TO, then COPY into DuckDBHours to dailyNo; full reloads onlyA DuckDB file you rebuildA script and a scheduler
Debezium and Kafka, then a consumer that writes ParquetSecondsYes, with your own merge logicParquet files, then DuckDBKafka, Kafka Connect, Debezium and a consumer
Managed ELT to a warehouse, then DuckDB over itMinutes, by planVaries by toolWarehouse tables, a second copy you pay to move and storeNothing; priced per row, per GB or per connector
OLake Go to Iceberg on Polaris (this guide)CDC on a schedule down to one minuteYes, as Iceberg row-level deletesApache Iceberg tables that DuckDB, ClickHouse, Trino and Spark all readOne Docker Compose stack, open source; your bucket plus your own compute

The postgres extension is the simpler choice for a one-off query against a small table you can reach directly. Writing Iceberg once avoids hitting production Postgres from every notebook, and gives every engine the same rows.

When not to use this​

  • One analyst, one laptop, one small table. The DuckDB postgres extension or a nightly COPY is less to run than a catalog and OLake Go.
  • Tables have no primary key. Inserts replicate; updates and deletes cannot be matched to a row and are not applied. Add a primary key or set REPLICA IDENTITY FULL first.
  • DuckDB must read equality deletes. DuckDB does not document them. Keep the job on positional deletes or deletion vectors, as on this page.

FAQs​

How do I get Postgres tables into DuckDB and keep them fresh?

Run OLake Go (open source, Apache 2.0) against a Postgres database with logical replication on, and point it at an Apache Polaris catalog. The first sync copies the tables in full, every later sync replays only the changes from the write-ahead log, and DuckDB attaches the catalog with its iceberg extension and reads the tables in place. No export, no CSV and no DuckDB file to rebuild.

Can DuckDB read Iceberg tables with row-level deletes?

Yes. DuckDB reads positional deletes, and deletion vectors from duckdb-iceberg 1.5.3. The run on this page used positional deletes and DuckDB 1.5.5 returned one row per key after the updates. Equality deletes also read correctly on the runs on this site, but DuckDB does not document them, so positional deletes are the safer pick when DuckDB is the main reader.

Does OLake Go copy the existing Postgres data before it starts reading the WAL?

Yes, automatically. A job in Full Refresh + CDC mode creates the replication slot, copies every selected table in parallel chunks, then switches to the slot. Nothing written during the copy is lost, and there is no separate backfill job.

Why Apache Polaris rather than DuckDB's postgres extension?

The postgres extension runs every query against the live database, which is fine for one small table and one analyst. A Polaris catalog holds a replicated copy as Iceberg that DuckDB, ClickHouse, Trino and Spark all read, keeps production Postgres out of the query path, and carries its own access control and vended storage credentials.

Does DuckDB need the bucket's access keys?

Not on AWS S3: Polaris vends short-lived credentials per table and DuckDB uses them. On a laptop against MinIO, the address Polaris hands out may not resolve, so the run on this page added an S3 secret and attached with ACCESS_DELEGATION_MODE 'none'.

Which Postgres data types change on the way to DuckDB?

numeric columns become Iceberg double and show up in DuckDB as DOUBLE, so round at query time when you need exact decimal arithmetic. timestamptz becomes TIMESTAMP WITH TIME ZONE in UTC. integer stays INTEGER, bigint BIGINT, varchar and text become VARCHAR, and json and jsonb arrive as VARCHAR.

What does OLake Go need on the Postgres side?

wal_level set to logical, a replication slot and a publication for the tables, and a user with the REPLICATION attribute and SELECT on the tables. Hosted Postgres on RDS, Aurora, Cloud SQL, Azure, Neon or Supabase exposes the same settings through a parameter group or dashboard toggle.

Postgres to SnowflakeSame source, read from Snowflake.Postgres to ClickHouseSame source, read from ClickHouse.Postgres to BigQuerySame source, read from BigQuery.Postgres to Amazon RedshiftSame source, read from Amazon Redshift.Postgres to DatabricksSame source, read from Databricks.Postgres to Apache IcebergSame source, read from Apache Iceberg.MySQL to DuckDBSame engine, replicated from MySQL.Postgres connector referenceEvery field, sync mode and troubleshooting note for the source.Query engine compatibilityWhich engines read which catalogs and delete formats.All integrationsEvery source and engine pair with a tested guide.

Deeper reading on this site: