What is logical replication and when is it useful?

Question

Accepted Answer

**Logical replication** replicates data changes at the level of **specific tables** (using a publish/subscribe model), rather than copying the entire database cluster byte-for-byte like physical (streaming) replication. This makes it flexible — selective, cross-version, and to different schemas — ideal for migrations and integrations.

## Logical vs physical replication

```text
PHYSICAL (streaming) → replicates the ENTIRE cluster at the storage/WAL level
  ✓ exact copy, simple   ✗ all-or-nothing, same Postgres version, read-only replica

LOGICAL → replicates specific TABLES' changes via a publish/subscribe model
  ✓ SELECTIVE (choose tables), CROSS-VERSION, to DIFFERENT schemas, replica is WRITABLE
  ✗ more setup, some limitations (DDL not replicated automatically, etc.)
```

## Setting up logical replication (pub/sub)

```sql
-- on the SOURCE (publisher): publish specific tables
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- (or FOR ALL TABLES)

-- on the TARGET (subscriber): subscribe to the publication
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=source dbname=mydb user=rep'
  PUBLICATION my_pub;
-- → the subscriber receives and applies changes to users and orders
```

A **publication** defines what to replicate (specific tables); a **subscription** consumes it. Changes (INSERT/UPDATE/DELETE) flow from publisher to subscriber.

## When logical replication is useful

```text
✓ ZERO-DOWNTIME MAJOR UPGRADES — replicate from an old version to a new version, then
  switch over (physical replication can't cross versions) — a key use case
✓ MIGRATIONS — move data to a new server/cluster with minimal downtime
✓ SELECTIVE replication — replicate only certain tables (not the whole database)
✓ INTEGRATIONS — feed specific data to another system (analytics, data warehouse)
✓ Consolidating data from multiple databases into one
✓ The replica is WRITABLE (can have its own additional data/tables)
```

## Why it matters

Logical replication is valuable PostgreSQL knowledge for **flexible data replication scenarios** — particularly migrations, upgrades, and integrations — so understanding it is useful senior-level knowledge for database operations and architecture.

Its key distinction from physical (streaming) replication is **flexibility**: rather than copying the entire cluster byte-for-byte (physical), logical replication replicates **specific tables** via publish/subscribe, and crucially works **across PostgreSQL versions** and to **different schemas**, with a **writable** subscriber.

This flexibility enables important use cases that physical replication can't handle: most notably **zero-downtime major version upgrades** (replicating from an old Postgres version to a new one, then switching over — a critical capability since physical replication requires matching versions, making logical replication the standard approach for upgrading major versions without downtime), **migrations** (moving data to a new server/cluster with minimal downtime), **selective replication** (replicating only certain tables), and **integrations** (feeding specific data to analytics systems, data warehouses, or other databases).

Understanding the publish/subscribe setup (publications defining what to replicate, subscriptions consuming them) and the appropriate use cases (especially upgrades and migrations) reflects valuable operational knowledge.

Since major version upgrades and migrations with minimal downtime are important, real operational challenges (and logical replication is the key tool for them), and since selective/cross-system replication enables valuable integration patterns, understanding logical replication — how it differs from physical, its flexibility (cross-version, selective, writable), and its use cases (especially zero-downtime upgrades and migrations) — is valuable senior-level knowledge for PostgreSQL operations and architecture, particularly relevant for the recurring, high-stakes need to upgrade and migrate databases without downtime, a topic demonstrating advanced understanding of Postgres deployment and operations.