PostgreSQL Logical Replication: Setup, Use Cases, and Limitations

Running a zero-downtime major PostgreSQL upgrade sounds impossible until you understand logical replication. Unlike physical replication, which ships raw byte changes, logical replication lets you selectively sync individual tables, filter rows, and even replicate across major versions — capabilities that unlock a class of operational patterns physical standby servers simply cannot deliver. Production teams at scale lean on this feature for analytics offload, multi-tenant data isolation, and live migrations between PostgreSQL 14 and 16 without a maintenance window. If you manage PostgreSQL in production and haven't wired up logical replication yet, this guide walks you through every step.

What Is PostgreSQL Logical Replication?

Logical replication is a publish/subscribe mechanism built into PostgreSQL (introduced in version 10) that decodes WAL entries into a stream of logical row changes — inserts, updates, and deletes — and applies them to one or more subscriber databases. The "logical" label distinguishes it from physical replication, which copies raw 8 KB data pages byte-for-byte.

Under the hood, the publisher runs a walsender process that reads WAL through the logical decoding layer and sends a change stream to the subscriber. The subscriber runs an apply worker that receives those changes and replays them using ordinary SQL. Because the changes are decoded into a portable, version-independent format, a PostgreSQL 14 publisher can replicate into a PostgreSQL 16 subscriber — a property that physical replication never had.

The feature set breaks down into three components:

• Publication — defined on the publisher, describes which tables (and in PG 15+, which rows/columns) to include.
• Subscription — defined on the subscriber, points to the publisher connection string and specifies which publication to consume.
• Replication slot — created automatically by the subscription; retains WAL on the publisher until the subscriber has consumed it.

Physical vs. Logical Replication: Key Differences

Before choosing logical replication, it helps to understand precisely where it diverges from streaming (physical) replication.

Physical replication remains the right choice for high-availability standby servers where you want a complete, byte-identical copy of the entire cluster. Logical replication shines when you need selectivity, cross-version compatibility, or a writable target.

Setting Up Logical Replication Step by Step

1. Configure the Publisher

Logical replication requires WAL to be written at the logical level. Edit postgresql.conf on the publisher:

# postgresql.conf on the publisher
wal_level = logical
max_replication_slots = 10   # one slot per subscription (minimum)
max_wal_senders = 10         # one walsender per subscriber connection

Restart PostgreSQL after changing wal_level — this is the only configuration change that requires a restart.

Grant the replication role to the user that will be used by the subscription:

-- On the publisher
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
-- Also grant SELECT on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO replicator;

Allow the subscriber to connect by adding an entry to pg_hba.conf:

# pg_hba.conf on the publisher
host  replication  replicator  subscriber_ip/32  scram-sha-256

2. Create the Publication

A publication defines what the publisher exposes. You can publish all tables or a specific list:

-- Publish every table in the database
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- Publish specific tables only
CREATE PUBLICATION pub_orders
  FOR TABLE orders, order_items, customers;

In PostgreSQL 15 and later you can add row filters and column lists directly:

-- PostgreSQL 15+: row filter and column list
CREATE PUBLICATION pub_active_orders
  FOR TABLE orders (id, customer_id, status, total_amount)
  WHERE (status != 'archived');

Inspect existing publications with the system catalog:

SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete
FROM pg_publication;

-- See which tables are included
SELECT * FROM pg_publication_tables WHERE pubname = 'pub_orders';

3. Create the Subscription

On the subscriber database, create the subscription pointing at the publisher:

-- On the subscriber
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=publisher_host port=5432 dbname=mydb user=replicator password=strong_password'
  PUBLICATION pub_orders;

When you run CREATE SUBSCRIPTION, PostgreSQL automatically:

1. Creates a replication slot on the publisher.
2. Copies the initial snapshot of all published tables (initial data copy).
3. Starts the apply worker to stream subsequent changes.

CREATE SUBSCRIPTION sub_orders
  CONNECTION '...'
  PUBLICATION pub_orders
  WITH (copy_data = false);

The target tables must exist on the subscriber before the subscription is created. Schema is not replicated — create tables manually or via a dump of the schema only:

-- Extract schema only from publisher
pg_dump --schema-only -t orders -t order_items -t customers \
  -d mydb -h publisher_host | psql -d mydb -h subscriber_host

4. Verify the Subscription

On the subscriber, check subscription state:

SELECT subname, subenabled, subslotname, substatus
FROM pg_subscription;

-- More detailed per-relation state
SELECT * FROM pg_subscription_rel;
-- srsubstate values: 'i' = initializing, 'd' = data copy, 's' = synchronized, 'r' = ready

Monitoring Replication Lag

Replication lag is the most important operational metric to track once logical replication is running.

-- On the subscriber: check lag per subscription
SELECT
  subname,
  received_lsn,
  latest_end_lsn,
  extract(epoch FROM now() - latest_end_time) AS lag_seconds
FROM pg_stat_subscription;

-- On the publisher: check replication slot lag (WAL retained)
SELECT
  slot_name,
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
  ) AS retained_wal,
  active
FROM pg_replication_slots
WHERE slot_type = 'logical';

Use Cases

Selective Table Synchronization

Physical replication copies the entire cluster. Logical replication lets you sync only the tables that matter to a given consumer. A common pattern is maintaining a dedicated reporting database that receives only the tables the analytics team queries, keeping sensitive PII tables entirely off the analytics host.

CREATE PUBLICATION pub_analytics
  FOR TABLE events, sessions, page_views, products
  -- exclude users, payments, orders with PII columns
;

Zero-Downtime Major Version Upgrades

This is the flagship use case. The workflow is:

1. Stand up a new PostgreSQL 16 instance alongside the existing PostgreSQL 14 primary.
2. Dump the schema from PG 14, apply it to PG 16.
3. Create a publication on PG 14 and a subscription on PG 16.
4. Wait for initial data copy to complete and lag to reach zero.
5. Put the application in a brief read-only mode (seconds, not minutes), verify lag is zero, update the application connection string, then re-enable writes on PG 16.

-- On PG 14 publisher
SELECT sequencename, last_value FROM pg_sequences WHERE schemaname = 'public';

-- On PG 16 subscriber, for each sequence:
SELECT setval('orders_id_seq', 1500000);  -- set above current max

Analytics Offload

Streaming OLTP changes into a dedicated read-only analytics replica over logical replication avoids the contention of running heavy analytical queries on the primary. Unlike physical standbys, the analytics subscriber can have its own indexes, materialized views, and even additional columns populated by triggers — giving the analytics team the freedom to optimize for their query patterns independently.

Multi-Tenant Data Isolation

In multi-tenant SaaS architectures, logical replication with row filtering (PostgreSQL 15+) enables per-tenant database shards. A central database publishes all tenants' data; tenant-specific subscriber databases subscribe with a row filter on the tenant_id column, receiving only their own data.

-- PostgreSQL 15+: per-tenant publication with row filter
CREATE PUBLICATION pub_tenant_42
  FOR TABLE orders, invoices, users
  WHERE (tenant_id = 42);

Limitations to Know Before You Ship

DDL Is Not Replicated

This is the most common production gotcha. When you run ALTER TABLE orders ADD COLUMN notes TEXT on the publisher, that change does not flow to the subscriber. You must apply DDL changes to the subscriber manually, in the correct order, before or after applying them to the publisher depending on the migration direction.

Sequences Are Not Replicated

Auto-increment sequence values advance on the publisher but are not streamed to the subscriber. If you promote the subscriber to a writer (as in an upgrade cutover), you must manually set all sequences to a safe value above the current maximum.

Large Objects Are Not Replicated

PostgreSQL large objects (lo type, lo_create) are not included in logical replication. If your schema uses large objects, you need a separate strategy to migrate or sync that data.

Tables Require a Replica Identity

For UPDATE and DELETE to replicate correctly, each published table needs a replica identity — by default this is the primary key. Tables without a primary key must be explicitly configured:

-- Use the full row as identity (less efficient but works without a PK)
ALTER TABLE legacy_table REPLICA IDENTITY FULL;

-- Or use a unique index
ALTER TABLE legacy_table REPLICA IDENTITY USING INDEX legacy_table_unique_idx;

Only DML Is Replicated

Logical replication replicates INSERT, UPDATE, DELETE, and TRUNCATE. It does not replicate grants, role changes, or other catalog operations.

Manage Logical Replication Without the Operational Overhead

Setting up logical replication correctly is only half the work. Keeping it healthy in production — watching for slot lag, coordinating DDL migrations across publisher and subscriber, handling sequence resets at cutover, and alerting when a subscriber falls behind — adds a persistent operational burden to your DBA workload.

JusDB gives you fully managed PostgreSQL with built-in tooling for logical replication workflows, including zero-downtime upgrade orchestration, automated lag monitoring, and runbook-driven DDL migration assistance. Whether you're setting up an analytics offload replica or planning a major version migration, JusDB handles the infrastructure so your team focuses on the data.

Talk to a JusDB engineer about your replication architecture, or start a free trial to see managed PostgreSQL logical replication in action.