Setting Up Logical Replication in PostgreSQL: A Step-by-Step Guide

Logical replication lets one PostgreSQL database stream row changes of selected tables to another, live, while both stay fully usable. It is the tool behind near-zero-downtime major version upgrades, moving a database to new hardware or the cloud, and feeding a reporting server with just the tables it needs.

It is also easy to set up almost right. This guide covers the full setup on PostgreSQL 16, plus the parts most tutorials skip: replica identity, the correct pg_hba.conf entry, sequences, DDL changes, conflicts and slot safety.

How a change travels from the publisher to the subscriber.

Physical vs logical replication

	Physical (streaming) replication	Logical replication
What is copied	The whole cluster, block by block	Row changes of selected tables
Standby is	Read-only, an identical copy	A normal read/write database
Major versions	Must be the same	Can differ, e.g. 13 → 16 (the basis of near-zero-downtime upgrades)
Granularity	All databases	Per table, per schema (15+), even per row and column (15+)
Replicates DDL / sequences	Yes (everything)	No: you handle these yourself
Typical use	HA, disaster recovery, read replicas	Upgrades, migrations, consolidation, reporting feeds

Rule of thumb: physical replication protects a database; logical replication moves data between databases.

Lab setup

	Publisher	Subscriber
Host	pg-pub · 192.168.56.10	pg-sub · 192.168.56.20
PostgreSQL	16	16 (could be newer)
Database	company	company
Replicate	public.employees, public.departments	same tables

Every code block below is tagged PUBLISHER or SUBSCRIBER so you always know where to run it.

01Configure the publisher

Logical decoding needs extra information in the WAL, so set wal_level to logical. This one setting needs a restart. The other two already default to 10, which is enough for most setups, so don't lower them.

PUBLISHER

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;     -- one per subscription (+ spare)
ALTER SYSTEM SET max_wal_senders = 10;           -- one per subscription (+ physical standbys)

-- Protect the disk: cap how much WAL a stuck slot may hold (PG 13+)
ALTER SYSTEM SET max_slot_wal_keep_size = '50GB';

PUBLISHER

sudo systemctl restart postgresql-16
psql -c "SHOW wal_level;"          # must return: logical

02Create the replication user and allow it in

PUBLISHER

CREATE ROLE repl_user WITH LOGIN REPLICATION PASSWORD 'Str0ng#Repl2026';

-- The initial table copy runs SELECT as this user
GRANT CONNECT ON DATABASE company TO repl_user;
GRANT USAGE ON SCHEMA public TO repl_user;
GRANT SELECT ON public.employees, public.departments TO repl_user;

Now allow the subscriber host in pg_hba.conf. Logical replication connects to a specific database, so the database column must be the database name (company). The replication keyword only matches physical replication connections.

PUBLISHER

# TYPE  DATABASE  USER       ADDRESS            METHOD
host    company   repl_user  192.168.56.20/32   scram-sha-256

PUBLISHER

SELECT pg_reload_conf();           -- pg_hba.conf only needs a reload, not a restart

SUBSCRIBER

psql "host=192.168.56.10 dbname=company user=repl_user" -c "SELECT 1;"

Test first: if this connection fails, the subscription will fail too. Fix the network, firewall (port 5432), listen_addresses and pg_hba before going on.

03Check replica identity

To replicate UPDATE and DELETE, the subscriber must be able to find the row. It uses the table's replica identity, which by default is the primary key. A published table without one will reject UPDATE and DELETE on the publisher with an error.

PUBLISHER

-- Tables in the schema without a primary key
SELECT c.oid::regclass AS table_name
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'r' AND n.nspname = 'public'
AND    NOT EXISTS (SELECT 1 FROM pg_constraint
                   WHERE conrelid = c.oid AND contype = 'p');

Fix them in order of preference:

PUBLISHER

ALTER TABLE public.audit_log ADD PRIMARY KEY (id);                       -- best
ALTER TABLE public.audit_log REPLICA IDENTITY USING INDEX audit_log_uk;   -- a unique, NOT NULL index
ALTER TABLE public.audit_log REPLICA IDENTITY FULL;                       -- last resort: logs whole rows, slower

04Create the publication

PUBLISHER

CREATE PUBLICATION app_pub FOR TABLE public.employees, public.departments;

-- Other options
-- CREATE PUBLICATION all_pub FOR ALL TABLES;                    -- superuser only
-- CREATE PUBLICATION hr_pub  FOR TABLES IN SCHEMA hr;           -- PG 15+
-- CREATE PUBLICATION ins_pub FOR TABLE orders WITH (publish = 'insert');

SELECT * FROM pg_publication_tables WHERE pubname = 'app_pub';

PostgreSQL 15 and later can also filter rows and columns, which is handy for regional feeds or for keeping sensitive columns off a reporting server:

PUBLISHER

CREATE PUBLICATION india_pub FOR TABLE public.employees (id, name, dept_id, country)
       WHERE (country = 'IN');

05Copy the schema to the subscriber

Logical replication copies data, not structure. Create the tables on the subscriber first. Instead of retyping each CREATE TABLE, take the DDL straight from the publisher so nothing is missed:

SUBSCRIBER

pg_dump -h 192.168.56.10 -U postgres -d company --schema-only \
        -t public.employees -t public.departments  > company_schema.sql

createdb company
psql -d company -f company_schema.sql

If the subscriber cannot log in to the publisher as postgres, run pg_dump on the publisher instead and copy the .sql file across.

Column names must match; the subscriber may have extra columns (with defaults), but not fewer. Data types should match too.

06Create the subscription

Run this as a superuser on the subscriber. It connects to the publisher, creates a replication slot there, copies the existing rows (copy_data = true is the default), then streams every new change.

SUBSCRIBER

CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=192.168.56.10 port=5432 dbname=company user=repl_user password=Str0ng#Repl2026'
  PUBLICATION app_pub
  WITH (copy_data = true,
        disable_on_error = true,     -- PG 15+: stop cleanly instead of retrying in a loop
        streaming = parallel);        -- PG 16+: apply large transactions in parallel (use 'on' for 14-15)

About the password: it is stored in pg_subscription, which only superusers can read. Alternatively, leave it out of the connection string and put it in the ~/.pgpass file of the postgres OS user on the subscriber.

Check that the initial copy finished. Every table must reach state r (ready):

SUBSCRIBER

SELECT srrelid::regclass AS table_name,
       CASE srsubstate WHEN 'i' THEN 'initializing' WHEN 'd' THEN 'copying data'
                       WHEN 'f' THEN 'copy finished' WHEN 's' THEN 'synchronized'
                       WHEN 'r' THEN 'ready' END AS state
FROM   pg_subscription_rel;

07Test it

PUBLISHER

INSERT INTO employees (name, salary, dept_id) VALUES ('Asha Rao', 72000, 10);
UPDATE employees SET salary = salary * 1.05 WHERE name = 'Asha Rao';
DELETE FROM employees WHERE name = 'Old Record';

SUBSCRIBER

SELECT id, name, salary FROM employees WHERE name IN ('Asha Rao','Old Record');

You should see Asha Rao with the updated salary, and no "Old Record" row.

08Monitor replication

On the publisher: is the slot active, and how far behind is it?

PUBLISHER

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag_size,
       wal_status                     -- reserved / extended / unreserved / lost
FROM   pg_replication_slots
WHERE  slot_type = 'logical';

SELECT application_name, state, sent_lsn, replay_lsn, replay_lag
FROM   pg_stat_replication;           -- application_name = subscription name

On the subscriber: is the apply worker running, and are there errors?

SUBSCRIBER

SELECT subname, pid, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM   pg_stat_subscription;          -- pid IS NULL means the worker is not running

SELECT subname, apply_error_count, sync_error_count
FROM   pg_stat_subscription_stats;    -- PG 15+

SELECT subname, subenabled FROM pg_subscription;

Watch inactive slots. A slot with active = false still keeps every WAL file the subscriber has not confirmed. Left alone, it fills the publisher's disk. Alert on lag_size, and drop slots that are no longer needed.

09Handle a conflict

The most common breakage: someone inserted a row directly on the subscriber, and the same key then arrives from the publisher. The apply worker hits a duplicate key error and stops (or retries forever on PG 14 and older). The subscriber log shows something like:

ERROR:  duplicate key value violates unique constraint "employees_pkey"
DETAIL: Key (id)=(105) already exists.
CONTEXT: processing remote data for replication origin "pg_16402" during message type "INSERT"
         for replication target relation "public.employees" in transaction 7841, finished at 0/3A1C2F8

You have two ways out:

SUBSCRIBER

-- Option A (usually right): remove the conflicting local row, let replication continue
DELETE FROM employees WHERE id = 105;
ALTER SUBSCRIPTION app_sub ENABLE;        -- if disable_on_error stopped it

-- Option B (PG 15+): skip the whole remote transaction (its changes are lost on the subscriber)
ALTER SUBSCRIPTION app_sub SKIP (lsn = '0/3A1C2F8');
ALTER SUBSCRIPTION app_sub ENABLE;

Prevention beats repair: treat replicated tables on the subscriber as read-only for applications, for example by granting only SELECT to app users.

10Make changes later

Add a table

BOTH

-- 1. Create the table on the subscriber first (pg_dump --schema-only -t ...)
-- 2. Add it to the publication

PUBLISHER

ALTER PUBLICATION app_pub ADD TABLE public.projects;

SUBSCRIBER

ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION;      -- starts the initial copy of the new table

Schema changes (DDL)

DDL is not replicated. Apply it to both sides in the safe order:

Change	Order
Add a column	Subscriber first, then publisher
Drop a column	Publisher first, then subscriber
Change a column type	Plan carefully; usually pause writes, change both, resume

11Use it for an upgrade or migration

Because the subscriber can run a newer major version, logical replication gives you an upgrade with only a few minutes of downtime: build a new PostgreSQL 16 server, subscribe to the old one, then switch over.

1. Let the subscriber catch up: the lag on the publisher should be near 0.
2. Stop application writes to the publisher (maintenance window starts).
3. Wait until confirmed_flush_lsn equals pg_current_wal_lsn().
4. Sync sequences. Their values are not replicated, so generate setval calls on the publisher and run them on the subscriber:
   PUBLISHER

   SELECT format('SELECT setval(%L, %s, true);',
                 schemaname || '.' || sequencename, last_value)
   FROM   pg_sequences
   WHERE  last_value IS NOT NULL;

5. Point the application at the new server and open it for writes.
6. Drop the subscription (this also removes the slot on the old server).

12Clean up

SUBSCRIBER

DROP SUBSCRIPTION app_sub;     -- also drops the replication slot on the publisher

If the publisher is already gone, DROP SUBSCRIPTION cannot reach it and fails. Detach the slot first:

SUBSCRIBER

ALTER SUBSCRIPTION app_sub DISABLE;
ALTER SUBSCRIPTION app_sub SET (slot_name = NONE);
DROP SUBSCRIPTION app_sub;

PUBLISHER

-- and if the publisher still exists, remove the orphaned slot there
SELECT pg_drop_replication_slot('app_sub');
DROP PUBLICATION app_pub;

Limitations to know

Not replicated	What to do
DDL (CREATE / ALTER / DROP)	Apply on both sides, in the order shown above
Sequence values	Sync with setval at cutover
Large objects (pg_largeobject)	Use bytea columns, or copy separately
Views, materialized views	Create on the subscriber; refresh MVs there
Users, roles, grants	pg_dumpall --globals-only

Troubleshooting

Symptom	Cause	Fix
no pg_hba.conf entry for replication connection	pg_hba uses the replication keyword, or a wrong address	Use the database name and the subscriber's /32 address; reload
logical decoding requires wal_level >= logical	wal_level changed but not restarted	Restart the publisher
cannot update table … because it does not have a replica identity (on the publisher)	Published table has no primary key	Add a PK or set REPLICA IDENTITY
Table stuck in state d (copying)	Large table, or a lock or permission problem	Check pg_stat_progress_copy and the subscriber log; GRANT SELECT to repl_user
relation … does not exist on the subscriber	Table not created on the subscriber	Create it from pg_dump --schema-only; REFRESH PUBLICATION
Growing lag	Slow apply on the subscriber, or very large transactions	Check subscriber I/O and indexes; use streaming = parallel (16+); split into several subscriptions
Publisher disk filling up	Inactive slot holding WAL	Fix or drop the subscription; set max_slot_wal_keep_size

About max_wal_senders: raising it does not reduce lag. It only limits how many replication connections may exist. Lag comes from how fast the subscriber can apply changes.

Feature availability by version

Feature	Since
Logical replication (publications / subscriptions)	10
TRUNCATE replicated	11
max_slot_wal_keep_size	13
streaming = on (large in-progress transactions)	14
Row filters, column lists, FOR TABLES IN SCHEMA, SKIP, disable_on_error, pg_stat_subscription_stats	15
streaming = parallel, origin = none (bidirectional), logical decoding on standbys	16
pg_createsubscriber, pg_upgrade keeps logical slots and subscriptions	17

Checklist

☐ wal_level = logical on the publisher (restart done)

☐ max_slot_wal_keep_size set to protect the disk

☐ repl_user with LOGIN, REPLICATION and SELECT on the tables

☐ pg_hba.conf entry with the database name, reloaded

☐ Every published table has a primary key or replica identity

☐ Schema created on the subscriber from pg_dump --schema-only

☐ All tables in state r in pg_subscription_rel

☐ Lag and inactive slots monitored

☐ Sequences synced before any cutover

Conclusion

Logical replication is simple at its core: a publication, a subscription, and a slot in between. Most production problems come from what it does not do: no DDL, no sequences, no conflict resolution. And an unattended slot can quietly fill a disk. Plan for those four, and it becomes one of the most useful tools a PostgreSQL DBA has.

Have you used logical replication for an upgrade or migration? Share your experience in the comments.

Tamilselvan Perumal · Lead Product Engineer & Senior DBA