PostgreSQL to BigQuery: How to Move Your Data
Move PostgreSQL into BigQuery with Airbyte. Choosing between CDC and xmin, loading methods, and the two BigQuery decisions you cannot reverse.
Moving PostgreSQL to BigQuery is one of the most common pipelines in analytics engineering. The application database holds the data everyone wants to analyse, and analysing it in place either slows the application down or is simply not what Postgres is built for.
This guide covers picking the right replication method for your database size, the two BigQuery decisions that are hard to reverse, and how to keep the replication slot from becoming an operational problem.
PostgreSQL to BigQuery at a glance:
Which replication method fits your database?
The Postgres source gives you three options, and the right one is mostly a function of size and whether deletions matter.
xmin is the sensible starting point for most databases. It uses the Postgres system column to track changes reliably, requires no logical replication configuration, and is the least operational overhead. It does not capture deletes.
CDC reads the write-ahead log and is what Airbyte recommends once a database passes roughly 500 GB, when you need deletions recorded, or when a table has a primary key but no reasonable cursor column. It costs more to set up and requires ongoing care.
Standard replication with a cursor you choose is the fallback for tables that suit neither. It is the easiest to misconfigure, because a cursor that is not strictly increasing will silently skip rows.
One xmin-specific failure is worth knowing about: if your database experiences xmin wraparound, replication performance degrades and already-synced data may be resynced. Airbyte checks for wraparound at setup and at the start of each sync, and returns a config error prompting a switch to CDC when it finds one. The troubleshooting guide covers the details.
How do you configure the Postgres source?
Create a dedicated read-only user for replication. For xmin or cursor-based replication, that plus network access is all you need. Configure the host, port, database, credentials, and schemas in the Airbyte UI and you are done.
For CDC there is more. Grant the user REPLICATION permission, create a dedicated replication slot using the pgoutput plugin with SELECT pg_create_logical_replication_slot('airbyte_slot', 'pgoutput');, set a replication identity on each table, and create a publication listing them. On managed Postgres you may also need to enable logical decoding at the instance level. On Cloud SQL, for example, that means setting the cloudsql.logical_decoding flag to on.
Then add the Airbyte Cloud IP addresses to your database's allowed networks. On Cloud SQL this is under Connections → Add Network.
What are the BigQuery decisions you cannot undo?
Dataset location
When configuring the BigQuery destination you set a Project ID, a Dataset Location, and a Default Dataset ID. The location is permanent. If you plan to join replicated Postgres tables against datasets that already exist in BigQuery, create everything in the same location, because cross-location joins are not something you can fix later without recreating the dataset and resyncing.
Loading method
Batched Standard Inserts uses the BigQuery driver's built-in conversion to turn inserts into file uploads loaded in batches. BigQuery manages those staging files and deletes them once the load completes. It is the simplest performant option.
GCS Staging gives you control over where staging files are written, which some teams need for audit or network reasons. The constraint: the bucket must use Google-managed encryption. Customer-managed encryption keys are not supported. Check the Encryption type row on your bucket's Configuration tab first, and make sure the service account can write to the bucket and path.
Service account permissions
The service account needs the BigQuery User and BigQuery Data Editor roles, with the key supplied as JSON. This is required on Airbyte Cloud and optional on open source. Most setup failures are one of those two roles missing.
How does Airbyte handle Postgres naming in BigQuery?
BigQuery dataset naming conventions are stricter than Postgres schema naming, so Airbyte adapts rather than failing. Invalid characters become underscores. Because datasets starting with an underscore are hidden in the BigQuery Explorer panel, Airbyte prepends converted namespaces with n to keep them visible.
A Postgres schema named with a leading underscore or a hyphen will therefore not appear in BigQuery under the name you expect. Check what was actually created after the first sync before writing queries or dbt sources against assumed names.
Frequently asked questions
Should I use CDC or xmin for Postgres to BigQuery?
xmin is the lower-overhead default and suits most databases. Move to CDC when you need deletions captured, when the database is around 500 GB or larger, or when tables have primary keys but no reliable cursor column.
Can I change the BigQuery dataset location after setup?
No. It is fixed when the dataset is created. Recreating the dataset in the right location and resyncing is the only fix, so decide before the first run.
Do I need a GCS bucket?
Only if you choose GCS Staging. Batched Standard Inserts requires no bucket, since BigQuery manages and cleans up its own staging files.
Why did my sync fail with a permissions error?
Usually the service account is missing BigQuery User or BigQuery Data Editor. If you are using GCS staging, also confirm it can write to the bucket and path.
What happens to my replication slot if I pause the connection?
Postgres keeps retaining WAL for that slot, which can fill your disk over time. Delete the slot if you are decommissioning the connection rather than pausing it briefly.
Get your PostgreSQL data into BigQuery
Pick the replication method that matches your database size, set the dataset location deliberately, and this pipeline is close to maintenance-free. Consolidating onto a different warehouse? See our guides to PostgreSQL to Snowflake and PostgreSQL to ClickHouse.
Connector behaviour described here reflects the Postgres source and BigQuery destination, verified August 2026. Check the linked documentation for current versions.
Integrate with 600+ apps using Airbyte
Move data from 600+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.