PostgreSQL high availability

Test switchover and failover as separate exercises, because they promise different things. A switchover is planned: the current primary is deliberately demoted, and once every standby has replayed the primary's final WAL position, no committed transaction should be lost. A failover is unplanned: the primary has crashed or become unreachable, a standby is promoted, and an asynchronous standby may be behind, so commits the primary acknowledged but had not yet shipped can disappear. Before any test runs, write acceptance criteria for each one and get sign-off from the database, platform and application owners. Include the tolerated write pause, the expected data loss for each replication mode, the required state of the old primary, and the evidence that proves each point. Agreeing these in advance keeps the team from calling a result acceptable after seeing it.

Run the test cases against a staging copy with the same topology as production and a steady write workload. Cover a planned switchover with traffic running, a primary crash (a forced stop with no clean shutdown), a network partition between primary and standby, loss of a synchronous standby during commits, failure of the failover manager itself, and client reconnection behavior. For a planned switchover, expect no lost commits and a short write pause that you have measured. For a crash with an asynchronous standby, expect possible loss limited to the replication lag at the moment of failure, and record that lag rather than assuming it. For a partition, the outcome depends on whether the failover manager can reach a quorum, and the cluster must never promote two primaries. For loss of a synchronous standby, the outcome depends on synchronous_standby_names and your commit settings, so the criteria must state which behavior you expect. For a failover manager failure, confirm what your design does: whether the current primary keeps serving and whether automatic promotion stays blocked.

Keep evidence for every run so results can be compared across runs and handed to auditors or the operations team. Record timestamps from each node, with clocks synchronized so events line up across hosts. Capture replication position before and after each event: the primary's current WAL location, each standby's replay location, and the replication status view on the primary. Note the timeline change after promotion, the failover manager's log entries, and the moment the new primary accepted its first write. On the application side, count errors by class (connection refused, read-only transaction, timeout, unique violation after retry) and record how long each client took to reconnect. Store the raw logs with the run, not only a summary, so a reviewer can check the sequence of events later.

Verify that the cluster fails safely, which matters more than the speed of failover. After any promotion, confirm that the old primary cannot accept writes: attempt a write through the application path and through a direct connection, and check that the old node reports it is in recovery or is otherwise unreachable for writes. Verify the fencing mechanism you designed, such as stopping the instance, removing its virtual address, or a fencing agent, and confirm it works during a partition, not only during a clean stop. Check that the old primary cannot rejoin as a writable node without a documented rebuild or rewind step. Finally, confirm that connection poolers and application connection strings route writes only to the new primary after the switch. Any run in which two nodes accept writes at once is a failed test, regardless of the other results.

A PostgreSQL high availability design is only as good as the routine that keeps it working after go-live. The first thing to settle is who watches what, because most failovers that go badly do so because a warning was visible but nobody owned it. Monitor replication state and lag on every standby, standby connectivity from the primary's point of view, the health of the external failover manager, and the availability of any synchronous standby. If a synchronous standby drops out, commits can stall or fall back to asynchronous behavior depending on how the setting is configured, so confirm with your own configuration what happens in that case and alert on it directly. Name an owner and an alert threshold for each of these items before the cluster takes production traffic.

The runbook needs the same discipline. Record the current topology, the failover manager's configuration, the names and roles of every node, the procedure for a planned switchover and for an unplanned failover, and the steps to rejoin a former primary as a standby. Review it after every change to the cluster, including changes to hardware, network paths, certificates, or the failover manager itself. Failover tests should recur on the same trigger. Each change that touches the topology or the failover path should be followed by a test that proves the new arrangement still promotes a standby and that clients reconnect as expected. A test that was passed a year ago describes a cluster that no longer exists.

Minor releases are the routine maintenance task. PostgreSQL minor releases do not change the internal storage format, so applying one means replacing the executables and restarting. In an HA cluster, apply the update to standbys first, then perform a planned switchover before restarting the old primary, so the restart happens on a node that is no longer serving writes. Set the order in writing, including which node goes first, who approves the switchover window, and how client connections are drained. Minor releases arrive at least once every three months under the PostgreSQL versioning policy, so this procedure will run often and should be rehearsed in a non-production environment.

Major upgrades are a separate project. They change the on-disk format and affect the HA topology, because the standbys, the failover manager's configuration and the backup chain all have to follow the new version. Plan the upgrade with its own schedule and rollback point. The two main options are pg_upgrade, which upgrades the data directory in place with a known downtime window, and logical replication, which can move data to a new major version with much less downtime because it works between different major versions. Your choice depends on data size, acceptable downtime and how much change the application can absorb. The upgrades topic covers how these options are weighed and scheduled.

Backups must be taken either from a standby or from the primary, using the approach your team has chosen, such as base backups with WAL archiving or incremental backups reconstructed at restore time. Taking backups from a standby reduces load on the primary, but the team must confirm that the backup captures what it needs and that it still works after a failover changes which node is the source. A backup job that reports success is not evidence that it can be restored. A restore test, carried out on a separate system and checked against expected data and recovery time, is the only proof that a backup works. Agree who runs the restore test, how often, and where the results are recorded. For every item in this section, including monitoring, runbook upkeep, failover tests, minor and major upgrades, and backup and restore tests, ask the team to name the owner in writing.