# How to Sync PostgreSQL to BigQuery Automatically

> Five ways to sync PostgreSQL to BigQuery in 2026: batch, CDC, Datastream, and managed ELT tools like Erathos — with setup steps and pricing.

Source: https://www.erathos.com/en/blog/sync-postgresql-bigquery-automatically
Em português: https://www.erathos.com/blog/sync-postgresql-bigquery-automatically
Published: 2026-08-31
Category: Tutorials

![How to Sync PostgreSQL to BigQuery](https://cms-media.erathos.com/How to Sync PostgreSQL to BigQuery - Header Blog (1).png)

PostgreSQL is where the app writes; BigQuery is where the analysts query. Getting data from one to the other without a human in the loop means picking between a scheduled batch sync and CDC. This guide walks through every option, along with the limits and prices for each.

## How do you sync PostgreSQL to BigQuery automatically?

You either schedule an incremental batch sync or run change data capture (CDC) on the write-ahead log. A scheduled sync works when the data can be hours old. CDC gets inserts, updates, and deletes into BigQuery within minutes.

The two work in different ways. A scheduled sync queries the source table on a timer, using a watermark column like `updated_at` to pick up only the rows that changed since the last run. CDC skips the queries. It reads the write-ahead log (WAL), the file PostgreSQL writes every change to before applying it, and streams those changes to the destination.

The difference that matters most is deletes. A deleted row stops showing up in queries, so a scheduled sync never sees it and the row stays in BigQuery forever. A delete does get written to the WAL, so CDC captures it and removes the row in BigQuery too.

![cdc postgreSQL to bigquery](https://cms-media.erathos.com/cdc postgreSQL to bigquery.png)

## What are all the ways to replicate PostgreSQL to BigQuery?

There are five: the BigQuery Data Transfer Service for scheduled batch, Google Datastream for CDC, a managed ELT (extract, load, transform) tool like Erathos, a hand-built export to Cloud Storage with scheduled loads, or Debezium with Kafka. The first two are Google-native; the last two you build and run yourself.

Option

How it moves data

Captures deletes

You pay for

[BigQuery Data Transfer Service](https://docs.cloud.google.com/bigquery/docs/postgresql-transfer)

Scheduled full or incremental batch with a timestamp watermark

No

Slot-hours per transfer run

[Google Datastream](https://docs.cloud.google.com/datastream/docs/quickstart-replication-to-bigquery)

WAL CDC with a Merge write mode

Yes, on tables with a primary key

Datastream plus BigQuery merge costs

Managed ELT ([Erathos](https://www.erathos.com/en/pipelines/postgresql-bigquery?slug=sync-postgresql-bigquery-automatically-2026&button=cta&utm_campaign=sync-postgresql-bigquery-automatically-2026&utm_source=blog&utm_medium=organic&utm_content=bydefault&landing_page=%2Fblog%2Fsync-postgresql-bigquery-automatically-2026&page_group=blog), Fivetran, Airbyte, Estuary)

Batch, cursor incremental, or CDC in a hosted service

Yes, with CDC

Rows, active rows, or GB moved, per tool

Hand-built export to GCS

COPY or pg\_dump, upload, scheduled load jobs

Only with change logic you write

Engineering time; batch loads are free

Debezium + Kafka

WAL CDC into Kafka, plus a BigQuery writer you build

Depends on the consumer you write

Infrastructure you operate

Cloud Data Fusion Replication supports [MySQL, SQL Server, and Oracle sources](https://docs.cloud.google.com/data-fusion/docs/concepts/replication), so it's off the table for PostgreSQL.

## Does BigQuery Data Transfer Service support PostgreSQL?

Yes. The BigQuery Data Transfer Service has a paid PostgreSQL connector that runs recurring transfers on a schedule you set. Incremental transfers are in Preview, accept only TIMESTAMP columns as the watermark, and [cannot sync deletes](https://docs.cloud.google.com/bigquery/docs/postgresql-transfer).

If a tutorial told you the service has no PostgreSQL connector, it's out of date. You create a transfer configuration in the console with the host, TLS settings, the tables to move, and a schedule, and BigQuery pulls the data on that schedule with no scheduler of your own.

The limits are specific. Full transfers reload the whole table each run. Incremental transfers need a TIMESTAMP watermark column that only grows, and an upsert also needs a primary key. Tables without a primary key or indexed column [can't move more than 2,000,000 records](https://docs.cloud.google.com/bigquery/docs/postgresql-transfer). If a run is still going when the next one is scheduled, the new run is skipped.

![one incremental (watermark) sync run](https://cms-media.erathos.com/one incremental (watermark) sync run.png)

Cost is measured in slot-hours, the compute unit BigQuery bills transfers in. Google's planning guideline is up to 20 slot-hours for each hour a transfer runs, which works out to [$1.20 per hour in us-central1](https://cloud.google.com/bigquery/pricing).

## When should you use Google Datastream instead?

Use Datastream when you need deletes and minute-level freshness without a third-party tool. It reads the PostgreSQL WAL through a replication slot and writes changes to BigQuery in Merge mode, so the destination tables [stay synchronized with the source](https://docs.cloud.google.com/datastream/docs/quickstart-replication-to-bigquery).

Setup means creating a connection profile for PostgreSQL, one for BigQuery, then a stream that names the replication slot and publication on the source. You pick a data staleness limit; the quickstart default is 15 minutes. A lower staleness setting means BigQuery runs merge jobs more often, and those jobs bill as normal BigQuery compute.

Datastream has sharp edges around primary keys. Tables without one are [append-only](https://docs.cloud.google.com/datastream/docs/destination-bigquery): every change lands as a new row with metadata instead of updating in place. Tables whose primary key is a FLOAT or REAL type are skipped entirely. Every replicated table also gains a datastream\_metadata column, and a single event tops out at 20 MB.

## What do you have to configure in PostgreSQL before CDC can start?

Four server settings, a role with replication permission, a publication, and a replication slot. The setup is the same whether the consumer is Datastream, Erathos, Airbyte, or Debezium.

The server settings first:

`ALTER SYSTEM SET wal_level = 'logical';`
`ALTER SYSTEM SET max_replication_slots = 10;`
`ALTER SYSTEM SET max_wal_senders = 10;`
`ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';`

The first three take effect only after a restart. The last one applies on a config reload with no restart. By default, a replication slot [retains unlimited WAL](https://docs.erathos.com/connectors/databases/postgresql), so a paused consumer can fill the source disk. Setting a limit trades that risk for a resync when the limit is hit.

Check the result:

`SELECT name, setting FROM pg_settings`
`WHERE name IN ('wal_level', 'max_replication_slots',`
`               'max_wal_senders', 'max_slot_wal_keep_size');`

`          name          | setting`
`------------------------+---------`
` max_replication_slots  | 10`
` max_slot_wal_keep_size | 10240`
` max_wal_senders        | 10`
` wal_level              | logical`

The pg\_settings view reports max\_slot\_wal\_keep\_size in megabytes, so 10240 is the 10GB we set. Then the role, publication, and slot:

`CREATE USER sync_user WITH PASSWORD 'change-me';`
`ALTER ROLE sync_user WITH REPLICATION;`
`CREATE PUBLICATION bigquery_pub FOR TABLE orders;`
`SELECT pg_create_logical_replication_slot('bigquery_sync_slot', 'pgoutput');`

The publication lists which tables to stream. The slot is the server-side bookmark that tracks how far the consumer has read, using pgoutput, the decoder built into PostgreSQL. One more command matters when the tool needs the full old row on updates and deletes, since the default only logs the primary key:

`ALTER TABLE orders REPLICA IDENTITY FULL;`

Full replica identity writes the entire before-image of each changed row into the WAL, so it [only makes sense when the tool asks for it](https://docs.erathos.com/connectors/databases/postgresql).

## Do Amazon RDS, Cloud SQL, Supabase, and Neon support PostgreSQL CDC?

All four support logical replication, so CDC to BigQuery works on each. What differs is the switch you flip, since managed providers don't let you edit the config file directly.

Provider

How you enable it

Google Cloud SQL

Set the cloudsql.logical\_decoding flag; pgoutput is built in, and slots are limited to [2 to 8 per GB of memory](https://docs.cloud.google.com/sql/docs/postgres/replication/configure-logical-replication)

Amazon RDS / Aurora

Set the rds.logical\_replication parameter to 1, which [stands in for wal\_level and the WAL settings](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication.html); create slots with SQL

Supabase

Logical replication is [supported out of the box](https://supabase.com/docs/guides/database/replication); one slot serves one publication

Neon

wal\_level already returns logical and [slots default to 10](https://neon.com/docs/guides/logical-replication-concepts); inactive slots are removed after a period

On Neon, a CDC pipeline you pause for too long loses its slot and has to resync from scratch.

## Which managed tools sync PostgreSQL to BigQuery automatically?

Erathos, Fivetran, Airbyte, and Estuary all move PostgreSQL data to BigQuery without code. They differ in the sync methods they offer, how fast they run, and what unit they bill on.

Tool

Sync methods

Fastest published sync

Pricing

[Erathos](https://www.erathos.com/en/pipelines/postgresql-bigquery)

Batch, cursor-based incremental, or CDC

Every 5 minutes

[$0 up to 1M rows/mo, $29 up to 2M, $250 up to 5M with CDC](https://www.erathos.com/en/pricing); billed on rows written to the warehouse

[Fivetran](https://www.fivetran.com/pricing)

Managed connector, log-based replication

15-minute syncs on Standard, 1-minute on Enterprise

Monthly active rows (MAR); free up to 500k MAR

[Airbyte](https://airbyte.com/how-to-sync/postgresql-to-bigquery)

CDC, xmin, or a user-defined cursor

Not published

Credit-based; prices not published

[Estuary](https://estuary.dev/blog/postgresql-to-bigquery/)

Real-time CDC capture plus BigQuery materialization

Streaming

$0.50/GB moved + $0.14/connector/hour

Here's how the pricing units break down. Fivetran's monthly active rows count [inserts and modifications, including deletes](https://fivetran.com/docs/core-concepts/usage-based-pricing/tracking-and-optimizing-usage/postgresql), and the first historical sync is free. Erathos counts rows written to the destination, so a full resync counts but an idle table costs nothing. Estuary bills on volume moved plus connector uptime.

![erathos pricing](https://cms-media.erathos.com/erathos pricing.png)

_Erathos plans are billed on rows written to the warehouse per month_

Method choice inside a tool matters too. Airbyte's own guide says its xmin mode [does not capture deletes](https://airbyte.com/how-to-sync/postgresql-to-bigquery) and recommends CDC for delete capture or for databases around 500 GB and up.

In Erathos, the setup is short: connect PostgreSQL over an open connection, static IP, or [SSH tunnel](https://docs.erathos.com/connectors/databases/postgresql), pick the tables, the schedule (every 5 minutes to daily), and the update type, then point it at a [BigQuery service account](https://www.erathos.com/en/connectors/bigquery?slug=sync-postgresql-bigquery-automatically-2026&button=cta&utm_campaign=sync-postgresql-bigquery-automatically-2026&utm_source=blog&utm_medium=organic&utm_content=bydefault&landing_page=%2Fblog%2Fsync-postgresql-bigquery-automatically-2026&page_group=blog). Batch and cursor modes skip the CDC SQL entirely. Erathos detects new source columns and adds them to BigQuery on its own, and each run logs execution time, row counts, and errors, with alerts in Slack or email. Every connector has a [14-day free trial](https://www.erathos.com/en/pipelines/postgresql-bigquery?slug=sync-postgresql-bigquery-automatically-2026&button=cta&utm_campaign=sync-postgresql-bigquery-automatically-2026&utm_source=blog&utm_medium=organic&utm_content=bydefault&landing_page=%2Fblog%2Fsync-postgresql-bigquery-automatically-2026&page_group=blog).

## How do CDC changes become upserts and deletes in BigQuery?

The sync tool merges each change into the destination table, matched on the primary key. An insert adds a row, an update rewrites it, and a delete removes it, so the BigQuery table mirrors the source table.

Datastream's Merge mode does this natively and stamps each row with a UUID (a unique ID string) and source timestamp in its metadata column. Without a primary key there is nothing to match on, so the table becomes [append-only](https://docs.cloud.google.com/datastream/docs/destination-bigquery): a delete arrives as a new row flagged in metadata, and downstream queries have to filter those flags themselves.

A hand-built pipeline has to do the merging itself, because [updating rows in BigQuery means MERGE statements](https://www.datafold.com/blog/postgres-to-bigquery-data-replication/), each one a billed query. The usual shape is to load each batch into a staging table, deduplicate it on the primary key, then MERGE it into the live table. That merge logic is most of the work in the DIY option.

## How much does it cost to load PostgreSQL data into BigQuery?

Batch loading into BigQuery is free through the shared slot pool; you pay for how the data gets there and for storage. Streaming inserts cost [$0.01 per 200 MiB](https://cloud.google.com/bigquery/pricing), and the Storage Write API costs $0.025 per GiB after a free 2 TiB each month.

![bigquery data ingestion](https://cms-media.erathos.com/bigquery data ingestion.png)

_BigQuery data ingestion pricing: batch loads are free, streaming is billed per volume_

That split explains the tool prices. A batch ELT tool bills you for its own service while the BigQuery load itself costs nothing. A CDC pipeline pays for ingestion through the Storage Write API and again for the background merge jobs that fold changes into the destination tables, billed as normal BigQuery compute.

Storage is the same in both cases: loaded data is billed at BigQuery's standard storage rates whether it arrived by batch or by stream.

## What can break a PostgreSQL-to-BigQuery sync?

A paused CDC consumer can hold WAL until the source disk fills, and that is only the first of five failure modes to plan for:

- **Retained WAL.** A slot with no limit [keeps WAL forever](https://docs.erathos.com/connectors/databases/postgresql) while its consumer is down. Set max\_slot\_wal\_keep\_size and watch slot lag from day one.
- **Missed deletes.** Data Transfer Service incremental runs and Airbyte's xmin mode skip deletes. Rows removed in PostgreSQL pile up in BigQuery unless the app soft-deletes or the pipeline uses CDC.
- **Primary-key gaps.** Datastream turns no-PK tables append-only and skips FLOAT and REAL primary keys; Data Transfer Service limits no-PK, no-index tables to 2,000,000 records.
- **Schema drift.** In a hand-built pipeline, a source schema change will likely make load jobs fail. Managed tools vary: Erathos detects new columns and adds them to the destination.
- **Destination outages.** Fivetran holds undeliverable data for up to twenty-four hours, then discards it; a longer outage can force a historical resync depending on how much WAL the source kept.

Pick the Data Transfer Service when hours-old data is fine and deletes don't matter, Datastream for Google-native CDC where you manage the slots yourself, or a managed tool like [Erathos](https://app.erathos.com/signup?slug=sincronizar-postgresql-bigquery-automaticamente-2026&button=cta&utm_campaign=sincronizar-postgresql-bigquery-automaticamente-2026&utm_source=blog&utm_medium=organic&utm_content=bydefault&landing_page=%2Fblog%2Fsincronizar-postgresql-bigquery-automaticamente-2026&page_group=blog) when you want the CDC checklist, retries, and schema handling done for you.