Setting Up PostgreSQL Streaming Replication for HA

Introduction

Streaming replication is the cornerstone of modern PostgreSQL high‑availability solutions. Unlike file‑based WAL shipping, streaming sends WAL records over a TCP socket in real time, allowing a standby to stay close to the primary’s state. This post walks through every step you need to set up streaming replication, from basic configuration to advanced fail‑over strategies, and ties the discussion into broader DBA practices such as performance tuning, backup automation, and cross‑platform insights (Oracle DBA, SQL Server, RMAN, Data Guard, ASM).

Architecture Overview

In a typical streaming replication topology, one primary node writes WAL records and streams them to one or more standby nodes. Standbys apply WAL to stay in sync, and can be promoted to primary on failure. The key components are:

• Primary postgresql.conf (streaming settings)
• Standby recovery.conf (now a set of parameters in postgresql.conf or standby.signal)
• Connection authentication via pg_hba.conf
• Optional pg_basebackup or third‑party tools for initial data copy
• Monitoring tools (e.g., pg_stat_replication, pg_rewind)

Prerequisites

1. Hardware & OS – Ensure each node meets the RAM, CPU, and disk I/O requirements for your workload. SSDs reduce WAL flush latency.
2. Networking – A dedicated, low‑latency network link between primary and standbys. Port 5432 (or custom) must be open.
3. PostgreSQL version – 9.6+ supports streaming replication out of the box. For advanced features (logical replication, hot standby promotion with pg_auto_failover), use 10+.
4. Credentials – Create a replication role:
   
CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'strongpass';

5. Backup strategy – Have a recent base backup before enabling replication. Use pg_basebackup or a tool like pgBackRest for database backup automation.

Setting Up the Primary

On the primary server, adjust postgresql.conf to enable WAL streaming and replication:

Explanation:

• wal_level must be replica (or logical for logical replication).
• max_wal_senders defines the number of concurrent replication connections.
• wal_keep_size keeps enough WAL for standbys that lag.
• synchronous_commit can be set to on if you want transactions to wait until replication acknowledges receipt.

Authentication

Update pg_hba.conf to allow the replication role from standby IPs:

Reload the configuration:

SELECT pg_reload_conf();

Preparing the Standby

Standby nodes need a fresh copy of the primary’s data directory. The simplest method is pg_basebackup:

After the copy completes, create standby.signal (or set primary_conninfo in postgresql.conf):

Optionally, enable synchronous_standby_names on the primary to control which standbys are considered synchronous.

Starting the Standby

Restart PostgreSQL on the standby:

sudo systemctl restart postgresql

Verify the replication status from the primary:

You should see a row for each standby with state = streaming and sync_state = async (or sync).

Monitoring & Metrics

• WAL lag – pg_stat_replication shows write_lag and flush_lag.
• Disk usage – Monitor wal_keep_size and pg_wal directory growth.
• Health checks – Use pg_isready or external tools (Prometheus exporter, pgWatch).
• Alerting – Configure alerts when lag > X ms or pg_stat_activity indicates long‑running transactions.

Fail‑over Strategies

Manual Promotion

On a standby, run:

This stops WAL replay and makes the standby the new primary. You must also update clients to point to the new primary and ensure the former primary is shut down or reconfigured as standby.

Automatic Fail‑over with pg_auto_failover

For zero‑downtime fail‑over, install pg_auto_failover and create a cluster:

Follow the pg_autoctl documentation to set up node registration, election, and monitoring.

High Availability Patterns

1. Primary‑Standby Pair – Single primary + one or more asynchronous standbys.
2. Multi‑Master (Logical Replication) – Use wal_level=logical and pglogical to sync across nodes.
3. Distributed Clusters (Citus) – Scale writes across shards, while replication ensures data locality.
4. Cloud‑Native (RDS, CloudSQL) – Leverage managed fail‑over but still configure pg_stat_replication for custom monitoring.

Performance Tuning for Replication

• WAL Buffers – Increase wal_buffers on primary to reduce WAL flush frequency.
• Checkpoint Settings – Tweak checkpoint_timeout, max_wal_size, and min_wal_size to balance durability vs. performance.
• I/O Scheduling – Use deadline or noop on SSDs; cfq on spinning disks.
• Network Compression – Use wal_compression = on to reduce bandwidth, at the cost of CPU.
• Connection Pooling – Deploy PgBouncer or Pgpool-II on standbys to reduce connection overhead for read‑only queries.

Backup and Point‑in‑Time Recovery (PITR)

Replication is often the first step toward a robust backup strategy. Combine streaming replication with:

• Base Backups – Periodic pg_basebackup or pgBackRest snapshots.
• WAL Archiving – archive_mode=on and archive_command to store WAL segments externally.
• Point‑in‑Time Recovery – Rewind the standby to a specific time using pg_waldump or pg_rewind.
• Automation – Integrate with CI/CD pipelines or configuration management tools.

Security Considerations

1. Encryption – Enable TLS for replication connections by setting ssl=on and ssl_ca_file on both primary and standby.
2. Least Privilege – Use a dedicated replication role with REPLICATION privilege only.
3. Firewall Rules – Restrict replication port to known IPs.
4. Monitoring Credentials – Avoid using superuser credentials for monitoring tools.
5. Audit Logging – Enable log_connections and log_disconnections to track replication sessions.

Common Pitfalls & Troubleshooting

• “Replication slots are full” – Increase wal_keep_size or use replication_slot to manage WAL retention.
• Lag spikes – Check network latency, disk I/O bottlenecks, or long transactions on the primary.
• Authentication failures – Verify pg_hba.conf and the replication role password.
• Promote error: “primary is already in recovery” – The node is still applying WAL; run pg_is_in_recovery() to confirm.
• Missing standby.signal – PostgreSQL 12+ uses this file; older versions use recovery.conf.

Best Practices Checklist

1. Use dedicated replication slots to avoid WAL backlog.
2. Keep primary and standby on the same OS version to avoid compatibility issues.
3. Implement regular fail‑over drills to validate promotion scripts.
4. Maintain consistent timezone settings across nodes.
5. Document the replication topology and recovery procedures.
6. Integrate replication metrics into central monitoring dashboards.

Conclusion

Streaming replication in PostgreSQL is a mature, flexible mechanism that can be combined with backup automation, performance tuning, and robust fail‑over solutions to build highly available, resilient databases. By following the steps outlined above and adhering to best‑practice guidelines, you can ensure that your PostgreSQL workloads remain online, consistent, and ready to scale. The same principles—configuration hygiene, monitoring, and automation—apply whether you manage on‑prem databases, cloud‑hosted instances, or hybrid environments alongside Oracle, SQL Server, and other enterprise systems.

If you found this guide helpful, subscribe to our newsletter and follow us on LinkedIn for more DBA insights.