skaidb v0.320.9

Witness and BI nodes

Part of Running a skaidb cluster.

Standalone nodes that mirror a cluster continuously without joining the ring: off-site read copies, partial mirrors, how the catalog follows, how to verify a mirror is complete, and the BI node built on the same mechanism.

Witness nodes

A witness is a standalone node (never a ring member, never counted in quorums) that mirrors chosen databases from a primary cluster on its own schedule — a cross-region, pull-based backup. Configure [witness] in its toml (primary SQL + internode addresses, witness-scoped credentials, databases) and pair it with server.read_only = true (drivers may connect for read-only queries; every mutation is refused for non-superusers). Pulls ride the internode protocol near-live: per-table write_seq change hints gate incremental stamps-walked delta pages (interval_secs, default 60 s), with a full sweep every full_sweep_interval_secs (default 24 h) as the anti-entropy backstop; pulls self-pace to at most witness.duty_pct (default 50 %) of the serving member's capacity. The primary's witnesses registry drives the overview-tab sync detail and holds tombstone GC back (up to witness_gc_config.grace_period_secs) until every live witness has pulled the deletes — per table: each table's delete markers are held only for the witnesses that actually mirror it, and only back to their watermark for that table, so one witness stuck on one table does not pin every other table's tombstones.

Two independent controls decide what a witness holds. The primary opts a table out for all witnesses with WITH (witness = false) / ALTER TABLE t SET (witness = false). Each witness then narrows what is left with witness.tables / witness.exclude_tables (see below) — the data owner decides what may leave the cluster, each backup decides what it does carry. A witness can never widen past the primary's flag. Row tables come from wherever a complete copy lives: one member with failover for a table every member holds whole, any live pin for a pinned table, and a scatter over every member (merged by last-writer- wins) for a table no single member holds whole. The primary reports that last case as SHOW TABLES' sharded column, true for an RF override below the member count and for a default-placement table when the cluster's default RF is below it. An open placement transition also scatters, over members and pins. Time-series tables mirror too: their samples move via time-windowed TsQuery pulls (scattered over every member — series placement shards them on RF < members primaries — unioned, deduped, merged any-aged), with the same watermark + full-sweep-backstop ladder as row tables. On the witness they are recreated as plain TIMESERIES tables with the source's series key but NO retention (a backup keeps what the primary ages out); rollups mirror as plain time-series tables (the rollup→source link is derived state).

Setting one up

On the primary, create the witness's scoped role. It needs SELECT as well as the writes — registration reads the row back before deciding between an INSERT and an UPDATE, so a role granted only INSERT, UPDATE fails on its first beat:

CREATE USER nasw_witness PASSWORD '<generated>';
GRANT SELECT ON witnesses TO nasw_witness;
GRANT INSERT ON witnesses TO nasw_witness;
GRANT UPDATE ON witnesses TO nasw_witness;

One privilege per statement: GRANT SELECT, INSERT … is a parse error, and so is CREATE USER … WITH PASSWORD.

Those grants are the witness's entire reach on the primary. It reads table schemas and rows over the pull path, which is authenticated separately by [auth] — the SQL role exists only to keep its registry row current.

On the witness host, run a standalone node — no seeds, never a ring member — with [witness] pointed at the primary's SQL and internode addresses. Both lists matter: the control plane (registration, schema listing) is SQL, the bulk pull is internode, and a firewall that passes only one produces a witness that registers and never mirrors.

[server]
read_only = true                     # the copy is a backup, not a replica to write to
data_dir  = "/var/lib/skaidb"

[cluster]
seeds = []                           # a witness never joins the ring

[auth]                               # must satisfy the primary's internode auth
internode_auth      = "cert"
internode_tls_cert  = "/etc/skaidb/tls/node.crt"
internode_tls_key   = "/etc/skaidb/tls/node.key"
internode_tls_ca    = "/etc/skaidb/tls/ca.crt"

[witness]
enabled                  = true
witness_id               = "nasw"    # stable identity; pairs with the primary's registry
region                   = "nas"
databases                = ["agencik"]
primary_sql_addrs        = ["10.0.0.1:7000", "10.0.0.2:7000", "10.0.0.3:7000"]
primary_internode_addrs  = ["10.0.0.1:7100", "10.0.0.2:7100", "10.0.0.3:7100"]
user                     = "nasw_witness"
password                 = "<generated>"
primary_tls              = true      # required if the primary sets client_tls = "required"
interval_secs            = 60

Mirroring part of a database

databases is the coarse control. Within those databases, tables is an allowlist and exclude_tables a denylist, both naming db.table with an optional trailing * for a prefix. Empty tables (the default) means every table; the denylist is applied after the allowlist.

[witness]
databases      = ["agencik"]
tables         = ["agencik.core_*", "agencik.gmail_emails"]  # only these
exclude_tables = ["agencik.core_scratch"]                    # minus this

Use it when one backup should not carry everything: a cheap off-site box that mirrors the small operational tables but not a 200 GB vector table, or two witnesses splitting a large database between them.

Every database, including ones that do not exist yet: databases = ["*"]. The wildcard is re-resolved on every pull cycle from the primary's SHOW DATABASES, so a database created after the witness started is on the mirror after the next cycle — no config change, no restart (with an explicit list, a new database is invisible to a BI node until an operator adds it on every witness and restarts each). default (the cluster's own bookkeeping) and the internal underscore databases are never part of it; a BI node still adds its _bi. Leave one database out with exclude_tables = ["scratch.*"]; names may accompany the wildcard. /status and the BI app show the resolved names, not *. Notes:

The catalog, not only the rows

A witness mirrors each table's views, foreign keys, check and exclusion constraints as well as its data, so a BI node's object pages and lineage graph describe the cluster's real schema rather than reporting that every table has none. Constraints are replayed NOT VALID — a mirror is partial by construction and enforces nothing anyway, since it applies replicated rows rather than client writes.

Three kinds are deliberately never replayed. A STORED generated column would run a whole-table rewrite through the ordinary write path, restamping every row with a local clock, after which the pull reads the primary's rows as older and stops converging — silently. NOT NULL validates every existing row and has no NOT VALID escape. DEFAULT and IDENTITY only affect writes, which a mirror never takes. A VIRTUAL generated column is replayed: nothing is stored under it and no ALTER on it touches a row (ADD … VIRTUAL is refused while a row carries the column, by a read-only scan), so the mirror holds the expression and computes the column from the primary's rows exactly as the primary does — a BI node reads total on the mirror and gets the same value.

Secondary indexes are opt-in, because they have to be built:

[witness]
mirror_indexes = true    # default false

On a mirror that is still filling, an index created before the rows arrive is maintained as they land and costs nothing extra. On one that already holds its data the first cycle after enabling this backfills every index — in the background, all of a table's indexes in one scan of it, with the node still serving — but a scan of every mirrored table is a real operation on a live node, so an operator asks for it rather than discovering it. It is worth asking for on a BI node, where analytical reads are exactly what an index helps; SHOW INDEXES reports (building) until each is ready.

  • Value-TOAST companions follow their base table. __toast__<t> holds a table's out-of-line blobs; it is never matched against these lists on its own, because a mirror holding a table without its blobs resolves those fields to nothing.
  • A database the primary drops is dropped on a wildcard witness once it has been missing from two consecutive successful SHOW DATABASES answers (one odd answer never erases data), together with the witness's sync state for its tables. An explicitly listed database is never dropped this way — like a deselected table, it is the operator's to remove.
  • A deselected table stops holding tombstones for this witness on its next cycle — it drops out of the heartbeat's watermarks, which is exactly how the primary learns to stop waiting for it. Deletes to that table may then be collected without this witness ever seeing them, so re-adding a table later is a full-resync-and-rebuild, not a resume.
  • Data already mirrored is not removed when a table leaves the selection; the local copy simply stops advancing. Drop it on the witness if you want the space back.
  • Selection is the witness stating what it carries, not an access control: the primary serves any table the witness's [auth] credentials can reach. Use the SQL role's grants for a boundary that has to hold.

The witness's certificate must be signed by the same CA the primary's internode port trusts — it is authenticated as a peer, not as a client. primary_tls covers the separate SQL connection; getting one right and the other wrong is the usual reason a witness starts, registers, and then mirrors nothing.

Verify it, in this order — the two questions have different answers and different sources:

# On the witness: has a cycle ever COMPLETED?
curl -s http://<witness>:7081/status | jq '.role, .last_pull_at, .databases'

# On any member: is it alive, and is it current?
curl -sk https://<member>:7443/status | jq '.witnesses[]'

last_pull_at is null until the first cycle finishes, and the first cycle copies everything — on a large database over slow storage that can take hours while the mirror is working perfectly. seen_age_secs answers "alive" and keeps moving throughout; sync_age_secs answers "current" and stays null until that first cycle closes. Alert on the second, not the first: a witness that is up but failing every cycle heartbeats on schedule, so only sync_age_secs (or last_pull_at) exposes it.

A cycle that ran is not the same as a mirror that is complete. One table that cannot be pulled no longer ends the cycle — the other tables stay current, the failed one keeps its watermark and its sweep cursor for the next attempt, and the cycle line says so:

witness: cycle ok — 71 tables, 148 rows pulled, 86 applied … — 1 TABLE(S)
NOT MIRRORED: onet.m_acme (no source reachable: busy: engine write-locked)

/status carries the same list as failed_tables, so an alert can watch it directly rather than parsing the log.

A slow bulk page — a time-series sweep or a scan page that takes longer than the internode read timeout — no longer counts toward the peer's circuit breaker. A loaded peer taking its time over fifty thousand samples is saying "I am busy", not "I am gone", and counting it used to suspend that peer for every other table in the same cycle. The timeout is judged by a quick ping instead: a loaded peer answers it at once, and only a peer that ignores the ping too (gone, or accepting connections without ever replying) counts. A refused or reset connection still counts, on any verb. A __toast__ companion is not listed when its parent already failed; it is listed when it failed alone, because then the rows are mirrored and their large values are not.

Registered witnesses are part of the admin surface even though they are outside the ring: RESET OOM COUNTER includes every registered witness (and accepts a witness id as its ON '<node>' target) by stamping the request on the witness's registry row — the witness applies it on its next pull cycle, exactly once per stamp, offline gaps included. The result row reports absorbed = NULL with a "stamped" status, since the apply happens on the witness's own schedule.

Seeing witnesses from outside. A witness is not a member, but the cluster does know it, and both sides say so on the unauthenticated GET /status. Each member publishes its registry:

"witnesses": [
  { "witness_id": "onetw", "alias": "onetw", "region": "slug",
    "registered_at": 1785251756333, "last_seen_at": 1785338156333,
    "seen_age_secs": 61, "sync_age_secs": 7204 }
]

sync_age_secs (oldest per-table watermark; null before the first cycle) is the one to alert on — a witness heartbeats on schedule, so seen_age_secs stays small even while one table is stuck. The matching Prometheus gauges are skaidb_witness_last_seen_age_seconds and skaidb_witness_oldest_sync_age_seconds (see METRICS.md).

The witness's own /status self-describes, so its witness_id pairs with the entry above and a monitor can file it under the cluster it backs up instead of as an unrelated standalone server:

{ "clustered": false, "ready": true, "role": "witness",
  "witness_id": "onetw", "region": "slug", "databases": ["onet"],
  "primary_sql_addrs": ["10.0.0.1:7000"],
  "interval_secs": 60, "duty_pct": 25,
  "last_pull_at": 1785338096128 }

It reports the primary by endpoint, not name: a witness mirrors only its configured databases, not default where the cluster's name lives, and its scoped role is granted the witnesses table alone. last_pull_at is this node's own last SUCCESSFUL cycle (null until one completes) — that is the tell for a witness that is up but failing every cycle, which never gets to send the heartbeat the primary-side gauge watches. Judge liveness against duty_pct, not interval_secs: at 25 % a long cycle rests roughly three times as long as it ran, so gaps far exceeding the interval are normal.

BI nodes

A BI node is a witness that also serves the business-intelligence app at /bi, so analytical reads land on a machine that serves nothing else. It is deployed exactly like a witness — same package, same unit, same readiness probe — with one extra section:

[bi]
enabled = true
advertise_url = "https://bi.example.net:7443/bi"

bi.enabled is refused on a cluster member (analytics there is what the node type exists to avoid) and requires server.read_only on a witness.

It advertises itself by writing roles and bi_url into its own witnesses row on every heartbeat — the only way a primary can learn a witness's HTTP address, since the registry is pull-only. Every other node then answers GET /bi with a 302 to it (path and query preserved, ?bi=<witness_id> pins one (a pinned node quiet for a day falls through; an unlisted name gets the "no BI node" page), ?region= prefers one, a node unseen for three heartbeats is skipped, and a node never redirects to itself), or with a page explaining how to deploy one when none is registered. witnesses[] in /status carries both fields; \bi in skaidbsh prints where the app lives.

See BI.md for the full reference.