Connection Routing for HA: VIPs, Pooler, and Read Scaling

The one thing to understand first

Replication and automated failover handle the database side of high availability, but they are useless if clients keep connecting to a dead or demoted node. Connection routing is how applications always reach the current primary for writes (and optionally a standby for reads), and how that target switches atomically during failover. Getting this layer wrong reintroduces the split-brain and downtime that the cluster machinery worked to prevent.

The routing layer must derive "who is primary" from the same source of truth as failover — never an independent guess. A VIP, HAProxy, or pooler that picks the primary on its own can point clients at the wrong node and undo every split-brain protection beneath it.

Approach 1: Virtual IP (VIP)

A floating virtual IP is assigned to whichever node is currently primary. The application always connects to the VIP; during failover the orchestration moves the VIP to the new primary. Clients need no awareness of which physical node is the leader.

• Pro: simple for clients; one stable address.
• Con: requires the same L2 network (or cloud API to reassign the IP); the move must be coordinated with promotion to avoid pointing at two primaries.

Approach 2: HAProxy with health checks

A load balancer like HAProxy sits in front and routes based on a health check that asks each node "are you the primary?" Patroni exposes a REST endpoint for exactly this; HAProxy queries it and sends traffic only to the node reporting leader status:

backend postgres_primary
  option httpchk GET /primary          # Patroni REST health check
  http-check expect status 200
  server pg1 10.0.0.1:5432 check port 8008
  server pg2 10.0.0.2:5432 check port 8008
  server pg3 10.0.0.3:5432 check port 8008
# Only the node answering 200 on /primary receives connections.

On failover the old primary stops returning 200 and the new one starts; HAProxy redirects automatically — no VIP juggling required.

Approach 3: pooler-based routing

A connection pooler (PgBouncer) can be the redirection point: on failover, the orchestration rewrites the pooler's target to the new primary and reloads it. Clients keep their connections to the pooler while the backend target changes underneath. This pairs naturally with the pooling you likely already run for connection scaling.

Splitting reads to standbys

HA standbys can do double duty as read replicas. Two common patterns:

• Separate endpoints: a "primary" endpoint (writes) and a "replica" endpoint (reads) — e.g. two HAProxy backends, one health-checking /primary and one /replica. The application sends reads to the replica endpoint.
• Driver-level split: some drivers/libraries route read-only transactions to replicas and writes to the primary automatically.

backend postgres_replicas
  option httpchk GET /replica
  http-check expect status 200
  balance roundrobin
  server pg2 10.0.0.2:5432 check port 8008
  server pg3 10.0.0.3:5432 check port 8008

Layer 3 — Watch it happen on your own database

Whichever approach you pick, the end-to-end flow is the same — trace it and you can see exactly where a stale or doubled route would creep in:

• Clients connect to a stable front (VIP, HAProxy, or pooler) — never directly to a node.
• Health checks tied to the orchestration determine the current primary.
• On failover, the front redirects atomically; no client reconfiguration.
• Reads optionally fan out to standbys via a separate replica endpoint, with lag-awareness for read-after-write.

You can prove the routing is correct from any client: ask the connection itself which node it landed on, then trigger a failover and watch the answer change without reconnecting through a new address:

-- Am I on the primary or a standby?
SELECT pg_is_in_recovery();        -- false = primary, true = standby
SELECT inet_server_addr();         -- which physical node this session reached
-- After a failover, the same VIP/HAProxy endpoint now returns false from the NEW node.

If pg_is_in_recovery() ever returns true on your write endpoint, your routing layer is pointing writes at a standby — the bug this whole layer exists to prevent.

Layer 4 — The levers this hands you

Connection routing gives you two policy decisions and one hard rule:

• Read-staleness policy (read-after-write). Routing reads to async standbys risks stale reads — a user writes, then immediately reads from a replica that hasn't replayed the change yet. Mitigations: send must-be-current reads to the primary, use synchronous_commit = remote_apply for those writes, or have the app track an LSN and wait for the replica to reach it. Decide this explicitly per query path.
• Atomic cutover. Make the switch atomic so clients can never reach two primaries at once — the routing move must be coordinated with promotion.
• Single source of truth (the hard rule). The routing layer must read "who is primary" from the same DCS/Patroni that drives failover — health-check the orchestration's /primary endpoint rather than guessing from a connection probe.

Layer 5 — What an Oracle DBA should expect vs what they get

Oracle bakes this layer into the client and listener stack; in Postgres you assemble it yourself:

• HAProxy/VIP routing ≈ SCAN listeners + transparent connection redirection. Oracle's SCAN and Data Guard role-based services route clients to the current primary automatically; Postgres needs an external VIP, HAProxy, or pooler to play that role.
• Read/write split ≈ role-based services / Application Continuity. Oracle can route read-only work to an Active Data Guard standby via services; Postgres does it with separate HAProxy backends (/primary vs /replica) or driver-level read/write splitting.
• No transparent failover of in-flight sessions. Oracle TAF / Application Continuity can replay an interrupted transaction; Postgres connections simply drop on failover and the app must reconnect and retry — there is no built-in equivalent.
• Read-after-write lag is the DBA's problem. Where Oracle's services and SCN-aware reads can hide some of this, Postgres makes you choose the staleness policy per query path yourself.

Key takeaway

Connection routing is the missing half of HA: clients must reach a stable front (VIP, HAProxy, or pooler) that derives the current primary from the same source of truth as failover and cuts over atomically, so writes never land on a demoted node or two primaries at once. Decide your read-after-write staleness policy per query path, and verify routing with pg_is_in_recovery() on each endpoint.