Your web application is growing: 10,000 queries per second, the database is choking, LCP has climbed to 3 seconds. Users are leaving. The solution is setting up Master-Slave replication on PostgreSQL. This also saves up to 40% on read infrastructure costs—replicas can run on cheaper instances. Our master-slave replication solution uses WAL streaming and Patroni for automatic failover, ensuring a high-availability database. We handle complete PostgreSQL replication setup including pg_hba configuration and replication slots. We configure a resilient cluster in 2-3 days.
Challenges We Address with Master-Slave Replication
At 10,000 read queries per second, replication reduces LCP from 3 seconds to 200 ms. We use hot standby—replicas serve SELECT queries, offloading the primary. Synchronous replication guarantees zero data loss but consumes 30% more resources due to waiting for acknowledgment. Asynchronous replication is faster but may lose the last transaction (less than 1% loss on failure). Choice depends on consistency requirements: for financial systems—synchronous, for web apps—asynchronous. Infrastructure budget savings can reach 30-50% by using replicas for reads.
How We Set Up Automatic Failover with Patroni
Patroni is the standard for automatic PostgreSQL failover in production. It uses a distributed lock via etcd. When the primary fails, Patroni automatically promotes the replica with the smallest lag, minimizing downtime to under 10 seconds. For example, Patroni is 3x faster than repmgr in failover scenarios, reducing downtime from 30s to under 10s. Operational costs are reduced by 40% by using replicas for reads and avoiding expensive high-availability hardware.
Setup steps:
- Deploy an etcd cluster of 3 nodes.
- Install Patroni on each PostgreSQL server.
- Configure Patroni with etcd endpoints.
- Start Patroni—it automatically elects a master.
- Configure HAProxy to route write queries to master and read queries to replicas.
How We Configure Replication
Architecture: The application writes only to the primary; reads go to replicas. WAL is transferred via streaming replication.
Primary and Replica Configuration
# Primary postgresql.conf wal_level = replica max_wal_senders = 5 wal_keep_size = 1GB max_replication_slots = 5 synchronous_commit = on # pg_hba.conf host replication replicator 10.0.1.0/24 scram-sha-256 # Create replication user (run on primary) -- CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password'; # On replica: run pg_basebackup # pg_basebackup -h 10.0.1.10 -U replicator -D /var/lib/postgresql/14/main -P -Xs -R # Replica postgresql.conf hot_standby = on hot_standby_feedback = on max_standby_streaming_delay = 30s Monitoring and Management
Replication slots ensure the primary does not delete WAL until received by replicas. Without slots, a replica restart may require full resync. Monitor lag:
-- Monitoring replication slots SELECT slot_name, active, restart_lsn, confirmed_flush_lsn, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_bytes FROM pg_replication_slots; -- Monitoring replica lag SELECT application_name, client_addr, state, write_lag, flush_lag, replay_lag FROM pg_stat_replication; Comparison: Synchronous vs. Asynchronous Replication
| Parameter | Synchronous Replication | Asynchronous Replication |
|---|---|---|
| Write latency | +30-50% | 0-5% |
| Data loss | 0 | ≤ 1 transaction |
| Read performance | High | High |
| Recommended for | Financial systems | Web applications |
Comparison: Failover Tools
| Tool | Coordination | Failover time | Complexity |
|---|---|---|---|
| Patroni | etcd/Consul | <10 s | Medium |
| repmgr | Standalone | <30 s | Low |
| PAF (Pacemaker) | Corosync | <20 s | High |
What Automatic Failover with Patroni Delivers
Automatic failover eliminates manual intervention when the primary fails. Downtime is reduced to under 10 seconds, critical for services requiring 99.9% uptime. Additionally, Patroni allows planned switchovers without stopping the application.
What Is Included in the Work (Deliverables)
This work includes the following deliverables:
- Deployment of Master and 1-2 replicas with optimized WAL parameters.
- Configuration of pg_hba, replication slots, and monitoring.
- Setup of PgBouncer for connection pooling and request routing.
- Installation and configuration of Patroni with etcd for automatic failover.
- Application integration: configuring read/write routing.
- Lag and performance monitoring (Prometheus + Grafana).
- Documentation: detailed operational guide.
- Access: SSH keys, database credentials, monitoring dashboards.
- Training: 2-hour session for your team.
- Support: 1 month of post-launch support.
Timelines and Cost
- Basic setup (Master + 1 replica, without failover): 1 day.
- Full cluster with Patroni and monitoring: 2-3 days.
Cost is calculated individually based on infrastructure complexity. Basic setup costs start at $2,000, and automatic failover adds $1,500. Potential monthly savings on read infrastructure exceed $1,000. Get a consultation—we will analyze your project free of charge and propose the optimal solution. Order the setup now and ensure 99.9% uptime!
Our Experience
We have completed over 50 projects configuring PostgreSQL clusters for web applications with loads up to 100,000 queries per second. Our engineers hold PostgreSQL Professional certifications and regularly speak at industry conferences. We guarantee quality and post-launch support.