Client routing: DNS, VIP, proxy, service discovery · PostgreSQL

A promotion that clients cannot find is not a recovery. This is frequently the largest term in the real RTO and the least examined part of an HA design.

The mechanisms

Mechanism	Failover time	Fails by
DNS change	TTL + caching	Clients caching past the TTL
Virtual IP	Seconds	Both nodes claiming it; ARP not converging
Proxy (HAProxy, pgpool)	Health-check interval	Being a single point of failure itself
`target_session_attrs`	Connection time	Only helping at connect
Service discovery	Watch latency	The discovery system being down

`target_session_attrs`

libpq accepts a list of hosts and an attribute the chosen host must satisfy.

Read-only / Safeasking for the primary, with the standby listed first— EXECUTION VALIDATED on 18.6 after a real failover. rbpg-sb (172.26.0.3) is the primary; rbpg-prim2 (172.26.0.2) is its standby.

$ psql "postgresql://postgres@rbpg-prim2,rbpg-sb:5432/postgres?target_session_attrs=read-write" \
-c "SELECT inet_server_addr(), pg_is_in_recovery()"

 172.26.0.3    in_recovery=false        <- the primary, listed second

Read-only / Safeasking for a standby, with the primary listed first— EXECUTION VALIDATED on 18.6.

$ psql "postgresql://postgres@rbpg-sb,rbpg-prim2:5432/postgres?target_session_attrs=standby" \
-c "SELECT inet_server_addr(), pg_is_in_recovery()"

 172.26.0.2    in_recovery=true         <- the standby, listed second

libpq tried each host in order and kept the first one matching. List order did not matter; the attribute did.

The values are any (default), read-write, read-only, primary, standby and prefer-standby.

What the application must do

Routing gets a new connection to the right place. These are the application’s job:

Notice the connection is dead. Many pools discover this only on next use, so the first query after a failover fails no matter how fast the failover was. A pool with a validation query or a TCP keepalive notices sooner.

Reconnect without stampeding. Two hundred clients reconnecting simultaneously against a cluster that has just started with a cold cache is a load event on top of an incident. Jittered backoff.

Retry safely. A transaction interrupted mid-flight may or may not have committed. Retrying a non-idempotent operation blindly is how a failover produces duplicate charges. This is application design, not configuration.

Fail loudly if the new primary is not writable. A client that silently ends up on a standby and treats cannot execute INSERT in a read-only transaction as a transient error will retry forever.

# libpq keepalives, so a dead connection is noticed rather than hung on
keepalives=1 keepalives_idle=30 keepalives_interval=10 keepalives_count=3
connect_timeout=5

Those settings matter more than most of the routing discussion, because without them a broken connection can hang for the operating system’s default TCP timeout — which can be many minutes.

The proxy trade, and why the sequencing bug is the one to look for

A proxy is the most common answer and it introduces a problem worth naming: it is now in the path of every query. Its availability becomes your availability, and it must be made redundant, which means you now have two HA problems.

That is usually still the right trade, because a proxy solves things nothing else does: it can hold a connection open while the backend changes underneath, drain connections rather than dropping them, and route by asking the HA stack directly rather than by guessing.

The important detail is how it decides which backend is primary, and there are two approaches with very different properties.

Asking PostgreSQL — SELECT pg_is_in_recovery() as a health check — is self-contained and it races. A node’s answer changes at the moment of promotion, and between the old primary answering false for the last time and the new one answering false for the first, there is a window where a proxy can believe in two primaries or none.

Asking the HA stack — Patroni’s REST API, or the consensus store — is authoritative, because the same source that decided the failover is answering the question. The proxy cannot disagree with the stack about who the leader is, which is exactly the property you want.

The sequencing bug this prevents is worth stating plainly, because it is common and it produces split-brain from a correct failover: the proxy notices the standby has become writable and starts sending writes to it before the old primary has been fenced. Both are now receiving writes. The failover was fine; the routing was ahead of it.

Whatever mechanism you use, the ordering must be fence, then promote, then route — lesson XV-03’s rule extended one step. A proxy that routes on its own health checks cannot honour that ordering, because it does not know the fencing happened. One that asks the HA stack can.

What to take from this

Four mechanisms, four failover times, four failure modes.
target_session_attrs picks a host by role at connect time, and list order does not matter — measured both ways after a real failover.
It does nothing for connections already open. Those break, and the application’s reconnect behaviour is your real RTO.
Set libpq keepalives and connect_timeout, or a broken connection can hang for minutes.
DNS TTL is a lower bound. Measure what your stack actually does.
A proxy should ask the HA stack who the primary is, not ask PostgreSQL. Fence, promote, then route.

Cross-course references

Linux for Production Sysadmins — Part XXIII (DNS) covers TTL and negative caching, which decide how long clients keep using a dead primary, and Part LVI (Keepalived and VRRP) covers the VIP alternative.
Kubernetes for Production Sysadmins — Part XXXVIII (Services), Part XXXIX (Service discovery) and Part XLI (CoreDNS) cover the same decision inside a cluster.
Observability for Production Sysadmins — Part LXIII (Synthetic monitoring) covers proving from outside that clients reach the current primary, which no database metric can tell you.

The mechanisms

target_session_attrs

What the application must do

What to take from this

Cross-course references

Quiz

`target_session_attrs`