High Availability in PostgreSQL: Streaming vs. Logical Replication

PostgreSQL offers robust replication capabilities that are essential for building high availability database infrastructures. Two primary replication methods stand out: Streaming Replication and Logical Replication. Each serves different use cases and comes with distinct advantages and limitations. This post explores both approaches to help you choose the right solution for your high availability needs.

Understanding High Availability in PostgreSQL

High availability ensures your database remains accessible even during hardware failures, maintenance operations, or unexpected outages. A well-designed HA setup typically includes:

• Primary database server(s)
• One or more replica servers
• Automatic failover mechanism
• Monitoring and alerting systems

Streaming Replication

Streaming replication is PostgreSQL’s physical replication solution, transferring data at the binary level through the Write-Ahead Log (WAL).

How Streaming Replication Works

1. The primary server writes modifications to its WAL
2. Standby servers connect to the primary via a persistent connection
3. WAL records are streamed in real-time to standbys
4. Standby servers apply these WAL records to maintain an exact copy

# On primary server postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

# In pg_hba.conf
host replication replica_user 192.168.1.0/24 md5

# On standby server recovery.conf (or postgresql.conf in newer versions)
primary_conninfo = 'host=primary_ip port=5432 user=replica_user password=secret'
recovery_target_timeline = 'latest'

Advantages of Streaming Replication

• Full database replication: Creates exact copies of the entire database
• Low overhead: Minimal impact on primary server performance
• Near real-time synchronization: Minimal replication lag
• Read scalability: Offload read queries to standby servers
• Simple setup: Relatively straightforward configuration

Limitations of Streaming Replication

• All-or-nothing approach: Can’t replicate selected tables or databases
• Same PostgreSQL version required: Primary and standby must match
• Read-only standbys: Standby servers can’t accept writes
• Server architecture dependency: Primary and standby must use the same system architecture

Logical Replication

Logical replication operates at the SQL/row level, replicating data changes rather than binary file changes.

How Logical Replication Works

1. Changes to the primary database are decoded into logical change records
2. These records are streamed to subscribers
3. Subscribers apply these changes to maintain replicated tables

# On publisher (primary) server
CREATE PUBLICATION sales_pub FOR TABLE customers, orders, products;

# On subscriber server
CREATE SUBSCRIPTION sales_sub 
  CONNECTION 'host=publisher dbname=sales user=repl_user password=secret' 
  PUBLICATION sales_pub;

Advantages of Logical Replication

• Selective replication: Replicate specific tables or databases
• Cross-version compatibility: Works between different PostgreSQL versions
• Writable subscribers: Subscribers can accept writes (with conflict management)
• Heterogeneous replication: Can replicate between different system architectures
• Data consolidation: Aggregate data from multiple sources
• Zero-downtime upgrades: Facilitate major version upgrades

Limitations of Logical Replication

• Higher overhead: Increased CPU usage on the publisher
• DDL changes not replicated: Schema changes must be handled separately
• Complex setup: More configuration options to manage
• Sequence values not synchronized: Must handle separately
• No replication of large objects: BLOB/CLOB data needs special handling

Comparing Replication Methods for High Availability

Feature	Streaming Replication	Logical Replication
Replication Scope	Entire cluster	Selected tables
Setup Complexity	Lower	Higher
Resource Usage	Lower	Higher
Version Compatibility	Same version required	Cross-version supported
Schema Changes	Automatically replicated	Not replicated
Standby Write Access	Read-only	Writable
Failover Capability	Native support	Requires additional tools
Data Transformation	Not possible	Possible

Implementing High Availability with Streaming Replication

A typical streaming replication HA setup includes:

1. Primary server: Handles read and write operations
2. Multiple standby servers: Read-only replicas
3. Load balancer: Distributes read queries
4. Failover manager: Monitors and promotes standby when primary fails

Tools like Patroni or Repmgr can manage automatic failover:

# Example Patroni configuration
scope: postgres-cluster
namespace: /postgres-cluster/
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.101:8008

etcd:
  host: 192.168.1.105:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        wal_keep_segments: 100
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"

Implementing High Availability with Logical Replication

Logical replication HA architectures typically involve:

1. Primary publisher: Main database for writes
2. Multiple subscribers: Can be read-only or serve specific applications
3. Custom failover logic: To manage promotions and re-subscriptions

-- Set up bidirectional replication for failover capability
-- On server A (initially primary)
CREATE PUBLICATION primary_pub FOR TABLE important_data;

-- On server B (initially standby)
CREATE PUBLICATION standby_pub FOR TABLE important_data;
CREATE SUBSCRIPTION primary_sub 
  CONNECTION 'host=server_a dbname=prod user=repl password=secret'
  PUBLICATION primary_pub
  WITH (enabled = true);

-- On server A (creating subscription but keeping it disabled initially)
CREATE SUBSCRIPTION standby_sub 
  CONNECTION 'host=server_b dbname=prod user=repl password=secret' 
  PUBLICATION standby_pub
  WITH (enabled = false);

-- During failover, enable the disabled subscription
ALTER SUBSCRIPTION standby_sub ENABLE;

Hybrid Approaches

For complete high availability solutions, combining both methods can be beneficial:

• Use streaming replication for core HA and disaster recovery
• Implement logical replication for specific use cases:
  • Upgrading to new PostgreSQL versions with minimal downtime
  • Creating specialized read replicas with schema transformations
  • Supporting cross-datacenter replication with reduced bandwidth

Best Practices for PostgreSQL High Availability

1. Test failover regularly: Ensure your failover mechanism works as expected
2. Monitor replication lag: React before it becomes problematic
3. Implement connection pooling: Tools like PgBouncer help manage connections during failover
4. Automate where possible: Use orchestration tools for consistency
5. Document procedures: Create clear runbooks for manual interventions
6. Consider network latency: Place replicas strategically based on geography
7. Implement proper backup strategy: Replication is not a backup substitute

Conclusion

Both streaming and logical replication offer powerful capabilities for building highly available PostgreSQL deployments. Streaming replication provides a simpler, more comprehensive solution when you need exact replicas and straightforward failover. Logical replication offers flexibility for complex scenarios, selective replication, and cross-version compatibility.

The best approach often depends on your specific requirements—many organizations implement both types of replication to leverage the strengths of each. By understanding the differences and trade-offs between these methods, you can design a high availability architecture that provides the right balance of reliability, performance, and operational simplicity for your PostgreSQL databases.