Logical Replication Fundamentals

Welcome to Logical Replication! This powerful PostgreSQL feature lets you replicate data based on actual data changes, not just physical blocks.

Unlike physical replication, which copies entire database files or blocks, logical replication focuses on the data itself, giving you much more flexibility.

The key distinction lies in what gets replicated:

• Physical Replication: Copies raw data blocks and internal database structures. It's like taking a full snapshot of the database at a byte level.
• Logical Replication: Replicates individual data changes (INSERTs, UPDATEs, DELETEs) as logical operations. It understands 'a row was inserted' rather than 'this block changed'.

Think of it as copying a whole book (physical) versus copying just the new sentences added to specific chapters (logical).

Logical replication leverages PostgreSQL's Write-Ahead Log (WAL).

• When data changes, entries are written to the WAL.
• A special process called logical decoding reads these WAL entries.
• It translates them into a stream of logical changes, like SQL statements (e.g., INSERT INTO users VALUES (...)).
• These logical changes are then sent to other databases.

In logical replication, there are two main roles:

• The publisher is the source database that generates and sends the data changes.
• It defines publications, which are specific sets of tables and/or types of operations (INSERT, UPDATE, DELETE) it wants to make available for replication.

Imagine a publisher as a news agency broadcasting specific news channels.

The other main role is the subscriber:

• The subscriber is the destination database that receives and applies the data changes.
• It creates subscriptions to specific publications offered by a publisher.
• The subscriber's tables must have a compatible schema (same table and column names/types) as the publisher's tables being replicated.

The subscriber is like a TV tuner picking up those specific news channels.

Let's trace a data change through logical replication:

1. A transaction commits on the publisher.
2. The changes are recorded in the WAL.
3. Logical decoding extracts these changes into a stream.
4. This stream is sent over the network to the subscriber.
5. The subscriber applies these changes to its local tables, effectively replicating the data.

Logical replication offers significant benefits:

• Selective Replication: You can choose to replicate only specific tables, not the entire database.
• Cross-Version Compatibility: It often allows replication between different major PostgreSQL versions (e.g., 13 to 15).
• Heterogeneous Targets: While primarily for PostgreSQL, it can be a source for other databases using external tools.
• Reduced Network Traffic: Only changed data is sent, not entire blocks.

Logical replication is incredibly versatile:

• Zero-Downtime Upgrades: Replicate to a newer PostgreSQL version, then switch over.
• Data Distribution: Send specific datasets to analytical databases or microservices.
• Data Consolidation: Combine data from multiple sources into a central reporting database.
• Selective Read Replicas: Create read-only copies of only the most frequently queried tables.

While powerful, logical replication has limitations:

• DDL (Schema Changes): Schema changes (like ALTER TABLE) are NOT automatically replicated. You must apply them manually on both publisher and subscriber.
• Sequence Replication: Sequences are not replicated; their values need separate handling.
• No Built-in Failover: Logical replication itself doesn't provide automatic failover capabilities like some physical replication setups (e.g., with Patroni).
• Large Objects: Large objects (bytea) are not replicated directly and need special attention.

Let's test your understanding of the core differences and advantages.

You've learned the fundamentals of Logical Replication!

• It replicates data based on logical row changes, offering high flexibility.
• It works via publishers defining publications and subscribers creating subscriptions.
• Key advantages include selective replication and cross-version compatibility.
• Remember that DDL and sequences are not automatically replicated and require manual handling.

Next, we'll dive into configuring publications and subscriptions to get this powerful system up and running!