Introduction
Modern applications require databases that are highly available, scalable, and resilient to failures. As businesses grow, database downtime can lead to lost revenue, poor user experiences, and operational disruptions.
To address these challenges, organizations often implement replication strategies that maintain multiple copies of their data across different servers.
PostgreSQL offers several replication mechanisms, with Logical Replication being one of the most flexible and powerful options.
Unlike traditional replication approaches that copy entire database changes at the storage level, Logical Replication works at the data level, allowing selective replication of tables and greater control over data distribution.
In this article, you'll learn how PostgreSQL Logical Replication works, its architecture, benefits, limitations, and how it supports high-availability systems.
What Is PostgreSQL Logical Replication?
Logical Replication is a feature in PostgreSQL that replicates data changes from one database server to another using a publish-subscribe model.
Instead of copying physical storage blocks, PostgreSQL replicates logical data changes such as:
INSERT operations
UPDATE operations
DELETE operations
Architecture:
Publisher
↓
Replication Stream
↓
Subscriber
The subscriber receives data changes and applies them to its own tables.
Why Use Logical Replication?
Organizations choose Logical Replication for several reasons.
Selective Replication
Only specific tables can be replicated.
Database Upgrades
Migrate data between PostgreSQL versions.
Geographic Distribution
Replicate data across regions.
Reporting Systems
Separate reporting workloads from production systems.
High Availability
Maintain synchronized database copies.
These capabilities make Logical Replication highly flexible.
Understanding Replication Types in PostgreSQL
PostgreSQL provides multiple replication approaches.
Physical Replication
Replicates storage-level changes.
Architecture:
Primary Database
↓
Physical WAL
↓
Replica
Characteristics:
Entire database replication
Exact copy of primary
Limited flexibility
Logical Replication
Replicates row-level changes.
Architecture:
Publisher
↓
Logical Changes
↓
Subscriber
Characteristics:
Table-level control
Flexible architecture
Cross-version compatibility
Publisher and Subscriber Concepts
Logical Replication uses two key components.
Publisher
The source database.
Responsibilities:
Track changes
Publish updates
Generate replication stream
Subscriber
The destination database.
Responsibilities:
Receive changes
Apply updates
Maintain synchronization
Workflow:
Publisher
↓
Change Stream
↓
Subscriber
Understanding Write-Ahead Logging (WAL)
PostgreSQL uses Write-Ahead Logging (WAL) to track changes.
Workflow:
Database Change
↓
WAL Entry
↓
Replication
Logical Replication extracts relevant information from WAL records and sends them to subscribers.
This ensures consistency and reliability.
Configuring Logical Replication
Several PostgreSQL settings must be configured.
Example:
wal_level = logical
Additional settings may include:
max_replication_slots = 10
max_wal_senders = 10
These parameters enable logical replication capabilities.
Creating a Publication
A publication defines which tables should be replicated.
Example:
CREATE PUBLICATION sales_pub
FOR TABLE orders;
Architecture:
Orders Table
↓
Publication
Only published tables participate in replication.
Publishing Multiple Tables
Example:
CREATE PUBLICATION app_pub
FOR TABLE
customers,
orders,
products;
This publication includes multiple tables.
Benefits include:
Centralized management
Simplified replication setup
Better organization
Creating a Subscription
The subscriber connects to the publication.
Example:
CREATE SUBSCRIPTION sales_sub
CONNECTION
'host=db1 dbname=sales'
PUBLICATION sales_pub;
Workflow:
Subscriber
↓
Connect
↓
Publication
The subscriber begins receiving changes automatically.
Initial Data Synchronization
When a subscription is created:
Existing Data
↓
Initial Copy
↓
Continuous Replication
This ensures the subscriber starts with a complete dataset.
Afterward, only incremental changes are transmitted.
Replicating INSERT Operations
Example:
INSERT INTO orders
VALUES (1, 'Laptop');
Workflow:
Insert
↓
WAL
↓
Publication
↓
Subscriber
The row appears automatically on the subscriber.
Replicating UPDATE Operations
Example:
UPDATE orders
SET status = 'Shipped'
WHERE id = 1;
Replication flow:
Update
↓
Logical Change
↓
Subscriber
The subscriber remains synchronized.
Replicating DELETE Operations
Example:
DELETE FROM orders
WHERE id = 1;
Workflow:
Delete
↓
Replication Stream
↓
Subscriber
Deleted rows are removed from subscribers as well.
Monitoring Replication Status
PostgreSQL provides monitoring views.
Example:
Join the conversation! Your thoughts help the community grow.