PostgreSQL's replication capabilities have matured substantially across recent versions. Multiple replication mechanisms now exist, each with specific characteristics that suit different operational requirements.
Selecting the right replication approach requires understanding the specific properties of each. This analysis documents the major PostgreSQL replication patterns and their practical implications.
Streaming replication overview
Streaming replication is PostgreSQL's built-in physical replication mechanism. Replicas continuously receive write-ahead log (WAL) records from the primary and apply them to maintain identical state.
Key characteristics:
Physical replication — entire database state replicated, including indexes and other physical structures.
Asynchronous by default with synchronous option available.
Replica is read-only when active.
Failover requires promoting a replica to primary.
All databases on the cluster are replicated together.
Streaming replication is appropriate for high availability, read scaling, and disaster recovery use cases.
Logical replication overview
Logical replication, available since PostgreSQL 10 and significantly improved in subsequent versions, replicates at the row level using a publish-subscribe model.
Key characteristics:
Row-level replication based on logical decoding of WAL.
Selective replication — specific tables or operations can be replicated.
Subscriber can be different PostgreSQL version (within compatibility window).
Subscriber is read-write — subscribers can have additional schema and data.
Substantial overhead compared to streaming replication for full-cluster scenarios.
Logical replication is appropriate for selective replication, cross-version migrations, and integration scenarios.
Physical replication using pg_basebackup
pg_basebackup creates physical backups that can serve as replication starting points or as standalone backups.
Key characteristics:
Creates exact physical copy of data directory.
Can serve as base for streaming replication.
Can serve as base for point-in-time recovery.
Substantial size and time for large databases.
Not real-time replication itself — typically combined with streaming replication for ongoing replication.
Trigger-based replication
Trigger-based replication uses application-level triggers to replicate data to other systems. Tools like Slony-I and Bucardo implement this pattern.
Key characteristics:
Substantial overhead — triggers run on every modified row.
Flexible — can replicate to non-PostgreSQL systems with appropriate adapters.
Complex to set up and operate.
Largely superseded by logical replication for most use cases.
Trigger-based replication is now mostly relevant for legacy deployments. New deployments should typically use logical replication instead.
Comparison matrix
Selecting between approaches:
Streaming replication advantages: Lowest overhead, simplest configuration, native PostgreSQL feature.
Streaming replication disadvantages: All-or-nothing replication, replicas must match version exactly, complete cluster replication only.
Logical replication advantages: Selective replication, cross-version compatibility, allows additional subscriber-side data.
Logical replication disadvantages: Higher overhead than streaming, more complex configuration, some DDL operations not replicated.
Trigger-based replication advantages: Maximum flexibility, can replicate to non-PostgreSQL targets.
Trigger-based replication disadvantages: Highest overhead, complex configuration, generally superseded by logical replication.
The selection depends on specific operational requirements.
High availability patterns
For high availability, streaming replication is the typical foundation:
Primary database with one or more streaming replicas.
Automatic or manual failover to replica when primary fails.
Connection routing through pgpool, HAProxy, or PostgreSQL's built-in pgbouncer.
Synchronous replication for zero-data-loss requirements (with performance trade-off).
Multiple replicas across availability zones for disaster recovery.
Production HA deployments typically use established tools (Patroni, repmgr) rather than building HA management from scratch.
Read scaling patterns
For read scaling, replicas serve read queries:
Streaming replicas receive read-only traffic from application.
Application or proxy routes reads to replicas, writes to primary.
Replica lag must be considered for read-after-write consistency.
Connection pooling typically separate per replica.
Logical replication can serve specialized read use cases (different schemas, different data).
Read scaling is straightforward with PostgreSQL's replication but requires application-level lag handling.
Disaster recovery patterns
For disaster recovery, replication provides geographic redundancy:
Streaming replicas in different geographic region.
Asynchronous replication accepts some data loss for performance.
Periodic base backups for recovery beyond replication time horizon.
Point-in-time recovery using WAL archive plus base backup.
Documented procedures for various failure scenarios.
Effective disaster recovery requires regular testing of recovery procedures.
Migration use cases
For migrations, logical replication has specific advantages:
Cross-version migrations (PostgreSQL 13 → 16, for example) without dump/restore.
Cross-platform migrations.
Selective migration of specific tables.
Zero-downtime migration patterns where application gradually shifts to new system.
Logical replication is now the recommended approach for most PostgreSQL migration scenarios.
Operational considerations
Operational characteristics differ across replication types:
Streaming replication monitoring: Replication lag, WAL retention, replica connection status.
Logical replication monitoring: Subscription lag, slot retention, individual table replication status.
Failover testing: Should be exercised regularly. Untested failover often fails.
Configuration management: Replication configuration must be maintained as systems evolve.
Capacity planning: Replication adds overhead that must be accounted for in capacity planning.
Each replication type has specific operational maturity requirements.
Common operational problems
Replication-related operational issues:
Replication lag. Various causes including primary writes exceeding replica capacity, network issues, replica resource constraints.
Replication slot retention issues. Slots holding WAL beyond available disk space.
Connection issues. Network or authentication problems disrupting replication.
Data divergence. Logical replication can produce divergence in specific failure modes.
Failover failures. Failover that doesn't complete cleanly producing split-brain or other issues.
Each issue has specific diagnostic and recovery approaches.
Tooling ecosystem
The PostgreSQL replication ecosystem includes:
Patroni: Mature HA management framework.
repmgr: Replication and HA management.
pgbackrest: Backup management with replication integration.
pglogical: Logical replication capabilities (some now integrated into core).
Bucardo: Multi-master replication (legacy but still maintained).
Cloud provider tools: AWS RDS, GCP Cloud SQL, Azure Database for PostgreSQL all provide managed replication.
Tool selection depends on specific deployment context.
Cloud vs self-hosted considerations
Cloud deployment affects replication considerations:
Cloud advantages: Managed replication reduces operational burden. Provider handles HA infrastructure.
Cloud disadvantages: Less control over replication topology. Provider-specific behavior. Cost scaling.
Self-hosted advantages: Maximum control. Specific topology choices. Cost management.
Self-hosted disadvantages: Substantial operational complexity. Requires expertise.
The choice between cloud and self-hosted involves substantial trade-offs beyond replication.
What's changed recently
Recent PostgreSQL versions have improved replication substantially:
PostgreSQL 14 added improvements to logical replication including streaming of in-progress transactions.
PostgreSQL 15 added column lists for logical replication.
PostgreSQL 16 added bidirectional logical replication and various other improvements.
The evolution has made logical replication increasingly capable for production scenarios that required streaming replication in earlier versions.
Recommendations by use case
Based on the analysis:
Standard high availability: Streaming replication with Patroni or similar.
Read scaling: Streaming replication with application-level routing.
Disaster recovery: Geographic streaming replication plus WAL archiving.
Cross-version migration: Logical replication.
Selective replication: Logical replication.
Integration with non-PostgreSQL systems: Logical replication with output plugins or trigger-based replication.
Zero-downtime migrations: Logical replication.
The right tool for each use case is generally clear once requirements are documented.
Conclusions
PostgreSQL replication has matured into a substantial capability set covering high availability, read scaling, disaster recovery, and migration scenarios. The choice between streaming and logical replication depends on specific requirements with each having clear advantages for specific use cases.
For engineers operating PostgreSQL in production, understanding the replication options and their trade-offs is essential. Effective replication strategy depends on matching the right tool to specific requirements.
The PostgreSQL replication ecosystem continues to evolve. Recent version improvements have substantially expanded what logical replication can do, making it appropriate for use cases that previously required streaming replication.
Citation
Kowalski, H. (2024). "PostgreSQL Replication Patterns: Streaming, Logical, and Physical Compared." PG vs MS Operations Series.
