PostgreSQL Autobase 2.11: Testing Failover and Recovery in High-Availability Clusters

A PostgreSQL high-availability cluster is only as reliable as its failover procedure.

Having a primary server and replicas does not prove that the system can recover from a failure. You need to test what happens when the primary disappears, how quickly a replica becomes the new leader, whether applications reconnect correctly, and how the failed node is returned to the cluster.

Autobase 2.11 adds more lifecycle management to its PostgreSQL platform, including scaling, upgrades, backup, restore, and point-in-time recovery. Its underlying high-availability workflow uses PostgreSQL replication with Patroni and a distributed configuration store such as etcd.

That makes Autobase 2.11 a useful platform for building a repeatable failover and recovery test.

Understanding the Autobase HA Architecture

A typical Autobase PostgreSQL cluster contains several components:

                    +----------------+
                    |  Application   |
                    +-------+--------+
                            |
                            v
                    +---------------+
                    | HAProxy / VIP |
                    +-------+-------+
                            |
                +-----------+-----------+
                |                       |
                v                       v
        +---------------+       +---------------+
        | PostgreSQL    |       | PostgreSQL    |
        | Primary       |       | Replica       |
        +-------+-------+       +-------+-------+
                |                       |
                +-----------+-----------+
                            |
                            v
                    +---------------+
                    |    Patroni    |
                    +-------+-------+
                            |
                            v
                    +---------------+
                    | etcd / DCS    |
                    +---------------+

Patroni manages PostgreSQL roles and participates in leader election through the distributed configuration store.

The routing layer then needs to direct new client connections to the current primary.

This creates several separate failure domains.

A successful database failover does not automatically mean that the application has recovered.

Failover and Switchover Are Different

These operations should not be confused.

A switchover is planned.

For example, you may move the primary role before server maintenance:

Primary A
    |
    | planned switchover
    v
Primary B

A failover is performed because the current leader is unavailable or cannot safely continue serving as primary.

Primary A
    X
    |
    | failure
    v
Replica B
    |
    v
New Primary

Autobase provides automation for both operations.

A normal maintenance test should use switchover first. It is easier to observe and gives the team a controlled way to validate that the cluster can change leadership.

A failure test should then simulate an actual primary outage.

Check the Cluster Before Testing

Never start a failover test without recording the initial state.

Check:

Node A: Primary
Node B: Replica
Node C: Replica

Replication:
Node B -> streaming
Node C -> streaming

DCS:
Healthy

Application:
Connected

The exact commands depend on the deployment, but Patroni provides cluster status information through its command-line tools.

A typical check looks like:

patronictl list

You want to know:

Which node is leader
Which nodes are replicas
Whether replicas are streaming
Whether any replica is lagging
Whether the DCS is healthy
Which node the application is currently using

Do not proceed if the cluster is already degraded. Otherwise, the test result becomes difficult to interpret.

Measure Replication Lag Before Failover

A replica that is several gigabytes behind the primary is not an equivalent failover target.

Autobase's Patroni configuration includes a maximum lag threshold for failover candidates.

A common configuration is:

maximum_lag_on_failover: 1048576

This value represents bytes.

In this example, a replica must be within the configured lag threshold to be considered eligible for automatic failover.

Before testing, check the actual replication state.

For example:

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

The exact columns available and their behavior depend on the PostgreSQL version and replication configuration.

The important point is to record lag before introducing failure.

Test Planned Switchover First

A planned switchover is the safest first test.

Use the cluster management tooling to move the leader role to a healthy replica.

For example:

patronictl switchover <cluster-name>

After the operation:

patronictl list

Confirm that the expected replica became the new leader.

Then test the application.

Do not stop at the database layer.

Run a write operation:

INSERT INTO failover_test
    (message, created_at)
VALUES
    ('switchover test', now());

Then read the value through the normal application connection path.

This confirms that the routing layer followed the new leader.

Simulate Primary Failure

After a successful switchover test, test an unexpected failure.

The safest approach is to perform this in an isolated test environment.

For example:

Before:
Node A = Primary
Node B = Replica
Node C = Replica

Failure:
Node A = Unavailable

Expected:
Node B or C = New Primary

Do not simply stop PostgreSQL if your objective is to test a complete infrastructure failure.

Different tests reveal different failure modes.

PostgreSQL Process Failure

Stop PostgreSQL on the primary.

This tests database-process recovery.

Patroni Failure

Stop Patroni while PostgreSQL remains available.

This tests the HA control plane.

Server Failure

Power off or isolate the primary node.

This is closer to an actual host failure.

Network Isolation

Block communication between the primary and other cluster members.

This is particularly important because network failures can create ambiguous states.

The test should verify that the system does not allow two nodes to operate as independent primaries.

What Should Happen During Failover?

A simplified sequence is:

Primary becomes unavailable
        |
        v
Patroni detects failure
        |
        v
DCS confirms leadership state
        |
        v
Eligible replica is promoted
        |
        v
Routing layer detects new primary
        |
        v
Application reconnects

The exact timing depends on Patroni configuration, database state, network conditions, and application connection behavior.

Autobase's default Patroni settings include values such as:

ttl: 30
loop_wait: 10
retry_timeout: 10

These settings influence failure detection and cluster coordination.

Do not turn them into a guaranteed failover time. Actual recovery time must be measured in the environment being tested.

Measure Recovery Time

A failover test should produce numbers.

At minimum, record:

T0 = Primary failure
T1 = Failure detected
T2 = New primary elected
T3 = Routing updated
T4 = Application reconnects
T5 = Successful write

Then calculate:

Detection time = T1 - T0
Promotion time = T2 - T1
Routing recovery = T3 - T2
Application recovery = T4 - T3
Total recovery = T5 - T0

This is much more useful than saying:

"Failover worked."

A cluster can successfully fail over while still causing unacceptable application downtime.

Test Client Connections

Database failover and application recovery are separate problems.

Suppose the application holds a connection to the old primary:

Application
    |
    v
Connection
    |
    v
Old Primary
    X

That connection cannot magically become a connection to the new primary.

The application needs a connection path that can reconnect.

This is why the HAProxy, VIP, or other routing mechanism must be included in the test.

Test:

Existing connection during failure.
New connection after promotion.
Connection pooling behavior.
Retry behavior.
Transaction behavior during failure.

A failed transaction should not automatically be retried unless the application knows the operation is safe to retry.

For example, retrying a read is usually different from retrying:

INSERT INTO payments (...)

The payment operation may have reached the database before the connection was lost.

Blind retries can therefore create duplicate business operations.

Test Data Safety

Failover can involve data that was committed on the old primary but had not yet reached the selected replica.

With asynchronous replication, some amount of data loss can be possible during a failure.

The test should therefore include:

Transaction committed
       |
       v
Check replica replay position
       |
       v
Fail primary
       |
       v
Promote replica
       |
       v
Check committed transaction

If the application requires stronger durability guarantees, evaluate synchronous replication.

Autobase supports synchronous replication configuration, including:

synchronous_mode: true
synchronous_node_count: 1

Synchronous replication changes the durability and availability trade-off because writes can depend on synchronous replicas being available.

Test the configuration that you actually intend to run in production.

Test Recovery of the Failed Node

Promoting a replica is only half of the recovery process.

After failover:

Before:
A = Primary
B = Replica

After:
A = Failed
B = Primary

You now need to return A to the cluster safely.

The old primary must not simply be started and allowed to assume that it is still the leader.

The cluster needs a controlled reinitialization or synchronization process.

Autobase provides a reinit_pgcluster operation for rebuilding a Patroni replica.

The expected state becomes:

A = Replica
B = Primary
C = Replica

The important validation is that the recovered node follows the new primary and does not create a second independent history.

Test pg_rewind and Reinitialization Scenarios

After a failover, the old primary may have a timeline that differs from the new primary.

PostgreSQL provides pg_rewind to efficiently bring a former primary back into alignment when its prerequisites are satisfied.

If rewind is not possible, the node may need to be rebuilt from a fresh base backup.

Your recovery test should therefore answer:

Can the old primary rejoin?
        |
        +--> Yes: rewind/rejoin
        |
        +--> No: rebuild replica

Do not assume that every failed primary can simply restart as a replica.

Test Backup and Point-in-Time Recovery

Autobase 2.11 adds backup and recovery management to the Platform UI.

The release supports backup configuration, on-demand backups, and restore options including:

Latest available state
Specific point in time
Specific backup

It also adds Ansible playbooks for enabling backups, creating backups, listing backups, and restoring clusters.

This should be tested separately from failover.

Failover answers:

Can another live replica take over?

Backup recovery answers:

Can the database be rebuilt or restored when the required live copy is unavailable or the data itself must be recovered to an earlier state?

These are different recovery mechanisms.

Test Point-in-Time Recovery

A useful recovery test is to create a known marker:

SELECT pg_create_restore_point('before_recovery_test');

Then perform controlled changes.

For example:

INSERT INTO recovery_test
    (message, created_at)
VALUES
    ('data before recovery test', now());

Create another change that should not exist after the chosen recovery point:

INSERT INTO recovery_test
    (message, created_at)
VALUES
    ('data after recovery point', now());

Restore the test environment to the desired point and verify which records are present.

Do this only on a disposable recovery environment unless you are performing an approved production recovery procedure.

Build a Failure-Test Matrix

A useful test plan should cover more than one failure.

Test	Expected result	What it validates
Planned switchover	Replica becomes primary	Controlled HA transition
PostgreSQL failure	Replica promoted	Database failure handling
Host failure	Replica promoted	Infrastructure failure handling
Network isolation	No unsafe dual primary	Split-brain protection
Replica lag	Unsafe replica not selected	Failover eligibility
Client connection loss	Application reconnects	Connection recovery
Old primary recovery	Node rejoins as replica	Cluster repair
Backup restore	Database restored	Backup validity
PITR	Database restored to target	Recovery process

This matrix becomes much more valuable when each test records actual timings and observations.

Common Mistakes

Testing Only Switchover

A planned switchover does not prove that the cluster can handle an unexpected server failure.

Ignoring Replication Lag

The closest replica is not necessarily the safest failover candidate.

Testing Database Failover Without the Application

A database can recover while the application remains unable to connect.

Restarting the Old Primary Without Reinitialization

The old primary may contain a different timeline and must be handled carefully.

Never Testing Recovery

A replica that has never been promoted is an assumption, not a proven recovery mechanism.

Treating Backups as Untested

A successful backup job does not prove that a complete restore will work.

Measuring Only Database Promotion

The application recovery time is what users actually experience.

Best Practices

Test planned switchover before destructive failure tests.
Record the cluster state before every test.
Measure replication lag before failover.
Test PostgreSQL, Patroni, host, and network failures separately.
Include HAProxy, VIP, or the application's actual connection path.
Measure time from failure to successful application write.
Test the data that was committed immediately before failure.
Rebuild or rewind the old primary after promotion.
Test backup restoration independently from failover.
Perform PITR tests on a separate recovery environment.
Repeat failure tests after major infrastructure changes.
Document the exact recovery procedure for operators.

Advantages and Disadvantages

Advantages	Disadvantages
Automates much of PostgreSQL HA management	HA still requires operational testing
Patroni handles leader management	Failover timing depends on configuration and workload
Supports planned switchovers and failover	Some committed data can be lost with asynchronous replication
Supports replica scaling	More nodes mean more infrastructure to operate
Backup and PITR management is available in 2.11	Restore testing remains the team's responsibility
Ansible automation supports repeatable operations	Recovery workflows can become complex
Supports self-hosted PostgreSQL environments	DCS and routing layers introduce additional components

A Practical Failover Test Procedure

For a test environment, use this sequence:

1. Verify all nodes are healthy
2. Record the current primary
3. Record replication lag
4. Verify application connectivity
5. Perform planned switchover
6. Verify application writes
7. Restore the original topology
8. Simulate primary failure
9. Measure detection and promotion
10. Verify application reconnection
11. Verify recent committed data
12. Rebuild the failed node
13. Verify replication
14. Test backup restoration
15. Test point-in-time recovery
16. Record the final results

The goal is not simply to prove that Autobase can promote a replica.

A useful HA test proves the complete recovery chain:

Failure
   |
   v
Detection
   |
   v
Leader election
   |
   v
Replica promotion
   |
   v
Traffic redirection
   |
   v
Application recovery
   |
   v
Old primary repaired
   |
   v
Cluster returns to healthy state

Autobase 2.11 adds more of the PostgreSQL lifecycle to one management platform, including scaling, upgrades, backups, restore, and day-to-day cluster operations. The remaining step is operational: repeatedly test the failure scenarios your production environment can actually experience.

A high-availability cluster should not be considered reliable because it has replicas. It should be considered reliable when the team can demonstrate, measure, and repeat the recovery procedure.