CockroachDB to PostgreSQL: How to Move Your Data

Moving data from CockroachDB to PostgreSQL looks solved before you start, because CockroachDB speaks the PostgreSQL wire protocol. You can point psql at it. Your Postgres driver connects without changes. So the reasonable assumption is that a Postgres replication tool will treat it like Postgres, read its write-ahead log, and stream changes across.

That assumption is wrong, and it is better to know now than after designing around it. This guide covers the managed path: replicating CockroachDB tables into Postgres with Airbyte, using cursor-based incremental sync, and the handful of setup decisions that determine whether the pipeline still works in six months.

CockroachDB to PostgreSQL at a glance:

Why move data from CockroachDB to PostgreSQL?

Two situations account for most of these pipelines.

The first is analytical. CockroachDB is doing what it is good at, surviving node failures and spanning regions, and none of that helps the analyst who wants to run a six-table join. Replicating into a single Postgres instance gives reporting tools something conventional to query, and keeps analytical load away from a cluster sized for transactional work.

The second is consolidation or migration. Postgres becomes the place where CockroachDB data meets everything else, or the destination the application will eventually move to. In a migration the pipeline runs alongside the old system during cutover, keeping Postgres current until traffic switches, which makes the sync mode decisions below considerably more consequential than they are for a reporting copy.

What do you need before you start?

Most of the trouble with this connector is set up on day one and discovered months later:

A read-only SQL user. Create one in CockroachDB and grant it SELECT on the schema you intend to replicate, rather than reusing an application account.

Default privileges for future tables. Run ALTER DEFAULT PRIVILEGES for that schema as well. Without it the grant covers only the tables that existed the moment you ran it, so anything created afterwards is invisible to the sync user. Nothing errors. New tables simply never appear in the catalogue, and nobody notices until someone asks why a table is missing from the warehouse.

A cursor column per table. Incremental sync needs a column that only ever increases and is set on every write. An updated_at timestamp maintained by your application is the usual choice, and a sequence-backed integer works when rows are never updated after insert.

One source per schema. The connector is scoped to a single schema per source configuration, so data spanning several schemas means several sources. That is mildly tedious and it does mean each one gets its own schedule and its own failure blast radius.

On the Postgres side you need a database, a schema, and a user with CREATE and INSERT on that schema. If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on both databases before you begin.

How do you build a CockroachDB to PostgreSQL pipeline in Airbyte?

Step 1: Create the CockroachDB user and grants

Create the read-only user, grant SELECT on the target schema, then run ALTER DEFAULT PRIVILEGES for the same schema so future tables are covered. Doing both in one session, at the start, is the single highest-value five minutes in this build. Retrofitting the second grant later works, but only after someone has noticed the gap.

Step 2: Configure the CockroachDB source

In Airbyte, click Sources in the left navigation, then New Source, and select CockroachDB. Supply the host, port, database, schema and the credentials from step 1. Airbyte tests the connection and then takes you to the new connection page. The general flow is covered in adding a source. If your data spans several schemas, repeat this step once per schema.

Step 3: Configure the PostgreSQL destination

Click Destinations, then New Destination, and select Postgres. Supply the host, port, database name, default schema and credentials, as described in adding a destination. Airbyte creates one table per stream and adds its own metadata columns alongside your data, so warn whoever reviews the schema that those are expected rather than stray. Details of the destination's behaviour are in the Postgres destination docs.

Step 4: Create the connection and choose sync modes

Click Connections, then New connection, and pick your source and destination. Airbyte fetches the schema, and you select tables and nominate a cursor column for each one you want to sync incrementally. Choose the sync mode per stream rather than globally: incremental append and deduped gives a table that mirrors current state, which suits entity tables like customers or orders, while plain incremental append keeps every version Airbyte ever saw, which suits anything where the history matters.

Set a replication frequency, save, and trigger the first sync. Then check the resulting tables against what you expected, because the first sync is when a badly chosen cursor shows itself most cheaply.

Why can't Airbyte use CDC with CockroachDB?

Wire compatibility is about the conversation between client and server, not about the storage engine underneath. PostgreSQL exposes its write-ahead log through logical replication slots, and that surface is what change data capture attaches to. CockroachDB, being a different database that happens to answer the same protocol, has no equivalent for Airbyte to read.

CockroachDB does have changefeeds of its own, which is why this trips people up more than it should. Those are a real feature and they are not what the Airbyte source reads, so their existence does not give you delete capture through this connector.

The practical consequence is that two things a cursor cannot see will not reach Postgres. Rows updated without touching the cursor column are never picked up again. And rows deleted in CockroachDB stay in Postgres indefinitely, because a query filtered on a cursor has no way to observe an absence.

How should you handle deletes?

For a reporting copy, often you do not need to. If the tables you care about are append-only, orders placed, events recorded, payments taken, there are no deletions to miss and cursor-based replication costs you nothing at all.

Where deletions do happen, the cleanest answer is a soft-delete column in CockroachDB that your application sets instead of issuing a DELETE, with the filtering done downstream in Postgres. The cursor picks up the update, the row is marked, and your views exclude it. This works well when you control the schema and not at all when you do not.

The fallback is a periodic full refresh on the affected tables. Weekly is usually enough to keep a reporting copy honest, and it is cheap on small tables and expensive on large ones, which is a good reason to apply it selectively rather than to the whole connection.

Frequently asked questions

Can I use CDC from CockroachDB to PostgreSQL with Airbyte?

No. The CockroachDB source is cursor-based only. CockroachDB's own changefeeds are not what the connector reads, so it cannot replicate deletes or catch updates that leave the cursor column untouched.

Why does my sync miss tables I created recently?

Almost always the missing ALTER DEFAULT PRIVILEGES step. Grant it for the schema, then refresh the source schema in Airbyte so the new tables appear in the catalogue.

Can one connection cover multiple CockroachDB schemas?

No. Configure one source per schema. All of them can point at the same PostgreSQL destination, so you end up with several sources and one destination.

What happens to rows deleted in CockroachDB?

They remain in PostgreSQL. Use a soft-delete flag in the source and filter downstream, or schedule a periodic full refresh on the tables where deletions actually occur.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven, from creating the source through to scheduling the connection. If you would rather manage pipelines as infrastructure, the same source, destination and connection can be provisioned from configuration files instead.

Get your CockroachDB data into PostgreSQL

You can have this connection running in an afternoon. Fix the default privileges once, nominate a cursor you trust on every table, and decide deliberately what a deletion should mean before anyone builds a report on top of the result.

Airbyte's connector catalog includes 600+ pre-built connectors, so if Postgres turns out to be a staging point rather than the endpoint, the same pattern applies to a warehouse. See PostgreSQL to BigQuery and PostgreSQL to Databricks for what changes when the destination is columnar.

Start syncing now →