Databases: configuration, migrations, transfer, shards, failover

This page is for whoever runs a merkhain instance or relay on something other than the defaults: choosing SQLite or PostgreSQL, upgrading schemas, moving data between backends, running a sharded relay and keeping it available. Everyday status, repair and owner recovery are in Operations.

• Backends and layouts
• Configuration
• First installation
• Upgrades and migrations
• Moving an instance from SQLite to PostgreSQL
• Moving a relay from SQLite to PostgreSQL
• Relay shards, replay protection, request routing
• Moving pools and replay buckets
• Several relay processes and operator sessions
• Backup and restore
• High availability and failover
• Search on SQLite and PostgreSQL
• Capacity
• Recovery from failed operations
• Limitations

Backends and layouts

merkhain supports SQLite and PostgreSQL 16 and 18, nothing else. Both drivers are pure Go, so the released binary stays static on every platform.

• SQLite is a file next to the binary's data: WAL, synchronous=FULL, one writer at a time. It needs no server and is the right choice for one person's instance and for small relays.
• PostgreSQL suits instances whose data should live on a managed server, and relays that need several relay processes, more concurrent writers, replication or shards. Concurrent writes to different pools do not wait for each other there.
• An instance database belongs to exactly one instance (one device). Never point two instances at the same database or schema, and never copy a database between devices: devices share memories through pools.

The relay's databases have roles:

A SQLite relay is one database with all three roles. A PostgreSQL relay has a control database that hosts every role no shard hosts, so one PostgreSQL database can host them all, or shards can take the pool and replay roles.

Configuration

The database section of config.json selects the databases. Every field is optional.

{"database": {
  "local": {"url": "postgres://bz:secret@db1:5432/merkhain_alice"},
  "relay": {
    "control": {"url": "postgres://bz:secret@db1/relay", "max_conns": 32},
    "shards": [
      {"name": "pool-a", "roles": ["pool"], "url": "postgres://bz:secret@db2/pools"},
      {"name": "replay-a", "roles": ["replay"], "url": "postgres://bz:secret@db3/replay"}
    ]
  }
}}

• URLs. SQLite URLs need an absolute path and take no query; their connection settings are fixed. PostgreSQL URLs are passed to the driver (postgres:// or postgresql://, with sslmode and the other libpq parameters). An instance database that is not on a Unix socket or loopback address needs sslmode=verify-full (with sslrootcert unless the server's CA is in the system store); other modes are refused (see Security). A search_path=NAME parameter selects an existing schema, so several merkhain databases can share one PostgreSQL database as separate schemas.

• Privileges. The configured user creates and alters the tables (first installation and migrate up), so it must own its schema or have CREATE on it. On PostgreSQL 15 and later, public grants that only to the database owner.

• pg_trgm. The local role's schema needs the pg_trgm extension (shipped with PostgreSQL, trusted since PostgreSQL 13) in a schema on the search_path. When the database has none, the baseline installs it into the current schema, which needs CREATE on the database; otherwise have a superuser run CREATE EXTENSION pg_trgm in a schema on the search_path first. The baseline refuses an installation the search_path cannot see. A user without either fails the first start with nothing applied:

  migrate: local migration 407 failed (nothing was applied): ERROR: permission denied to create extension "pg_trgm" (SQLSTATE 42501)
  migrate: local migration 407 failed (nothing was applied): ERROR: pg_trgm is installed in schema ext, which is not on the search_path ({ek,public}): add it to the search_path of the database URL (SQLSTATE P0001)
  

  The database stays empty, and every command (agent hooks too) fails with that error until the extension is available. After a superuser runs CREATE EXTENSION pg_trgm SCHEMA public (or in another schema on the search_path), the next command initializes it.

• Credentials stay in the configuration. merkhain config, migrate status, status, the console and error messages show URLs redacted (no password), and the console shows postgres://host:port/dbname only. Keep config.json owner-only, or put the URL into a root-owned environment file of the service (see Relay).

• Prefer config.json over the environment for the instance database. Capture hooks and MCP servers are started by Claude Code and Codex, not by the service, and only see MERKHAIN_DATABASE_URL if the agent's environment has it. A URL in config.json reaches every process.

• Connection budget. Each process opens up to max_conns connections to each of its databases. Size PostgreSQL's max_connections for every relay process, migrate/shard/move command and instance process that runs at the same time.

• Choose the layout before the first start. Each database is listed once, and roles that share a database are migrated together and are never added to an initialized database later. Adding a shard for a role later moves that role off the database that hosted it before (see Limitations).

First installation

A genuinely empty database is initialized automatically, by the first command or service that opens it. On PostgreSQL an advisory lock, and on SQLite a cross-process migration lock, make concurrent first starts safe: one initializes, the others wait and then use the result. A database that is not empty but has no merkhain schema is refused.

• Instance: set the URL, then follow Local memory. merkhain status reports database.driver.

• Single-database relay (SQLite or PostgreSQL): set the URL and start serve remote.

• Sharded relay: create the empty databases, then initialize and register them before any relay serves traffic:

  merkhain migrate up --relay                  # control and every configured shard
  merkhain shard register pool-a replay-a      # all shards at once
  merkhain shard status
  merkhain serve remote                        # on every relay host
  

  Register all replay shards in one register call: the first registration of a replay shard places all 4,096 replay buckets, spread over the replay shards it registers; shards registered later get no buckets until a bucket move. Configure the replay shards before serving traffic (see Limitations).

packaging/ci/postgres-smoke.sh runs these steps against PostgreSQL with the release binary.

Upgrades and migrations

Who applies schema migrations depends on the database's role, on SQLite and PostgreSQL alike:

• Automatic: the instance database and the Personal sync relay. The next normal open after a binary upgrade (any command, a capture hook, the MCP server, serve local) applies pending migrations. No command and no shutdown are needed.
• Explicit: standalone relay databases (serve remote: the control database and every shard). After an upgrade, serve remote and the relay commands refuse a database with pending migrations and name merkhain migrate up.

merkhain migrate status                 # read-only: every configured database, its roles, versions, pending migrations
merkhain migrate up --local             # the instance database (optional: the next normal open does it too)
merkhain migrate up --relay             # the relay's control database and every shard
merkhain migrate up --database pool-a   # one database: local, control or a shard name

Migrations of a database run in one transaction under its migration lock (a SQLite write lock, a PostgreSQL transaction-scoped advisory lock), so a failure or crash leaves the database unchanged and the next open (or migrate up) tries again; a ledger is never forced or downgraded automatically. Concurrent openers inspect the database first, take no lock when it is already usable, and inspect it again under the lock, so each migration is applied once. While a migration runs, other processes wait briefly for the lock: a capture hook at most its own time budget, after which it fails retryably (exit 75), PostgreSQL connections at most their 10-second lock_timeout. On PostgreSQL the schema change itself also takes table locks that ordinary queries wait for; the advisory lock only coordinates migrators. Each role has its own history (migrate status shows state, version, latest, pending and dirty per role, the database's policy and, while migrations are pending, a hint saying how they get applied).

Mixed versions. From the first release with automatic migrations (the bridge release) on, every migration keeps the reads, writes and data semantics of older binaries working, so processes of the previous version keep running against an upgraded database and can restart against it. Such a database is ahead for the older binary: a clean ledger above the newest version it knows. It is healthy and usable; migrate up of the older binary leaves it unchanged, and its migrate down and migrate force refuse it. Rolling an application back means running the older binary against the upgraded database, never reverting the schema. Binaries released before the bridge release still refuse a newer schema (schema too new); upgrade every process to the bridge release first. A database older than the baselines is refused: see below.

The relay that Personal sync over Tailscale runs inside serve local keeps its own SQLite file, personal-sync-relay.db in the data directory. It is not a configured database: migrate status lists it once it exists, --relay leaves it out, and it upgrades automatically when serve local starts it (merkhain migrate up --database personal-sync-relay does it right away).

Instance upgrade: see Install. Nothing needs to be stopped.

Relay upgrade (rolling):

1. Back up every relay database (Backup and restore).
2. With the new binary, run merkhain migrate up --relay once (from one host) while the old relay processes keep serving. Each database migrates atomically; if the command stops partway through several databases, run it again: databases already migrated are skipped.
3. Replace the binary on the relay hosts and restart them one at a time. To roll back, start the old binary again; it serves the migrated databases.

Run migrations as the service's user and with the service's configuration: with the shipped systemd unit sudo systemd-run --pty -p DynamicUser=yes -p StateDirectory=merkhain-relay -E MERKHAIN_HOME=/var/lib/merkhain-relay /usr/bin/merkhain migrate up --relay (add -p EnvironmentFile=… if the unit uses one); with the container docker run --rm -v merkhain-relay:/var/lib/merkhain [-e MERKHAIN_RELAY_DATABASE_URL=…] merkhain-relay migrate up --relay.

Long migrations. A migration that backfills existing data runs as one statement in the migration transaction, without progress output, and holds its locks until it commits. New migrations keep startup work short and move large backfills to resumable background work.

The recall comparison's tables

The recall comparison is opt-in and keeps its queue, memory_get followups and fuzzy vocabulary in its SQLite evidence index, not in the local database. No migration of the local database goes with this, and nothing is dropped: recall_comparisons, recall_followups and the fuzzy_* tables of migrations 320 and 405 stay for older binaries, which keep reading and writing them. This binary never writes to them. serve local copies what the queue tables hold into the index in bounded background batches (and goes on copying what an older hook still writes), while an evidence index exists or comparison is on. The vocabulary is derived data and is rebuilt in the index. Evidence records and judgments were already in the evidence root and do not move.

Databases below the baselines

Each role's history starts at a baseline, the schema its earlier migrations built, squashed at commit 7ccdaeea: local 407, control 530, pool 520, replay 470. A database with any of those versions or newer opens normally: the baseline does not touch it and its ledger stays valid, even if it was migrated step by step. An older one, including a database from before the role ledgers existed (with only a schema_migrations ledger), is refused by every command and left unchanged, with an error that names the commit.

To keep such a database, upgrade it first with a binary built from that commit: back it up, open it with that binary (any normal command; merkhain migrate up for a standalone relay database), then install the current binary. The upgrade notes for older migrations are in docs/guide/databases.md at that commit.

Upgrading to event payload retention (408)

Migration 408 adds four nullable columns (events.payload_pruned_at, events.payload_stub, events.payload_restored_at, extraction_jobs.payloads_pruned_at), two partial indexes and a trigger on events. It rewrites no rows and is additive. serve local removes event content only if you set retention.event_payload_days: see Raw event retention.

Which older binaries this covers. No version has been tagged or released yet, so there is no bridge release to pin. The range is what can open a current database: from 23df9e7c (the rename to merkhain, #550, which changed data paths and migration ledgers without compatibility, and already holds migration 405) to bfedb0d4, the last commit before 408. Automated tests run a real binary built from each end on SQLite and PostgreSQL (TestOlderBinaryReplaysPrunedTranscript); commits between them were not run, though capture's comparison and queries do not change between them. Nothing older was tested.

An older binary keeps capturing, extracting and serving recall against a database with pruned payloads. It shows pruned prompts with their author and an empty text, and does not know why they are empty.

It can also replay a transcript of a pruned session, comparing each record with the stored row byte for byte. So that this stays a duplicate, and its source keeps advancing, the trigger puts the replayed payload back into the row just before that comparison. The row is still pruned for current binaries and for extraction, no event is captured again and the prompt search index stays empty, but the plaintext of a replayed event is back in the database until the next retention pass of a current binary (hourly, and at the start of serve local), whatever retention.event_payload_days now says. If you run only an older binary from then on, it stays there.

A replay that changes an event's kind, time or the pruned row's compared fields (author, agent_id, phase, sidechain) is still a conflict, for both binaries, and changes nothing. One that changes only other content is not, because the pruned row has nothing to compare it with.

migrate down --local drops the columns, the indexes and the trigger. Pruned payloads do not come back, and an older binary's replay of a pruned transcript then reports event_identity_conflict and stops that source, because no trigger answers its comparison any more. To go back without that, stay on the upgraded schema (older binaries accept it) or restore the backup.

Down and force

merkhain migrate down --database pool-a --role pool [--steps N]
merkhain migrate force --database pool-a --role pool VERSION

• migrate down reverts migrations newer than the baseline only. While a role's latest version is its baseline, migrate down has nothing to revert. On a sharded relay, down and force need --database NAME (exactly one database). A database that hosts more than one role, such as a SQLite or single-database relay (control, pool and replay), also needs --role ROLE.
• migrate force records a version after you repaired a schema another tool left dirty. It is never run automatically and cannot repair a ledger that migrate status rejects (unknown version, partially initialized); those need a restore. A dirty ledger above the binary's versions was left by a newer binary's failed migration; repair it with that binary.
• migrate down and migrate force refuse a role that is ahead of the binary: only the binary that knows those migrations may revert or record them. Rolling an application back runs the older binary against the upgraded schema; to return to the exact earlier schema, restore the backup.

Moving an instance from SQLite to PostgreSQL

Pointing an existing instance at PostgreSQL by itself starts a new, empty database: the data in merkhain.db stays where it is, while the device identity and pool membership in the configuration directory still refer to it. While the PostgreSQL database holds no local data and <data_dir>/merkhain.db does, status reports db_sqlite_data_left_behind and serve local logs a warning. If that file cannot be read, status reports db_sqlite_data_left_behind_unknown instead, with sqlite_data_left_behind_reason: it may or may not hold data. Copy the data with merkhain transfer local:

1. Create an empty PostgreSQL database (or schema) owned by the configured user.

2. Stop every merkhain process using the instance: serve local, and the agents whose hooks and MCP servers use it.

3. merkhain migrate status --local: if the SQLite database has pending migrations, run merkhain migrate up --local on it first. A database that is ahead (migrated by a newer binary) is copied with that newer binary.

4. Copy, verify and activate:

   MERKHAIN_DATABASE_URL='postgres://bz:secret@db1/merkhain_alice' merkhain transfer local
   

   --to defaults to the configured local database (database.local.url or MERKHAIN_DATABASE_URL), so the target's password stays out of the process arguments, which other local users can see. --to refuses a URL with a password (in its userinfo or a password/sslpassword parameter); with --to, keep the password in PGPASSWORD or ~/.pgpass: PGPASSWORD=… merkhain transfer local --to 'postgres://bz@db1/merkhain_alice'. --from defaults to the SQLite instance database and --batch (rows per checkpoint) to 500. The transfer holds the source's write lock for the whole run, copies every table in checkpointed batches, keeps IDs, timestamps, bytes, event sequences, jobs, resolutions, ciphertext and sync cursors, restores the sequences, rebuilds the PostgreSQL search indexes (memory and prompt; migration 405's fuzzy vocabulary, which only older binaries use, starts empty and is rebuilt by serve local in the background), and compares row counts and content hashes of every table. Only then does it mark the target usable; until then every command and migrate refuse it, and migrate status shows "transfer": "copying". Exit 75 means a writer still uses the source, or another transfer into the same target is running: stop it and run the command again. Run it again after any interruption too; it continues from the last checkpoint, and on a verified target it does nothing.

5. Cut over explicitly: set database.local.url in config.json to the target. Start serve local and check merkhain status (database.driver: postgres) and a merkhain recall search.

The SQLite file is left as it was (SQLite may checkpoint its WAL into the main file) and is your way back until you delete it: switching the URL back returns to the data as it was at the transfer. Anything written on PostgreSQL after the cutover is not in it, and there is no transfer from PostgreSQL to SQLite.

Do not write the source between the first run and the cutover. A source changed after the first run is detected by its fingerprint and the transfer refuses the target; so does a target that some other process wrote to. In both cases drop the target schema (or database) and transfer again. Search result cursors from the SQLite instance are not valid on PostgreSQL; timeline cursors are. Identity and keys live in the configuration directory and are unaffected.

Moving a relay from SQLite to PostgreSQL

merkhain transfer relay copies a SQLite relay database offline into a PostgreSQL relay: a control database and the pool and replay shards registered there. One PostgreSQL database registered as control works too, and so does a control database hosting the replay role with only pool shards registered (it then keeps every replay nonce). A PostgreSQL control database that hosts the pool role without being registered is not a target: register control as a shard. Prepare the target like a new relay (First installation), then transfer and switch:

# 1. Back up relay.db, then stop `serve remote`. Nothing may write relay.db until the switch.
# 2. Configure database.relay (control and shards) in config.json, then:
merkhain migrate up --relay
merkhain shard register pool-a pool-b replay-a   # every shard that should receive pools or replay buckets, in one call
merkhain transfer relay                          # --from defaults to <data_dir>/relay.db; --batch N (default 500)
# 3. Start `serve remote` with this configuration. That start is the cutover.

The transfer copies the creation grants and their receipts, pool provisions, enrollment locators (creating one for every enrollment stored before locators existed), and every pool's records, sequences and counters, memberships, devices, enrollments, key wraps and labels unchanged, so cursors continue and retried creations get their receipts. Each pool goes to the shard a new pool would be placed on (draining shards receive none), keeping its route generation; each nonce, expired ones included, goes to the shard of its device's replay bucket with its expiry, so requests seen by the SQLite relay stay replays. Statistics are rebuilt for the new routes; finished move jobs are not copied. The output is JSON with per-database row counts and hashes and the next step; a rerun after verification changes nothing.

The source is refused while it has an active writer (exit 75: stop it, retry), while it holds unfinished work (a live or interrupted pool or bucket move, a pool move journal, a pool creation that was interrupted: start serve remote on it once so it completes, a fenced pool or replay bucket, or rows in a table the transfer does not copy), or when its schema is not current (migrate up --relay first). A target is refused when it is not migrated, has no active pool shard, has replay buckets without a route (register the replay shards, or let the control database host the replay role), holds relay data other than its registry, or a shard's identity does not match its registration; a refused target is left untouched. Every database is marked before anything is copied, so neither serve remote, migrate nor shard register runs on the target until the transfer is verified: shards first, control last. Each table is copied in checkpointed batches; after an interruption, run the command again. Do not write the source or change the shard registry in between: either is refused as another transfer; drop the target databases and start again. The source keeps its content and can be started again instead of switching. Exit codes are those of transfer local: 0 verified, 2 usage, 75 retry, 1 otherwise.

Relay shards

The relay's control database keeps a registry of the databases hosting the pool and replay roles (configured shards, and control when it hosts them). Migrate a shard first, then register it:

merkhain migrate up --relay           # control and every shard
merkhain shard register pool-a        # verify and activate; several names at once are fine
merkhain shard status                 # read-only: registered and configured shards, routes, probes
merkhain shard drain pool-a           # no new pools on pool-a; existing pools stay

register refuses a database that is unreachable, whose schema is not current, or whose identity does not match: the first registration stamps an identity into the database, so a name that later points at another database, or a database configured under a second name, is detected. Registering a draining shard activates it again. A shard keeps the roles it was registered with. Shards cannot be unregistered: drain a pool shard and move its pools away.

A new pool is placed once, on one of the active pool shards, and that route is stored. Registering more shards never moves existing pools; only an explicit move does. The 4,096 replay buckets are placed on the active replay shards when the first replay shard is registered and also stay where they are. serve remote routes every request through them (see Request routing).

Creating a pool reserves the pool identity (and its creation grant) for one exact genesis in the control database, creates the pool on its shard, then publishes its route and the grant's receipt. A relay that stops in between completes the creation when it starts again, or when the client retries; until then the pool has no route and the grant cannot be used for anything else. Enrollment locators are published in the control database before the enrollment is stored.

Replay protection

Every signed device request consumes its nonce once, on the replay shard that owns the device's bucket (the 4,096 buckets above), whichever pool or relay process served it. A nonce transaction holds its bucket's fence in shared mode, so requests never wait for each other unless they reuse the same nonce. Each replay shard also records the route generation at which it owns a bucket; a relay with a stale route, or a shard that does not own the bucket yet, refuses the request with a retryable unavailable error and records nothing. So does an unreachable control or replay database, a bucket without a route, or a shard URL that now points at another database: replay protection fails closed, and clients retry with a new nonce. The request's timestamp window is checked again inside the nonce transaction, so a request that gets there after its window is refused as expired, even if its timestamp was fresh when it arrived.

Nonces are deleted in the background one minute after they expire, once at startup and then every minute, in batches of at most 1,000 rows (10 batches per shard and round, so a backlog is worked off over several rounds). Requests no longer delete them. Several relay processes may prune the same shard. On SQLite each batch briefly takes the database's write lock. A control database that hosts the replay role itself (every SQLite relay, and a PostgreSQL control database when no shard hosts replay) serves every bucket until a replay shard is registered.

The first registration of a replay shard places all buckets at once. Before it does, it checks every configured replay database: none may hold a live nonce of a bucket placed on another database, or ownership left by earlier routes (a moved bucket). Such history means the control database was rebuilt, or another database served replay so far, and placing over it could accept nonces recorded elsewhere, so registration refuses to place anything. Restore the control database, or register the database that served replay alone and move buckets from it.

Request routing

serve remote serves each request from the database that is authoritative for it, without consulting the others:

• Pool requests (membership, records, rotations, enrollment offers and sponsor routes, key mailboxes) go to the shard of the pool's route in the control database. Pools without a route (created on a single-database relay, or before routes existed) are served by the control database when it hosts the pool role; otherwise they do not exist. A pool whose creation has not been published is not served.
• Enrollment-only requests (claim, claimant status and completion) resolve the enrollment's locator in the control database, then the pool's route. Only a control database hosting the pool role resolves enrollments stored before locators existed.
• Nonces go to the replay shard of the device's bucket (see Replay protection).
• Pool creation reserves in the control database, initializes on the destination shard and publishes there (see above); interrupted creations are resumed at startup and then every minute.

Any number of relay processes may serve the same databases; each caches routes and shard connections. Every pool shard records the route generation at which it serves each pool, and every request checks it inside its transaction, so a relay with a stale route (the pool was moved) refreshes it once, and a pool that is moving is answered with a retryable 503 unavailable (Retry-After: 1) without reading or writing anything. A registered shard that is not configured on a relay, unreachable (opening one is bounded by 10 seconds), not current, or whose stamped identity does not match its registration fails only the requests of its own pools, the same way. A shard is verified again after it failed, so a URL that now reaches another database fails closed. Clients retry with a new request.

Relay Administration's pages work on every relay backend and read only the database authoritative for them:

• The overview reads the control database: its schema state, grant counts and the published statistics (with their freshness: fresh, stale or unknown). The other configured databases are listed with their driver, roles and connection pool counters and a postgres://host:port/dbname endpoint, never credentials or connection parameters; they are not queried. Only a SQLite control database also reports its file, WAL and page sizes; nothing runs a PRAGMA or probes a file on PostgreSQL. Relay-wide enrollment counts are shown only when every pool is stored on the control database; on a relay with pool shards they are reported as unavailable instead of reading every shard. Settings → Storage shows the same schema state, database list and SQLite sizes.
• Creation permissions (creation grants) are issued, listed and revoked in the control database on SQLite and PostgreSQL alike.
• The Teams page reads the control database. When the pools live on pool shards it lists the published pool summaries: a new team appears, and its counts change, after its shard's next statistics collection (one minute by default), and the list has no update times.
• A team's detail, members, history and invitations (enrollments) are read on the pool's shard. A pool without a statistics row reports its record counts as unknown.

Moving pools

A pool moves from its shard to another registered, active pool shard while relays keep serving it:

merkhain move pool pol_... pool-b    # start the move and run it to completion
merkhain move status                 # live moves; `move status ID` shows one move and its progress
merkhain move resume ID              # continue an interrupted move (stopped, crashed, or failed)
merkhain move cancel ID              # undo a move that has not fenced its source yet
merkhain move cleanup ID             # remove a done move's copy from the source shard

The move first fences the destination so it refuses the pool, then journals the keys of the pool's changed rows on the source and copies the pool in pages while it is written. It applies the journal until it is drained and verifies the copied records. Then it fences the source, which waits for in-flight writes: from here the pool answers 503 unavailable (clients retry) until the move completes. It makes the destination equal to the source and verifies the membership, devices, enrollments, key wraps, records, record counter and deduplication keys, advances the route in the control database, and activates the destination. Record sequences and client cursors are unchanged; other pools on either shard are not affected.

Every step is recorded in the control database and is idempotent, so move resume continues an interrupted move from where it stopped, from any machine with the relay configuration. Runs of the same move may overlap. Before the source is fenced, move cancel stops the journal and removes the partial copy; afterwards a move can only be resumed, so a failed or interrupted move keeps that pool unavailable until it is resumed. At no point do two shards accept writes of the pool.

The source keeps its fenced copy, so relays that still route to it are redirected, until move cleanup. Cleanup refuses while the source holds the pool again (it moved back). Moving a pool back to a shard that still has an old copy replaces that copy. Moves need a pool route: pools without one (stored on a control database that hosts the pool role) cannot move, and a SQLite relay is a single database. The pause while the pool is fenced grows with the rows changed during the move and the rows of the pool other than records (records are verified before the fence). Until cleanup, per-shard row counts include the fenced copy; the published statistics count a pool on its routed shard only.

A relay binary older than pool migration 634 does not know removal manifests and would lose them in a move. It can no longer finish one: on a pool with manifests its fence fails with relay_manifest_fence before the point of no return and the pool keeps serving from the source, so cancel the move or run it with a current binary. A pool without manifests moves as before. A move a current binary took part in cannot be activated by an older one (relay_manifest_guard); if an older binary has already switched the route, the pool is unavailable until a current binary resumes the move, which restores the manifests from the fenced source; move cleanup refuses a move that is not done, so that source stays. Upgrade every relay process before you move pools.

Moving a replay bucket

A bucket moves to another registered, active replay shard while relays keep serving it. Buckets are numbered 0 to 4095. move status, move resume ID and move cancel ID work as for pools. move status lists live bucket moves under bucket_moves:

merkhain move bucket 1234 replay-b   # start the move and run it to completion

The move is a job in the control database that resumes from its last step after a crash or failure:

1. The destination fences the bucket, so it refuses the bucket's nonces until the end.
2. The source's records of the bucket are copied to the destination online. The bucket stays available.
3. The source fences the bucket, after waiting for its in-flight requests. From here the bucket is unavailable and the move can only go forward; before, it can be cancelled.
4. The records are merged again from the frozen source, keeping the later expiry where both shards have a record, and checked.
5. The bucket's route moves to the destination at the next generation.
6. The destination activates the bucket.

Only the moving bucket is unavailable, from step 3 until step 6, and requests for it get a retryable 503. Relays with a stale route refresh it. The source never accepts the bucket again unless a later move brings it back at a higher generation. A move deletes nothing and has no cleanup step: the source's copy expires and is pruned like any other record, pruning keeps running during a move, and the source's fence row stays as the forwarding tombstone. A cancelled move leaves the destination fenced with the records copied so far; both only refuse more, and a later move to that destination starts from there. Selecting a bucket's records reads the whole nonce table of the source (once online, and once while the bucket is unavailable), which holds only the replay window, so the pause grows with the source's nonce count.

Several relay processes

Any number of serve remote processes, on one host or several, may serve the same databases (PostgreSQL only; a SQLite relay is one process on one host). Each process caches routes and shard connections and refreshes them on its own, so no coordination between processes is needed. For a deployment behind a load balancer:

• Give every process the same database configuration, including every shard: a shard missing from one process's configuration fails the requests of its pools on that process.
• Give every process the same operator token. Each process creates <config_dir>/relay-operator-token on its first start if it is missing; copy one file to every host before their first start (or replace the others and restart). The token only authorizes pool creation.
• Keep clocks synchronized (NTP). Request signatures and nonce expiry are checked against each process's clock.
• Operator console sessions are node-local. An operator's browser session (/ui/) exists only in the memory of the process that issued it (8 hours, at most 64 per process) and ends when that process restarts. Route the console with session affinity: a sticky cookie or source-address hash for /ui/, or send /ui/ to one designated process. Without affinity the console signs the operator out at random. Creation grants are stored in the control database and work from every process. The device API (/v1/) needs no affinity.
• Every process collects pool statistics from every shard each minute and prunes nonces on every replay shard. Both are idempotent; the work grows with the number of processes.
• Budget connections: processes × databases × max_conns.

Backup and restore

Back up every database before migrate up, transfer and anything else on this page that changes data.

SQLite (instance and relay): online sqlite3 FILE "VACUUM INTO '/backup/…'", or stop the service and copy the file with its -wal and -shm. Details: Operations and Relay.

PostgreSQL, one database: pg_dump takes a consistent snapshot while merkhain runs:

pg_dump -Fc -d 'postgres://bz@db1/merkhain_alice' -f merkhain-$(date +%F).dump
pg_restore -d 'postgres://bz@db1/merkhain_alice_restored' merkhain-$(date +%F).dump   # into an empty database

Restore into an empty database (or schema) with all merkhain processes using it stopped, point the configuration at it, and check merkhain migrate status before starting. A restored database must have the schema the binary expects or a newer one: an older dump of the instance database is migrated by the next normal open (a relay database's by migrate up), and a newer one is used as is by binaries from the bridge release on.

PostgreSQL, sharded relay: separate pg_dumps of control and the shards are not one consistent snapshot. A control database restored to an earlier point than a shard can route pools and buckets differently from what the shards hold. A restored older pool shard loses acknowledged records, which clients then report as relay_mismatch / membership fork; a restored older replay shard forgets consumed nonces. For a consistent set, stop every relay process, dump every relay database, then start them again, or use point-in-time recovery of every server to the same moment. Restore the whole set, never control alone, and with every relay process stopped. Prefer the replication below to restores for durability.

Keys are not in any database; pair database backups with the owner recovery export (Operations).

High availability and failover

merkhain acknowledges a request only after its commit, and reads authorization, replay protection and synchronization data from the database it writes to; there is no read-replica setting. Whether an acknowledged commit survives the loss of a PostgreSQL server therefore depends on how that server is replicated. merkhain does not provision replication, promote standbys or move addresses.

The supported setup, for every relay database (control, pool shards, replay shards) and a PostgreSQL instance database:

• Synchronous standbys. synchronous_commit = on (the default; remote_apply also works) with the standbys named in synchronous_standby_names. A commit then waits until a standby has flushed it, so nothing is acknowledged that the standby lacks. Run at least two standbys with ANY 1 (s1, s2) or FIRST 1 (s1, s2), so losing one does not stop writes.
• Fenced promotion. Before promoting a standby, stop the old primary for good (power it off, kill it, or cut it off from clients), then promote the standby and repoint the database's address. Running relays reconnect on their own and requests fail retryably meanwhile; a changed URL in the configuration needs a restart (running processes keep their connection and log restart_to_apply).
• Keep the old primary fenced. Fencing is PostgreSQL configuration plus this procedure: merkhain has no epoch or fencing token and cannot tell an old primary from the current one. An old primary cannot acknowledge anything only while no synchronous standby follows it. Before it can run or be reached again, repoint every other standby to the new primary or stop it: with ANY 1 (s1, s2) or FIRST 1 (s1, s2), a second standby that still follows (or reconnects to) the revived old primary lets it acknowledge writes, a split brain. Never let clients reach it either: crash recovery makes the commits that were waiting for the standby visible to readers. Rebuild it as a standby of the new primary.
• Prefer a virtual IP to a DNS change. Connections to a primary that vanished without closing them (powered off, cut off) are noticed only when TCP keepalive gives up, after about 2.5 minutes, and until then the relay answers unavailable. A virtual IP moved to the promoted server ends them at once; a DNS change does not. The database URL cannot shorten this: client keepalive parameters such as keepalives_idle or tcp_user_timeout are not supported and would be sent to the server as settings.

After such a failover every acknowledged record keeps its sequence and envelope, pool and replay-bucket routes and fences keep one owner, and every used request nonce is still refused as a replay. Clients retry the requests that failed during the switch with new nonces.

Asynchronous standbys are not a supported failover target. With asynchronous replication (an empty synchronous_standby_names, synchronous_commit = local or off, or any lagging replica) the primary acknowledges commits the standby has not received. Promoting such a standby loses them: records that clients consider uploaded disappear, other records reuse their sequences (clients that pulled the lost ones never fetch the new ones), nonces of used requests are forgotten, and lost route changes can leave a pool or bucket with two owners. Use asynchronous replicas for backups only, and restore from them like from a backup (Backup and restore), not as a failover. Never let two servers accept writes for the same database.

While no synchronous standby is connected

Writes wait instead of being acknowledged. merkhain never cancels a commit that waits for a standby, because PostgreSQL would then make it visible on the primary without the standby having it, and PostgreSQL's statement_timeout does not end that wait either. Meanwhile:

• clients give up after their own deadline and retry later; nothing is acknowledged, and retries are idempotent;
• each waiting request holds a connection and its pool's (or bucket's) lock, so further writes to that pool fail with busy after 10 seconds, and requests fail with unavailable once the database's connections (max_conns) are taken. Reads keep working while connections remain;
• migrate up and the completion of transfer local/transfer relay wait the same way;
• restarting relays does not help: the waiting transactions stay on the server.

Treat such a primary as degraded, not hung. Alert when pg_stat_replication shows no sync or quorum standby and when pg_stat_activity shows backends with wait_event = 'SyncRep'. Then either reattach a synchronous standby (the waiting commits complete durably) or fail over to it with a fenced promotion (they were never acknowledged). pg_terminate_backend, pg_cancel_backend, a fast shutdown or emptying synchronous_standby_names release them as committed locally but not replicated, so a failover afterwards loses them: do that only when no standby will be promoted and the standbys are rebuilt from this primary.

Search on SQLite and PostgreSQL

Recall uses each engine's native full-text search through the same query syntax (words, "phrases", OR, -exclusions, prefix*), the same scopes, resolved and superseded visibility, fallbacks and result limits. Results can differ:

Search page cursors are bound to the engine and refused by the other one; start the search again.

Capacity

The relay benchmark report measures pool concurrency, replay, statistics freshness and move pauses on one laptop: PostgreSQL 16 and 18 with control, two pool shards and two replay shards on two servers, three relay processes, and a single-process SQLite relay as the baseline. Its numbers belong to that host, dataset and load. It is not a capacity statement, and no figure for any number of users, developers or devices is claimed. Rerun it on your own hardware with packaging/bench/relay.sh (Docker; PG_VERSIONS, REPEAT and the MERKHAIN_BENCH_* knobs). What it showed, and what follows from the design:

• One pool is one writer at a time. Appends to a pool are serialized by the pool's lock until commit, so a hot pool is bound by commit latency (about 535 appends/s on that host, with the other writers waiting for the lock). Writes to different pools, and nonces of different requests, run concurrently; independent pools do not wait on each other.
• SQLite has one writer per database. Every signed relay request writes a nonce, so a SQLite relay serializes all writes. On one host without a network hop it was faster than PostgreSQL for every measured workload; PostgreSQL adds shards, several processes and online moves, not single-host speed.
• Connections: with the default 16 connections per database and process, no request waited for a connection at 16 concurrent clients. Budget processes × databases × max_conns against max_connections.
• Pool moves paused the moving pool for about 125–205 ms (pools up to ~7k records or 256 devices; larger ones were not measured). Most of the pause is the final comparison while fenced, which grows with the pool's membership and device rows and with the records appended since the last verification. Other pools were unaffected.
• Replay-bucket moves pause the moving bucket for one scan of the source shard's whole nonce table (about 9 µs per row on that host: 0.3–0.5 s for ~35k rows, 2.1–2.5 s for ~240k). A replay shard holds about 600 rows per sustained request/s it serves (nonces live about 10 minutes), so move buckets off a busy shard when it is quiet.
• Nonce pruning removes up to 10,000 rows per replay database and process each minute; while a round leaves expired rows behind, the next round follows after one second instead, so pruning keeps up with accepted traffic rather than capping it at about 170 signed requests/s per replay shard. Each device may send a burst of 600 signed requests, then 10 per second, per relay process (429 busy beyond that), which bounds the nonces one device adds.
• Published statistics lag by up to a minute plus the collection round; a round reads the pool shards one after the other (about 100 ms for two shards and ~66 pools under load), and takes up to about the number of shards times the shard timeout when shards are slow.

Not measured: network latency between relays and databases, replication, TLS, long runs, large datasets, more than two shards per role, and PostgreSQL instance databases (capture and recall).

Recovery from failed operations

Limitations

• SQLite and PostgreSQL 16 and 18 only. PostgreSQL runs with the english text search configuration; diacritic folding (unaccent) is not used.
• The recall comparison keeps its queue and fuzzy vocabulary in the SQLite evidence index on both dialects (recall-benchmarks/v1/index.db in the data directory), so its spelling candidates come from spellfix1 on both. Full-text search still runs on the instance database, with its engine's stemming and ranking, so the fuzzy results can differ between dialects.
• A SQLite relay is one database and one process; it has no shards and its pools cannot move. transfer relay moves it to PostgreSQL offline; there is no online path and no transfer back to SQLite.
• Pools stored on a PostgreSQL control database that hosts the pool role have no route and cannot move.
• Adding pool or replay shards to a configuration takes that role away from the database that hosted it. A control database that served every replay bucket, then gets replay shards, abandons the nonces it recorded for up to one replay window: configure replay shards before serving traffic, or keep the relays stopped for one window (about 10 minutes, twice the accepted 5-minute clock skew) before switching.
• Shards cannot be unregistered, and a registered shard's roles cannot change.
• The replay history check at the first bucket placement only sees the replay databases configured on the process that registers, and judges "live" by that host's clock.
• Duplicate databases are detected by identical URLs and by identity stamps on registration, not by comparing servers under different spellings before registration.
• Expired enrollment locators and published pool provisions are never cleaned up.
• db_sqlite_data_left_behind only checks the default <data_dir>/merkhain.db.
• On sharded relays the console's team list comes from published statistics (up to a minute old, no update times) and relay-wide enrollment counts are unavailable.
• merkhain does not detect an asynchronous replication setup, has no fencing token against a revived old primary, and does not bound a commit waiting for a missing synchronous standby. The failover tests promote one cluster with a single standby; the two-standby split brain that the fencing procedure prevents is not tested. A PostgreSQL instance database's failover is covered by the same adapter behavior, not by its own test.
• For contributors: go test -race ./internal/relay against PostgreSQL takes more than 10 minutes; CI gives it a timeout of 30 minutes. Keep the repository's test timeouts when running it locally.