Logical Replication for Sharding
Use PUBLICATION/SUBSCRIPTION logical replication to move tables between clusters and support major-version upgrades.
Logical vs Physical Replication
Physical replicates BYTES of WAL — whole cluster, same version. Logical replicates ROWS — selected tables, possibly across PG versions:
- Subscribe to specific tables
- Replicate from PG 14 → PG 16 (upgrades!)
- Two-way / multi-master with care
- Different schemas allowed
Setting Up: Publisher
Mark the primary as a publisher:
-- postgresql.conf
wal_level = logical
-- Create a publication:
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;
-- Or all tables in the database:
CREATE PUBLICATION pub_all FOR ALL TABLES;All lessons in this course
- Streaming Replication and WAL
- Logical Replication for Sharding
- Failover and Leader Election (Patroni, Stolon)
- Read Replicas and Connection Routing