PostgreSQL Logical Replication in Production: Selective Sync, Slot Safety, and Conflict Recovery

Context & Architecture

Six months ago, our 3.8 TB primary PostgreSQL database began struggling under heavy reporting queries. Our BI team needed continuous, low-latency updates from just four core tables: orders, order_items, payments, and inventory. Meanwhile, the remaining 170 tables in the schema generated intense write activity from session stores and ephemeral audit logs.

At the time, we relied on standard physical streaming replication. Physical replication mirrors the cluster byte-for-byte at the storage block level. Under our peak load of 12,000 writes per second, the primary churned through 400 GB of Write-Ahead Logs (WAL) every single day. The analytics replica spent nearly all its NVMe I/O replaying WAL blocks rather than executing analytical queries. We were burning 4 TB of expensive disk simply to query roughly 65 GB of business data.

Logical replication solves this problem by decoupling data movement from the underlying disk blocks. An engine component called pgoutput decodes WAL entries into individual row-level DML tuples (INSERT, UPDATE, DELETE). This allows you to publish specific tables, stream changes across different PostgreSQL major versions, and build tailored indexes on the replica without touching the primary database.

Migrating our analytics node to logical replication slashed replica disk write throughput from 420 MB/s to under 25 MB/s. It also reduced our replica storage footprint by 92%. But logical replication introduces its own operational challenges. If an unmonitored replica drops offline, its replication slot will hoard WAL files on the publisher until disk space hits 100%.

Prerequisites & Environment Setup

Logical replication uses native PostgreSQL functionality available since version 10, meaning no external daemons or third-party extensions are required. In this walk-through, both nodes run PostgreSQL 16 on Ubuntu 22.04 LTS. The publisher hosts our production database at 192.168.1.10, while the subscriber runs at 192.168.1.20.

Start by confirming that PostgreSQL is active on both hosts:

# Run on both Publisher (192.168.1.10) and Subscriber (192.168.1.20)
sudo apt update && sudo apt install -y postgresql-16 postgresql-contrib
sudo systemctl enable postgresql
sudo systemctl start postgresql

On the publisher, provision a dedicated user with minimal privileges. The account needs the REPLICATION attribute and read access to the published schema:

-- Execute on Publisher (app_prod database)
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'SecretClusterPass2026!';

GRANT CONNECT ON DATABASE app_prod TO replicator;
GRANT USAGE ON SCHEMA public TO replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replicator;

Next, whitelist the subscriber’s IP address in /etc/postgresql/16/main/pg_hba.conf on the publisher:

# Allow logical replication connection from Subscriber host
host    app_prod        replicator      192.168.1.20/32         scram-sha-256

Step-by-Step Configuration

Getting replication running requires configuring the publisher WAL decoder, declaring the publication, and initializing the subscription worker.

1. Publisher Engine Settings

Open /etc/postgresql/16/main/postgresql.conf on the publisher. Ensure the WAL level can support logical decoding and configure enough replication workers:

# /etc/postgresql/16/main/postgresql.conf on Publisher
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
listen_addresses = '*'

Because changing wal_level requires a full server restart, run:

sudo systemctl restart postgresql

2. Create the Publication

Logical replication streams data changes, not Data Definition Language (DDL). You must define table structures manually on both sides before starting replication. In this example, we replicate only orders and order_items:

-- On Publisher: Create sample business schema
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_status VARCHAR(32) NOT NULL,
    total_amount NUMERIC(12,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    item_id BIGSERIAL PRIMARY KEY,
    order_id BIGINT REFERENCES orders(order_id),
    sku VARCHAR(64) NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10,2) NOT NULL
);

-- Publish only the selected tables
CREATE PUBLICATION analytics_pub FOR TABLE orders, order_items;

3. Subscriber Configuration & Initial Sync

On the subscriber node, allocate background worker capacity in postgresql.conf:

# /etc/postgresql/16/main/postgresql.conf on Subscriber
max_logical_replication_workers = 8
max_sync_workers_per_subscription = 4

Reload configuration changes with sudo systemctl reload postgresql. Create the matching tables on the subscriber, omitting unneeded foreign keys or adding analytics-specific indexes as required. Then start the subscription:

-- On Subscriber: Define matching schemas
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_status VARCHAR(32) NOT NULL,
    total_amount NUMERIC(12,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    item_id BIGSERIAL PRIMARY KEY,
    order_id BIGINT REFERENCES orders(order_id),
    sku VARCHAR(64) NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10,2) NOT NULL
);

-- Create subscription and initiate snapshot sync
CREATE SUBSCRIPTION analytics_sub
    CONNECTION 'host=192.168.1.10 port=5432 dbname=app_prod user=replicator password=SecretClusterPass2026!'
    PUBLICATION analytics_pub
    WITH (copy_data = true, create_slot = true);

Setting copy_data = true causes Postgres to copy initial table snapshots before applying live WAL changes. For our 45-million-row orders table, this initial transfer took 14 minutes across a private 10 Gbps network link.

4. Resolving Replication Conflicts

Unlike physical replicas, subscriber databases are writable. This flexibility introduces risk. If a local script inserts a row with a primary key that collides with an incoming write from the publisher, replication stops immediately.

When this happens, the apply worker retries every 5 seconds. This retry loop floods Postgres system logs and drives up replication lag. Inspect /var/log/postgresql/postgresql-16-main.log on the subscriber to find the failing commit LSN (for example, 0/3142A90).

-- Option A (PostgreSQL 15+): Skip the colliding transaction cleanly
ALTER SUBSCRIPTION analytics_sub DISABLE;
ALTER SUBSCRIPTION analytics_sub SKIP (lsn = '0/3142A90');
ALTER SUBSCRIPTION analytics_sub ENABLE;

-- Option B: Manually resolve the record on the Subscriber
DELETE FROM orders WHERE order_id = 9042;
ALTER SUBSCRIPTION analytics_sub ENABLE;

Prevent conflicts before they happen. Restrict user privileges on the subscriber by granting only SELECT to reporting users. Reserve write permissions for the replication worker role.

Monitoring & Slot Management

An abandoned replication slot is an outage waiting to happen. The publisher guarantees data delivery by retaining all WAL files from the slot’s restart_lsn forward. If a subscriber crashes and stays offline, WAL files accumulate indefinitely until disk space runs out completely.

1. Tracking Replication Lag and Slot Size

Run this diagnostic query on the publisher to check slot activity and retained disk bytes:

-- Check slot status and retained WAL volume
SELECT 
    slot_name, 
    plugin, 
    active, 
    wal_status,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

-- Monitor real-time replay lag
SELECT 
    client_addr, 
    application_name, 
    state, 
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_lag,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag
FROM pg_stat_replication;

2. Protecting the Publisher Disk

To ensure a broken subscriber cannot take down your primary cluster, set a WAL retention ceiling in postgresql.conf on the publisher:

# Enforce a 64 GB hard cap on WAL retention per slot
max_slot_wal_keep_size = 65536MB

If replication lag exceeds 64 GB, Postgres invalidates the slot and sets its wal_status to lost. The publisher recycles the old WAL files to protect primary disk space. The subscriber must then be re-synced from scratch, but your production database stays online.

3. Decommissioning Abandoned Slots

Always drop subscriptions from the subscriber with DROP SUBSCRIPTION analytics_sub;. If the subscriber server was destroyed or decommissioned without cleanup, drop the dangling slot directly on the publisher:

-- Free WAL retention immediately by dropping the abandoned slot
SELECT pg_drop_replication_slot('analytics_sub');

Logical replication gives engineering teams precise data isolation without the heavy infrastructure tax of full-cluster streaming. Keep replication slots monitored, cap your WAL retention sizes, and your downstream analytical pipelines will run reliably for years.