Logical Replication and Logical Decoding

The one thing to understand first

Where physical replication ships raw WAL blocks, logical replication ships logical row changes — "insert this row", "update that row" — reconstructed from the WAL by logical decoding. Because it operates at the row level via SQL, publisher and subscriber can differ in major version, architecture, and even schema, and you can replicate a subset of tables. This flexibility powers upgrades, selective data distribution, and integration pipelines.

Logical replication is not a copy of the database — it is a stream of row-level changes applied by a normal writable backend. That single fact explains every difference: cross-version, partial, conflict-prone, and DDL-blind.

Logical decoding: turning WAL into changes

The WAL was designed for physical recovery, not human-readable changes. Logical decoding (src/backend/replication/logical/) reads the WAL and, using catalog information, reconstructs each committed transaction as an ordered stream of row changes. An output plugin formats that stream; the built-in pgoutput plugin feeds native logical replication, while plugins like wal2json emit JSON for change-data-capture (CDC) consumers.

Publications and subscriptions

-- Publisher: declare what to replicate
CREATE PUBLICATION app_pub FOR TABLE orders, customers;
-- or FOR ALL TABLES

-- Subscriber: consume it
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=primary dbname=app'
  PUBLICATION app_pub;
-- Initial sync copies current data, then streams ongoing changes.

A subscription creates a logical replication slot on the publisher to track its progress and ensure WAL is retained until consumed.

Replica identity: how UPDATE/DELETE find rows

To replicate an UPDATE or DELETE, the subscriber must identify which row changed. That is the replica identity: by default the primary key. A table with no primary key needs REPLICA IDENTITY FULL (logs the whole old row, expensive) or a unique index designated as identity — otherwise updates and deletes cannot be replicated:

ALTER TABLE legacy REPLICA IDENTITY FULL;  -- if no PK available

What it does and does not replicate

• Replicates: INSERT/UPDATE/DELETE on published tables, and TRUNCATE on modern versions.
• Does NOT replicate: DDL (schema changes must be applied on both sides manually), sequence values, large objects. Schema drift breaks replication — coordinate DDL carefully.

Conflicts on the subscriber

Unlike a physical standby (read-only), a logical subscriber is a normal writable database. If local writes or constraints clash with an incoming change, replication stops with a conflict. You resolve it by fixing the data or skipping the offending change (advancing the subscription origin), then replication resumes. Designing the subscriber to avoid conflicting writes is essential.

Layer 3 — Watch it happen on your own database

Two catalogs tell you whether replication is healthy and how far behind it is — watch them on both sides:

-- On the subscriber: per-table sync state and apply position
SELECT subname, relid::regclass, srsubstate, srsublsn
FROM   pg_subscription_rel;
SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM   pg_stat_subscription;

-- On the publisher: WAL retained for each subscription's slot
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS retained
FROM   pg_replication_slots;

Watch srsubstate move from d (initial copy) to r (ready/streaming). On the publisher, the retained column climbing means a subscriber has fallen behind or disconnected — that WAL cannot be removed until it catches up.

Layer 4 — The levers this hands you

Logical replication is a flexibility tool — use it where row-level, cross-version streaming wins:

• Near-zero-downtime major upgrades (the blue/green pattern).
• Selective replication — send only certain tables to a reporting or regional database.
• Change data capture — stream changes to Kafka, search indexes, or data warehouses via output plugins.
• Consolidation — many publishers into one subscriber (with care over key collisions).

And the operational watch-points that come with it:

• An inactive or far-behind slot retains WAL and can fill the publisher's disk — monitor pg_replication_slots.
• Initial table sync of large tables is heavy; consider replicating in stages.
• Keep schemas in lockstep; apply DDL to the subscriber first for additive changes (DDL is not replicated).
• Tables without a primary key need REPLICA IDENTITY FULL (expensive) or a unique index, or their UPDATE/DELETE cannot replicate.

Layer 5 — What an Oracle DBA should expect vs what they get

Logical replication is closest to Oracle GoldenGate, and an Oracle DBA should map it accordingly:

• Logical replication ≈ GoldenGate / LogMiner-based CDC, built into the engine and free — not a separately licensed product. Logical decoding plays LogMiner's role of turning redo into row changes.
• The subscriber is a live writable database, like a GoldenGate target — so conflicts are real and replication stops on a constraint clash, exactly as a GoldenGate apply process would error. Postgres has no built-in automatic conflict-resolution rules; you fix data or skip the change.
• DDL is not replicated. GoldenGate can optionally capture DDL; native Postgres logical replication cannot — schema changes must be coordinated manually on both sides.
• Sequences and large objects are not replicated either — a gap that catches Oracle DBAs expecting full-database parity.

Key takeaway

Logical replication decodes the WAL into row-level changes and replays them via SQL on a normal writable subscriber, enabling cross-version upgrades, partial replication, and CDC — at the cost of conflicts, no DDL/sequence replication, and a replica-identity requirement for UPDATE/DELETE. Monitor the publisher's slot lag so an abandoned subscriber never fills the disk, and keep schemas in lockstep to avoid breaking the stream.