Logical Replication CDC From Postgres to Cloud Warehouses

Logical replication CDC works because one setting, wal_level = logical, forces Postgres to write enough detail into the write-ahead log to reconstruct row-level changes rather than just block-level diffs, and that single parameter makes everything downstream possible. The WAL was never built for this purpose. It exists for crash recovery, so logical replication just rides along on data Postgres was already writing, and you don't get a second write path doubling the cost of every transaction. Because the WAL sender process just reads WAL files that already exist on disk, your running application sees no extra write overhead on its normal transaction path.

Turning this on is not a quiet change. wal_level = logical can't be applied with a reload. It needs a full server restart, and that restart is the one planned moment of downtime the entire CDC setup requires: calling pg_reload_conf() does nothing for this parameter, no matter how often it gets tried.

Once the WAL carries enough information, something still has to turn raw WAL bytes into events you can use. Postgres has shipped a default output plugin, pgoutput, in core since version 10, and it speaks a compact binary protocol that most modern CDC connectors expect. An alternative, wal2json, emits plain JSON instead, which is easier to parse from application code. It's a server extension rather than a core component, though most managed cloud providers already have it installed and ready to enable. A third option, decoderbufs, emits Protocol Buffers and still turns up in older tutorials, but it is not what current pipelines default to.

It helps to be precise about what this mechanism is not. Physical, or streaming, replication ships WAL byte-for-byte to a standby server for high availability, while logical replication decodes that same WAL into row-level events that can go anywhere: another Postgres instance, a message queue, a warehouse. The two solve different problems and shouldn't be treated as interchangeable tools. Physical replication is the right choice for failover and read scaling; logical replication is the right choice the moment changes need to leave Postgres entirely. Everything that follows in a CDC pipeline, the publication that decides what gets replicated and the slot that tracks how far a consumer has read, builds on the output this decoding layer produces.

Publications and replication slots: the two objects that make CDC reliable, and fragile

Two server-side objects sit between the decoded WAL and a working CDC pipeline. A publication declares which tables and which operations get replicated, and a replication slot is a durable bookmark that stops Postgres from recycling WAL a consumer hasn't read yet. Together they make a pipeline selective and reliable, but the slot carries a storage risk the publication doesn't share.

Setting up a publication is declarative and carries almost no risk to the source database. CREATE PUBLICATION my_pub FOR TABLE orders, customers; marks those tables for replication, and nothing else changes on the source. Postgres 15 extended this with row and column filtering on publications, so a consumer can be scoped down to only the rows and columns it actually needs rather than entire tables. A single publication can feed multiple subscriptions, but each consumer still needs its own slot to track its own read position.

The slot is where the real operational stakes live. It carries two LSN values that matter to anyone running this in production: restart_lsn, the oldest WAL position the slot still needs, and confirmed_flush_lsn, the furthest position the consumer has actually acknowledged. Postgres will not delete WAL that a slot still needs, and that guarantee is the entire reason CDC can promise not to lose changes. But the slot only moves forward when the consumer sends a StandbyStatusUpdate message confirming what it has received. If the consumer dies without sending that confirmation, the slot just stops advancing, and it waits.

Watching this is not optional work that can be deferred to later. A slot left behind by a dead test consumer can accumulate WAL silently until the disk fills, a failure mode that occurs often enough in production to be treated as a near-certainty rather than an edge case. max_slot_wal_keep_size caps how much WAL a lagging slot is allowed to pin, which limits the blast radius, but it is a safety net, not a substitute for actually watching pg_replication_slots and calling pg_drop_replication_slot on anything abandoned.

Replica identity controls whether UPDATE and DELETE events include the old row's values. Get it wrong and the stream breaks, sometimes with a loud error and sometimes by quietly dropping information the consumer needed. By default, Postgres uses a table's primary key to identify which row changed on the subscriber side. A published table with no primary key will throw a missing replica identity error the moment an UPDATE tries to go through. REPLICA IDENTITY FULL solves that by writing the entire previous row image into the WAL on every change, which works reliably but costs real WAL volume, and should be reserved for tables that genuinely have no unique key to fall back on.

Postgres version history and pipeline capability

What a logical replication CDC pipeline can actually do depends heavily on which major version of Postgres is running on the source, because several features that production teams now rely on arrived only in the last few release cycles. Mapping a source server's version against this list is the fastest way to know what's available before attempting a feature that isn't.

Postgres 10 introduced logical replication itself, along with the pgoutput plugin shipped in core. This is the floor: nothing described in this piece applies to any version older than 10.

Postgres 14 added the pg_read_all_data role, which simplifies granting a CDC role read access across captured tables instead of issuing explicit SELECT grants table by table.

Postgres 15 added row and column filtering directly on publications, so a CDC role can get only the rows and columns it actually needs. That cuts both data exposure and the amount of work the WAL decoder has to do.

Postgres 16 added logical decoding from a standby and parallel apply. The WAL sender's work can be offloaded away from the primary server entirely, a change that matters most for high-throughput sources under heavy write load.

If you run on a managed cloud provider, you add an entirely separate layer of configuration on top of whatever a given Postgres version supports, and that is where most setup friction actually happens.

Managed cloud providers and Postgres configuration

Every major cloud provider supports logical replication, but each one wraps it in its own configuration that has to be set correctly before a CDC pipeline can connect at all, and a handful of these constraints don't appear anywhere in the core Postgres documentation.

On AWS RDS and Aurora, logical replication requires setting the rds.logical_replication parameter to 1 in the parameter group and rebooting the instance, plus granting the rds_replication role to whichever role will run the replication connection. Aurora adds a wrinkle beyond that: the WAL sender only runs on the writer instance, so read replicas cannot serve as a CDC source on PostgreSQL 15 and earlier. PostgreSQL 16 added support for logical decoding from read replicas, closing that gap for anyone running the newer version.

On Google Cloud SQL, you need to enable the cloudsql.logical_decoding flag before anything else will work. Logical replication and decoding for Cloud SQL for PostgreSQL reached public preview in 2021.

Azure Database for PostgreSQL, Supabase, and Neon each expose their own version of this same toggle.

One blocker catches teams by surprise regardless of provider: connection poolers. Transaction-mode poolers like PgBouncer cannot carry the replication protocol at all. A CDC pipeline has to connect directly to Postgres, bypassing any pooler sitting in the path, even on systems where the application itself routes every other query through that same pooler.

Security practice stays consistent across every provider: build a dedicated role scoped to what CDC needs and nothing more. On Postgres 14 and later, that means the REPLICATION attribute combined with pg_read_all_data. On older versions, it means the REPLICATION attribute combined with explicit SELECT grants on each table being captured. A superuser credential or an application user's existing credential should never be the one making the replication connection.

Getting the provider-level configuration right only earns a working connection. What happens after the connection is live, once data actually starts flowing, is where most pipelines run into trouble.

The four production failure modes that break Postgres CDC pipelines

Four categories of failure account for most of the damage done to Postgres CDC pipelines in production: slot bloat, TOAST column edge cases, DDL propagation gaps, and failover slot eligibility. All four share one trait: each stays invisible right up until it becomes catastrophic.

Slot bloat happens when a consumer lags or disconnects and WAL keeps accumulating behind it. A slot's restart_lsn cannot move forward until the consumer confirms receipt, so a slow consumer, a crashed consumer, or one that was simply abandoned pins WAL on the primary indefinitely. Left unchecked, the disk fills, Postgres loses the ability to checkpoint or recycle WAL segments, and in the worst case the primary itself crashes. The fix has a clear order of operations: set max_slot_wal_keep_size to cap how much WAL a lagging slot can pin, accepting that this may eventually invalidate the slot rather than let it take down the server; alert on inactive_since in pg_replication_slots, since a slot that's been inactive for more than a few minutes needs immediate attention; and drop abandoned slots right away, recreating the pipeline from a fresh snapshot rather than letting a dead slot keep accumulating WAL in the background.

TOAST column edge cases are subtler because they don't throw an error. Postgres stores large column values above a certain size threshold in a separate TOAST table, so the WAL only records a pointer to that value, not the value itself. If a row gets updated but the TOASTed column isn't touched in that transaction, the logical decoding event can omit that column's value entirely, so the consumer gets a partial row with no indication anything is missing. The fix is to apply REPLICA IDENTITY FULL on any table with TOAST columns the pipeline actually cares about, or to confirm the output plugin is configured to fetch the full TOAST value rather than relying on the pointer.

DDL propagation gaps break pipelines at the schema level rather than the data level. Logical replication moves data, not structure, so an ALTER TABLE run on the source does not appear downstream on its own. A column added on the source simply won't appear at the destination until someone adds it there too. A column dropped or retyped is worse: the decoder may emit an event referencing a column the destination no longer expects, or the destination may expect a column the source has already removed, and the stream breaks in either direction. This is a named limitation of logical replication CDC generally, not a bug specific to any one setup. The production posture that holds up is treating every schema migration as two deliberate phases: apply additive changes at the destination first, then apply them on the source, and for anything destructive, pause the pipeline and replay from a fresh snapshot instead of letting the stream run through the gap.

Postgres 17 introduced logical replication failover, but it does not automatically cover every standby in a cluster. A standby only becomes eligible to carry a slot's state forward once the subscriber has advanced that slot at least one time while the standby was actively receiving slot metadata. A standby added recently, or one that happened to be offline during the last confirmation from the subscriber, is not eligible and cannot take over the slot when a promotion happens. After a promotion to a standby that wasn't eligible, the CDC pipeline has to recreate its slot from a new snapshot, re-syncing the entire initial load into the warehouse from scratch. The only real mitigation is to check standby eligibility before an incident forces the question, by querying pg_replication_slots on the primary and confirming the target standby actually appears as a recipient of slot metadata.

Consuming the change stream: direct client libraries versus managed pipelines

Once the WAL is decoding correctly and the four failure modes above are accounted for, there's a genuine architectural choice left to make about how the decoded stream gets consumed. One path is to speak the replication protocol directly from application code; the other is to hand the stream to a managed pipeline that takes care of delivery, buffering, and loading into a warehouse.

Writing a direct client gives full control over every part of the process: how acknowledgments get sent back to the slot, how retries are handled, how events get batched before they're written somewhere else. That control comes with real responsibility attached. The client has to manage StandbyStatusUpdate confirmations correctly so the slot advances and WAL gets freed on schedule. It has to handle reconnection logic cleanly so a dropped connection doesn't turn into an abandoned, bloating slot. It has to deal with TOAST pointers, schema drift, and output plugin quirks as part of its own code rather than relying on someone else to have solved them already. For a team with a small number of source databases and engineers who already understand the replication protocol in depth, this path offers the most flexibility and the lowest ongoing cost.

A managed pipeline takes on that operational weight, but you get less direct control in exchange. If you run CDC across many source databases, or you don't have the engineering time to build and maintain protocol-level client code, this tradeoff usually favors the managed route.