What is logical replication in PostgreSQL?
Answer
Logical replication (PostgreSQL 10+) replicates data at the logical (row) level rather than the physical (WAL block) level. Unlike streaming replication, logical replication can replicate select tables, replicate between different PostgreSQL versions (for zero-downtime upgrades), and replicate to external systems. Based on a publish/subscribe model: Publisher: CREATE PUBLICATION mypub FOR TABLE orders, users;. Subscriber: CREATE SUBSCRIPTION mysub CONNECTION '...' PUBLICATION mypub;. Requirements: wal_level = logical. Supports INSERT, UPDATE, DELETE, TRUNCATE replication. Limitations: DDL changes are not replicated, sequences are not replicated. Use cases: selective replication, data migration, multi-master setups (with care), feeding data to external systems (Debezium CDC).
Previous
What causes poor query plans and how do you fix them?
Next
How do you implement row-level security (RLS) in PostgreSQL?
More PostgreSQL Questions
View all →- Advanced What is the query planner in PostgreSQL and how does it work?
- Advanced What causes poor query plans and how do you fix them?
- Advanced How do you implement row-level security (RLS) in PostgreSQL?
- Advanced What is table bloat in PostgreSQL and how do you fix it?
- Advanced How does PostgreSQL handle deadlocks?