When a PostgreSQL primary server fails without a streaming replication standby, the crisis stretches for hours — especially on databases from 500 GB to 5 TB. An hour of downtime for such a server can cost $10,000 depending on the business, and RTO becomes unacceptable. Typical case: a client with a 2 TB database, primary falls during peak hours. According to our statistics, 70% of failures occur at night when the on-call engineer doesn't respond immediately. Without a replica, RTO is 8 hours with pg_restore. With configured streaming replication, failover takes 2 minutes, and RPO is under 1 second in synchronous mode.
We solve this by configuring WAL-based replication — we set up a standby that maintains an exact copy of the data with millisecond lag. Over 10 years, we have never lost a single transaction with properly configured synchronous replication. Result: failover in minutes and the ability to distribute read queries. Below is the practical configuration we use in production.
Problems We Solve
- Data loss on primary failure: default asynchronous streaming replication may lose recent transactions. We configure synchronous mode where critical.
- Standby lag: without replication slots, a prolonged standby outage requires a full pg_basebackup — on terabyte-sized databases, that means hours of downtime. Solution: replication slots + monitoring.
- Query conflicts on standby: long read-only queries block WAL replay. We enable hot_standby_feedback — the standby informs the primary about its active queries, so the primary postpones vacuum.
PostgreSQL streaming replication is approximately 50 times faster than recovery from backup (minutes vs. hours). Proper replication setup prevents losses up to $50,000 per year, and our clients typically recover the investment within three months.
How We Do It
We use the stack: PostgreSQL 16, Patroni for automatic failover, Prometheus + Grafana for monitoring. Typical primary configuration:
wal_level = replica max_wal_senders = 5 wal_keep_size = 1GB hot_standby = on track_commit_timestamp = on Standby initialization via pg_basebackup with the --wal-method=stream option — data copying proceeds simultaneously with WAL reception, eliminating desynchronization. After creating standby.signal and configuring primary_conninfo, the standby is ready in 15-30 minutes under typical load. Patroni automatically switches roles on primary failure, reducing RTO to 2-5 minutes.
The Importance of Replication Slots
Replication slots guarantee that the primary does not remove WAL until the standby acknowledges receipt, which is critical for streaming replication setups. If the standby disconnects for an hour, the slot preserves WAL, and after recovery, replication continues without a full pg_basebackup. However, monitoring is mandatory: if the standby is unavailable for a long time, WAL accumulates and can fill the disk. We recommend combining slots with wal_keep_size (e.g., 2 GB) — this provides a safety net against sudden overflow.
Comparison of Synchronous and Asynchronous Replication
| Parameter | Asynchronous | Synchronous (FIRST 1) |
|---|---|---|
| Data loss | Possible (milliseconds to seconds) | Zero data loss |
| COMMIT latency | Minimal (no waiting) | Latency = RTT + fsync on standby |
| Performance | High | 10-30% lower depending on network |
| RPO | Usually < 1 s | 0 (with proper network) |
Synchronous replication ensures zero data loss but slows writes by 10-30% compared to asynchronous.
Monitoring Replication Lag
The most accurate method is the query SELECT now() - pg_last_xact_replay_timestamp() on the standby. In production, we configure alerts via Prometheus: if lag exceeds 10 seconds, the on-call engineer receives a Telegram notification. We also monitor WAL volume in slots (the restart_lsn field). When a standby is down for long (more than 1 hour), a slot can accumulate up to 50 GB of WAL — a disk threat. We recommend setting automatic alerts when slot usage reaches 80%. On servers with SSD and 10GbE network, latency is typically < 1 ms.
Work Process
- Analysis — audit current configuration, load, and SLA for RPO/RTO.
- Design — select mode (async/sync), network, slots, monitoring.
- Implementation — configure postgresql.conf and pg_hba.conf on primary, create replication user, perform pg_basebackup, configure standby.signal and recovery.conf, then verify connectivity.
- Testing — verify lag, failover, and performance in synchronous mode.
- Documentation — runbook, alerts, on-call instructions.
Common Mistakes and Their Consequences
| Mistake | Consequence | Solution |
|---|---|---|
| Missing replication slots | Full pg_basebackup on disconnect | Use replication slots |
| No WAL disk monitoring | Disk fills, service crashes | Alerts at 80% slot usage |
| Incorrect synchronous settings | On latency-sensitive services, performance drops 30% | Load test |
What's Included
- Configuration of primary and standby tailored to your hardware.
- Creation of replication slots and monitoring of their usage.
- Setup of alerts on lag (e.g., when delay exceeds 10 seconds).
- Optional: installation of Patroni for automatic failover.
- Handover of documentation and training for your team.
Timeline and Cost
Configuring a two-node cluster with PostgreSQL streaming replication takes 4-8 man-hours. If you need the full package with monitoring, alerting, and Patroni, the timeline extends to 1-3 days. Typical cost for a two-node cluster setup, including monitoring and documentation, is $3,500. Cost is calculated individually — we will estimate the project after a brief. Get a consultation from an engineer — we will help you choose the optimal scheme for your SLA. Order an audit of your current PostgreSQL configuration. Contact us for cost estimation.
We have configured replication for databases from 500 GB to 5 TB — in all cases, we achieved lag < 1 second in asynchronous mode. Over 10+ years of experience, we have had zero data loss incidents with properly configured synchronous replication.
More details in the PostgreSQL Documentation.