(opens in a new tab)

PostgreSQL Logical Replication: A Practical Guide

August 15, 2026 by Chat2DB Team

PostgreSQL offers two replication mechanisms that solve different problems. Streaming (physical) replication copies the write-ahead log byte for byte, producing an identical binary replica of the entire cluster. Logical replication decodes the WAL into row-level changes and replays them as INSERT, UPDATE and DELETE statements on the subscriber.

That difference unlocks things physical replication cannot do: replicating a single table, replicating between different major versions, replicating into a database that also accepts its own writes, and performing near-zero-downtime major version upgrades.

Physical versus logical, concretely

The two limitations that catch people out are the last two: logical replication does not replicate schema changes, and tables need a replica identity — normally a primary key — before updates and deletes can be replicated.

Configuring the publisher

Logical replication requires wal_level = logical, which needs a restart:

-- On the publisher
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;

sudo systemctl restart postgresql

Verify:

SHOW wal_level;   -- must return 'logical'

Create a dedicated role for replication and allow it to connect. In pg_hba.conf:

host    appdb    replicator    10.0.0.0/24    scram-sha-256

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong-password-here';
GRANT USAGE ON SCHEMA public TO replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

Creating a publication

A publication defines which tables are replicated:

-- Specific tables
CREATE PUBLICATION app_pub FOR TABLE orders, customers, order_items;
 
-- Or everything in the database
CREATE PUBLICATION all_pub FOR ALL TABLES;
 
-- Or everything in a schema (PostgreSQL 15+)
CREATE PUBLICATION sales_pub FOR TABLES IN SCHEMA sales;

You can also restrict which operations replicate, and — since PostgreSQL 15 — filter rows and columns:

-- Only replicate inserts and updates, not deletes
CREATE PUBLICATION insert_only_pub FOR TABLE events
    WITH (publish = 'insert, update');
 
-- Row filter: only replicate European orders
CREATE PUBLICATION eu_pub FOR TABLE orders WHERE (region = 'EU');
 
-- Column list: don't send the PII columns
CREATE PUBLICATION safe_pub FOR TABLE customers (id, created_at, country);

Inspect what exists:

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Preparing the subscriber

The subscriber needs the tables to already exist with matching column names and compatible types. Logical replication will not create them for you. Dump the schema only:

pg_dump -h publisher-host -U postgres -d appdb \
        --schema-only -t orders -t customers -t order_items \
        > schema.sql
 
psql -h subscriber-host -U postgres -d appdb -f schema.sql

Creating the subscription

-- On the subscriber
CREATE SUBSCRIPTION app_sub
    CONNECTION 'host=10.0.0.10 port=5432 dbname=appdb user=replicator password=strong-password-here'
    PUBLICATION app_pub;

By default this immediately creates a replication slot on the publisher and starts an initial data copy of every table, then switches to streaming changes. Watch it:

-- On the subscriber
SELECT * FROM pg_subscription_rel;

The srsubstate column reports each table's progress: i = initialize, d = data copy, f = finished copy, s = synchronized, r = ready (streaming normally).

Skipping the initial copy

If you have already loaded the data yourself — for instance from a consistent dump — skip the copy:

CREATE SUBSCRIPTION app_sub
    CONNECTION '...'
    PUBLICATION app_pub
    WITH (copy_data = false);

Be careful: this assumes the subscriber data exactly matches the publisher at the slot's start position. Getting it wrong produces silent divergence.

Replica identity: the requirement people miss

To replicate an UPDATE or DELETE, PostgreSQL must identify the affected row on the subscriber. By default it uses the primary key. A table without one fails at the first update:

ERROR: cannot update table "widgets" because it does not have a replica identity
       and publishes updates

Fix it by adding a primary key, or by nominating a unique index:

-- Preferred
ALTER TABLE widgets ADD PRIMARY KEY (id);
 
-- Or point at an existing unique, NOT NULL index
CREATE UNIQUE INDEX widgets_uid ON widgets (uid);
ALTER TABLE widgets REPLICA IDENTITY USING INDEX widgets_uid;
 
-- Last resort: log every column in the WAL. Correct, but expensive.
ALTER TABLE widgets REPLICA IDENTITY FULL;

REPLICA IDENTITY FULL makes the subscriber match rows by comparing every column, which means a sequential scan per changed row if the subscriber lacks a suitable index. Use it only for small tables.

Monitoring lag

On the publisher, pg_stat_replication shows how far behind each subscriber is:

SELECT application_name,
       state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn))   AS pending_send,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn))             AS pending_replay,
       write_lag, flush_lag, replay_lag
FROM   pg_stat_replication;

On the subscriber, pg_stat_subscription shows worker status:

SELECT subname, pid, received_lsn, latest_end_lsn, latest_end_time
FROM   pg_stat_subscription;

The replication slot trap

This is the failure that takes production down. A replication slot guarantees the publisher retains WAL until the subscriber has consumed it. If a subscriber goes offline and stays offline, the publisher retains WAL forever — until the disk fills and the database refuses to write.

Check slot lag regularly:

SELECT slot_name,
       active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM   pg_replication_slots
ORDER  BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Two defences. First, alert on retained_wal crossing a threshold well below your free disk space. Second, cap retention so PostgreSQL invalidates a hopeless slot rather than dying:

-- PostgreSQL 13+
ALTER SYSTEM SET max_slot_wal_keep_size = '100GB';
SELECT pg_reload_conf();

An invalidated slot means that subscriber must be rebuilt — but the publisher stays up, which is the right trade.

Drop slots you no longer need:

SELECT pg_drop_replication_slot('app_sub');

Note that inactive slots also block VACUUM from removing dead tuples across the whole database, so a forgotten slot causes bloat long before it causes a disk-full incident.

Handling conflicts

If a row already exists on the subscriber, an incoming INSERT raises a unique violation and the apply worker stops — retrying the same change forever until you intervene. The error appears in the subscriber log:

ERROR: duplicate key value violates unique constraint "orders_pkey"
CONTEXT: processing remote data for replication origin "pg_16401" during "INSERT"
         for replication target relation "public.orders" in transaction 743

Resolve it either by fixing the data on the subscriber, or by skipping the offending transaction:

-- PostgreSQL 15+: skip a specific transaction reported in the log
ALTER SUBSCRIPTION app_sub SKIP (lsn = '0/1A2B3C4');
 
-- Older versions: advance the origin past it
SELECT pg_replication_origin_advance('pg_16401', '0/1A2B3C5');

Skipping loses that change permanently, so confirm what it contained first.

DDL is not replicated

Adding a column on the publisher does not add it on the subscriber. The safe order of operations is:

1. Add the nullable column on the subscriber first.
2. Add it on the publisher.
3. Backfill.

Dropping a column reverses the order — publisher first, then subscriber. Getting this backwards breaks the apply worker with a column-mismatch error. After changing a publication's table list, tell the subscriber to notice:

ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION;

Using logical replication for a major version upgrade

This is the highest-value use case. Rather than a hours-long pg_upgrade outage, you can cut over in seconds:

1. Build a new cluster on the target major version.
2. Load the schema with pg_dump --schema-only.
3. Create a publication on the old cluster and a subscription on the new one.
4. Wait for the initial copy and for replication lag to reach near zero.
5. Stop application writes, confirm lag is zero, verify row counts.
6. Reset sequences on the new cluster — logical replication does not replicate sequence values.
7. Point the application at the new cluster.

Step 6 is the one most commonly forgotten, and it manifests as duplicate key errors immediately after cutover. Generate the resets from the old cluster:

SELECT format('SELECT setval(%L, %s);', seq, last_value)
FROM (
  SELECT c.oid::regclass::text AS seq,
         (SELECT last_value FROM pg_sequences s
          WHERE s.schemaname = n.nspname AND s.sequencename = c.relname)
  FROM   pg_class c
  JOIN   pg_namespace n ON n.oid = c.relnamespace
  WHERE  c.relkind = 'S' AND n.nspname = 'public'
) t;

Run the generated statements on the new cluster before opening it to traffic.

Wrapping up

Logical replication is the tool for selective, cross-version, writable replication — and the mechanism behind the least painful major-version upgrades available. The three things that will bite you are the missing replica identity on tables without primary keys, unreplicated DDL and sequences, and replication slots quietly retaining WAL until the disk fills. Monitor slot lag from day one and the rest is routine.

When you are validating a cutover, Chat2DB (opens in a new tab) makes it straightforward to connect to publisher and subscriber side by side and compare row counts, schemas and sample data before you switch traffic over.