Logical Replication: Surgical Precision
Streaming Replication is an “all-or-nothing” deal. You get the entire database cluster, including users, bloat, and indexes.
Logical Replication is different. It allows you to replicate specific tables (or even specific rows/columns) to other databases, even if they are running different versions of Postgres or different operating systems.
It turns the raw, binary WAL stream into a stream of logical changes (INSERT, UPDATE, DELETE).
2. The Architecture: Pub/Sub
Logical replication uses a Publisher/Subscriber model.
- Publisher: The node where changes originate.
- Subscriber: The node that receives changes and applies them.
How it Works: Logical Decoding
The magic happens in the WAL Sender. Instead of sending raw WAL bytes, it uses a Logical Decoding Plugin (default: pgoutput) to "decode" the WAL into a logical format.
- Raw WAL: "Bytes at offset 0x1234 changed from A to B."
- Logical Message: "INSERT INTO users (id, name) VALUES (1, 'Alice');"
This stream is then sent to the subscriber, which applies the SQL-level changes.
4. Interactive: Logical Decoding Visualizer
See how a raw WAL record is transformed into a logical change stream. Select an operation and watch the decoding process.
6. Configuration: Setting the Stage
Step 1: Enable Logical Level on Publisher
In postgresql.conf:
wal_level = logical
Restart required.
Step 2: Create Publication (Publisher)
You can choose which tables to publish.
-- Publish all tables
CREATE PUBLICATION my_pub FOR ALL TABLES;
-- OR Publish specific tables
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- OR Publish only specific rows (Postgres 15+)
CREATE PUBLICATION my_pub FOR TABLE orders WHERE (amount > 1000);
Step 3: Create Subscription (Subscriber)
On the destination database:
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=publisher_ip port=5432 dbname=mydb user=rep_user password=secret'
PUBLICATION my_pub;
Postgres will now:
- Connect to the publisher.
- Create a replication slot.
- Snapshot the initial data (copy existing rows).
- Begin streaming changes.
8. Top 3 Use Cases
1. Zero-Downtime Major Version Upgrades
Streaming replication requires the Primary and Standby to run the exact same major version (e.g., both 14.x). Logical replication works across versions.
- Strategy: Set up Postgres 16 as a subscriber to Postgres 14. Wait for sync. Switch over app.
2. ETL and Analytics
Don’t run heavy analytics queries on your production OLTP database.
- Strategy: Replicate only the necessary tables (e.g.,
orders,payments) to a Data Warehouse instance. You can even replicate to a different database schema!
3. Geographical Distribution
Replicate a us_users table to a US server and eu_users to an EU server for lower latency reads.
10. Gotchas and Limitations
[!CAUTION] Schema Changes (DDL): By default, DDL (like
ALTER TABLE) is NOT replicated. If you add a column on the Publisher, you must manually add it on the Subscriber, or replication will break.
[!WARNING] Unique Constraints: If you insert a row on the Subscriber that conflicts with an incoming change from the Publisher, replication will halt. You must manually resolve the conflict.
[!IMPORTANT] Sequences: Sequences (used for
SERIALorIDENTITYcolumns) are not replicated. After a switchover, you must manually reset the sequences on the new primary to avoid ID collisions.
[!NOTE] This module explores the core principles of Logical Replication, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.