Physical replication creates a byte‑identical standby for high availability, while logical replication streams selective SQL changes for upgrades and data distribution. The choice hinges on whether you need whole‑cluster fast recovery or per‑table flexibility across versions. Here’s how the engines differ, how to set them up, and how to avoid the common conflict traps.
| Feature | Physical Replication | Logical Replication |
|---|---|---|
| Scope | Entire cluster only | Table‑level or row‑filtered |
| Standby mode | Read‑only (Hot Standby) | Fully writable subscriber |
| Version requirement | Same major version | Cross‑version (e.g. PG13 → PG15) |
| DDL handling | Replicated automatically | Must be applied manually |
| Sequence sync | Automatic | Requires manual steps |
| Key use cases | HA, DR, read scaling | Online major upgrades, ETL, data warehousing |
The Engine Difference: WAL Blocks vs Logical Row Changes
Physical replication ships write‑ahead log (W AL) blocks page‑for‑page from the primary to the standby, replicating the entire cluster state. Because the standby receives the same binary changes, it can never diverge, and the replication is strictly synchronous or asynchronous. Logical replication, however, decodes these WAL records into a stream of logical row changes—INSERT, UPDATE, DELETE—and sends them to subscribers. This allows per‑table granularity, cross‑version compatibility, and even transformations if you use a custom output plugin.
Setting Up Each Replication Type
Physical replication starts with a base backup (pg_basebackup -R) and a recovery.conf or standby.signal file. The primary streams WAL to the standby via a replication slot, and the standby stays in continuous recovery. Because it’s a whole‑cluster replica, you can’t pick and choose databases—you get everything, including bloat and schema changes.
Logical replication requires a publication on the primary (CREATE PUBLICATION for chosen tables) and a subscription on the subscriber (CREATE SUBSCRIPTION). The subscriber pulls changes from a replication slot dedicated to that publication. You can replicate only a subset of tables, apply row filters, and even republish to further subscribers. The subscriber is fully writable, so you can add local tables or indexes freely.
Common Conflict Traps (and How to Avoid Them)
-
Replication slot bloat – If a subscriber disconnects for too long, the slot keeps WAL on the primary, risking disk exhaustion. Monitor
pg_replication_slotsand set amax_slot_wal_keep_sizein PostgreSQL 13+. -
Vacuum conflicts – Long‑running transactions on a physical standby can block vacuum cleanup on the primary. Use
hot_standby_feedbackto let the primary know about standby queries. -
Primary key requirements – Logical replication requires tables to have a primary key (or
REPLICA IDENTITY FULL). Without it, updates and deletes will fail. - DDL divergence – Logical replication doesn’t replicate schema changes. You must apply them manually on both sides, or use a tool like pglogical to handle DDL replication.
For a real‑time view of replication lag, slot health, and standby status, you can query pg_stat_replication and pg_stat_wal_receiver. However, when you manage multiple clusters, a dedicated monitoring tool like MyDBA consolidates those metrics into a single dashboard, alerts on slot growth, and helps you spot lag before it becomes a problem.
When to Choose Which
Use physical replication when you need a perfect, read‑only copy of the entire cluster for high availability, disaster recovery, or read scaling. It’s the simplest to set up and guarantees zero data divergence.
Use logical replication when you need selective data distribution, online major‑version upgrades with near‑zero downtime, or a writable subscriber for ETL pipelines. It’s more flexible but requires more manual oversight.
Both can coexist—you can run physical replication for HA and logical replication for a reporting server at the same time, as long as you monitor the extra WAL generation.
Choosing between logical and physical replication isn’t a one‑size‑fits‑all decision. Physical replication gives you a bulletproof, byte‑identical standby that’s hard to break, while logical replication opens the door to cross‑version upgrades, partial data sharing, and multi‑master‑like setups—if you’re willing to manage the schema and slot discipline. The real key is monitoring: replication slots that grow unchecked, lag that creeps up unnoticed, or a standby that falls behind can turn a resilience strategy into a data loss event. That’s why having a clear view of your replication health is as important as the initial configuration.
pgdba Editorial builds MyDBA, a Postgres monitoring and health‑check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=logical-vs-physical-replication-postgres
Whether you’re running physical or logical replication, keep an eye on your WAL slots and lag. Try MyDBA for free to get instant visibility into replication status, set up alerts for slot growth, and make sure your data stays where it needs to be.
Top comments (0)