Postgres to BigQuery CDC Tutorial: From the WAL to a Four-Part Table Name

The first Postgres to BigQuery pipeline most teams write is a nightly export followed by bq load. It holds up until someone asks why an order that changed this morning still shows yesterday's status, or why a row deleted in Postgres is still counted in a report.

The common ways to move the data:

• A scheduled export and bq load. A script, a scheduler and a staging bucket. Freshness is hours to daily, and updates and deletes need full reloads or your own merge.
• Datastream for BigQuery. Google's managed change data capture (CDC) service. You set up a connection profile and a stream, run nothing yourself, and the rows land in native BigQuery datasets.
• Debezium and Kafka with a BigQuery sink connector. Changes arrive within seconds. You operate Kafka, Kafka Connect, Debezium and the sink connector.
• Managed extract, load, transform (ELT) tools such as Fivetran, Estuary or Airbyte Cloud. Nothing to host, and freshness in minutes depending on the plan. Pricing is per row, per GB or per connector, for a second copy inside BigQuery.

This tutorial builds a different shape. 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 a BigLake catalog. BigLake is Google Cloud's Iceberg representational state transfer (REST) catalog, and the files stay in a Cloud Storage bucket you own.

BigQuery then queries the tables by a four-part name. There is no dataset to create, no external table to declare and no load job, and DuckDB and Spark can read the same files through the same catalog.

I ran this against Postgres 16 and BigQuery in us-central1 with OLake Go v1.1.0. The first sync copied 48 records in 35 seconds, and a second sync replayed 12 changes in 58 seconds. Every number below is from that run.

What you'll build

• Source: a Postgres database named shop with two tables, customers and orders
• Destination: a BigLake catalog on one Cloud Storage bucket, written by a service account
• Pipeline: OLake UI from its Docker Compose install, running one job that snapshots the tables and then replays the WAL
• Readers: the BigQuery SQL editor, plus DuckDB at the end

You need: Postgres 12 or later, Docker, a Google Cloud project where you can enable an API and create a service account, and about 30 minutes.

Step 1: Prepare Postgres

If you already have a database, skip the container. For a local test, this starts Postgres 16 with the four settings logical replication needs:

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 an existing server the same four settings are ALTER SYSTEM SET statements, and wal_level needs a restart. On Cloud SQL, AlloyDB, Amazon Relational Database Service (RDS), Aurora or Azure, it is a flag or a parameter group instead.

Connect with psql -h localhost -U postgres -d shop and create the two tables and the starting data:

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;

Then the role, the publication and the replication slot OLake Go reads through:

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;

Three notes for a real database:

• Primary keys. A table without one needs ALTER TABLE <table> REPLICA IDENTITY FULL;, otherwise its updates and deletes are not applied.
• One slot and one publication per job. Two jobs on the same slot read each other's changes, and both lose data.
• The slot pins WAL. Postgres keeps WAL on disk 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: Set up Google Cloud

Everything here happens in the Google Cloud console, in the project that will hold the tables.

1. Enable the API. Under APIs and services, enable the BigLake API.
2. Create a bucket. In Cloud Storage, create a bucket in the region where BigQuery will run the queries. The bucket name becomes the catalog name in BigQuery, so choose one you don't mind typing in SQL.
3. Create a service account. In IAM and Admin (IAM is Identity and Access Management), Service Accounts, create one, for example olake-biglake. Grant it BigLake Editor on the project and Storage Object User on the bucket.
4. Download a JSON key. On the service account's Keys tab, choose Add key, Create new key, JSON. The console downloads the file once.

There is no catalog to create. BigLake makes a single-bucket catalog named after the bucket the first time OLake Go calls the endpoint with gs://<bucket> as the catalog path.

⚠️ Leave the catalog in its default end-user credential mode. OLake Go 1.1.0 does not send the header BigLake asks for in vended-credentials mode, so a catalog switched to that mode rejects the destination test.

The account you run BigQuery queries with needs the BigQuery Job User role plus read access to the catalog.

Step 3: Start OLake 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.

Open Sources, click Create Source and pick Postgres. Leave Enable CDC on, then enter olake_slot as the Replication Slot and olake_pub as the Publication. For the connection I used host host.docker.internal because Postgres ran in another container, database shop and user olake.

Click Set up source. OLake Go tests the connection, then checks wal_level, the replication privilege, the slot and the publication. If one fails, the dialog shows the current value next to the recommended one.

Step 4: Add the BigLake destination

Open Destinations, click Create Destination, pick Apache Iceberg as the connector and BigLake as the catalog type. Then fill in:

Field	Value
REST Catalog URI	https://biglake.googleapis.com/iceberg/v1/restcatalog
BigLake Catalog Path	gs://<bucket>, bucket root only, with no folder after it
Authentication Type	GCP
GCP Service Account JSON	the whole key file from step 2
Catalog Name	olake_iceberg
GCP Project ID	your project ID

Click Create. OLake Go calls the BigLake endpoint, creates the catalog on first contact, then writes and drops a test table in the bucket. A 403 from biglake.googleapis.com or storage.googleapis.com here tells you which of the two grants is missing.

Step 5: Create the job and run the first sync

1. Open Jobs and click Create Job. Name it postgres_to_bigquery and give it a schedule.
2. Select the source and the destination from the last two steps.
3. Under Advanced Settings, set Target Query Engines to BigQuery, Spark and DuckDB.
4. Click Next. On the Streams page, tick customers and orders, keep the sync mode on Full Refresh + CDC and the ingestion mode on Upsert.
5. Click Create Job, then choose Sync Now from the job's menu.

The target engines setting does real work: OLake Go offers only the delete formats that every listed engine reads. My run used Positional Deletes, and deletion vectors were not in the list because BigQuery reads them only in preview.

In my run the first sync copied 8 customers and 40 orders (48 records, 2.23 KB) and finished in 35 seconds, container start included. It recorded the WAL position before reading the tables, so nothing written during the copy is lost.

OLake Go names the BigLake namespace after the job, the database and the schema. Mine became postgres_to_bigquery_shop_public, and the Iceberg DB field at the top of the Streams page changes it before the first sync.

Step 6: Query the tables in BigQuery

There is nothing to register. The bucket name is the catalog, the namespace is the dataset, and the query uses all four parts in backticks:

SELECT id, name, city, tier
FROM `<project-id>.<bucket>.postgres_to_bigquery_shop_public.customers`
ORDER BY id;

SELECT status, count(*) AS n, round(sum(amount), 2) AS total
FROM `<project-id>.<bucket>.postgres_to_bigquery_shop_public.orders`
GROUP BY status ORDER BY status;

Run it in the bucket's region. In the SQL editor that is More, then Query settings, and from a terminal it is a flag:

bq query --project_id=<project-id> --location=<region> --use_legacy_sql=false "<sql>"

Step 7: Change rows and sync again

This is the part a nightly reload gets wrong. Back in psql:

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 58 seconds. Most of that time was the WAL reader waiting to confirm the slot was idle.

For customers it wrote one 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.

Re-run the SELECT, with no refresh and no ALTER TABLE first. Liam Carter is gold, Zara Ali is in Abu Dhabi, and the two new customers are there.

The _op_type column shows how each row arrived: r for the snapshot, c for an insert, u for an update and d for a delete. The orders roll-up matched Postgres to the cent: 16 delivered for 8659.68, 18 placed for 10522.26 and 11 shipped for 6397.25.

Schema changes. Add a column in Postgres and OLake Go adds it to the table on the next sync, in the same commit. Widening changes such as int to bigint are applied, and narrowing or incompatible changes fail the sync instead of corrupting the table.

Read the same tables from DuckDB

Nothing is exported for this. DuckDB attaches BigLake as an Iceberg REST catalog with a Google access token. Because the catalog is in end-user mode, it also needs its own storage credentials: a hash-based message authentication code (HMAC) key for the service account, created under Cloud Storage, Settings, Interoperability.

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

-- gcloud auth print-access-token, or a token minted for the service account
CREATE SECRET biglake_catalog (
  TYPE iceberg,
  TOKEN '<access token>',
  EXTRA_HTTP_HEADERS MAP {'x-goog-user-project': '<project-id>'}
);
CREATE SECRET biglake_files (TYPE gcs, KEY_ID '<hmac access id>', SECRET '<hmac secret>');

ATTACH 'gs://<bucket>' AS biglake (
  TYPE iceberg,
  ENDPOINT 'https://biglake.googleapis.com/iceberg/v1/restcatalog',
  SECRET biglake_catalog
);

SELECT id, name, city, tier FROM biglake.postgres_to_bigquery_shop_public.customers ORDER BY id;

DuckDB 1.5.5 returned the same 10 customers and the same roll-up as BigQuery. Spark 4.0.1 with Iceberg 1.10.0 did too, from a laptop, using the same service account key. The access token lasts an hour, so replace the secret when a query fails with 401.

Data types to know about

Postgres	BigQuery shows	Note
integer, serial, bigint	INT64
numeric(p,s), real, double precision	FLOAT64	stored as doubles; round when you need exact decimal arithmetic
timestamptz, timestamp	TIMESTAMP	the instant in Coordinated Universal Time (UTC)
varchar, text, uuid, json, jsonb	STRING	use PARSE_JSON or JSON_VALUE

Tips from the run

• Set the query location. The default US multi-region returns Not found: Dataset for a catalog whose bucket is regional.
• Don't reach for INFORMATION_SCHEMA. It does not resolve catalog tables. bq show --schema <project>:<bucket>.<namespace>.<table> lists the columns instead.
• Plan for maintenance. Each sync adds a small data file and a delete file, and BigLake rejects a table's metadata.json above 1 MB. Expire old snapshots and compact on a schedule, for example with Spark's expire_snapshots and rewrite_data_files procedures.

How this compares

Method	Freshness	Updates and deletes	Where the data lands	What you run
Export and bq load	hours to daily	no; full reloads or your own merge	native BigQuery tables	a script, a scheduler and a staging bucket
Datastream for BigQuery	near real time, by Google's description	yes	native BigQuery datasets	nothing; a connection profile and a stream
Debezium, Kafka and a sink connector	seconds	yes, with the connector's upsert mode	native BigQuery tables	Kafka, Kafka Connect, Debezium and the connector
Fivetran, Estuary, Airbyte Cloud	minutes, by plan	varies by tool	BigQuery tables, a second copy	nothing; per-row, per-GB or per-connector pricing
OLake Go to BigLake (this post)	CDC on a schedule down to one minute	yes, as Iceberg row-level deletes	Iceberg tables that BigQuery, Spark and DuckDB all read	one Docker Compose stack, open source

If BigQuery is the only reader and you prefer a managed Google service, Datastream is the simpler choice. The route above earns its setup when a second engine needs the same rows, or when you want to own the files.

It also has a limit worth stating: a catalog table is read from Parquet in the bucket on every query, so it will not match native BigQuery tables on speed.

Where to go from here

• Point the source at your real database. Cloud SQL, AlloyDB, RDS, Aurora, Azure, Neon and Supabase expose the same settings through a flag, a parameter group or a dashboard toggle.
• For a dashboard that needs native speed, keep the Iceberg tables as the source and load the hot slice into a native table on a schedule.
• The full guide, with the Spark connection, the command line setup and the troubleshooting section, is on olake.io.

Stuck on a step, or seeing a different message than the ones above? Leave a comment with the exact text and I'll take a look.