What PostgreSQL setup is best for high availability?

For high availability (HA) with PostgreSQL, the “best” setup is usually:

Recommended baseline

Primary + one or more physical replicas + automatic failover manager + connection routing

A common production pattern is:

• 1 primary PostgreSQL instance
• 1–2 standby replicas using streaming replication
• Automatic failover handled by a tool such as:
  • Patroni (very common, flexible, works well with Kubernetes too)
  • repmgr (simpler to operate)
  • pg_auto_failover (good if you want a more opinionated solution)
• A VIP / load balancer / proxy in front of the cluster for client connections:
  • HAProxy
  • PgBouncer for pooling
  • Keepalived for virtual IPs
  • or a managed service’s built-in endpoint

Best-practice architecture

A solid HA design looks like this:

• 3 nodes minimum
  • 1 primary
  • 2 replicas
• Synchronous replication if you need minimal data loss
• Asynchronous replication if you prefer lower latency and can tolerate some data loss
• Witness / quorum mechanism for split-brain prevention
• Automated failover
• Application retry logic for transient errors
• Backups + point-in-time recovery (PITR) for disasters, not just failover

Which option is “best”?

It depends on your goals:

If you want the safest general-purpose HA

Patroni + etcd/Consul + HAProxy + streaming replication

Why:

• mature and widely used
• supports automatic leader election
• handles failover cleanly
• good operational visibility

If you want simpler operations

repmgr + streaming replication

Why:

• easier to understand
• less moving parts
• good for smaller teams

If you want the least operational burden

Use a managed PostgreSQL service with HA built in:

• AWS RDS / Aurora PostgreSQL
• Google Cloud SQL
• Azure Database for PostgreSQL
• Crunchy Bridge, Aiven, etc.

This is often the best choice if you don’t want to manage failover, monitoring, backups, and upgrades yourself.

Important note: HA is not backup

HA protects against:

• instance failure
• host failure
• some network issues
• planned maintenance with minimal downtime

But it does not fully protect against:

• accidental DELETE
• corrupted data replicated to all nodes
• schema mistakes
• ransomware
• regional disasters

You still need:

• regular backups
• WAL archiving
• tested restore procedures
• ideally off-site copies

My practical recommendation

If you’re self-hosting and need strong HA:

3-node PostgreSQL cluster with Patroni + synchronous replication for critical writes + HAProxy + PgBouncer + PITR backups

If you’re in the cloud and want the easiest robust answer:

Use a managed PostgreSQL service with multi-AZ / regional HA enabled

If you tell me:

• on-prem or cloud
• acceptable downtime
• acceptable data loss
• expected write volume
• Kubernetes or VMs

…I can recommend a specific HA design.