Skip to content
Rowsafe
Docs

Standby servers and failover

Keep a second server in sync, read from it, and promote it when the primary dies.

A standby is a second server that stays in sync with your database. Read from it, promote it when the primary dies or needs maintenance, or let Rowsafe fail over automatically (off by default). Rowsafe builds it from your backups and keeps the old primary stopped after a promotion.

This page describes PostgreSQL. Other engines: MySQL and MariaDB, MongoDB, ClickHouse, Redis and Valkey.

Servers not connected to the dashboard can have a standby too, with one command on each server: see On servers on their own.

What you need

  • Another server with the Rowsafe agent installed natively (not Docker), and an empty PostgreSQL cluster of the same major version (pg_createcluster 18 standby --port 5433 --start).
  • Restarts allowed on both servers (sudo rowsafe-allow restart, see permissions).
  • Backups running, and the same extensions on both servers. ALTER SYSTEM settings are copied; /etc/postgresql files aren't.
  • For streaming (seconds behind instead of about a minute), a network path from the standby to the primary's PostgreSQL port. Otherwise it follows through your bucket.

v1 limits

One standby per database. No tablespaces outside the data directory. Linux with systemd, as the agent installer sets up.

Create a standby

Click Standby, Add a standby, pick the server and cluster, and confirm its key fingerprint. Or:

rowsafe standby add app --host db-2

Pairing and your secrets

The standby needs your bucket settings and passphrase. Rowsafe never sees them: the primary's agent encrypts them to the standby's key, and Rowsafe only relays the box. Compare the fingerprint shown with what the server prints:

sudo -u postgres rowsafe-agent key
Fingerprint: 7F3A-91C2-0B4E-D8A1
Public key:  ...

If they differ, stop. To make servers refuse any other key, even from a compromised Rowsafe, pin them in /etc/rowsafe/agent.env and restart the agent:

/etc/rowsafe/agent.env
ROWSAFE_STANDBY=pinned
ROWSAFE_STANDBY_PEERS=7F3A-91C2-0B4E-D8A1,2C9E-5B10-77AD-E403

ROWSAFE_STANDBY=off keeps a server out of standbys.

What happens

  1. The primary gets a replication role, allowed from the standby's addresses only. No restart.
  2. The standby server sets the empty cluster's data aside and restores the latest backup as a standby.
  3. It follows through the bucket (about a minute behind) and streams when it can (seconds behind). Without TLS, it streams only over a private address.

Use it for reads

Connect like to the primary, with the same users. It refuses writes. Long queries there can be cancelled; for long reports, set max_standby_streaming_delay.

Watch it

Standby shows its state, lag and a timeline. Alerts:

AlertWhen
Standby downIts agent or PostgreSQL isn't reporting, or creating it failed.
Standby behindIt replays more than 10 minutes late (you can change the threshold).
Replication lagThe existing rule: a streaming standby is further behind than the threshold.
Standby promotedThe database moved to its standby and has no standby since. Critical.
Fenced old primary runningPostgreSQL runs on a fenced old primary. Critical.

If it breaks, click Rebuild the standby. To move a database for good, see Move a database.

Promote the standby

Click Standby, Promote, and type the name (or rowsafe standby promote app).

When the primary is up

Nothing is lost. Rowsafe fences (stops) the primary, the standby replays everything and promotes, and backups continue on the new server. Apps are disconnected for about a minute.

If the standby can't catch up within 3 minutes, it doesn't promote and says how much is missing: promote anyway (and lose that), or start the old primary again.

When the primary is down

Type db-1 is down to confirm it will stay stopped. The standby promotes with what it has; later changes are lost (the timeline shows up to when). When the old primary comes back, its agent stops PostgreSQL.

Fencing: the old primary stays stopped

Two servers must never both take writes. The old primary is stopped, comes up read-only if started (standby.signal), and its agent stops it again if found running, even offline.

It stays fenced until you:

  • Rebuild it as the new standby. Its data is reused if it stopped cleanly; otherwise it's kept aside for 7 days.
  • Unfence it, only when the standby wasn't promoted.
  • Forget it, when the server is gone for good.

Automatic failover

Click Standby, Automatic failover, and type the name. It needs a streaming standby, so two witnesses (Rowsafe and the standby) can tell a dead primary from a cut network.

Rowsafe promotes only when all of these hold:

  1. The primary's agent hasn't reported for the wait you set (default 3 minutes, at least 1).
  2. The standby can't reach the primary's PostgreSQL either, for as long.
  3. The standby is running, reporting and replaying.
  4. At most the data loss you allow would be lost (default 1 minute): the standby was streaming, and no further behind, until that long before the primary was last heard from.
  5. No restart, health fix, Rewind, agent update or other standby change is running or queued.

Then it fences the old primary and promotes, with a critical alert. Standby shows each condition live.

Split brain

One case automatic failover can't see: the primary cut off from Rowsafe and the standby, but still reachable by your apps. Both servers then take writes until Rowsafe can stop the old one. Those writes are kept, but merging them back is manual.

To avoid it:

  • Put your apps behind a Rowsafe pooler that can follow a promotion (see pooler limits).
  • Keep the standby on the same network path as your apps, so a cut that isolates the primary from the standby also isolates it from the apps.
  • Leave automatic failover off and promote by hand. With the primary up, a manual promotion loses nothing.

Connect your apps

Give apps both servers; the driver picks the writable one, so they follow a promotion. Standby shows these strings.

postgresql://[email protected]:5432,10.0.0.2:5432/DBNAME?target_session_attrs=read-write

libpq 10+ keeps the first host that accepts writes. For reads on the standby, use target_session_attrs=prefer-standby (libpq 14+) with the standby first.

Make sure your app retries a failed transaction.

Remove a standby

Click Standby, Remove (or rowsafe standby remove app). The cluster gets its own data back, and the primary loses the standby's role.

From a terminal and AI agents

The CLI has it all: rowsafe standby, add, promote, rebuild, unfence, failover and remove. See CLI reference.

AI agents can promote, remove and change failover as the person who connected them, if an owner or admin. Creating or rebuilding needs a person to compare the key, so it's refused for agents.

On servers on their own

Two servers where Rowsafe runs on its own (not connected to the dashboard) can be a pair too, for PostgreSQL. Each server keeps its own half and they never talk through Rowsafe: they share only your bucket and its passphrase. You run one command on each server.

What you need

  • The same bucket and passphrase on both servers. Install Rowsafe on the second server with the bucket and passphrase of the first (the passphrase you saved when you installed it). Nothing secret is copied between the servers: the replication password is derived from the passphrase on each.
  • On the second server, an empty PostgreSQL cluster of the same major version that Rowsafe may stop and start (the installer's restart question, or sudo rowsafe-allow restart PORT).
  • For streaming, PostgreSQL on the primary listening on an address the standby can reach (listen_addresses). Otherwise the standby follows through the bucket, about a minute behind.

Set it up

On the primary, let the standby stream (optional):

sudo rowsafe standby link shop --address 10.0.0.6

It adds a replication role allowed from that address only, and prints the command for the other server.

On the standby's server:

sudo rowsafe standby create shop --primary 10.0.0.5

You type the name to confirm. The empty cluster's data is set aside (put back if you remove the standby), the latest backup is restored, and it follows the primary: streaming when it can, through the bucket always.

sudo rowsafe standby shop on either server shows its half: on the primary, the stream and how far behind it is; on the standby, its mode, lag, last change replayed, and whether it can reach the primary.

Switch over (nothing lost)

On the primary: sudo rowsafe standby fence shop. PostgreSQL stops for good (read-only if started, kept stopped by Rowsafe), its last changes go to the bucket, and the bucket records where its last change is.

On the standby: sudo rowsafe standby promote shop. It replays up to that last change, promotes, and backs up to the same bucket from then on. Point your apps at it.

Changed your mind after step 1? sudo rowsafe standby unfence shop on the primary starts it again, after checking in the bucket that the standby wasn't promoted.

When the primary is down

On the standby: sudo rowsafe standby promote shop --primary-down and type db-1 is down. The bucket then records that this server is the primary. When the old primary comes back, its agent reads that and Rowsafe stops PostgreSQL there, even if it never really went down.

The limit

Between the old primary coming back and its agent reading the bucket (about a minute), apps still pointed at it can write there. A server that can't reach the bucket is never stopped this way (its backup alerts fire). Those writes are kept on it, not on the new primary. So promote with --primary-down only when the primary is down or will stay stopped, and prefer a switchover.

There is no automatic failover on servers on their own: telling a dead primary from a cut network needs a witness outside both servers. Connect both servers to the dashboard for that.

Afterwards

  • Make the old primary the new standby: on it, sudo rowsafe standby create shop --primary NEW-PRIMARY-ADDRESS (its data is reused when it stopped cleanly; otherwise kept aside for 7 days), and on the new primary sudo rowsafe standby link shop --address OLD-PRIMARY-ADDRESS.
  • Remove the standby: sudo rowsafe standby remove shop on its server (the cluster gets its own data back), and sudo rowsafe standby unlink shop on the primary.
  • Let a stopped old primary go: sudo rowsafe standby forget shop on it.

Alerts on each server's own channels: standby down or behind, the standby can't reach the primary (on the standby's server: the only outside view of the primary), promoted, and a fenced old primary found running.

Limits: PostgreSQL only, one standby per database, and backups in a bucket (not SFTP). The new primary lists backups from its first one on; older ones stay in the bucket.

MySQL and MariaDB

  • The other server: an empty server of the same version series (8.4 with 8.4), agent installed natively.
  • On both servers, say yes to "Allow standby servers with this server?" (or --mysql-standby).
  • Seeded from your backups, then it replicates from the primary's binary log. It needs a network path to the primary's MySQL port (no bucket-only mode).
  • Fencing makes the old primary read-only instead of stopping it, and ends its sessions.
  • Removing drops what Rowsafe loaded.
  • Apps use one server. After a promotion, point them at the new primary (or a DNS name, or ProxySQL).
  • On servers Rowsafe creates (Rowsafe Cloud, Create a server for me), MySQL 8.4 and MariaDB 11.8 come ready: tick With a standby or click Add a standby, and the server's name follows the primary.

MySQL and MariaDB limits

On MariaDB, SUPER or READ_ONLY ADMIN logins can still write to a read-only server. Configuration files aren't copied. The primary needs its binary logs since the latest backup.

MongoDB

The standby joins the primary's replica set.

  • The other server: an empty MongoDB of the same version, installed with --mongodb-standby and --allow-restart. The primary needs --mongodb-standby too.
  • Joining restarts the empty server once (you confirm). The primary isn't restarted.
  • It never becomes primary by itself (priority 0, no vote) until you promote it. MongoDB copies the data from the primary.
  • Connect your apps with both servers and the set's name (mongodb://host1,host2/db?replicaSet=rs0). Drivers find the primary.
  • Removing takes it out of the set; its data stays.

ClickHouse

A ClickHouse standby follows through your bucket only, usually within a minute. No connection between servers, no ClickHouse Keeper. See Protect ClickHouse.

  • What you need: an empty ClickHouse of the same version or newer, kept ready for clones at install.
  • Read-only for everyone but Rowsafe.
  • Promote makes the old primary read-only, then the standby writable. No automatic failover.
  • Rebuild turns the old primary into the standby; its databases are set aside (rowsafe_before_*).
  • Remove leaves the server empty and writable.

Redis and Valkey

Real replicas.

  • Both servers are installed with --redis-standby. Rowsafe creates the standby's server (ports 6390 to 6399) or takes an empty one.
  • It gets the primary's users and copies the data from the primary, then follows every change, read-only.
  • Fencing makes the old primary refuse writes (it still serves reads).
  • Apps use the primary. After a promotion, point them at the new one.
  • Details: Protect Redis or Valkey.
Edit on GitHub