Transaction Replay for Failover Continuity

Problem Being Solved

Core Problem Statement

Order, payment and booking services write to the database in short transactions. When the primary database stops, even for a planned restart, every connection open on it breaks: in-flight statements fail with a connection error, and each application has to reconnect, re-apply its session settings and work out on its own whether the interrupted work ran. Retry code written service by service behaves differently from team to team, and a blind retry of a COMMIT whose outcome is unknown can apply the same order twice.

HeliosDB Lite keeps a replicated copy of the data on a warm standby, but replication alone does not keep client sessions alive. Something between the applications and the database has to reconnect them, restore their state and decide what may safely run again.

Root Cause Analysis

Who Suffers Most

1. Teams running transactional services on HeliosDB Lite: a primary restart for maintenance turns into user-facing errors unless every service carries its own reconnect-and-retry code.

2. Platform teams: want failover behaviour defined once for services written in different languages and drivers, instead of reviewing retry code in each of them.

3. On-call engineers: need a specific SQLSTATE when a transaction could not be recovered, instead of a generic connection reset that hides whether the work ran.

HeliosDB Lite Solution

Architecture Overview

This illustrative deployment combines two documented surfaces. HeliosDB Lite provides the replicated pair: a primary that streams its write-ahead log (WAL) to a warm standby, which applies it and serves reads. Replication is an opt-in build feature: the heliosdb-lite binary must be built from the licensed source distribution with the ha-tier1 feature (cargo build --release --features ha-tier1). A binary built without it refuses to start with any --replication-role other than standalone, and the stock Dockerfile of the distribution builds without it. HeliosProxy, a separate open-source product, sits between the applications and both nodes: it pools connections, sends writes to the primary and reads to healthy nodes, and its Transaction Replay keeps client sessions open across a backend fault.

          Order service (any PostgreSQL driver)
                        |
                        v
   +-----------------------------------------------+
   | HeliosProxy                                   |
   |  session pooling, read/write split            |
   |  health checks, Transaction Replay (tr_mode)  |
   +-----------+---------------------+-------------+
          writes                   reads
               |                     |
               v                     v
   +-------------------+  WAL   +-------------------+
   | HeliosDB Lite     | -----> | HeliosDB Lite     |
   | primary           |        | warm standby      |
   +-------------------+        +-------------------+

When the primary stops, HeliosProxy marks it unhealthy after failure_threshold failed health checks, and reads continue on the healthy standby. A write that needs the primary waits for a healthy primary for up to write_timeout_secs. An open session stays connected: within that same deadline the proxy reconnects to the primary, restores the session's tracked SET state and, depending on tr_mode, re-runs what can be re-run safely. A COMMIT whose outcome is unknown is never retried; the client receives SQLSTATE 08007 and checks before retrying.

HeliosProxy does not promote standbys, and automatic failover for HeliosDB Lite is planned. Transaction Replay therefore covers the outages in which the primary comes back at its address within write_timeout_secs: a restart, a crash followed by a supervisor restart, a rolling restart. When the primary host is lost, an operator or an external HA manager promotes the warm standby by restarting it with --replication-role primary, then changes the role entries in HeliosProxy's [[nodes]] and reloads HeliosProxy with SIGHUP or restarts it. The new roles apply to new connections; sessions opened before the reload keep the configuration they started with, so plan for applications to reconnect after a promotion.

HeliosProxy release verification runs against PostgreSQL, not HeliosDB Lite, so rehearse the drill in Example 4 against your own pair before relying on it.

Key Capabilities

Concrete Examples with Code, Config & Architecture

The examples follow an illustrative order service. Host names, ports and credentials are placeholders.

Example 1: Start the HeliosDB Lite Primary and Warm Standby

Build the server with the replication feature, then start both nodes. Every node needs HELIOSDB_JWT_SECRET. --standby-hosts is informational: the primary prints it at startup, and standbys connect to the primary on their own.

# In the unpacked source distribution: replication is the ha-tier1 build feature
cargo build --release --features ha-tier1
export HELIOSDB_BIN=./target/release/heliosdb-lite
export HELIOSDB_JWT_SECRET="$(cat /etc/heliosdb/jwt.secret)"

# Primary (db-primary.example.com)
$HELIOSDB_BIN start \
  --data-dir /var/lib/heliosdb \
  --listen 0.0.0.0 --port 5432 \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD" \
  --replication-role primary \
  --replication-port 5433 \
  --standby-hosts db-standby.example.com:5433 \
  --sync-mode semi-sync

# Warm standby (db-standby.example.com): applies the primary's WAL and serves reads
$HELIOSDB_BIN start \
  --data-dir /var/lib/heliosdb \
  --listen 0.0.0.0 --port 5432 \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD" \
  --replication-role standby \
  --primary-host db-primary.example.com:5433

With --sync-mode semi-sync or sync, the primary waits for a standby to confirm each change. If no standby confirms in time, the change completes on the primary and a warning is logged; with no standby connected, every change waits that long. Use async when write latency matters more than a confirmed copy.

Example 2: HeliosProxy with Transaction Replay

Transaction Replay is configured with top-level keys of proxy.toml: there is no separate replay section and no cargo feature to enable. Leaving out [pool_mode] keeps session pooling, the mode to use with HeliosDB Lite: transaction and statement pooling reset connections with DISCARD ALL, which is planned for Lite. HeliosDB Lite does not implement pg_is_in_recovery(), so keep the default static topology, where the primary is the node configured with role = "primary".

listen_address = "0.0.0.0:6432"
admin_address  = "127.0.0.1:9090"

# Transaction Replay: top-level keys, part of the default build
tr_enabled = true
tr_mode = "transaction"      # none | session | select | transaction
tr_read_functions = []
write_timeout_secs = 30      # one deadline for the whole recovery

# The proxy authenticates clients and opens every backend connection itself
[auth]
mode = "scram"
auth_file = "/etc/heliosproxy/userlist.txt"

[pool]
min_connections = 2
max_connections = 50
idle_timeout_secs = 300
max_lifetime_secs = 1800
acquire_timeout_secs = 30
test_on_acquire = true

[load_balancer]
read_strategy = "round_robin"
read_write_split = true
latency_threshold_ms = 100

[health]
check_interval_secs = 5
check_timeout_secs = 3
failure_threshold = 3
success_threshold = 2
check_query = "SELECT 1"

[[nodes]]
name = "lite-primary"
host = "db-primary.example.com"
port = 5432
role = "primary"
weight = 100
enabled = true

[[nodes]]
name = "lite-standby"
host = "db-standby.example.com"
port = 5432
role = "standby"
weight = 100
enabled = true

Start it with heliosdb-proxy --config /etc/heliosproxy/proxy.toml. tr_mode = "transaction" is opt-in: verification catches a replay whose results differ, but it cannot make now(), random() or a RETURNING serial reproduce their first values, so use it for deterministic or idempotent transactions and keep "session" otherwise. The auth_file needs a plaintext entry for each application user: in pass-through mode the proxy holds no password, cannot open a fresh connection to a node that asks for one, and the recovery fails with 08006.

Example 3: Application Code (Python)

The order service connects to HeliosProxy with its usual PostgreSQL driver. It chooses the order ID itself, so an 08007 can be resolved by looking the order up, and it treats 40001 like any serialization failure.

import uuid

import psycopg2

DSN = "host=proxy.example.com port=6432 dbname=heliosdb user=orders_app"


def insert_order(conn, order_id, customer_id, total):
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO orders (id, customer_id, total, status) VALUES (%s, %s, %s, 'pending')",
            (order_id, customer_id, total),
        )
    conn.commit()


def order_exists(conn, order_id):
    with conn.cursor() as cur:
        cur.execute("SELECT 1 FROM orders WHERE id = %s", (order_id,))
        return cur.fetchone() is not None


def place_order(conn, customer_id, total, attempts=3):
    order_id = str(uuid.uuid4())  # chosen by the client, so an unknown outcome can be checked
    for _ in range(attempts):
        try:
            insert_order(conn, order_id, customer_id, total)
            return order_id
        except psycopg2.Error as e:
            conn.rollback()
            if e.pgcode == "40001":  # replay declined or diverged: run the transaction again
                continue
            if e.pgcode == "08007":  # COMMIT outcome unknown: look before retrying
                if order_exists(conn, order_id):
                    return order_id
                continue
            raise
    raise RuntimeError("order not placed")


conn = psycopg2.connect(DSN)  # password from PGPASSWORD or ~/.pgpass
print(place_order(conn, customer_id=42, total=99.90))

Example 4: Failover Drill and Promotion Runbook

Rehearse both in staging. The drill keeps one client session open through HeliosProxy, writes a row per second and restarts the primary in the middle. The session should continue; a statement whose outcome was unknown at the fault returns one 08007 error, and the Transaction Replay counters show what the proxy did.

PROXY="host=proxy.example.com port=6432 dbname=heliosdb user=orders_app"
psql "$PROXY" -X -c "CREATE TABLE failover_drill (id INTEGER PRIMARY KEY)"

# One session through HeliosProxy, one INSERT per second
( for i in $(seq 1 120); do
    echo "INSERT INTO failover_drill (id) VALUES ($i);"
    sleep 1
  done ) | psql "$PROXY" -X -e > drill.log 2>&1 &

# Meanwhile, on db-primary: stop the HeliosDB Lite primary, then start it again
# with the command from Example 1 before write_timeout_secs runs out

wait
grep -E 'ERROR|FATAL' drill.log
psql "$PROXY" -X -t -A -c "SELECT count(*) FROM failover_drill"
curl -s http://127.0.0.1:9090/metrics/prometheus | grep -o 'heliosdb_proxy_tr_[a-z_]* [0-9]*'

When the primary host is lost, promote the standby by hand:

# 1. On the standby: its connection to the lost primary and the last position it received
psql -h db-standby.example.com -U postgres -d heliosdb -X \
  -c "SELECT connection_state, local_lsn, lag_bytes FROM pg_replication_primary"

# 2. Restart the standby as the primary on the same data directory. Use async until a
#    new standby is attached: a sync or semi-sync primary without one waits on every change.
$HELIOSDB_BIN start \
  --data-dir /var/lib/heliosdb \
  --listen 0.0.0.0 --port 5432 \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD" \
  --replication-role primary \
  --replication-port 5433 \
  --sync-mode async

# 3. In proxy.toml: lite-standby gets role = "primary", lite-primary gets enabled = false.
#    Reload HeliosProxy and check which node it now treats as the primary.
kill -HUP "$(pidof heliosdb-proxy)"
curl -s http://127.0.0.1:9090/topology

# 4. Return the old host as a standby of the new primary
#    (--replication-role standby --primary-host db-standby.example.com:5433)
#    only after rehearsing that step in staging.

Technical Advantages

Design Properties

• Recovery in one place: HeliosProxy reconnects, restores session state and replays for every client that speaks the PostgreSQL wire protocol, instead of retry code in each service.
• Specific errors: the proxy returns real PostgreSQL SQLSTATEs (57P01, 08007, 08006, 40001), so existing driver and application retry logic keeps working.
• Verified replay: in transaction mode each replayed statement's response is digested and compared with what the client already saw, and a divergence rolls the replay back.
• Conservative refusals: transactions at SERIALIZABLE or REPEATABLE READ, a COPY in progress, responses too large to digest and reads that call functions outside the side-effect-free set are not replayed.
• Stated limits: SQL-level PREPARE statements, temporary tables, open cursors and advisory locks are not restored across a failover; applications that depend on them keep their own handling.

Adoption Strategy

Phase 1: Staging Drill

Objective: Show that sessions through HeliosProxy continue across a restart of the HeliosDB Lite primary.

Actions:

1. Build HeliosDB Lite with ha-tier1 and start a primary and a warm standby (Example 1)
2. Put HeliosProxy in front with tr_mode = "session" (Example 2)
3. Run the failover drill and the promotion runbook (Example 4)
4. Size write_timeout_secs and the [health] thresholds to the restart time you measured

Success Criteria: The drill session continues across a primary restart, and the promotion runbook is written down and rehearsed.

Phase 2: Pilot Service

Objective: Move one write-heavy service behind HeliosProxy.

Actions:

1. Point the service's connection string at HeliosProxy
2. Handle 08007 by checking the outcome and 40001 by retrying (Example 3)
3. Watch the heliosdb_proxy_tr_* counters on /metrics/prometheus
4. Switch to tr_mode = "transaction" only for deterministic transactions

Success Criteria: Planned primary restarts reach the service as specific SQLSTATEs at most, not as dropped connections.

Phase 3: Production Rollout

Objective: Cover the remaining services and the on-call procedure.

Actions:

1. Move the remaining services behind HeliosProxy
2. Add the promotion runbook to the on-call documentation
3. Alert on lag_ms in pg_replication_standbys on the primary
4. Repeat the drill after each upgrade of HeliosDB Lite or HeliosProxy

Success Criteria: Every service writes through HeliosProxy, and on-call engineers can promote the standby from the runbook.

Conclusion

HeliosDB Lite supplies the replicated pair: a primary and a warm standby that applies its WAL, from a binary built with the ha-tier1 feature. HeliosProxy Transaction Replay keeps client sessions open while the primary restarts: it reconnects, restores the tracked session settings and, in the opt-in transaction mode, replays the interrupted transaction and verifies the result. A COMMIT whose outcome is unknown is never retried; the client receives 08007 and checks before retrying. SQL-level prepared statements, temporary tables, open cursors and advisory locks are not restored across a failover. Promoting the standby is a manual or external step today; automatic failover is planned.