Architecture

PostgreSQL Logical Replication: Setup, Pitfalls and Monitoring

Logical replication is how you upgrade PostgreSQL with minutes of downtime, split a database or feed a reporting server. It is also how you fill a primary's disk with WAL. Here is a setup that works, the restrictions that bite, and what to watch on large databases.

· 13 min read · AG Data Team

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);
Replica identity is the first thing that breaks. UPDATE and DELETE need a way to find the row on the subscriber: by default the primary key, or a suitable unique index. A table with no usable identity in a publication that replicates updates makes those updates fail on the publisher, which means your application gets errors, not just the replica (publication docs). 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

What logical replication does not copy

These are listed in the restrictions page, and each one has caused a failed cutover somewhere:

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:

Key takeaways

PostgreSQL Logical replication Migrations Replication
Work with us

Planning a logical replication cutover?

We run PostgreSQL upgrades and migrations over logical replication on production databases of many terabytes, including the slot monitoring, sequence sync and cutover plan.

Get in touch More from the blog