How does a libpq connection string listing several PostgreSQL hosts with target_session_attrs=read-write find a newly promoted primary, and what does it not do?
answer
- decided at connect time only
- hosts tried in listed order
- in_hot_standby reported at startup
- connect_timeout counts per host
basics
~20 slibpq tries each listed host in order and, with target_session_attrs=read-write, keeps only a server not in hot standby and not default read-only. It acts only when connecting: open connections break at failover and the application must reconnect.
solid answer
~40 sA string such as `host=pg-tix-a,pg-tix-b dbname=tickets target_session_attrs=read-write connect_timeout=3` makes libpq try the hosts in order, connect and authenticate, then check the session: `read-write` accepts a server that is not in hot standby and has `default_transaction_read_only` off; `primary` checks only hot standby; `any` is the default. Servers from 14 on report `in_hot_standby` and `default_transaction_read_only` at startup, so the check usually needs no extra query. A failed check closes that connection and moves on; a failed **authentication** stops the whole list. `connect_timeout` applies to each host separately, and `load_balance_hosts=random` shuffles the order. What it does not do: move existing sessions, retry the failed transaction, split reads from writes, or fence an old primary that comes back writable.
code
bash · 2 linespsql 'postgresql://app@pg-tix-a:5432,pg-tix-b:5432/tickets?target_session_attrs=read-write&connect_timeout=3' \
-c 'SELECT inet_server_addr(), pg_is_in_recovery();'go deeper
Know that a libpq connection string can list several hosts and that target_session_attrs=read-write makes it pick the writable one.
Explain the six modes, the difference between primary and read-write, per-host connect_timeout, and that the check happens only at connect time.
Design the client side of a failover: retries with backoff, idempotent transactions, what happens if the old primary returns writable, and how long reconnection can take.
Decide when driver-level host lists are enough and when a proxy or VIP layer earns its operational cost, given the drivers your services actually use.
## The problem it solves After a promotion the primary lives on another host, and something has to tell clients where to write. Virtual IPs and proxies are one answer, covered by the connection-routing concept leaf. PostgreSQL's client library, **libpq**, has a built-in one: a connection string can list several hosts and state what kind of server the session needs. psql, the C API and the many drivers built on libpq get it for free; drivers that implement the wire protocol themselves offer their own equivalent options. ## The connection string The same setting in keyword/value form and in URI form: ``` host=pg-tix-a,pg-tix-b port=5432 dbname=tickets user=app target_session_attrs=read-write connect_timeout=3 postgresql://app@pg-tix-a:5432,pg-tix-b:5432/tickets?target_session_attrs=read-write&connect_timeout=3 ``` In keyword form `host`, `hostaddr` and `port` take comma-separated lists of equal length, except that a single `port` applies to every host. Every other option is shared: you cannot give each host its own user. The environment variable `PGTARGETSESSIONATTRS` sets the same option. ## The six target_session_attrs modes | Value | Accepts a server that | |---|---| | `any` (default) | answers at all | | `read-write` | is not in hot standby **and** has `default_transaction_read_only` off | | `read-only` | would not accept read-write transactions by default: a standby, or a primary set read-only | | `primary` | is not in hot standby | | `standby` | is in hot standby | | `prefer-standby` | is a standby if any listed host is one; otherwise any server | ## How libpq picks a host 1. It tries hosts in the listed order, and each host's addresses in the order DNS returns them, unless `load_balance_hosts = random` shuffles both levels. 2. For each it connects and authenticates. An unreachable host is skipped; an **authentication failure** ends the attempt without trying the remaining hosts. 3. It checks the new session against `target_session_attrs`. Servers from 14 on report `in_hot_standby` and `default_transaction_read_only` as parameter status at startup, so libpq can decide without a query; against older servers it sends a query instead, `SHOW transaction_read_only` or `SELECT pg_catalog.pg_is_in_recovery()` depending on the mode. 4. A server that does not match is disconnected and the next host is tried. If none matches, the connection attempt fails. `connect_timeout` is **per host and per address**. With three single-address hosts and `connect_timeout=3`, a client can spend about nine seconds before giving up. Without it, a host that silently drops packets can hold the attempt for as long as the operating system's TCP connect timeout. ## What it does not do - **It does not move open connections.** When the primary dies, sessions on it break. The application must catch the error, reconnect and retry the transaction; libpq only acts while connecting. - **It does not split reads and writes.** One connection goes to one server. Sending reads to standbys needs a second connection string, for example with `target_session_attrs=standby`, or a proxy. - **It does not fence.** If the old primary comes back writable, a `read-write` connection whose list puts it first will use it. Keeping the old primary stopped until it has been rewound is the operator's job. - **It does not wait for promotion.** In the seconds before the standby finishes promoting, every host fails the `read-write` check; a retry loop with backoff in the application covers that gap. ## Choosing a mode per pool - **Writes**: `read-write`, so a session never lands on a standby or on a primary set read-only for maintenance. - **Reports and other stale-tolerant reads**: `prefer-standby` with `load_balance_hosts=random`, which spreads sessions over the standbys and still connects to the primary if no standby is up. - **Reads that must never touch the primary**: `standby`, accepting that the pool fails while no standby is available. - **Tools that only need any server**: leave the default `any`. Which reads tolerate replication lag is an application decision covered by the read-replica concept leaf; libpq only chooses the server. ## In a planned switchover On the ticketing platform both application pools list `pg-tix-a,pg-tix-b` with `target_session_attrs=read-write` and a short `connect_timeout`, and the checkout path retries on connection errors. When `pg-tix-b` is promoted and `pg-tix-a` rejoins as its standby, `pg-tix-a` now reports `in_hot_standby = on`, so new connections skip it and land on `pg-tix-b`, with no configuration change and no DNS update.
- In libpq, what is the difference between target_session_attrs=primary and read-write?`primary` only requires that the server is not in hot standby. `read-write` also requires `default_transaction_read_only` to be off. A primary deliberately set read-only, for example during maintenance, is accepted by `primary` but skipped by `read-write`.
- Why set connect_timeout in a libpq multi-host string used for PostgreSQL failover?Each host gets its own timeout. Without one, a host that drops packets instead of refusing them keeps the attempt waiting for the operating system's TCP timeout before libpq moves to the next host, which can stall every new connection for a long time during an outage.
- What happens to read-write clients if the old PostgreSQL primary restarts writable after a failover?libpq accepts the first writable host in its list, so clients listing the old primary first can write to it while others write to the new primary: split brain. libpq cannot detect that; the old primary must stay down, or be fenced, until it has been rewound into a standby.
- libpq moves open connections to the new primary automatically after promotion.
- target_session_attrs=read-write sends writes to the primary and reads to standbys.
- connect_timeout limits the whole host list rather than each host.
- If authentication fails on the first host, libpq simply tries the next one.
- target_session_attrs=any skips servers that are in hot standby.