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 ways to achieve this, and walks through building such a system from initial setup to handling failover.

Understanding PostgreSQL Replication

Among the options available for a PostgreSQL replication setup, streaming replication is the most widely used for high availability. A standby server receives changes from the primary in real time, which keeps data consistent and makes failover possible. It relies on the Write-Ahead Log (WAL), streaming transaction logs from the primary to the replica so that every 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 (a primary and a replica), network connectivity between them, the same PostgreSQL version on both, and SSH access to both machines.

Step-by-Step PostgreSQL Replication Setup

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. These enable WAL streaming and let standbys connect. Next, add a line to pg_hba.conf permitting the replicator user to make replication connections from the standby's IP using scram-sha-256. Then create a dedicated replication role with the REPLICATION and LOGIN privileges and a strong password, and restart PostgreSQL to apply the changes.

Step 2: Create a replication slot

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

Step 3: Initialize the standby

Stop PostgreSQL on the standby and delete its existing data directory. Clone the primary with pg_basebackup, pointing it at the primary's host, using the replicator user, and setting the target directory to $PGDATA. The command uses the flags -P, -R, -X stream, -C and -S standby1. The -R flag automatically generates the required replication configuration.

Step 4: Start the standby 

Start PostgreSQL on the standby, then run SELECT pg_is_in_recovery();. A result of true confirms it is running as a standby.

Step 5: Verify streaming

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

Step 6: Test replication

Create a table named replication_test on the primary and insert a row. If you can see that row when querying the standby, replication is working.

Step 7: Monitor health 

Regular monitoring is essential. Use pg_stat_replication and measure lag with SELECT now() - pg_last_xact_replay_timestamp();. These queries reveal delays, disconnected replicas and sync problems before they affect production. Lag should normally stay low. Persistent or growing lag can point to network bottlenecks, insufficient resources or long-running transactions on the primary.

Step 8: Failover for High Availability

Failover lets a standby be promoted to primary if the primary becomes unavailable, which limits downtime and keeps the service running. 

Best Practices for PostgreSQL Failover 

  • Use high-availability tools such as Patroni to automate failover and manage the cluster.
  • Test failover and failback procedures regularly.
  • Keep regular backups of both primary and standby servers.
  • Continuously monitor replication lag, server health and connectivity, with alerts for anomalies.
  • Document failover and recovery procedures so you can respond quickly during outages.

Manual failover Without automatic failover, you can promote the standby by running SELECT pg_promote();. Afterward, SELECT pg_is_in_recovery(); should return false, confirming the server is now acting as the primary.

Mafiree's PostgreSQL Replication Services

Mafiree offers PostgreSQL DBA services covering replication setup, performance monitoring and smooth failover. Its experts can help you design and implement replication strategies, monitor replication health and performance, implement automated failover, and ensure compliance with data protection standards. The blog also points readers 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 gives near real-time synchronization between primary and standby servers, and it supports high availability, disaster recovery and read scalability. Proper configuration, monitoring and failover procedures are what maximize uptime and reduce risk. Mafiree positions itself as able to help with new deployments or improvements to existing systems.