PostgreSQL Logical Replication: Setup, Monitoring & Troubleshooting

2026-04-08

PostgreSQL Logical Replication: Setup, Monitoring & Troubleshooting

PostgreSQL logical replication decodes WAL changes into a logical format and streams them to subscribers, enabling selective table replication, cross-version upgrades, and multi-directional data flows between PostgreSQL instances without requiring identical physical copies.

The Problem

Streaming (physical) replication sends raw WAL bytes to replicas, creating byte-for-byte copies of the entire cluster. This works well for high availability, but falls apart when you need something more selective: replicating a subset of tables, replicating between different PostgreSQL major versions, or feeding changes into a data warehouse that runs a different schema.

Logical replication solves these use cases by decoding WAL into row-level change events (INSERT, UPDATE, DELETE) and applying them on the subscriber. You define a publication on the source with a set of tables, create a subscription on the target, and PostgreSQL handles the initial data copy and ongoing change streaming automatically.

The complexity emerges after setup. Logical replication does not replicate DDL changes -- an ALTER TABLE ADD COLUMN on the publisher is invisible to the subscriber. Schema changes require manual coordination, and getting the order wrong causes replication to break. Conflict resolution is minimal: if an INSERT on the subscriber violates a unique constraint because the row already exists, replication stops until you resolve it manually. Replication lag is harder to diagnose than with streaming replication because the metrics live in different system views. And certain operations -- sequences, TRUNCATE on older versions, large objects -- simply are not replicated, catching teams off guard.

On managed services like Aurora PostgreSQL, logical replication adds another layer of configuration. You need to set rds.logical_replication = 1 in the parameter group and reboot, configure wal_level = logical (which increases WAL volume), and deal with the fact that replication slots persist WAL segments even when the subscriber is down, potentially filling your storage.

How to Detect It

Start by verifying that logical replication is configured and checking the status of publications and subscriptions:

-- Check if wal_level supports logical replication
SHOW wal_level;
-- Must return 'logical'. If it returns 'replica', logical replication won't work.

-- List all publications on the publisher
SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete, pubtruncate
FROM pg_publication;

-- List published tables for each publication
SELECT pubname, schemaname, tablename
FROM pg_publication_tables
ORDER BY pubname, schemaname, tablename;

On the subscriber side, check subscription status and replication lag:

-- Check subscription status and lag on the subscriber
SELECT
    subname AS subscription_name,
    received_lsn,
    latest_end_lsn,
    latest_end_time,
    pg_size_pretty(
        pg_wal_lsn_diff(received_lsn, latest_end_lsn)
    ) AS apply_lag_bytes
FROM pg_stat_subscription
WHERE subname IS NOT NULL;

-- Check replication slot status on the publisher (critical for disk usage)
SELECT
    slot_name,
    plugin,
    slot_type,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
    ) AS slot_lag_bytes,
    wal_status
FROM pg_replication_slots
WHERE slot_type = 'logical';

The slot_lag_bytes column is the most important metric to watch. An inactive slot with growing lag means WAL segments are accumulating on the publisher and will eventually fill the disk. The wal_status column (PG13+) tells you whether the slot's WAL is still available (reserved), at risk (extended), or already lost (lost).

Check for replication conflicts that have stalled the subscription:

-- On the subscriber: check for apply errors
SELECT
    subname,
    worker_type,
    relname,
    last_error_message,
    last_error_time
FROM pg_stat_subscription_stats
WHERE last_error_message IS NOT NULL;

How myDBA.dev Shows It

myDBA.dev's Replication Topology page provides a visual map of your replication architecture, showing publishers, subscribers, and the connections between them. Each node displays its role (primary, replica, logical subscriber) with real-time status indicators. You can immediately see which subscriptions are active, which are lagging, and which have stopped entirely -- without connecting to each instance individually and running separate queries.

Replication topology showing publisher and subscriber nodes with connection status and lag indicators

The replication lag chart tracks lag over time, making it possible to distinguish between a momentary spike during a bulk operation and a sustained increase that indicates the subscriber cannot keep up. This historical view is critical for logical replication because lag behaves differently than streaming replication -- a single large transaction on the publisher can cause the subscriber to pause until the entire transaction is decoded and applied, creating a lag spike that resolves on its own.

Replication lag chart showing lag trends over time with spike detection

myDBA.dev also monitors replication slot WAL retention, alerting you before an inactive logical replication slot fills your disk. This is one of the most common operational issues with logical replication -- a subscriber goes offline for maintenance, the slot keeps retaining WAL, and hours later the publisher runs out of disk space.

How to Fix It

Step-by-step setup

On the publisher, set wal_level and create the publication:

-- On the publisher: set wal_level (requires restart)
ALTER SYSTEM SET wal_level = 'logical';
-- Restart PostgreSQL after this change

-- Create a publication for specific tables
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items, customers;

-- Or publish all tables in a schema (PG15+)
CREATE PUBLICATION analytics_pub FOR TABLES IN SCHEMA analytics;

On the subscriber, create the subscription:

-- On the subscriber: create the subscription
-- This automatically creates a replication slot on the publisher
-- and copies existing data before streaming changes
CREATE SUBSCRIPTION orders_sub
    CONNECTION 'host=publisher.example.com port=5432 dbname=myapp user=replicator password=secret'
    PUBLICATION orders_pub;

The subscriber will perform an initial table sync (copying all existing rows) and then switch to streaming mode for ongoing changes. Monitor the sync status:

-- Check initial sync progress on the subscriber
SELECT
    srsubid,
    srrelid::regclass AS table_name,
    CASE srsubstate
        WHEN 'i' THEN 'initializing'
        WHEN 'd' THEN 'data_copying'
        WHEN 's' THEN 'synchronized'
        WHEN 'r' THEN 'ready'
    END AS sync_state
FROM pg_subscription_rel;

Handling schema changes

DDL is not replicated. You must apply schema changes on both sides, in the right order:

-- Step 1: Apply on subscriber FIRST (it must accept the new column)
ALTER TABLE orders ADD COLUMN priority_level TEXT DEFAULT 'normal';

-- Step 2: Apply on publisher
ALTER TABLE orders ADD COLUMN priority_level TEXT DEFAULT 'normal';

-- Step 3: Refresh the subscription to pick up the schema change
ALTER SUBSCRIPTION orders_sub REFRESH PUBLICATION;

If you add the column on the publisher first, replication breaks because the subscriber receives rows with a column it does not know about.

Resolving conflicts

When a conflict occurs (typically a unique constraint violation), replication stops. Fix it by removing the conflicting row on the subscriber:

-- On the subscriber: skip the conflicting transaction
-- First, find the failing LSN from pg_stat_subscription
ALTER SUBSCRIPTION orders_sub DISABLE;

-- Remove or update the conflicting row
DELETE FROM orders WHERE order_id = 12345;

-- Re-enable the subscription
ALTER SUBSCRIPTION orders_sub ENABLE;

On PostgreSQL 15+, you can set the subscription to skip conflicts automatically:

ALTER SUBSCRIPTION orders_sub SET (disable_on_error = false);

Setting up REPLICA IDENTITY

For UPDATE and DELETE to work, the subscriber needs a way to identify which row to modify. By default, this uses the primary key. Tables without a primary key need an explicit replica identity:

-- If the table has no primary key, use FULL (sends all columns)
ALTER TABLE events REPLICA IDENTITY FULL;

-- Or use a unique index
CREATE UNIQUE INDEX idx_events_event_id ON events (event_id);
ALTER TABLE events REPLICA IDENTITY USING INDEX idx_events_event_id;

Without a replica identity, UPDATE and DELETE operations on the publisher will fail with an error.

Aurora PostgreSQL logical replication

For AWS Aurora PostgreSQL, enable logical replication through the parameter group:

-- In the RDS parameter group (requires reboot):
-- rds.logical_replication = 1
-- This automatically sets wal_level = logical

-- After reboot, create publication and subscription as normal
-- Aurora also requires the rds_replication role:
GRANT rds_replication TO replicator_user;

How to Prevent It

Monitor replication slot WAL retention

The single most dangerous failure mode is an inactive replication slot accumulating WAL. Set a maximum slot WAL size to prevent disk exhaustion:

-- PostgreSQL 13+: limit WAL retained per slot
ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();

When a slot exceeds this limit, PostgreSQL invalidates it rather than filling the disk. The subscriber will need to be re-synced, but the publisher stays online.

Plan for schema changes

Document a schema change procedure for logical replication environments. The key rule: always apply additive changes (ADD COLUMN, ADD TABLE) on the subscriber first, and destructive changes (DROP COLUMN, DROP TABLE) on the publisher first. Keep a checklist that includes ALTER SUBSCRIPTION ... REFRESH PUBLICATION after any table-level changes.

Set up conflict handling

For bidirectional or multi-publisher setups, design your schema to minimize conflicts. Use UUID primary keys generated independently on each node, and avoid patterns where the same row can be modified on multiple nodes. For unidirectional replication, ensure the subscriber tables are read-only to application connections -- use a separate role without INSERT/UPDATE/DELETE privileges.

Track replication lag continuously

Logical replication lag is normal during large transactions but should return to near-zero quickly. myDBA.dev tracks lag trends over time and alerts when lag exceeds configurable thresholds, distinguishing between transient spikes and sustained degradation. Monitor both the apply lag (how far behind the subscriber is in applying changes) and the slot lag (how much WAL the publisher is retaining), since they indicate different problems.

Know the limitations

Logical replication does not replicate: sequences (use pg_dump --data-only -t '*_seq' periodically), DDL changes, large objects, materialized view refreshes, or TRUNCATE on PostgreSQL versions before 11. Plan for these gaps in your architecture rather than discovering them in production.

FAQ

What are the limitations of PostgreSQL logical replication?

Logical replication does not replicate DDL (schema changes), sequences, large objects, or materialized views. TRUNCATE replication was added in PostgreSQL 11. Each table needs a replica identity (primary key or unique index) for UPDATE and DELETE operations. Large transactions are decoded in memory on the publisher, which can cause memory pressure. Replication slots retain WAL indefinitely when the subscriber is offline, risking disk exhaustion.

How do I set up logical replication step by step?

Set wal_level = logical and restart PostgreSQL. Create a publication on the source (CREATE PUBLICATION), then create a subscription on the target (CREATE SUBSCRIPTION) with the connection string to the publisher. PostgreSQL handles the initial data copy and ongoing streaming automatically. Ensure tables have primary keys for UPDATE/DELETE replication.

How does logical replication work with Aurora PostgreSQL?

Set rds.logical_replication = 1 in the parameter group and reboot the instance. Grant the rds_replication role to your replication user. Then create publications and subscriptions using standard PostgreSQL commands. Note that Aurora's storage model means replication slot WAL retention behaves differently -- monitor AuroraReplicaLag in CloudWatch alongside the PostgreSQL-level metrics.

How does logical replication handle schema changes?

It does not -- DDL is not replicated. You must apply schema changes manually on both publisher and subscriber. For additive changes (new columns, new tables), apply on the subscriber first so it can accept the new data format. Then apply on the publisher and run ALTER SUBSCRIPTION ... REFRESH PUBLICATION. Getting the order wrong breaks replication.

How do I resolve logical replication conflicts?

Disable the subscription, fix or remove the conflicting data on the subscriber, and re-enable the subscription. On PostgreSQL 15+, you can set disable_on_error = false to skip conflicts automatically. For production systems, design your schema to minimize conflicts: use UUID primary keys, keep subscriber tables read-only, and avoid patterns where the same row is modified on multiple nodes.