Setting Up PostgreSQL Logical Replication

PostgreSQL logical replication enables selective table-level replication between databases, supporting different PostgreSQL versions, different schemas, and even cross-platform replication. Unlike physical (streaming) replication, logical replication decodes WAL changes into a logical format, allowing fine-grained control over what data gets replicated. This guide covers setup, configuration, and production best practices.

Logical vs. Physical Replication

Physical replication copies the entire database cluster byte-for-byte, which is simpler but less flexible. Logical replication offers several advantages:

• Selective replication — replicate specific tables, not the entire database
• Cross-version support — replicate between PostgreSQL 14 and 16, for example
• Writable subscribers — the receiving database can have its own tables and writes
• Schema flexibility — publisher and subscriber can have different indexes, views, or additional columns
• Zero-downtime upgrades — replicate to a newer PostgreSQL version, then switch over

Prerequisites

Both publisher and subscriber need PostgreSQL 10 or later (16+ recommended). The publisher must have the following configuration:

# postgresql.conf on the publisher
wal_level = logical
max_replication_slots = 10    # At least one per subscription
max_wal_senders = 10          # At least one per subscription + streaming replicas

Restart PostgreSQL after changing wal_level:

sudo systemctl restart postgresql

Step 1: Configure the Publisher

On the publisher database, create a publication for the tables you want to replicate:

-- Connect to the publisher database
psql -U postgres -d sourcedb

-- Replicate specific tables
CREATE PUBLICATION my_pub FOR TABLE orders, customers, products;

-- Or replicate all tables in the database
CREATE PUBLICATION all_tables_pub FOR ALL TABLES;

-- Replicate with row filters (PostgreSQL 15+)
CREATE PUBLICATION filtered_pub FOR TABLE orders WHERE (region = 'US');

Create a Replication User

CREATE USER replicator WITH REPLICATION PASSWORD 'SecureReplicationPass!';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replicator;

Allow Replication Connections

# pg_hba.conf — allow the subscriber to connect
host    sourcedb    replicator    10.0.0.0/24    scram-sha-256

Step 2: Prepare the Subscriber

The subscriber database must have the same table structures. Create them manually or use pg_dump --schema-only:

# Dump schema from publisher
pg_dump -U postgres -d sourcedb --schema-only -t orders -t customers -t products > schema.sql

# Apply schema on subscriber
psql -U postgres -d targetdb < schema.sql

Step 3: Create the Subscription

-- Connect to the subscriber database
psql -U postgres -d targetdb

CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher-ip port=5432 dbname=sourcedb user=replicator password=SecureReplicationPass!'
    PUBLICATION my_pub
    WITH (
        copy_data = true,          -- Initial data sync
        create_slot = true,        -- Auto-create replication slot
        synchronous_commit = off   -- Better performance for async replication
    );

The initial sync copies all existing data using COPY, then switches to streaming logical changes. Monitor progress:

-- On the subscriber
SELECT * FROM pg_stat_subscription;

-- On the publisher
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

Step 4: Verify Replication

-- Insert a row on the publisher
INSERT INTO orders (customer_id, total, status)
VALUES (1, 99.99, 'new');

-- Check it appears on the subscriber (may take milliseconds)
SELECT * FROM orders ORDER BY id DESC LIMIT 1;

Managing Publications

-- Add a table to an existing publication
ALTER PUBLICATION my_pub ADD TABLE invoices;

-- On the subscriber, refresh to pick up the new table
ALTER SUBSCRIPTION my_sub REFRESH PUBLICATION;

-- Remove a table
ALTER PUBLICATION my_pub DROP TABLE products;

-- List publications
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Monitoring Replication Lag

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

-- On the subscriber: check subscription status
SELECT subname, received_lsn, latest_end_lsn, latest_end_time
FROM pg_stat_subscription;

Handling Conflicts

Unlike physical replication, logical replication can encounter conflicts when the subscriber has its own writes. Common conflicts include:

• Unique constraint violations — a row with the same primary key exists on the subscriber
• Missing referenced rows — foreign key constraints fail

When a conflict occurs, replication stops. Check the subscriber logs and resolve:

-- Skip a conflicting transaction
ALTER SUBSCRIPTION my_sub DISABLE;
SELECT pg_replication_origin_advance('pg_16389', '0/1A2B3C4D'::pg_lsn);
ALTER SUBSCRIPTION my_sub ENABLE;

Use Case: Zero-Downtime PostgreSQL Upgrade

1. Set up the new PostgreSQL version as a subscriber
2. Create publications on the old server and subscriptions on the new one
3. Wait for initial sync to complete and replication lag to reach zero
4. Stop writes to the old server, verify subscriber is caught up
5. Switch your application connection strings to the new server
6. Drop the subscription and old server when confirmed stable

Best Practices

• Always set wal_level = logical even if you only use streaming replication today — it enables future flexibility
• Monitor replication slot disk usage — inactive slots prevent WAL cleanup and can fill your disk
• Use ALTER SUBSCRIPTION ... DISABLE during subscriber maintenance rather than dropping and recreating
• Test conflict resolution procedures before going to production
• Set max_replication_slots higher than your current need to accommodate future subscriptions