PostgreSQL ships with two replication mechanisms that solve different problems, but they get confused often enough that teams use one where the other is required — and discover the difference during a failover. Physical (streaming) replication is for high availability and read scaling. Logical replication is for selective data movement and zero-downtime major version upgrades. Using logical replication as a drop-in HA replacement leaves you with sequence values that have diverged, DDL changes that never arrived at the subscriber, and a schema state on the standby that does not match the primary.

Situation

Most PostgreSQL deployments start with physical streaming replication. It works, it is simple to configure, and for HA purposes it does exactly what is needed: a replica that is continuously kept in sync and can be promoted in seconds if the primary fails.

Logical replication was added in PostgreSQL 10 and extended significantly in each subsequent release. It has a specific purpose: moving a subset of data across PostgreSQL instances that may differ by major version, schema, or platform. The canonical use case is a zero-downtime major version upgrade — replicate from a PG14 primary to a PG15 target, validate, then promote.

The Problem

Teams encounter confusion when they try to use logical replication for HA or try to use physical replication for version upgrades.

The failure mode that hurts: an engineer sets up logical replication from a PG13 primary to a PG14 standby as the HA plan, does no DDL synchronization, runs several migrations over six months, and then fails over. The standby runs, but queries immediately fail because the schema is months out of date.

How do we safely distinguish these mechanisms and use the right one for the right operational constraint?

Core Concept

flowchart TD
    subgraph Physical Replication
    P1[Primary — PG14] -->|Raw WAL Bytes| S1[Standby — PG14]
    S1 -.->|Exact Clone| R1[Read Only Query]
    end

    subgraph Logical Replication
    P2[Publisher — PG14] -->|Decoded Row Changes| S2[Subscriber — PG15]
    S2 -.->|Writeable Target| R2[Zero Downtime Upgrade]
    end

What this diagram shows: Physical replication sends raw WAL bytes to an exact binary copy of the primary that must run the same major PostgreSQL version and stays read-only. Logical replication decodes individual row changes and sends them to a subscriber that can run a different PostgreSQL version and accept writes — which is what enables zero-downtime major version upgrades.

Physical replication copies WAL byte-for-byte. The replica is a binary clone of the primary: same files, same transaction IDs, same system catalog. This means it requires the same PostgreSQL major version as the primary (minor version differences are allowed). It replicates everything — all databases, all tables, all sequences, system catalogs — because it is literally replaying the raw write-ahead log.

Logical replication decodes WAL into row-level changes: INSERT, UPDATE, DELETE events per table. A publication on the primary defines which tables to send; a subscription on the target applies those changes. The target is a separate, writeable PostgreSQL instance — it can be a different major version, a different schema, or even a different Postgres fork.

There are specific limitations of logical replication that dictate when it can be used:

DDL is not replicated. Schema changes executed on the publisher — ALTER TABLE, CREATE INDEX, ADD COLUMN — are not sent to the subscriber. The subscriber’s schema must be managed separately. A column added on the primary will not exist on the subscriber, and the replication stream will fail when it encounters rows with that column.

Sequences are not replicated. Sequence state (the current counter) is not sent over logical replication. After promotion of a logical subscriber, all SERIAL and IDENTITY columns will restart from wherever the sequence was initialized on the subscriber — which may be far below the primary’s current value, causing primary key conflicts on first insert.

Large objects are excluded. PostgreSQL logical replication does not support pg_largeobject — any data stored via the large object interface is not sent.

PropertyPhysical ReplicationLogical Replication
WAL contentRaw bytes, page-levelDecoded row changes
Version requirementSame PG major versionCross-major-version capable
ScopeEntire clusterPer-table, per-publication
DDL replicatedYes (byte-for-byte)No — must apply manually
Sequences replicatedYesNo
Large objectsYesNo
Subscriber writeableNo (hot standby read-only)Yes
Primary use caseHA, read replicasVersion upgrades, selective sync
Failover timeSeconds (promote standby)Minutes (manual schema validation needed)

In Practice

PostgreSQL’s streaming replication documentation (postgresql.org/docs/current/warm-standby.html) describes physical replication’s behavior: the standby continuously applies WAL records and can be promoted instantly because it shares the same timeline and transaction state as the primary.

PostgreSQL’s logical replication documentation (postgresql.org/docs/current/logical-replication.html) documents the known limitations explicitly: “Only DML operations are replicated. Schema changes (DDL) are not replicated.” The documentation also notes that “sequences are not replicated” and recommends that operators who use logical replication for version upgrades must handle sequence advancement manually during the cutover.

The documented pattern from the PostgreSQL logical replication documentation is that the initial table sync for a new subscription copies the current table contents as a snapshot — on large tables this can take hours, and replication lag accumulates during that window. Physical replication has no equivalent initial sync cost because it starts from a base backup and streams from there.

Where It Breaks

The limitations of logical replication create operational risk if used incorrectly:

ScenarioWhat breaksWhy
DDL on publisher not applied to subscriberReplication stream errors when row data includes columns not present in subscriber schema; apply worker stopsLogical replication does not decode or forward DDL; subscriber schema must be kept in sync manually
Sequence values diverge after failoverFirst INSERT after promotion generates IDs that conflict with rows that existed on the former primarySubscriber sequences were never updated; they restart from initialization value, not primary’s current value
Initial snapshot for large tablesReplication lag grows during the hours-long initial sync; the subscriber cannot be used as an HA target during this windowLogical replication’s initial sync is a table-level snapshot copy, not a streaming catchup

For a zero-downtime major version upgrade, the sequence problem is solved by advancing the subscriber’s sequences past the primary’s current values before promotion. PostgreSQL’s pg_upgrade documentation recommends scripting this using setval() against each affected sequence immediately before the promotion cutover.

What to Do Next

  • Problem: Teams treating logical replication as a drop-in HA mechanism get schema drift and sequence conflicts at promotion time — failover appears to succeed, then applications fail immediately.
  • Solution: Use physical streaming replication for HA; reserve logical replication for cross-version migration or selective data movement, and build explicit DDL sync and sequence advancement steps into the cutover runbook.
  • Proof: After a logical replication setup, query SELECT schemaname, tablename FROM information_schema.tables WHERE table_schema = 'public' on both primary and subscriber and diff the results — schema parity must be verified before any promotion.
  • Action: If you have an existing logical replication setup intended for HA, audit it this week: list all DDL changes since the subscription was created and confirm each was applied on the subscriber.