Architecting Selective Data Sync with PostgreSQL Logical Replication

The Challenge: Selective Synchronization

Physical replication in PostgreSQL is an all-or-nothing operation; it clones the entire database cluster. When you need to move only a subset of tables to a reporting instance, synchronize specific data across different PostgreSQL versions, or consolidate data from multiple shards into a single warehouse, physical replication is too blunt a tool.

The solution is Logical Replication. Unlike physical replication, which copies disk blocks, logical replication streams data changes based on a replication identity (usually a primary key). This allows for granular control over what data moves and where it lands.

The Minimal Design

A logical replication setup consists of two primary components: a Publication on the source (Publisher) and a Subscription on the target (Subscriber). This creates a streaming replication slot that tracks the Write-Ahead Log (WAL) for specific tables.

Implementation Requirements

• WAL Level: The publisher must have wal_level set to logical. This ensures the WAL contains enough information to decode the changes into a logical format.
• Table Identity: Every table being replicated must have a Primary Key or a Unique Index. Without a replication identity, the subscriber cannot identify which row to update or delete, causing the replication process to fail.
• Schema Parity: Logical replication does not synchronize Data Definition Language (DDL) changes. You must manually create the table structures on the subscriber before starting the subscription.

Configuration Example

To synchronize a specific table called orders from a production server to a reporting server, execute the following steps. This assumes PostgreSQL 13 or newer.

On the Publisher (Source):

-- Run as superuser or user with REPLICATION attribute
CREATE PUBLICATION reporting_pub FOR TABLE orders;

On the Subscriber (Target):

-- Run as superuser
CREATE SUBSCRIPTION reporting_sub
CONNECTION 'host=publisher_ip port=5432 user=rep_user password=secret dbname=prod_db'
PUBLICATION reporting_pub;

Risk: The CREATE SUBSCRIPTION command initiates an initial data copy. For tables with millions of rows, this can cause significant I/O load on both the publisher and subscriber.

Trust and Data Boundaries

Logical replication introduces a network dependency and a security surface that must be managed via the pg_hba.conf file on the publisher.

• Network Access: The publisher must allow the subscriber's IP address to connect.
• User Privileges: Create a dedicated replication user. Do not use a superuser for the connection. The user requires the REPLICATION attribute and SELECT permissions on the published tables.

Operational Checks and Verification

Once the subscription is active, you must monitor the health of the replication slot to prevent the publisher from running out of disk space.

Verifying Data Flow

Insert a test record into the publisher's table and query the subscriber. If the record appears, the pipeline is functional. To check the status of the replication slot on the publisher, run:

-- Run on Publisher
SELECT slot_name, active, restart_lsn
FROM pg_replication_slots
WHERE slot_name = 'reporting_sub';

Monitoring Lag

Use pg_stat_replication on the publisher to identify lag. A growing gap between the sent_lsn and write_lsn indicates the subscriber cannot keep up with the write volume.

Failure Modes and Design Pivots

Common Failure Points

• WAL Accumulation: If the subscriber disconnects or crashes, the publisher continues to hold all WAL files necessary for the subscriber to catch up. If left unchecked, this can fill the publisher's disk, leading to a full database outage.
• Constraint Violations: If a row is manually inserted into the subscriber with a primary key that already exists on the publisher, the replication worker will stop with a unique constraint violation error.

When to Change the Design

Logical replication is not a universal solution. You should pivot your architecture if you encounter the following conditions:

Rollback Procedure

To stop replication and remove the associated overhead:

1. On the Subscriber: Run DROP SUBSCRIPTION reporting_sub;. This removes the subscription and stops the connection.
2. On the Publisher: Run DROP PUBLICATION reporting_pub;. This removes the publication and deletes the associated replication slot, allowing the publisher to reclaim WAL space.