PostgreSQL to PostgreSQL: How to Replicate Your Data

Replicating PostgreSQL to PostgreSQL sounds like it should be the easiest pipeline you will ever build, and sometimes it is. But Postgres already ships with two forms of native replication, so the first question is not how to configure Airbyte. It is whether you should be using Airbyte for this at all.

This guide covers where a managed pipeline genuinely beats native replication, where it does not, and how to configure the connection when it is the right choice.

PostgreSQL to PostgreSQL at a glance:

Should you use Airbyte or native Postgres replication?

Answer this before you build anything, because getting it wrong means maintaining a pipeline you did not need.

Use native replication when you want a hot standby, a read replica for load spreading, or a full copy of one database at the lowest possible latency. Postgres logical replication and physical streaming replication are built for exactly this, run continuously rather than on a schedule, and produce a genuine replica rather than an approximation. No third-party tool will beat them at that job.

Use Airbyte when the two instances are not easy peers. That covers a surprising number of real situations: replicating across cloud providers or from on-premises into a managed service, where network topology makes native replication awkward. Consolidating several application databases into one reporting Postgres, which native replication cannot do because a subscriber cannot merge multiple publishers into the same tables. Copying a selective subset of tables into a sandbox. Or moving between major versions during an upgrade path.

The pattern is that native replication wins on fidelity and latency for one-to-one copies, while a managed pipeline wins on flexibility when the topology, the selection, or the number of sources is the hard part.

Which replication method should you choose?

The Postgres source offers three, and the decision turns on size and whether deletions matter.

xmin is the low-overhead default. It tracks changes through the Postgres system column, needs no logical replication setup, and suits most moderate databases. It does not capture deletes.

CDC reads the write-ahead log and is what Airbyte recommends once a database passes roughly 500 GB, when deletions need recording, or when tables have primary keys but no sensible cursor. It is the closest thing to native replication behaviour, at the cost of more setup and ongoing care.

Standard cursor-based replication is the fallback. It is also the easiest to get wrong, because a cursor that is not strictly increasing will skip rows without raising an error.

How do you set up the pipeline?

Step 1: Configure the source

Create a dedicated read-only user. For xmin or cursor replication, that plus network access is the whole job: enter host, port, database, credentials, and the schemas you want.

For CDC, grant the user REPLICATION permission, create a dedicated slot with SELECT pg_create_logical_replication_slot('airbyte_slot', 'pgoutput');, set a replication identity on each table, and create a publication listing them. A table selected in the Airbyte UI but missing from the publication will silently fail to replicate. Managed services may also need logical decoding enabled at the instance level.

Treat the slot as an object with a lifecycle. An abandoned slot makes Postgres retain WAL indefinitely and can fill the disk on the database you are replicating from, which is to say your production one. Delete it when you retire the connection. The source troubleshooting guide covers the failure modes, including what to do when syncs revert to full refreshes because Postgres removed WAL segments Airbyte still needed.

Step 2: Configure the destination

Add a Postgres destination with host, port, database, default schema, and a user that can write to it. Airbyte Cloud connects over SSL or TLS by default. If either instance restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to both allow lists, not just one.

Step 3: Set the namespace deliberately

This matters more here than in any other pairing, because source and destination speak the same dialect and it is easy to create a mess. Mirroring the source structure recreates your source schema names on the destination, which is usually what you want when the destination is a separate instance. Destination Default writes everything into one schema you nominate, which is the safer choice when you are consolidating several source databases and would otherwise get schema collisions.

If source and destination are the same instance, and they occasionally are for sandbox refreshes, make certain the destination schema is not one the source is also reading. Otherwise Airbyte will start replicating its own output on the next sync.

What does the destination table actually look like?

Not identical to the source, and this is the expectation most people get wrong about same-database replication.

From version 3.0.0 the destination uses Direct Load, writing records straight into final tables with no intermediate raw tables. Each table carries your columns plus three Airbyte metadata columns: _airbyte_raw_id, _airbyte_extracted_at, and an _airbyte_meta JSONB column recording per-row typing changes.

What does not come across is everything that is not data: indexes, foreign keys, check constraints, sequences, triggers, views, and functions. Airbyte replicates table contents, not schema objects. If your goal is a working copy of an application database rather than a queryable copy of its rows, you will need to recreate those separately, and native tooling such as pg_dump is a better fit for that part.

Two other limits apply. Airbyte's documentation is direct that Postgres is not a data warehouse and recommends destinations of roughly 10GB or less. And Postgres truncates identifiers at 63 bytes, which can cause column name collisions with unusually long or deeply nested names. The destination troubleshooting guide covers both.

Frequently asked questions

Why not just use native Postgres logical replication?

For a one-to-one copy of a single database at the same version, you probably should. Airbyte earns its place when you are crossing cloud providers, consolidating several source databases into one destination, replicating a selective subset of tables, or working around a topology that makes native replication impractical.

Will my indexes and constraints be recreated?

No. Airbyte replicates table data. Indexes, foreign keys, sequences, triggers, views, and functions are not carried across and must be created on the destination separately if you need them.

Can I replicate several Postgres databases into one?

Yes, and this is one of the strongest reasons to choose a pipeline over native replication, which cannot merge multiple publishers into the same destination tables. Use the Destination Default namespace setting, or set stream prefixes, so tables from different sources do not collide.

Are deletes reflected on the destination?

Only with CDC. Neither xmin nor cursor-based replication can see a deleted row, so those methods leave the destination holding records the source no longer has.

What if source and destination are the same instance?

It works, but choose the destination schema carefully. If it falls inside the schemas the source reads, the next sync will pick up Airbyte's own output tables and replicate those too.

Get your PostgreSQL data into PostgreSQL

Decide against native replication on purpose rather than by default, pick a replication method that matches your size, and set the namespace before the first sync. Outgrowing Postgres as a destination? See our guide to PostgreSQL to BigQuery. Consolidating a document store into the same destination? See MongoDB to PostgreSQL.

Start syncing now →