PostgreSQL High Availability Setup: Master-Slave Replication

Configure PostgreSQL high availability with streaming replication to ensure your database is always available, minimizing downtime and protecting your data.

Why High Availability Matters

Database downtime can cost enterprises thousands of dollars per minute. High availability (HA) ensures business continuity, data protection, and seamless user experiences even during hardware failures or maintenance windows.

Key benefit: Zero-downtime deployments and automatic failover capabilities protect your business from costly outages.

PostgreSQL Replication Types

PostgreSQL offers several replication methods, each suited for different scenarios:

1. Streaming Replication (Recommended for HA)
2. Logical Replication (for selective data replication)
3. File-based Log Shipping (legacy approach)

Why Streaming Replication?

Streaming replication provides:

• Real-time data synchronization
• Minimal replication lag
• Simple configuration and maintenance
• Reliable failover capabilities

Setup Steps

1. Configure Primary Server

Edit the postgresql.conf file on your primary server:

# Replication settings
wal_level = replica
max_wal_senders = 3
wal_keep_size = 64

Configuration breakdown:

• wal_level = replica: Enables WAL logging for replication
• max_wal_senders = 3: Allows up to 3 standby servers
• wal_keep_size = 64: Retains 64MB of WAL files

2. Create Replication User

Create a dedicated user with replication privileges:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

Security tip: Use a strong password and store it securely. Consider using certificate-based authentication for production environments.

3. Configure pg_hba.conf

Add replication access rules to pg_hba.conf:

# Allow replication connections from standby server
host replication replicator standby_ip/32 md5

Replace standby_ip with your actual standby server's IP address.

4. Setup Standby Server

Initialize the standby server using pg_basebackup:

pg_basebackup -h primary_ip -D /var/lib/postgresql/data -U replicator -P --wal-method=stream

Command options:

• -h primary_ip: Primary server hostname/IP
• -D /var/lib/postgresql/data: Data directory on standby
• -U replicator: Replication user
• -P: Show progress
• --wal-method=stream: Stream WAL files during backup

Monitoring Replication

Check Replication Status

Monitor active replication connections on the primary server:

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    replay_lag
FROM pg_stat_replication;

Key Metrics to Monitor

• replay_lag: Time delay in applying WAL records
• sync_state: Synchronization mode (async/sync)
• state: Connection state (streaming/catchup)

Monitoring tip: Set up alerts for replication lag exceeding acceptable thresholds (typically 10-30 seconds).

Failover Strategy

A robust failover plan is critical for maintaining high availability:

Manual Failover Steps

1. Detect primary failure

   • Monitor primary server health
   • Verify primary is truly down (not network issue)

2. Promote standby to primary

   pg_ctl promote -D /var/lib/postgresql/data
   

3. Reconfigure application connections

   • Update connection strings to point to new primary
   • Use connection poolers for easier failover

4. Setup new standby

   • Configure old primary as new standby (if recovered)
   • Or provision a new standby server

Automated Failover

Consider tools like:

• Patroni: Distributed consensus-based HA
• repmgr: Replication manager for PostgreSQL
• Stolon: Cloud-native PostgreSQL HA

Best Practices

Here are essential best practices for production deployments:

• Regular failover testing: Test your failover procedures monthly
• Monitor replication lag: Set alerts for lag > 10 seconds
• Use synchronous replication for zero data loss (with performance trade-off)
• Implement connection pooling: Simplifies application failover
• Document runbooks: Maintain clear failover procedures
• Backup both servers: Replication is not a backup solution

Critical warning: Streaming replication protects against hardware failure but not against data corruption or human error. Always maintain separate backups!

Conclusion

PostgreSQL streaming replication provides robust high availability with minimal data loss. By following this guide, you can set up a reliable HA cluster that protects your business from costly downtime.

Remember: Regular testing of failover procedures is essential to ensure your HA setup works when you need it most.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in PostgreSQL, SQL optimization, and enterprise database solutions.

Related Articles

• Database Performance Tuning Best Practices
• Cloud Database Migration Strategies
• High Availability Database Setup

Need help with PostgreSQL optimization? Contact us for expert database consulting.