High Availability and Failover: RPO/RTO, Listeners, Patroni and Making the Application Survive Failover

Version note: The examples in this post were run in a single-machine lab with PostgreSQL 18.7 (two containers: a primary and a streaming replica), SQL Server 2025 (CU10, 17.0.5035.5), .NET 10, Npgsql 10.0.3, Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3, Microsoft.EntityFrameworkCore.SqlServer 10.0.15 and Microsoft.Data.SqlClient 7.1.2. We triggered failover by hand (pg_promote()); Patroni, the SQL Server availability group listener and the high availability options of managed services were not set up in the lab and are described from their documentation.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, runs PostgreSQL with a primary and an async streaming replica. The replica's network connection drops for a moment; meanwhile customers place 50 orders, all committed on the primary. Right after that the primary's server crashes and the on-call engineer promotes the replica. The last state on the primary:

SELECT max(order_id) AS last_order_on_primary FROM orders;
SELECT application_name, state FROM pg_stat_replication;

 last_order_on_primary
-----------------------
               1000600

 application_name |   state
------------------+-----------
 pg2              | streaming

The primary hasn't noticed yet that the replica's connection is gone; pg_stat_replication still shows streaming. Guess: after promoting the replica, how many of those 50 orders are there?

SELECT pg_promote();
SELECT max(order_id) AS last_order_on_new_primary FROM orders;

 last_order_on_new_primary
---------------------------
                   1000550

None. 50 orders that customers were told "your order has been received" are gone. In post 04 we saw the loss risk of an async replica in theory; in this post we turn it into measurable targets, take failover apart and, most importantly, measure how the application survives failover across two real failovers.

RPO, RTO, HA and DR

Two numbers should open every conversation: RPO, the most data you accept losing in a failure, measured in time, and RTO, how quickly the service must be able to write again. In the scenario above the RPO wasn't zero: every commit that hadn't reached the replica was lost. The RTO was however long it took the on-call engineer to wake up and run the command. Two families of tools serve these targets: HA, keeping the service going through a single failure with a short interruption, and DR, bringing data and service back after a large disaster such as losing a region or data corruption.

Neither replaces the other. A backup isn't HA: restoring from a backup can take hours. A replica isn't a backup either: a DELETE run by mistake on the primary is applied to the replica within milliseconds; going back to before that moment needs point-in-time recovery. For Bookstore the team agreed on these targets: for payment and order data, HA with an RPO of zero and an RTO under one minute; DR with an RPO of five minutes and an RTO of one hour.

The Parts of Automatic Failover

What turns a manual failover into an automatic one is four parts. Health checking: knowing the primary is really down (not just slow). Decision: deciding with a quorum which replica becomes the new primary; if two nodes decide on their own, both can become primary (split brain). Fencing: guaranteeing the old primary won't accept writes if it comes back. Routing: clients finding the new primary (a listener, DNS, a proxy, or the client trying several hosts). The theory of the first three is in the Distributed Systems Fundamentals series posts "Consensus and Raft" and "Distributed Locks, Leases and Fencing Tokens"; here we look at how the engines solve them.

In SQL Server, an Always On availability group builds these parts with a Windows Server Failover Cluster, or with Pacemaker on Linux. Automatic failover requires the replica to be in synchronous commit and automatic failover mode; to an asynchronous replica you can only fail over by hand, accepting data loss (forced failover). Routing is done by the AG listener: clients connect to a DNS name and the listener points to whichever replica is primary at the moment. Microsoft recommends that clients connecting to a listener put MultiSubnetFailover=True in their connection string: SqlClient tries all of the listener's IP addresses in parallel and connects to the new address faster after a failover. The business critical tiers of Azure SQL Database and Azure SQL Managed Instance and Amazon RDS's Multi-AZ option offer this mechanism as a service. We didn't try this paragraph in the lab; it's based on documentation.

PostgreSQL itself has no automatic failover; pg_promote() only turns a replica into a primary. An external tool makes the decision and does the fencing. The most common is Patroni (4.1.5 on the day of writing): it runs next to each node, keeps a time-limited leader record in a distributed configuration store such as etcd, Consul, ZooKeeper or Kubernetes, demotes a primary that can't renew the record and elects a new leader. The /primary endpoint of its management API over HTTP returns 200 only on the leader; a load balancer like HAProxy uses that endpoint to route write traffic to the right node. Azure Database for PostgreSQL's zone-redundant HA and RDS Multi-AZ do the same job as a service. We didn't set these up in the lab either. In the lab we triggered failover by imitating what Patroni would do by hand: we killed the primary container with docker kill and ran pg_promote() on the replica a few seconds later.

How Does the Application Survive Failover? First Attempt

The face of a failover users see is the application. Npgsql can build a data source that knows several hosts; with TargetSessionAttributes.Primary it hands out connections only from the server that is primary:

Host=pg1:5432,pg2:5432;Database=bookstore;Username=bookstore_api;Password=<secret>;Timeout=3

NpgsqlDataSource dataSource = new NpgsqlDataSourceBuilder(Db.Env("BOOKSTORE_PG_HA")).BuildMultiHost()
    .WithTargetSession(TargetSessionAttributes.Primary);

DbContextOptions<CheckoutContext> contextOptions = new DbContextOptionsBuilder<CheckoutContext>()
    .UseNpgsql(dataSource, npgsql =>
        npgsql.EnableRetryOnFailure(maxRetryCount: 10, maxRetryDelay: TimeSpan.FromSeconds(2), errorCodesToAdd: null))
    .Options;

Checkout places an order every 100 ms. The order and everything that must be written with it are in one transaction, inside CreateExecutionStrategy so EF Core's execution strategy can run that transaction again as a whole:

Guid key = Guid.CreateVersion7();
await using CheckoutContext db = new(contextOptions);
IExecutionStrategy strategy = db.Database.CreateExecutionStrategy();
string server = await strategy.ExecuteAsync(async ct =>
{
    // the whole unit of work is retried together, with the same idempotency key
    await using IDbContextTransaction tx = await db.Database.BeginTransactionAsync(ct);
    await db.PlaceOrderAsync(key, 4242, 99.90m, ct);
    string host = await db.Database.SqlQueryRaw<string>("SELECT inet_server_addr()::text AS \"Value\"").SingleAsync(ct);
    await tx.CommitAsync(ct);
    return host;
}, CancellationToken.None);

At second 8 we killed the primary, at second 14 we promoted the replica. What the application saw (repeated lines shortened; 172.22.0.2 is pg1, 172.22.0.3 is pg2):

[  0.6s] order 1 written on 172.22.0.2/32
[  8.1s] ef: info: 11/14/2026 21:50:41.335 CoreEventId.ExecutionStrategyRetrying[10404] (Microsoft.EntityFrameworkCore.Infrastructure)
[  8.2s] order failed after retries: NpgsqlException: No suitable host was found.
[  8.3s] order failed after retries: NpgsqlException: No suitable host was found.
[ 18.1s] order failed after retries: NpgsqlException: No suitable host was found.
[ 18.2s] order 72 written on 172.22.0.3/32
attempts=377 placed=279 failed=98

Two things stand out. First, the retry kicked in only once. The broken connection's error was treated as transient, but the "No suitable host was found" that followed (one of two hosts is down, the other isn't primary yet) isn't on Npgsql's transient list; EF Core passed it to the user without retrying. 98 orders returned an error. Second, even though the replica became primary at second 14, writes started at 18.2. Npgsql caches the hosts' roles for Host Recheck Seconds (10 by default): after seeing pg2 as "standby" once, it didn't ask again for 10 seconds. The outage the user saw was about 10 seconds and 98 errors.

Second Attempt: A Retry That Waits for the Failover

We changed two settings. We added Host Recheck Seconds=1 to the connection string and widened the execution strategy to also retry connection-level Npgsql errors that aren't server errors:

public sealed class FailoverAwareRetryStrategy(ExecutionStrategyDependencies dependencies)
    : NpgsqlRetryingExecutionStrategy(dependencies, maxRetryCount: 10, maxRetryDelay: TimeSpan.FromSeconds(2), errorCodesToAdd: null)
{
    protected override bool ShouldRetryOn(Exception exception) =>
        base.ShouldRetryOn(exception) || exception is NpgsqlException and not PostgresException;
}

npgsql.ExecutionStrategy(dependencies => new FailoverAwareRetryStrategy(dependencies));

PostgresExceptions are errors returned by the server (constraint violations, syntax errors); retrying them makes no sense. The remaining NpgsqlExceptions are about not reaching a server. This time the roles were reversed: pg2 was primary, pg1 the replica. At second 8 we killed the primary, at second 14 we promoted the replica:

[  0.4s] order 1 written on 172.22.0.3/32
[  8.0s] retry: A transient exception occurred during execution. The operation will be retried after 0ms.
[  8.1s] retry: A transient exception occurred during execution. The operation will be retried after 1088ms.
[  9.2s] retry: A transient exception occurred during execution. The operation will be retried after 2000ms.
[ 11.2s] retry: A transient exception occurred during execution. The operation will be retried after 2000ms.
[ 13.2s] retry: A transient exception occurred during execution. The operation will be retried after 2000ms.
[ 15.2s] order 70 written on 172.22.0.2/32
attempts=211 placed=211 failed=0

No errors at all: the order placed at that moment waited 7 seconds and was written to the new primary. After the promotion, the outage lasted at most one retry interval. There's a trade-off: the HTTP request waits during the retries too. 10 retries with intervals of up to 2 seconds can keep a request waiting for up to 15 seconds; that shouldn't be longer than the HTTP timeout or the user's patience. The general rules of retries, backoff and jitter are in the System Design series post "Reliability Patterns".

Is Retrying Safe? Idempotent Writes

The danger of retrying is this: the commit may have completed on the primary while the connection dropped before the response reached the client. In post 04 we saw the "transaction has already committed locally" warning when a sync commit was canceled. In a case like that, an INSERT retried blindly creates the same order a second time. The solution is to make the work itself idempotent: every order request carries an idempotency key generated on the client, the key is unique in the database, and a repeated request does nothing:

ALTER TABLE orders ADD COLUMN idempotency_key uuid;
CREATE UNIQUE INDEX ux_orders_idempotency_key ON orders (idempotency_key);

public Task<int> PlaceOrderAsync(Guid idempotencyKey, int customerId, decimal total, CancellationToken ct) =>
    Database.ExecuteSqlInterpolatedAsync($"""
        INSERT INTO orders (customer_id, status, total_amount, created_at, idempotency_key)
        VALUES ({customerId}, 'Pending', {total}, now(), {idempotencyKey})
        ON CONFLICT (idempotency_key) DO NOTHING
        """, ct);

The key stays the same across retries, because it was generated outside the execution strategy. The count on the new primary after two failovers:

SELECT pg_is_in_recovery(), count(*) AS orders_with_key, count(DISTINCT idempotency_key) AS distinct_keys
FROM orders WHERE idempotency_key IS NOT NULL;

 pg_is_in_recovery | orders_with_key | distinct_keys
-------------------+-----------------+---------------
 f                 |             491 |           491

279 successful orders in the first attempt, 211 in the second, plus 1 row added by hand before: 491. No order was written twice, and no order reported as successful was lost (in both attempts the replica was caught up at the moment of failover). In a real checkout the key is a value the client (browser or mobile app) generates on every "Place order" attempt and sends to the API in a header, so a user's double click falls under the same rule. Details are in the Solution Patterns series post "Idempotency Keys".

The SQL Server Side: Dropped Connections and User Transactions

Microsoft.Data.SqlClient's idle connection resiliency notices that an idle connection was dropped on the server side and silently reconnects on the next command. ConnectRetryCount is 1 by default. We opened a connection, ended its session with KILL from another session and ran another query on the same SqlConnection:

ConnectRetryCount=1 before: spid=57 connection=751ba813-030c-4b98-9ee7-a530b28c6ae9
KILL 57 from another session
after:  spid=57 connection=650b9538-6fe2-4cda-bfbf-9f17cd04df78
ConnectRetryCount=0 before: spid=57 connection=b954e88c-4937-40f0-962c-a646da98c28c
KILL 57 from another session
SqlException 0: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 2 - Connection was terminated)

In the first run ClientConnectionId changed: SqlClient opened a new physical connection in the background (the spid is the same because the server reused the number). In the second run the same situation reached the application as an error. This feature is only for idle connections: a connection dropped in the middle of a transaction can't be recovered, which needs a retry at the application level. The pool also clears the affected pool when it sees a fatal error (such as a failover), as described in post 02.

The same rule applies to EF Core's retrying execution strategy for SQL Server, and EF Core reminds you with an error. With EnableRetryOnFailure on, starting the transaction outside the strategy gives:

await using IDbContextTransaction tx = await db.Database.BeginTransactionAsync();
OrderStatus order = await db.Orders.SingleAsync(o => o.OrderId == 1);
order.Status = "Paid";
await db.SaveChangesAsync();
await tx.CommitAsync();

InvalidOperationException: The configured execution strategy 'SqlServerRetryingExecutionStrategy' does not support user-initiated transactions. Use the execution strategy returned by 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as a retriable unit.

A strategy can't rerun half of a transaction; it's all or nothing. The same code worked when wrapped in strategy.ExecuteAsync (with CreateExecutionStrategy: updated=1). The pattern in the PostgreSQL example was the same.

Testing Failover

An untested failover is a failover that doesn't work. The two attempts above are a small example of a game day (a planned failure drill) that can be set up and repeated in minutes. Questions to answer regularly in a production-like environment: how many seconds do writes stop when the primary dies (measured RTO), which requests returned errors and what the user saw, how many operations were lost (measured RPO), what happens when the old primary comes back (does it join as a replica, or become a second primary), and did the alerts fire. The Bookstore team decided to do this every quarter, before a campaign period. In post 10 we'll make this drill part of the end-to-end scenario.

Production Notes

Watch RPO together with replica lag. With an async replica, the possible loss is the lag at the moment of failover; a lag alert is really an RPO alert.

Don't bring the old primary straight back. An old primary that comes back on its own may have diverged from the new primary with the writes it took while disconnected. In PostgreSQL it should join as a replica with pg_rewind or a fresh pg_basebackup; Patroni does this itself. In the lab, after each failover we deleted the old primary and rebuilt it from the new primary.

Think of timeouts as a chain. The connection timeout, the retry count and interval, the HTTP request's timeout and the load balancer's timeout must fit together. If the HTTP request fails before the retries finish, the user sees an error while the work may still complete behind the scenes; that's why the idempotency key is required.

Account for the connection storm after a failover. The pools of all pods try to connect to the new primary at the same time. The connection budget and PgBouncer from post 02 apply here too; if you use PgBouncer, it also has to find the new primary.

Common Pitfalls

Turning on retries for non-idempotent operations: a retry does work again that was thought to have failed but was actually committed. Make the work idempotent first, then turn on retries.

Trusting the default transient list: in the lab "No suitable host was found" wasn't retried and 98 orders returned an error. Measure which exceptions actually arrive during a failover and tune the strategy accordingly.

Starting the transaction outside the strategy: EF Core catches this with an error on SQL Server, but hand-written retry loops can repeat half a transaction.

Assuming zero RPO with an async replica: the 50 orders at the start of the post were lost exactly this way. Zero RPO needs sync replication and at least two replicas (post 04).

Never trying failover: a misconfigured listener connection string, a cached DNS record or a default like Host Recheck Seconds only shows up in a real failover.

A Small Experiment

★☆☆ Repeat the first attempt with Host Recheck Seconds=1 but the default execution strategy. How do the outage time and error count change?

★★☆ Repeat the async loss experiment with synchronous_standby_names = 'pg2'. What happens to orders placed while the replica's network is down, and how many orders are lost when you kill the primary and promote the replica?

★★★ Move checkout to an ASP.NET Core endpoint: take the idempotency key from the Idempotency-Key header, and have a second request with the same key return the first order's id. Verify the result with a client that sends the same request twice during a failover.

In the next post we use the replica for read capacity: routing reads to the replica and writes to the primary in .NET, SQL Server's read-only routing and Npgsql's prefer-standby, and the read-your-writes problem that ends with a user not seeing the order they just placed in the list.