PostgreSQL Replication Setup: Streaming Replication and Failover

Overview

Keeping PostgreSQL highly available and its data protected matters for modern applications. The blog presents streaming replication with failover as one of the most effective approaches and walks through building it, from initial setup to handling a failover.

Understanding PostgreSQL Replication

Among the options for a PostgreSQL replication setup, streaming replication is the most widely used for high availability. A standby receives changes from the primary in real time, keeping data consistent and making failover possible. It relies on the Write-Ahead Log (WAL), streaming transaction logs so each committed transaction reaches the standby with minimal delay. 

Why Streaming Replication?

• Real-time data synchronization
• Minimal lag between primary and replica
• Automatic failover through tools like Patroni
• Read scaling by offloading queries to replicas

Prerequisites

You need two PostgreSQL servers (primary and replica), network connectivity between them, the same PostgreSQL version on both, and SSH access to both machines.

PostgreSQL Replication Setup Steps

Step 1: Configure the primary

 In postgresql.conf, set listen_addresses='*', wal_level=replica, max_wal_senders=10, max_replication_slots=10, wal_keep_size=2GB, archive_mode=on and hot_standby=on. Add a pg_hba.conf line allowing the replicator user to make replication connections from the standby's IP using scram-sha-256. Create a dedicated role with REPLICATION and LOGIN privileges and a strong password, then restart PostgreSQL.

Step 2: Create a replication slot

Run pg_create_physical_replication_slot('standby1'). Slots keep WAL files from being removed before the standby receives them. They are optional but strongly recommended in production.

Step 3: Initialize the standby

Stop PostgreSQL on the standby and delete its data directory. Clone the primary with pg_basebackup, specifying the primary's host, the replicator user and $PGDATA as the target, with the flags -P, -R, -X stream, -C and -S standby1. The -R flag automatically generates the replication configuration.

Step 4: Start the standby

Start PostgreSQL and run SELECT pg_is_in_recovery();. A result of true confirms it is a standby.

Step 5: Verify streaming

On the primary, query pg_stat_replication for application_name, client_addr, state and sync_state. A healthy setup shows "streaming" and "async" (or "sync" if synchronous replication is configured).

Step 6: Test replication

Create a replication_test table on the primary and insert a row. If it appears on the standby, replication works.

Step 7: Monitor health

Use pg_stat_replication and measure lag with SELECT now() - pg_last_xact_replay_timestamp();. These reveal delays, disconnected replicas and sync problems before they affect production. Lag should stay low; persistent or growing lag may indicate network bottlenecks, insufficient resources or long-running transactions on the primary.

Step 8: Failover

Failover promotes a standby to primary if the primary becomes unavailable, limiting downtime and keeping the service running.

PostgreSQL Failover Best Practices

• Use high-availability tools like Patroni to automate failover and manage the cluster.
• Regularly test failover and failback procedures.
• Back up both primary and standby servers regularly.
• Continuously monitor replication lag, server health and connectivity, with alerts for anomalies.
• Document failover and recovery procedures for quick response during outages.

Manual failover: Without automatic failover, promote the standby with SELECT pg_promote();. Afterward, SELECT pg_is_in_recovery(); should return false, confirming the server is now the primary.

Mafiree's PostgreSQL Replication Services

Mafiree offers PostgreSQL DBA services covering replication setup, performance monitoring and smooth failover. Its experts can design and implement replication strategies, monitor replication health, implement automated failover and ensure compliance with data protection standards. The blog also points to a separate Mafiree guide on choosing between streaming, logical and other replication types.

Conclusion

A dependable replication system is crucial for data integrity and business continuity. Streaming replication provides near real-time synchronization and supports high availability, disaster recovery and read scalability. Proper configuration, monitoring and failover procedures maximize uptime and reduce risk, and Mafiree positions itself as able to help with new deployments or improvements to existing systems.