PostgreSQL logical replication streams row changes (inserts, updates, deletes, truncates) from a publication on one server to a subscription on another, table by table. Unlike physical streaming replication, the two sides can run different major versions and different schemas, which is exactly why it is the standard tool for near-zero-downtime upgrades and migrations.
When to use it, and when not to
Use logical replication to move to a new major version or new hardware with a short cutover, to replicate a subset of tables into a reporting database, or to consolidate several databases. Do not use it as your high-availability replica: physical replication copies everything, including DDL and sequences, and is simpler to fail over. We cover the cutover side in zero-downtime PostgreSQL migrations; this article is about getting the replication itself right.
Step 1: the publisher
# publisher postgresql.conf (restart required)
wal_level = logical
-- publisher: a role for the subscriber and a publication
CREATE ROLE repl_sub WITH LOGIN REPLICATION PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_sub;
CREATE PUBLICATION app_pub FOR TABLE orders, customers, order_items;
-- or: FOR TABLES IN SCHEMA public / FOR ALL TABLES
-- tables without a primary key cannot replicate UPDATE/DELETE
SELECT c.relname
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_index i WHERE i.indrelid = c.oid AND i.indisprimary);
wal_levelmust belogical, and it can only be set at server start, so plan the restart.max_replication_slotsmust cover every subscription plus some reserve for table synchronization, andmax_wal_sendersshould be at least that plus your physical replicas (configuration settings).- By default a publication replicates all four operation types.
publishcan limit that, but only for ongoing changes, not the initial copy.
REPLICA IDENTITY FULL is the documented fallback, but without a suitable index on the subscriber each change can mean a very inefficient search.Step 2: the subscriber
# copy the schema first: DDL is not replicated
pg_dump --schema-only -h old-db -d app | psql -h new-db -d app
-- subscriber
CREATE SUBSCRIPTION app_sub
CONNECTION 'host=old-db dbname=app user=repl_sub password=...'
PUBLICATION app_pub;
-- defaults: copy_data = true, create_slot = true, enabled = true
- The schema must exist first. DDL is not replicated, here or later.
CREATE SUBSCRIPTIONcreates a replication slot on the publisher by default, and in that case it cannot run inside a transaction block (CREATE SUBSCRIPTION).copy_data = truecopies existing rows before streaming changes. Usefalseonly if the data is already there and consistent.- On PostgreSQL 18 the default for
streamingchanged fromofftoparallel, so large in-progress transactions can be applied by parallel workers (PostgreSQL 18 release notes). - The subscriber needs enough worker capacity:
max_logical_replication_workers(default 4) covers apply, parallel apply and table sync workers. On PostgreSQL 18 the subscription limit ismax_active_replication_origins; earlier versions usedmax_replication_slotsfor this.
What logical replication does not copy
These are listed in the restrictions page, and each one has caused a failed cutover somewhere:
- DDL. A column added on the publisher but not on the subscriber stops replication with errors until you fix the schema. Apply additive changes to the subscriber first.
- Sequences. Rows arrive with their IDs, but the subscriber's sequences still sit at their start values. Before switching writes, move them forward:
-- before cutover: move every sequence past the replicated data SELECT setval('orders_id_seq', (SELECT max(id) FROM orders) + 1000); - Large objects. Not replicated, and there is no workaround except storing the data in normal tables.
- Views, materialized views, foreign tables. Only tables, including partitioned tables, can be published.
- TRUNCATE across foreign keys. It works if all affected tables are in the same subscription, and fails on the subscriber if some of them are not.
Conflicts and how to get unstuck
The subscriber applies changes like normal DML. If a change violates a unique constraint, for example because someone wrote to the subscriber directly, the apply worker stops with an error such as conflict=insert_exists in the subscriber log, and replication waits until you resolve it (conflicts). A missing row for an UPDATE or DELETE does not stop replication: the change is skipped and, on PostgreSQL 18, logged and counted in pg_stat_subscription_stats.
The clean fix is to correct the data on the subscriber. The blunt fix is to skip the failing transaction:
-- the finish LSN comes from the conflict message in the subscriber log
ALTER SUBSCRIPTION app_sub SKIP (lsn = '0/14C0378');
Skipping drops the entire transaction, including changes that had nothing to do with the conflict, so the subscriber may no longer match the publisher. With streaming = parallel the finish LSN may not appear in the log; the docs suggest switching streaming to on or off and reproducing the error to get it.
Monitoring: the slot is what hurts you
-- publisher: how much WAL each slot is holding back
SELECT slot_name, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS retained
FROM pg_replication_slots
WHERE slot_type = 'logical';
-- subscriber: workers, initial sync state, errors and conflicts
SELECT * FROM pg_stat_subscription;
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel WHERE srsubstate <> 'r';
SELECT subname, apply_error_count, sync_error_count FROM pg_stat_subscription_stats;
A logical slot keeps every WAL segment the subscriber has not confirmed. If the subscriber is down, disabled or stuck on a conflict, WAL piles up on the publisher. max_slot_wal_keep_size defaults to -1, meaning no limit (replication settings). PostgreSQL 18 adds idle_replication_slot_timeout to invalidate slots that stay inactive, but it is off (0) by default. Alert on retained WAL per slot, not just on subscriber lag; our PostgreSQL replication lag guide covers the lag side.
When you are done, DROP SUBSCRIPTION also drops the slot on the publisher. If the publisher is unreachable the command fails; you then disable the subscription, set slot_name = NONE, drop it, and remove the orphaned slot on the publisher by hand (DROP SUBSCRIPTION). Forgotten slots from abandoned tests are a classic cause of full disks.
Logical replication on large databases
On a 10 GB test database the initial copy takes seconds and none of the following matters. On a multi-terabyte production database it is the whole project:
- The initial copy takes hours or days. Each table is copied by a table sync worker, and the publisher keeps the WAL generated meanwhile. On a write-heavy system that can be hundreds of gigabytes. Size the publisher's disk for the whole copy window, and add tables in batches with
ALTER PUBLICATION ... ADD TABLEandALTER SUBSCRIPTION ... REFRESH PUBLICATIONrather than all at once. - Skip the copy where you can. PostgreSQL 17 added
pg_createsubscriber, which turns a physical standby into a logical subscriber, so the terabytes do not have to be copied row by row (PostgreSQL 17 release notes). - Load on the publisher. In our experience the initial copy of a big table is a long sequential read plus a long-running transaction on the publisher, which also holds back vacuum. Schedule it off-peak and watch bloat on hot tables.
- Indexes on the subscriber. A common approach is to create secondary indexes after the initial copy to speed it up, but the replica identity index must exist before streaming starts, or each UPDATE and DELETE turns into an expensive search.
- Big transactions. A single batch job touching 50 million rows becomes one huge transaction to decode and apply. Break such jobs into chunks during the migration window.
- Rehearse the cutover. Stop writes, wait for retained WAL to reach zero, sync sequences, switch the application, and time all of it on a production-sized copy first.
Key takeaways
- Set
wal_level = logical(restart), create a publication, copy the schema, then create the subscription. - Every published table needs a primary key or a suitable replica identity, or UPDATE and DELETE fail on the publisher.
- DDL, sequences and large objects are not replicated; sync sequences before cutover.
- Conflicts stop replication; fix the data or use
ALTER SUBSCRIPTION ... SKIPwith care. - Monitor WAL retained by each logical slot and drop slots you no longer use.
- On large databases, plan disk for the initial copy window, add tables in batches, and consider
pg_createsubscriber.