High availability for a small SaaS backend

Harrow Lane's morning with and without a standby

Take a hypothetical vendor, Harrow Lane Scheduling, whose booking software salons keep open at the front desk all day. Its backend is 11 virtual machines, including a 200 GB PostgreSQL database backed up nightly at 1:00 a.m. When the software stops, salons take bookings on paper, and the first thing each one asks is how long it will last, so "we have backups" satisfied nobody after Harrow Lane's previous outage.

At 9:40 the database host's disk controller fails. Monitoring pages the engineer on call at 9:43, and by about 10:00 the engineer has given up on the host and starts restoring onto a new server. Copying 200 GB at an assumed 200 MB/s takes about 17 minutes (1,000 seconds). The rest of the morning goes to building the server, replaying WAL, repointing the application, and checking it. Without continuous WAL archiving, every booking since 1:00 a.m. is gone. Continuous archiving brings the restore up to the last archived segment, and on a quiet database archive_timeout caps how stale that segment can get, at the price of a full 16 MB file in the archive for every forced switch. Plan your next restore covers recovery targets and restore testing.

Now give the same database a streaming standby managed by Patroni on its default settings. The primary dies at 9:40:00. Its leader lock in etcd expires at most 30 seconds after the last renewal, and the standby's Patroni loop checks every 10 seconds, so promotion starts within about 40 seconds. Harrow Lane's connection string names both database hosts with a 5 second connect timeout, so new connections reach the promoted node within seconds. Pooled connections still open to the dead host can hang far longer unless the pool sets a TCP timeout. The bookings lost are the commits that had not reached the standby, an amount that tracks the replication delay at the moment of failure.

Managed databases land in the same range: AWS says RDS Multi-AZ failovers typically take 60 to 120 seconds. RDS keeps that standby synchronous, so acknowledged commits survive, and every open connection still drops while the instance's DNS name moves to the standby. A self-managed pair on asynchronous replication can lose its last few commits, and its old primary has to be fenced off and rebuilt, although the rebuild can wait for a quiet hour.

Bad deletes, broken migrations and other failures promotion cannot fix

A standby replays every change the primary makes, so a DELETE without a WHERE clause or a release migration that drops a column reaches it as soon as the change streams across. Promotion helps when a server or a database process dies and when a host needs a reboot for patching. The other four rows in the table recover from backups. Restoring to a point in time needs continuous log archiving (WAL on PostgreSQL, log backups on SQL Server) and takes as long as Harrow Lane's morning restore. On Petrichor, a customer files a ticket and a Petrichor engineer takes the backup or runs the restore.

A delayed standby shortens recovery from a bad statement. With recovery_min_apply_delay = '1h', a replica holds an hour of WAL before applying it, so a bad statement caught within the hour can be recovered by stopping replay short of it, which is faster than a full restore. That replica never stands for election.

Count the servers with no partner

Harrow Lane's three web and API servers already take traffic through a reverse proxy, which stops routing to any server that fails. They need no partner once sessions move out of process memory and uploads move off local disk. Both of those are code changes for Harrow Lane's developers.

Five servers have no partner: the reverse proxy, the worker that runs the scheduler, the PostgreSQL primary, the Redis server holding sessions and the job queue, and the NFS server with customer uploads. The monitoring VM stands alone as well, and it carries the third vote for both the database and Redis. The design adds three servers (a second proxy, a PostgreSQL standby and a Redis replica), so 11 becomes 14, and each one is another server to patch and watch.

The NFS server stays a single point of failure until uploads move to storage that keeps copies on more than one host. Powering it off in a drill should show uploads failing while bookings continue.

The reporting replica looks like a free standby, and the design keeps it out of failover with Patroni's nofailover: true tag. A PostgreSQL hot standby pauses replay for a conflicting query for up to max_standby_streaming_delay, 30 seconds by default, before canceling it. The alternative, hot_standby_feedback, holds back vacuum on the primary until tables bloat. A replica busy with month end reports would carry that lag into any election it won.

Three votes are only as independent as the hosts under them, so both database servers and the monitoring VM go on separate physical hosts, which most hypervisors express as an anti-affinity rule. etcd writes every change to disk before acknowledging it and is sensitive to write latency, so give each member storage that month end I/O on the database cannot saturate. A slow etcd can make a healthy primary miss its lock renewal and demote itself.

keepalived moves a floating IP between the two proxies over VRRP. It sends its adverts to the multicast group 224.0.0.18 unless unicast_peer lists the other proxy, and on a network that filters multicast the proxies stop hearing each other and both claim the address.

Each worker runs a scheduler process that asks for a session-level pg_try_advisory_lock on a direct connection of its own, since a session lock means nothing through a pooler in transaction mode. Only the holder schedules jobs. The other process retries every few seconds and takes over when the holder's connection drops. A lock taken per job only stops two copies from running at the same moment, and a short job can still run on both workers a second apart.

Redis replicates asynchronously, and Sentinel can lose acknowledged writes during a failover, so the job queue has to tolerate a job that runs twice or never runs. min-replicas-to-write 1 narrows the window by refusing writes when the replica stops acknowledging, and with a single replica it also refuses writes whenever that replica is down. The application's Redis client needs Sentinel support. A client configured with one Redis address keeps writing to the old primary.

Automatic database failover needs a third vote

High availability on two servers works for the proxy pair because neither proxy holds data. At the database, two nodes that lose contact cannot tell a dead partner from a broken link, and guessing wrong leaves two primaries taking writes. PostgreSQL ships nothing that detects a failed primary, and its documentation says the old primary must be shut out, because two systems that both think they are primary "will lead to confusion and ultimately data loss".

The fix is a third vote on a separate host. A two member etcd cluster tolerates zero failures and a three member cluster tolerates one, and a fourth member adds no tolerance over three. Patroni keeps its leader lock in etcd, so the etcd member on the monitoring VM decides which database server stays primary when the two lose contact. SQL Server gets the same effect from a cluster witness, and a file share witness belongs on a machine physically separate from the cluster nodes. Redis Sentinel needs at least three Sentinels on three separate machines for the same reason.

Patroni fences by self-demotion: a primary that cannot renew its lock stops taking writes. A Patroni process that hangs or gets killed cannot demote anything, and the Linux watchdog covers that case by resetting the node when Patroni stops sending keepalives. Patroni uses /dev/watchdog when it can open it, and watchdog: mode: required makes a node refuse leadership without one.

With one standby, the replication mode picks which failure you accept. Asynchronous replication, the PostgreSQL default, can lose the last commits at failover. Waiting for the standby on every commit, as synchronous replication does, means a crashed standby holds commits open. Patroni's synchronous_mode lets only a synchronous standby or the last known leader stand for election, and when no synchronous standby is left, the primary goes on accepting writes without that guarantee. synchronous_mode_strict blocks writes until one returns. Strict mode fits only when one lost booking costs more than a write outage every time the standby goes down.

By default, a Patroni primary that cannot update its leader lock is demoted and restarted read only, so an etcd outage stops writes while both database servers are healthy. Failsafe mode, off by default, keeps the primary running as long as it can still reach every other Patroni member.

SQL Server Standard edition offers basic availability groups: two replicas, one database per group, and no reads or backups on the secondary. An application with two databases needs two groups that fail over independently, so the databases can end up primary on different nodes. A suspect database triggers nothing unless DB_FAILOVER = ON is set, and even then a failed data disk can go unnoticed until SQL Server next reads or writes the file. Licenses held without Software Assurance or a subscription carry no failover rights, so price the second server's license in.

• Automatic database failover suits a small team once a third vote runs on a separate physical host, the application reconnects without a restart, and failover drills with the developers have passed.
• Manual promotion fits while there is no independent third host or the application still assumes one database server. A person confirms the old primary is off, then promotes the standby. Fencing still comes first, because Microsoft warns that forcing a failover while clients can reach the original primary "incurs some risk of split brain behavior".

Patroni's ttl, loop_wait and retry_timeout

ttl defaults to 30 seconds, with a minimum of 20, and sets how long Patroni waits before starting automatic failover; loop_wait adds up to 10 more. With retry_timeout at its default of 10, etcd or network trouble shorter than that leaves the leader in place. Patroni requires loop_wait + 2 * retry_timeout to stay at or below ttl, and the defaults sit exactly on that line, so shortening ttl means shortening the other two with it. A shorter ttl catches a dead primary sooner and also fails over on shorter network blips. maximum_lag_on_failover caps the replication lag, in bytes, that a standby can carry and still stand for election.

What the application assumes about the old primary

An application failover starts with every open connection dropping, or hanging when the old host lost power. New connections reach the new primary, and whatever the application pinned to the old one breaks at that moment: an address, a cached DNS answer, a scheduled job, a login.

The fixes split between the environment and the code. A host that only rents space will not advise on load balancing or answer design questions like these, so on rented servers all of it lands on the developers. On Petrichor, load balancing is a paid option, and database work such as copying logins and Agent jobs to every replica goes in as a ticket that a Petrichor engineer does. The connection string, the retry logic and idempotent writes stay with the developers, because each one lives in the application.

For booking software, the dangerous case is a write that committed a moment before its connection dropped. A blind retry books the same appointment twice. An ID generated by the client, backed by a unique constraint, makes the retry fail on the duplicate key and leaves one booking.

• postgresql://db1:5432,db2:5432/app?target_session_attrs=read-write&connect_timeout=5 names both hosts. libpq tries them in order until one succeeds, and read-write skips a standby. Other drivers use their own syntax, so check the driver's documentation.
• Set connect_timeout explicitly. Left unset, libpq waits indefinitely, and the timeout applies to each host separately. A host that has lost power never refuses the connection the way a stopped service does, so the client waits out the timeout on it first: two hosts at 5 seconds can cost up to 10.
• Existing connections hang for longer. A pooled connection to a host that lost power gets no reset, and at the Linux default tcp_retries2 of 15, retransmission gives up after at least 924.6 seconds, over 15 minutes. Set tcp_user_timeout and the keepalives_* parameters in the connection string, or a socket timeout in the pool, so a request stuck on the dead primary fails within seconds and retries on the new one.
• A single address that follows the primary is the alternative to a host list. Point the proxy's health check at Patroni's REST API, where GET /primary returns 200 only on the node running as primary with the leader lock, and in HAProxy add on-marked-down shutdown-sessions so sessions to a demoted node get cut. On SQL Server, connect through the availability group listener with MultiSubnetFailover=True, even on a single subnet.
• DNS caching decides how long anything that finds the database by name keeps the old address. Set the JVM's DNS cache TTL to 60 seconds or less (networkaddress.cache.ttl=60), because some JVM configurations never refresh until restart.
• Jobs on the database servers need a primary check. SQL Server Agent jobs have to exist on every replica, each starting with a step that exits unless sys.fn_hadr_is_primary_replica('app') returns 1. Cron scripts on PostgreSQL hosts run SELECT pg_is_in_recovery(); first and exit on a standby.

Logins and Agent jobs on every SQL Server replica

An availability group copies only what sits inside its databases. Logins live in master and Agent jobs in msdb, so both have to be recreated on every instance that hosts a replica. The same goes for server configuration settings, credentials, server-level permissions, the key setup for encrypted data, replication settings, parts of Service Broker applications, and server-level triggers. A SQL login recreated without its original SID leaves an orphaned database user, and the application's login fails on the new primary. The sp_help_revlogin procedure scripts logins with their original SIDs and password hashes. Contained database users travel with the database.

Timing a planned switchover and a forced power off

A failover design stays unproven until the developers who wrote the application have watched it through five drills. Petrichor runs a point-of-sale platform's high availability environment in production. For a change like that, our engineers design the target environment, present it to the customer, and test it repeatedly with the customer's developers before cutover. Split each drill so one person runs the infrastructure side while the developers watch their own application and logs, because they know which screens and jobs matter. The runbook and rollback are covered in how a migration rehearsal runs.

Measure each drill with a write probe that runs on an application server and uses the application's own connection string. Every 200 ms it inserts a row with a client-generated ID into CREATE TABLE ha_probe (id uuid PRIMARY KEY, ts timestamptz NOT NULL DEFAULT clock_timestamp()); and appends the ID and its local time to a file once the insert is acknowledged. The largest gap between consecutive entries in that file is the write outage users saw, measured on one clock; a gap computed from ts would mix the old and new primaries' clocks. An ID in the file that is missing on the new primary is a lost commit.

Drills cost developer time, and they run in production or in a faithful copy of it.

• Planned switchover in a quiet hour, with patronictl switchover --candidate db2 or ALTER AVAILABILITY GROUP [app_ag] FAILOVER; on the target secondary. It proves the application reconnects when nothing is broken.
• Forced power off of the primary VM from the hypervisor. A service stop closes connections cleanly and hides both the connect_timeout problem and the hung pooled connections.
• Network cut between the database hosts, with both left running. Exactly one node should accept writes.
• Replication stopped quietly, by pausing the standby's replication or filling its disk. It passes when a person gets told. We keep a person watching the automation, because automation that has stopped working can go on reporting that it runs.
• Failback. With use_pg_rewind on, Patroni runs pg_rewind to turn the old primary into a standby, and pg_rewind needs data checksums or wal_log_hints. initdb turns checksums on by default only from PostgreSQL 18, so run SHOW data_checksums; and SHOW wal_log_hints; before the drill. After a forced failover, SQL Server brings the old primary's databases back SUSPENDED, and log truncation on the new primary waits while any secondary stays suspended, so the log grows until someone runs ALTER DATABASE [app] SET HADR RESUME; or removes the replica.

Five numbers to record after every drill

1. Seconds from the power off to the first successful probe write.
2. Acknowledged writes missing on the new primary.
3. Requests that returned errors to users.
4. Jobs that ran twice or never ran.
5. Time to rebuild the old primary as a standby.

Run the drill again after a schema change, a driver upgrade, or a new service.

If the requirement covers losing a data center

A database pair inside one facility shares its power, cooling and network, so it does nothing for the last row of the failure table. Petrichor runs on hardware we own in a single data center. A vendor whose customers need the application to survive losing a facility belongs with a provider that has several. Load balancing is a paid option for SaaS hosting on Petrichor, scoped on a call.

An AWS Availability Zone is one or more discrete data centers with separate, redundant power and networking, up to 60 miles from the other zones in its region, and RDS Multi-AZ keeps its synchronous standby in a different zone. A vendor already on Multi-AZ that must survive a facility loss would give that protection up by moving to one site, and should stay where it is.

When customers can absorb a restore that takes a morning and the restores are tested, rehearsed backups are the proportionate spend. The standby can wait until a third host exists and the application reconnects on its own.

List your servers, mark the ones that hold state, and note how the application finds its database. A consultant will go through that list with you on a call and name every server that has no partner.