PostgreSQL Logical Replication: Easy Step-by-Step Explanation

When managing PostgreSQL in production, there are many situations where you need the same data available across multiple databases for analytics, reporting, high availability, migrations, or syncing distributed applications.

That’s where replication becomes essential.

PostgreSQL supports two different replication approaches depending on the use case:

• Physical replication, which mirrors the entire database cluster at a storage level
• Logical replication, which streams row-level data changes (INSERT, UPDATE, DELETE) to one or more target databases in near real time.

Logical replication enables selective table replication, cross-version upgrades, multi-region sync, and seamless data sharing across applications without cloning the full database.

This guide explains PostgreSQL logical replication step by step, shows real-world use cases, breaks down common limitations, and provides a hands-on setup tutorial you can follow in your own environment.

What Is PostgreSQL Logical Replication?

PostgreSQL logical replication is a row-level data streaming mechanism that copies data changes, such as INSERT, UPDATE, and DELETE operations—from one PostgreSQL database (the publisher) to one or more target databases (the subscribers) in near real time. Instead of duplicating the entire database cluster at the storage level, logical replication selectively replicates only specific tables or datasets, giving teams fine-grained control over what is synchronized.

Logical replication relies on logical decoding of the Write-Ahead Log (WAL). PostgreSQL reads changes recorded in the WAL, converts them into logical messages, and ships them to subscribers, where the operations are applied in the exact commit order. This guarantees consistency even when the source database workload is high.

Logical replication enables use cases that are not possible with physical (binary) replication, including:

• Replicating only selected tables or schemas instead of the entire cluster
• Replicating across different PostgreSQL major versions (e.g., 12 → 15)
• Replicating between different platforms (e.g., Linux → Windows)
• Allowing writes on the subscriber, unlike physical read-only replicas
• Streaming data to multiple subscribers simultaneously

In other words, physical replication is ideal for high-availability failover, whereas logical replication is best for data distribution, workload offloading, cross-system integration, and version upgrades.

When to Use PostgreSQL Logical Replication (Key Use Cases)

PostgreSQL logical replication is most valuable when you need specific datasets, not the entire cluster, to stay synchronized across systems. Below are the most common and production-proven scenarios where logical replication is the right fit.

1. Business Continuity & Performance Scaling

Logical replication helps maintain performance and uptime without overloading the primary database.

Use Case	What Logical Replication Enables
Real-time reporting & analytics	Offload heavy read/BI workloads to a reporting database while keeping data instantly up-to-date.
High-availability read scalability	Write on the primary scale reads across multiple subscribers for high throughput.
Offline-capable or edge systems	Local databases keep functioning during network outages and sync automatically when online again (common in retail, logistics, toll booths, factory floors).

2. Modernization & Migration

Logical replication allows data movement without disrupting ongoing workloads.

Use Case	What Logical Replication Enables
Zero-downtime PostgreSQL version upgrades / migrations	Sync PG 12 → PG 16 (or cross-platform Linux → Windows) while both systems stay online.
Geo-distributed applications	Maintain region-specific replicas to reduce latency while keeping critical tables synchronized globally.

3. Data Distribution Across Systems

Ideal when multiple applications or services need consistency without sharing a central DB.

Use Case	What Logical Replication Enables
Microservices with shared reference data	Keep specific tables (e.g., products, SKUs, currencies, users) synchronized across multiple isolated service databases.
Multi-application data sync	Share selected tables across ERP, CRM, analytics platforms, and other SaaS/internal systems without full-database replication.

The Architecture of PostgreSQL Logical Replication

Logical replication in PostgreSQL is built on a publish and subscribe model, where changes in specific tables are replicated from one database to another using row-level data.

Publisher: The publisher node creates a publication on the source PostgreSQL database that defines what to replicate. It tracks row-level data changes and doesn’t replicate schema changes.

Subscriber: The subscriber node creates a subscription on the target PostgreSQL database that defines where to replicate. It connects to a publication on the source, receives the data changes, and applies them to local tables.

To sum it up, you can set up a publication on the source database to share table changes, and create a subscription on the target database to receive those changes.

But how is the logical replication in PostgreSQL done? Let’s find out!

Prerequisites & Requirements for Logical Replication

Before setting up logical replication, PostgreSQL requires certain database settings, privileges, schema conditions, and network rules to be in place. Missing even one of these prerequisites is the most common reason replication fails during setup.

Logical replication requires four types of readiness:

1. PostgreSQL version compatibility
2. Database parameters (postgresql.conf)
3. Schema requirements
4. Permission and network access

Each is explained below.

1. PostgreSQL Version Compatibility

Logical replication is supported in PostgreSQL 10 and later.

Capability	Supported?
Replication between different PostgreSQL major versions	✔
Linux → Windows or cross-platform replication	✔
Bidirectional multi-master replication	❌ (requires third-party tools)

Optimal stability and features begin from PostgreSQL 12+ due to improvements in replication workers, conflict handling, and slot management.

wal_level = logical
max_replication_slots = 4          # or more based on number of subscriptions
max_wal_senders = 10               # controls concurrent replication connections

2. Required Parameters in postgresql.conf (Publisher Side)

On the publisher database, the following parameters must be configured:

wal_level = logical
max_replication_slots = 4          # or more based on number of subscriptions
max_wal_senders = 10               # controls concurrent replication connections

Optional (recommended for performance in multi-table sync):

max_sync_workers_per_subscription = 4

Restart or reload PostgreSQL after updating parameters.

3. Schema & Table Requirements

Logical replication applies row-level changes. Therefore:

Requirement	Why it matters
Tables must have a primary key or a unique key	Required to identify rows during UPDATE / DELETE
Tables must already exist on the subscriber	Logical replication does not replicate DDL
Column names must match	Order may differ, but names must be identical
Triggers and constraints are not copied	Must be created manually if needed

If a table does not have a primary key, set:

ALTER TABLE table_name REPLICA IDENTITY FULL;

This allows PostgreSQL to replicate UPDATE/DELETE operations by storing full row images.

4. User Privileges & Network Access

Logical replication requires:

On the publisher (source)

• A dedicated replication user
• SELECT permissions on replicated tables

Example:

CREATE USER replicator WITH REPLICATION PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

In pg_hba.conf (publisher)

Allow the subscriber to connect:

host    replication    replicator    <subscriber_ip>/32    md5

Then reload configuration:

SELECT pg_reload_conf();

How PostgreSQL Logical Replication Works Internally

For accurate row-level sync, PostgreSQL logical replication follows a two-step process:

Step 1: Initial Table Synchronization

On creating the first subscription, PostgreSQL copies the existing rows of published tables to the subscriber, ensuring both nodes are in sync before replicating real-time changes.

The workflow:

• Snapshot creation: A snapshot of each published table is taken from the publisher, which is associated with a Log Sequence Number (LSN).

• Table sync: Each table is handled by a table synchronization worker to transfer data from the publisher to the subscriber using standard SQL copy.

Multiple sync workers can run in parallel, but only one per table. This is controlled using the max_sync_workers_per_subscription configuration parameter.

• WAL buffers: Logical decoding captures the changes in the publisher from the WAL and buffers them until the sync finishes.

• Consistency: Once the initial data is copied, the buffered changes are applied from the exact snapshot LSN, ensuring no changes are skipped.

Step 2: Streaming Data Changes

After the initial sync, PostgreSQL transitions to real-time change replication. Further modifications on the publisher are extracted from the WAL and streamed to the subscriber using logical decoding.

The workflow:

• Capture changes: PostgreSQL reads changes from the WAL and converts them into logical messages. Changes include:

INSERT
UPDATE
DELETE

• Streaming: Changes are transmitted through a persistent replication connection managed by the apply worker on the subscriber.

• Enforcement: The apply worker applies changes consistently to maintain transactional consistency.

Note: Logical replication is asynchronous, which means there might be a slight delay depending on:

• Network speed
• System performance
• WAL generation rate
• Replication slot lag

Hands-On Tutorial: Set Up PostgreSQL Logical Replication (Publisher & Subscriber)

This walkthrough explains how to configure logical replication between two PostgreSQL servers:

Role	Purpose	Example Host
Publisher	Source database that sends row-level changes	10.0.0.10:5432
Subscriber	Target database that receives and applies changes	10.0.0.11:5432

You can follow the same steps whether PostgreSQL is running on Linux, Windows, containers, or cloud-managed services (e.g., RDS, Aurora PostgreSQL, Azure PostgreSQL).

Step 1: Configure the Publisher (Source Database)

1.1 Update postgresql.conf

Edit the config file and enable logical replication:

wal_level = logical
max_replication_slots = 4
max_wal_senders = 10

Restart PostgreSQL:

sudo systemctl restart postgresql

1.2 Allow the subscriber to connect (pg_hba.conf)

Add this entry:

host    replication    replicator    10.0.0.11/32    md5

Reload config:

SELECT pg_reload_conf();

1.3 Create a replication user

CREATE USER replicator WITH REPLICATION PASSWORD 'StrongPassword#2025';

Grant SELECT on replicated tables:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

If any table does not have a primary key, define one or use REPLICA IDENTITY FULL.

Step 2: Configure the Subscriber (Target Database)

Ensure PostgreSQL is running normally on the subscriber — no specific config changes are required for logical replication.

Physical replication requires hot_standby = on, but logical replication does not.

Create matching tables on the subscriber:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name TEXT,
    updated_at TIMESTAMP
);

Logical replication will not copy schema — the table must exist already.

Step 3: Create a Publication on the Publisher

On the publisher:

CREATE PUBLICATION ecommerce_pub
FOR TABLE customers;

To replicate multiple tables, list them separated by commas.
To replicate all tables:

CREATE PUBLICATION ecommerce_pub FOR ALL TABLES;

Step 4: Create a Subscription on the Subscriber

On the subscriber:

CREATE SUBSCRIPTION ecommerce_sub
CONNECTION 'host=10.0.0.10 port=5432 dbname=postgres user=replicator password=StrongPassword#2025'
PUBLICATION ecommerce_pub;

PostgreSQL will now:

• Begin real-time WAL-based change streaming
• Take a snapshot of the customers table
• Copy all existing rows to the subscriber

Step 5: Verify Initial Synchronization

On the publisher:

INSERT INTO customers VALUES (1, 'Alice', NOW());

On the subscriber:

SELECT * FROM customers;

Expected output:

id	name	updated_at
1	Alice	2025-01-10 …

If the row appears, initial sync is complete.

Step 6: Verify Real-Time Change Replication

Insert another record on the publisher:

INSERT INTO customers VALUES (2, 'Bob', NOW());

Query on the subscriber:

SELECT * FROM customers;

Expected result:

id	name	updated_at
1	Alice	…
2	Bob	…

This confirms that logical replication is streaming new changes automatically.

What happens next

Logical replication continues to stream:

• INSERT
• UPDATE
• DELETE

in the exact commit order, ensuring the subscriber stays consistent with the publisher.

Your environment is now ready for production-grade logical replication — and from here you can choose whether to maintain manual replication or move to a managed CDC platform like Hevo for automated schema changes, monitoring, and transformations.

PostgreSQL Logical Replication Implementation Options: Manual vs Hevo’s Automated Logical Replication

Method 1: PostgreSQL logical replication using Hevo’s automated data pipeline

Hevo offers a fully managed, no-code solution for logical replication of PostgreSQL data to cloud warehouses and BI tools via PostgreSQL’s WALs. Here’s how it works:

Step 1: Connect the PostgreSQL source

Hevo supports connections to:

• Amazon RDS PostgreSQL
• Amazon Aurora PostgreSQL
• Azure Database for PostgreSQL
• Self-hosted PostgreSQL instances

• Enable wal_level = logical when configuring the source.

Step 2: Configure the replication mode

Hevo offers three modes for data ingestion:

• Logical WAL replication: Uses PostgreSQL’s logical decoding feature to stream row-level changes in real time.

• Custom SQL: To define a custom SQL query to extract data from the source at scheduled intervals.

• Table mode: Pulls data from one or more tables individually for fine-grained control over ingestion.

Step 3: Set up the target destination

The workflow:

• Choose your target destination (e.g., Redshift, Snowflake, BigQuery, etc.).
• Define the target schema or dataset where replicated tables will be written.

Hevo automatically:

• Creates destination tables (if they don’t exist)
• Maps column types based on source schema
• Handles nested or complex data structures during load

Step 4: Enable auto schema mapping

The workflow:

• Hevo detects the source schema, which includes tables, columns, and data types during the initial sync.
• The detected schema is mapped to the destination schema.
• The platform detects new columns added over time and adjusts the mapping to reflect the changes.

You can manually review or customize the mapped schema in Hevo’s UI to ensure it aligns with your destination data model.

Step 5: Trigger real-time data replication

The workflow:

• Once the initial data is ingested, Hevo begins incremental replication using PostgreSQL’s WAL-based Change Data Capture (CDC).

• All new INSERT, UPDATE, and DELETE operations are captured in real time and applied to the destination.
• Hevo continuously reads changes from the logical replication slot and applies them in real time.

This reduces replication lag, minimizes source load, and ensures up-to-date data with high throughput and low latency.

Step 6: Monitor and maintain replication with Hevo

Validate replication using Hevo’s key features:

• Use the monitoring dashboard to track pipeline status, sync progress, and data latency in real time.
• Set custom alerts and failure notifications for schema mismatches, ingestion delays, and transformation errors.
• Use 24/7 live technical support to resolve configuration errors, schema drift, or replication slot mismanagement.

What customers say about Hevo:

Method 2: PostgreSQL logical replication using manual method

Below is a step-by-step breakdown for configuring logical replication manually:

Step 1: Set up logical replication parameters

To use logical replication, update your PostgreSQL configuration to set wal_level = logical. By default, it’s set to log physical changes, which won’t support logical replication.

Locate the postgresql.conf file:

• For Linux: /etc/postgresql/<version>/main/postgresql.conf 
• For Windows: C:\Program Files\PostgreSQL\<version>\data\postgresql.conf

After opening the file, add the following parameter under the WRITE-AHEAD LOG setting:

wal_level = logical

Now, restart the PostgreSQL service after saving the configuration:

• For Linux:

sudo service postgresql stop
sudo service postgresql start

• For Windows: Use the Services control panel to restart the PostgreSQL service.

Next, we’ll verify the logical WAL level by running this SQL command:

SHOW wal_level;

Step 2: Create a publication on the publisher

Use this command to create a publication:

CREATE PUBLICATION my_publication FOR ALL TABLES;

Or, specify the tables you need to publish:

CREATE PUBLICATION my_publication FOR TABLE table1, table2;

You can also choose to limit the changes that will be published:

CREATE PUBLICATION my_publication FOR TABLE table1
WITH (publish = 'insert, update');

Note: For tables without a primary key, you must explicitly define one, or use REPLICA IDENTITY FULL:

ALTER TABLE table1 REPLICA IDENTITY FULL;

Step 3: Define the subscription

After the publications have been created, you can go ahead to create subscriptions in the subscriber node:

CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=<publisher_host> port=5432 dbname=yourdb user=replicator password=secret'
PUBLICATION my_publication;

• Creates a subscription named my_subscription.
• Subscribes to the my_publication publication.
• Copies existing data from the published tables.
• Starts streaming live changes to subscribers.

An Example of Postgres Logical Replication

Here, we have taken an example of two PostgreSQL instances running on the same host. Starting with one PostgreSQL instance running on port 5432 as the publisher, and another on port 5431 as the subscriber.

The pre-setup requirements include:

• wal_level parameter in postgresql.conf must be set to logical.
• max_replication_slots ≥ number of subscriptions.
• max_wal_senders ≥ number of active replication connections.

Step 1: Create the table on the publisher

Connect to the PostgreSQL instance running on port 5432 and execute:

CREATE TABLE table1(x INT PRIMARY KEY, y INT);

The above command creates a table named table1 with two integer columns, x and y. Column x is the primary key for the table to identify rows during UPDATE and DELETE operations.

Step 2: Insert a row into the publisher

Here, we insert a test row in table1 to confirm the transfer of existing data to the subscriber.

INSERT INTO table1 VALUES (10, 11);

• Inserts one row into table1 on port 5432.
• Value 10 goes to column x, and 11 goes to column y.

Step 3: Set up publication for replication

Create a publication:

CREATE PUBLICATION my_publication FOR TABLE table1;

• Creates a publication named my_publication.
• PostgreSQL tracks row-level changes on table1 for replication to subscribers.

Step 4: Recreate the table on the subscriber node

Create the same table on the PostgreSQL instance running on port 5431:

CREATE TABLE table1(x INT PRIMARY KEY, y INT);

This step is necessary because logical replication doesn’t replicate schema changes.

Step 5: Set up replication on the subscriber

Now, we create a subscription to link with the publication:

CREATE SUBSCRIPTION my_subscription 
CONNECTION 'host=localhost port=5432 dbname=postgres' 
PUBLICATION my_publication;

• Creates a subscription named my_subscription.
• Connects to the publisher on port 5432.
• Subscribes to the publication, my_publication.
• Triggers initial synchronization to copy table1 data to the subscriber.

Step 6: Verify sync

Here we check the replication of rows inserted in Step 2 (10,11):

SELECT * FROM table1;

• PostgreSQL takes a snapshot of the table on the publisher.
• Copies the data to the subscriber using a table synchronization worker.
• Inserts the data using standard SQL.

Step 7: Confirm streaming replication

We simply insert a new row into the publisher and check if it gets updated in the subscriber:

INSERT INTO table1 VALUES (20, 25);

Next, we run the following query on the subscriber to verify changes:

SELECT * FROM table1;

If you see this:

 x  | y
----+----
10  | 11
20  | 25

This confirms that the replication is working and that changes are being streamed from the publisher to the subscriber.

Monitoring & Troubleshooting PostgreSQL Logical Replication

Once logical replication is enabled, it’s important to monitor whether the subscriber is staying in sync and diagnose issues quickly when changes aren’t replicated. Below are the simplest and most reliable checks.

How to Monitor Replication

What to check	Where to check	What you want to see
Subscription health	On the subscriber	Status should be replicating
Replication slot activity	On the publisher	Slot should be active
Streaming connection	On the publisher	State should be streaming
Lag	On the publisher	Lag values should remain steady or decreasing

If subscription is active, slot is active, and connection is streaming → replication is healthy.

Common Issues & Quick Fixes

Problem	Most likely root cause	Quick solution
Subscriber created but no data copied	Table doesn’t exist on subscriber	Create the same table manually on subscriber
Initial sync works but new changes aren’t replicated	Table has no primary key	Add PK or set REPLICA IDENTITY FULL
Permission denied	Replication user doesn’t have SELECT	Grant SELECT on replicated tables
Subscriber cannot connect to publisher	pg_hba.conf does not allow it	Add rule for subscriber IP and reload config
Replication stops unexpectedly	Replication slot dropped	Recreate subscription
Disk space growing uncontrollably	Subscriber offline for long → WAL retention	Bring subscriber back online or drop slot

Quick Indicators of a Healthy Replication Setup

• Subscriber shows status = replicating
• Replication slot on publisher is active
• WAL sender on publisher is streaming
• Data inserted on publisher appears on subscriber within seconds

If all four are true, logical replication is functioning properly.

Limitations in Postgres Logical Replication

Let’s discuss some limitations in the process of logical replication in PostgreSQL:

1. It doesn’t replicate the schema or DDL.
2. It doesn’t replicate sequences.
3. Tables must have a primary key or unique key to participate in the PostgreSQL logical replication process.
4. Bi-directional replication is not supported.
5. Large objects are not replicated using PostgreSQL logical replication.

Take a look at the limitations of PostgreSQL logical replication in detail.

Use Cases of Postgres Logical Replication

• Notifying subscribers when changes happen in a single database or a subset of a database.
• Triggers that go off when specific updates are sent to the subscriber.
• Combining many databases into one (for example, analytical uses). replicating across many PostgreSQL major versions.
• Replicating data between Linux and Windows PostgreSQL systems.
• Giving several user groups access to duplicated data.
• Allowing many databases to share a portion of the database.
• The publisher and subscriber should have the same fully qualified name in the database.
• The order of the columns in the subscriber table is irrelevant; the column names must match. Furthermore, a subscription table may have the same amount of columns or more.
• Large objects and sequence data replication are not currently supported. 

Version Notes: Using Logical Replication Before PostgreSQL 12

1. Each publication has the ability to publish modifications to many subscribers, and each subscriber can subscribe to numerous publications.

2. You may use the ALTER PUBLICATION command to add or delete tables from an existing publication.

3. The subscriber is now unable to get a replication of the database structure and DDL definitions. The subscriber must have the published tables.

4. A standard table, not a view, materialized view, partition root table, or foreign table, may be duplicated. 

5. The publisher and subscriber should have the same fully qualified name in the database.

6. The order of the columns in the subscriber table is irrelevant; the column names must match. Furthermore, a subscription table may have the same amount of columns or more.

7. Large objects and sequence data replication are not currently supported. 

Read More About: Migrate Data from PostgreSQL to SQL Server

Conclusion

In this article, you have done the following:

• You configured PostgreSQL to perform logical replication. 
• You built the publisher and subscriber nodes. 
• You demonstrated how to implement the publisher and the subscriber nodes on the same host. 
• You demonstrated how to keep the publisher and the subscriber nodes in sync.

If you want to set up PostgreSQL logical replication without having to write code as described above, you can use an automated data integration solution such as Hevo.

Want to take Hevo for a spin? Sign Up or a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also checkout our unbeatable pricing to choose the best plan for your organization.

FAQs