Backup verification¶

SqlBak takes the database backups and uploads them to S3. This job proves the objects in S3 can actually be restored — which SqlBak's own test jobs do not, because they verify the local copy rather than the S3 object we would fall back on if we lost a database server.

It runs at 09:00 daily on tools-server-02 only, downloads the newest full backup for each production database, restores it into a scratch database, checks the result and then deletes everything again.

A full run over all five databases takes about 1 hour 40 minutes on the current 8 GB instance, and is almost entirely I/O bound — measured CPU time is under 12 minutes of that.

Disaster recovery runbook¶

What to do when a production database is actually lost or corrupted. The numbers below are measured, not estimated — read the caveat on timings before quoting one to anyone.

What you have¶

Daily logical dumps, one object per database, in s3://bckup-storage-024893219915-eu-north-1-an/db/daily/<name>/ named <name><YYYYMMDDHHMM>.zip (region eu-north-1).

Download and extraction took under two minutes even for bkdk.

Because these are logical dumps, you can restore one database without touching the others. That matters here: bk-db-01 runs bkdk, bknl and bkse in the same MariaDB instance. A physical backup would be all three or nothing. If one site's database is corrupted, this is the only option that leaves the other two alone.

What you do not have¶

• Point-in-time recovery. SqlBak's incremental backups are enabled, but the chain has never been replayed and this job does not verify it. Assume you can recover to the last full backup — up to 24 hours of loss — until someone has proven otherwise.
• Binary logs off-site. They stay on the production database hosts, so losing the server loses them too.

Timings, and why yours will be slower¶

Every figure above was measured on tools-server-02, which runs with innodb_flush_log_at_trx_commit=0 and innodb_doublewrite=0. That is safe there because the data on it is disposable.

A production host with durable defaults will be slower, and by how much has not been measured here — all three measurement runs had durability off. Treat "roughly double" as a planning assumption, not a number to quote. If it matters, measure it once on the target host with bkse, which takes 90 seconds.

During a restore you can legitimately turn durability off, because a crash mid-import means starting over regardless. There is nothing to lose that is not already lost.

This is a global setting, not a session one, so it affects everything else on that instance for as long as it is set — which on bk-db-01 means the two other live databases. Set it back the moment the import finishes, before the restored database takes traffic:

SET GLOBAL innodb_flush_log_at_trx_commit = 0;   -- before the import
SET GLOBAL innodb_flush_log_at_trx_commit = 1;   -- immediately after

innodb_doublewrite needs a restart on MariaDB 11.8, so it is only worth changing when you are restoring onto a host that is not serving yet.

The procedure¶

Run this on the target host, not on tools-server-02 — the verification host has a 127 GB volume sized for one database at a time, and moving a restored tablespace between machines is slower than restoring again.

1. Get the credentials. Both live in the vault, and both are already on tools-server-02 if you need them fast:

ansible-vault view inventories/tool-server/host_vars/tools-server-02/vault.yml
# vault_restore_verify_aws_access_key_id / _secret_access_key
# vault_restore_verify_archive_password

2. Find the newest full backup. Incrementals live in the same prefix, so match the full-backup name explicitly:

aws s3 ls "s3://bckup-storage-024893219915-eu-north-1-an/db/daily/bkdk/" \
  --region eu-north-1 | grep -E 'bkdk[0-9]{12}\.zip$' | tail -5

Check the date before you download. Restoring yesterday's backup over today's partially working database is a second incident.

3. Download and extract. The password goes in over stdin so it stays out of the process list:

aws s3 cp "s3://…/db/daily/bkdk/bkdk<stamp>.zip" /var/tmp/ --region eu-north-1
printf '%s
' "$ARCHIVE_PASSWORD" | 7z x -y -o/var/tmp/restore /var/tmp/bkdk<stamp>.zip

Budget about 1.6× the source database size — measured — for the extracted .sql and the InnoDB tablespace together, because both exist on the same filesystem at once. For bkdk that is roughly 83 GB from a 2.1 GB archive, so sizing from the archive will mislead you badly: the ratio is about 45×, which is what restore_verify_expansion_factor uses.

4. Import. Into a new database name, not over the damaged one — you want the option to compare, and to fall back if the restore is worse than what you have:

mariadb -e "CREATE DATABASE bkdk_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci"
{ echo "SET SESSION unique_checks=0;"
  echo "SET SESSION foreign_key_checks=0;"
  cat /var/tmp/restore/*.sql
} | mariadb -D bkdk_restore

If the import stops with error 1227 ("you need the SET USER privilege"), the dump is defining views or routines as a production account that does not exist on this host. Strip the clause rather than granting the privilege:

sed -E -e 's/DEFINER=`[^`]*`@`[^`]*`//g' \
       -e "s/DEFINER='[^']*'@'[^']*'//g" /var/tmp/restore/*.sql | mariadb -D bkdk_restore

5. Check it before you switch. The same checks the daily job runs, by hand.

The table prefix differs per installation — wpn0_ on one site is not wp_ on the next — so find it before you use it:

SELECT table_name FROM information_schema.tables
 WHERE table_schema = 'bkdk_restore' AND table_name LIKE '%options';

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'bkdk_restore';
SELECT COUNT(*) FROM bkdk_restore.<prefix>options;
SELECT COUNT(*) FROM bkdk_restore.<prefix>posts;
SELECT MAX(post_modified) FROM bkdk_restore.<prefix>posts;

Compare the table count against /var/lib/restore-verify/history.json on tools-server-02, which holds what the last verified restore saw. That file is the reason you are not guessing here.

MAX(post_modified) tells you how much you actually lost — the honest number to give the business, rather than "up to 24 hours".

6. Switch over. Rename or repoint, then set durability back if you changed it, and confirm the site works before deleting anything.

Expected RTO¶

For the largest site, bkdk: about 5 minutes to fetch and extract, 50 minutes to import with durability off, plus checks and switch-over. Call it 1.5 hours, and more like 2 on a host with durable defaults.

Everything else is faster, and bkse is under 10 minutes end to end.

If the business ever needs an RTO under an hour for bkdk, that is the point to revisit physical backups (mariabackup). It was deliberately not built: the gain would be roughly 20 minutes against 50, for the cost of another agent running on the production database hosts.

What it checks¶

Once per run, before any target¶

Per database¶

The drift check is the one that catches a backup which quietly stopped including tables. A plain restore test would load such a backup happily.

required_tables and min_rows do different jobs, and the difference is easy to miss:

• required_tables is structural — the table must exist. It covers WordPress core (options, posts, users, postmeta) and WooCommerce HPOS (wc_orders, wc_order_addresses, wc_order_operational_data, wc_orders_meta). All table names are given without a prefix; the real prefix is detected in the restored database.
• min_rows is both an absolute floor and the list of tables the drift check compares against the previous successful run. A table not listed here is never compared, so it could be halved without anyone noticing. Tables where no honest minimum exists are listed with 0 — that buys the drift comparison without asserting a count. wc_orders is one of them: the table exists on every site, but sites that have not enabled HPOS keep their orders in posts/postmeta and leave it empty.

required_tables is a list, and Ansible's combine(recursive=True) replaces lists rather than merging them. Exempting one target therefore means restating the whole list in its own sanity: block. min_rows is a mapping and merges key by key.

Note that the drift baseline in history.json is only updated after a successful run. A failure therefore compares against the last known-good state rather than against yesterday's failure.

Why the object filter is a whitelist¶

SqlBak uploads incremental backups into the same prefix as the full ones, and an incremental is a set of binary logs rather than a dump. Picking "whatever is newest" would restore one as if it were a full backup and fail with "almost certainly truncated" or "no .sql file inside the archive" — both pointing at the wrong thing while the real full backup is fine.

A monitor that goes red for the wrong reason every morning is worse than no monitor, because a genuine failure then looks like the usual noise. So anything that does not match the known full-backup naming is ignored, and a prefix holding only incrementals fails with a message that says exactly that.

What it does NOT check¶

Worth stating plainly, so the green check-in is not read as more than it is.

• Stored routines, triggers and events. The sanity checks count tables and rows only, and none of these sites use routines, triggers or events, so no check for them is deliberately built. The SqlBak user still needs the EVENT privilege, which it was missing until 2026-08-25 — not because there are events to dump, but because mysqldump aborts the whole dump on the SHOW EVENTS denial regardless.
• The incremental chain. Only the newest full backup is restored. Point-in-time recovery on top of it is unproven.
• Binary logs. They never leave the production database hosts, so a lost server also loses them.
• Referential integrity. The import runs with foreign_key_checks=0 and unique_checks=0 for speed, exactly as a real restore would.
• That the data is correct. It proves the backup loads and is roughly the size it was yesterday. It cannot tell you the application wrote the right rows.

Alerting¶

Each database has its own Sentry Cron Monitor, slug restore-verify-<name>. The monitor configuration is upserted on every check-in, so adding a target to restore_verify_targets creates its monitor automatically — nothing to set up in the Sentry UI.

Sentry raises an issue when a run reports error, when a run does not check in at all, and when a check-in starts but never finishes within max_runtime. The last two are the ones that matter most: a job that silently stopped running is the usual way a verification setup rots. Routing to Slack is configured in Sentry, not in this repository.

A run killed by the systemd timeout takes the third path — the in-progress check-in is never closed, and Sentry marks it failed once max_runtime passes.

Every check-in is logged with its HTTP status:

restore-verify-bkse: check-in 'ok' accepted by Sentry (HTTP 202)

A 202 is not proof the check-in was stored. The ingest endpoint answers 202 for "queued for processing" and discards the payload later, silently, if anything downstream refuses it.

That is not theoretical. The five monitors existed, looked configured, and sat at "waiting for first check-in" for weeks while every request came back 202. The cause turned out to be the Sentry cron monitor quota: the upsert still created the monitor, and check-ins against it were accepted and then dropped. Nothing in the response said so, and nothing in the journal could have.

Two things follow from that, and both are now built in. The check-in carries only a status, a trace context and the monitor configuration, matching the cURL example Sentry shows on the monitor's own setup page — there is no check-in id, because the endpoint returns none and Sentry pairs the in_progress and terminal check-ins by slug itself. And the HTTP status is logged either way, so a broken channel is at least visible to anyone reading the journal.

But logging only proves the request left the machine and was accepted. The only end-to-end proof that alerting works is a deliberately missed run raising an issue in Sentry. Do that once, on purpose, and then again after any change to this integration — or to the Sentry plan.

Check-in margin¶

restore_verify_sentry_checkin_margin_min is how long after the scheduled time a check-in may arrive before Sentry counts it as missed. It is set to 240 — the whole run window — and not to something tight, because all five monitors share one schedule while the run is sequential.

The timer fires once at 09:00 and the databases are verified one after another, so the last monitor does not check in until roughly an hour later:

Plus up to five minutes from RandomizedDelaySec. With the default margin of 30 minutes, bkdk would have raised a missed check-in every single morning and kddk intermittently — and a daily false alarm is the fastest way to teach a team to ignore the monitor.

The value deliberately tracks restore_verify_max_runtime_min: systemd kills the run at that point, so a check-in that has not arrived by then genuinely means the run did not get there. The cost is that a run which never started at all is noticed at 13:00 rather than 09:30. For a daily job that is the cheaper error.

One trap when changing it: monitor_config is upserted when the monitor checks in. A new margin for bkdk would therefore not be written until 09:46, after the old margin had already expired, so the first run after the change still raises one false alarm. Push the configuration ahead of the run — one check-in per slug, or an edit in the Sentry UI.

Why a failure looks the way it does in Sentry¶

The cron monitor tells you which database failed, because the slug names it. It carries no free text, so on its own it cannot tell you why.

The reason is shipped separately, to Sentry Logs. Every line the run logs — object keys, sizes, durations, sanity and drift results, and every warning — is sent to the OTLP endpoint derived from the same DSN (restore_verify_sentry_otlp_url), so a failure reads as

bkdk: newest backup is 28h old, limit is 12h

in Sentry, with database as a searchable attribute and no SSH session needed.

The run and the check-in share a trace id, and each database gets its own span, so a failed monitor links to exactly the lines that explain it.

Three deliberate choices worth knowing:

• Shipped after every database, not once at the end. The run that most needs its logs is the one systemd kills at TimeoutStartSec, and a single shipment at the end would never be sent. The exit trap makes a final attempt, so the target that was in flight when the kill landed is included.
• Successful runs ship too. Import durations and object sizes become queryable history rather than something that lives only in journald and in a status.json that is overwritten every morning.
• Logs are not the alarm. The cron monitor is. The OTLP endpoint is in open beta, and log retention is 7–30 days depending on the Sentry plan against 90 for errors — but if log shipping breaks, you lose the explanation, not the warning, and the journal still has it. Set restore_verify_sentry_otlp_url to "" to turn it off.

Log volume is roughly a hundred lines a day, against 5 GB included.

Timeout¶

restore_verify_max_runtime_min becomes TimeoutStartSec on the service unit.

It applies to the whole run, not to each database. The script has no per-target timeout, so a slow first database eats the budget of the ones after it. This is what ended the run of 2026-08-20 mid-import of bknl: two smaller databases had already spent part of the same two-hour budget.

It is currently 240 minutes against a measured 1h38m on the 8 GB instance.

Buffer pool sizing¶

mariadb-buffer-pool.service runs before every MariaDB start — at boot and on a manual restart — and writes innodb_buffer_pool_size into /etc/mysql/mariadb.conf.d/71-buffer-pool.cnf from the RAM the machine has at that moment. The value in 60-performance.cnf is only a floor that is safe to boot with on any instance size.

This exists because the figure used to be deployed from host_vars, which coupled it to whoever ran the playbook last. Both directions of getting it wrong hurt: too small on a large machine and imports crawl silently at a fraction of the speed; too large on a small machine and MariaDB refuses to start at all. A Hetzner resize requires the server to be powered off, so boot is the one moment guaranteed to see the truth.

The unit is pulled in with Wants=, not Requires=, so a broken generator leaves MariaDB starting on the previous value rather than not starting.

How much RAM is actually needed¶

Measured across three instance sizes on the same data:

8 GB is enough. Only the largest database notices, and it costs 6.7%. Anything above that is wasted — 24 GB bought nothing over 9 GB.

CPU time barely moves across machines with 16 down to 4 vCPUs, so a faster processor would not be measurable here. Choose the instance type on price.

Running it manually¶

sudo systemctl start --no-block restore-verify.service
journalctl -u restore-verify -f
systemctl list-timers restore-verify.timer

--no-block is not optional. The unit is Type=oneshot, so systemctl start waits for the whole run — and if the SSH session drops, the job is cancelled with it.

Progress is logged every restore_verify_progress_interval_sec (300 by default) during an import, as a percentage of the size the last successful run recorded. That is what distinguishes a slow import from a hung one in the journal alone.

Read the result:

sudo jq . /var/lib/restore-verify/status.json    # last run, per database
sudo jq . /var/lib/restore-verify/history.json   # baseline for the drift check

To keep the restored data for debugging — it is deleted by default:

sudo /usr/local/sbin/restore-verify.sh --keep

--keep leaves production personal data on the host. The script prints the exact commands to clean up when it finishes.

Proving the alarm works¶

A restore test that has never been seen to fail is not evidence. Neither is a green check-in: it says the job ran, not that the job would have noticed.

/usr/local/sbin/restore-verify-selftest.sh runs the real job against the real backup with one deliberately broken expectation at a time. Per scenario it asserts three things:

1. the job exits non-zero
2. the expected message appears in the journal
3. an error check-in is accepted by Sentry

The third is the point. A check that fails without reporting is the same as no check at all.

sudo restore-verify-selftest.sh all          # every scenario, on the smallest database
sudo restore-verify-selftest.sh drift        # one scenario
sudo restore-verify-selftest.sh all bknl     # against a specific database

drift is the most valuable of the six. The other checks compare against fixed numbers written in this repository; drift compares against what the last successful run actually saw, so it is the only one that notices a database shrinking on its own. It is also the only one that cannot be verified by reading the configuration.

Everything it changes — config.json and the drift baseline in history.json — is restored on every exit path, including a kill, and each scenario narrows the run to a single database so a full pass costs about ten minutes rather than two hours.

Run it after any change to the job, to the sanity rules, or to the Sentry integration. A single scenario leaves the monitor red until the next successful run; happy clears it immediately.

It refuses to start while a run is in flight, and it re-checks per scenario that systemctl start actually began a new invocation. Both matter more than they look: restore-verify.service is Type=oneshot without RemainAfterExit, so it reports activating for its whole run and never becomes active. A guard built on systemctl is-active is therefore blind at exactly the moment it needs to see, and systemctl start against a unit already running does not start anything — it joins the existing job and returns that job's result. On 2026-08-28 that made the first scenario grade a completely different run and report a broken freshness check that was never broken.

What it still does not prove¶

That Sentry raises an alarm when the job does not run at all. That is the failure mode a verification setup actually dies of, and it cannot be tested from inside the job. Stop the timer for a day and confirm a missed check-in appears — or read one off a real incident, as happened on 2026-08-27 when bkdk checked in at 09:52 against a margin that expired at 09:30.

Adding a database¶

1. Add the scratch database to mariadb_databases in inventories/tool-server/host_vars/tools-server-02/main.yml. The name must start with bckp_test_.
2. Add an entry to restore_verify_targets in the same file.
3. Deploy: make tool ENV=tool-server.

The MariaDB grants and the Sentry monitor need no configuration — both are derived from restore_verify_targets, and the table prefix is detected in the restored database.

restore_verify_targets:
  - name: newsite
    source_host: kd-db-01
    s3_prefix: "db/daily/newsite/"
    target_database: bckp_test_newsite
    # table_prefix: "wp_"      # only if auto-detection cannot decide
    # max_age_hours: 48        # only if this backup runs on another schedule

Check the disk first. Peak usage is roughly 1.6× the source database size, measured — the extracted .sql and the InnoDB tablespace both live on the same filesystem. It is per database, not cumulative, because each target is purged before the next one starts, so the volume only has to hold the largest.

Why it cannot touch production data¶

Five independent layers, each of which holds on its own:

1. restore_verify_enabled defaults to false. The role is only in tool.yml.
2. An assert fails the playbook if the role is loaded for a host in the db group, if it is enabled outside restore_verify_allowed_hosts, or if any target_database does not match ^bckp_test_[a-z0-9_]+$.
3. The MariaDB user is granted privileges only on the declared bckp_test_* databases. Even a completely misconfigured script is refused by the database engine.
4. The script re-checks the hostname, the marker file /etc/restore-verify/enabled and every database name at run time.
5. The IAM key is read-only: s3:GetObject and s3:ListBucket on one prefix. No write, no delete. SqlBak's own key is deliberately not reused.

Data protection¶

The scratch databases are emptied after every run — DROP DATABASE followed by CREATE DATABASE, so the empty database still matches its mariadb_databases declaration.

Cleanup runs from an EXIT trap, with TERM and INT routed through it, so it also runs when systemd kills the job at its timeout — which is a normal ending here, not a rare accident. Without it, an interrupted import leaves a scratch database full of production rows.

The work directory is emptied rather than removed (find … -mindepth 1 -delete). ProtectSystem=strict makes its parent read-only, so removing the directory itself fails with "Read-only file system".

Two settings would otherwise retain restored rows and are handled explicitly:

• Binary logging would keep every imported row for the binlog retention period. The role asserts log_bin is OFF on this host and refuses to deploy otherwise.
• The slow query log stores full statement text. The import session sets long_query_time = 3600 so bulk inserts are never logged.

Secrets¶

The archive password is read into a shell variable and passed to 7z over stdin, so it never appears in /proc/<pid>/cmdline or in the journal.

It lives in vault rather than in a secrets manager for a specific reason: the AWS key that can download every backup is in the same vault. Splitting the two would buy an audit trail, not confidentiality — anyone holding the vault gets the archives either way. Keeping both in one place matches the convention every other secret in this repository follows.

Rotation is ansible-vault edit followed by a playbook run against tools-server-02. The value must match the password set on the backup jobs in the SqlBak UI.

The archives are encrypted with ZipCrypto, not AES-256, despite the setting in the SqlBak UI. This was confirmed by inspecting a real archive. ZipCrypto is cryptographically broken and falls to a known-plaintext attack — and a SQL dump is about as predictable a plaintext as exists, opening with the same mysqldump header every time. The archive password is therefore not a meaningful control against someone who obtains the objects. Confidentiality rests on the S3 bucket policy and the IAM keys. This is worth raising with SqlBak.

Troubleshooting¶

no full backup under s3://… Either the prefix or the IAM policy is wrong, or only incremental backups have landed. Objects not matching <name><YYYYMMDDHHMM>.<ext> directly under the prefix are skipped deliberately — list the prefix and compare.

extraction failed Look at the line immediately before it: <name>: extractor said: … carries 7z's or gunzip's own message, and it is what tells a wrong password from a damaged download. Without it the two incidents raise an identical alert.

ERROR: Wrong password means vault_restore_verify_archive_password does not match the password set on the SqlBak job. A CRC or data error means the object in S3 is corrupt, which is a far more serious finding — the backup itself is bad, not the verification of it.

That detail was added on 2026-08-28 after a deliberate wrong-password test raised a correct alarm that could not say why.

expected exactly one table ending in 'options' Either the restore is incomplete, or the database holds more than one WordPress installation. Set table_prefix explicitly on that target.

import failed Check whether the dump contains statements the scoped user is not allowed to run. Three are handled automatically: CREATE DATABASE and USE are commented out, and DEFINER= clauses are stripped from views, triggers and routines.

That last one is why you will not see error 1227 ("you need the SET USER privilege"). A production dump defines its views as DEFINER=`someuser`@`somehost`, and creating an object that runs as another account requires a global privilege this user must never hold. The clause is removed so the object belongs to the importing user instead. Nothing is lost: the production account does not exist on this host, so a verbatim import could never work here, and what we verify is that the schema and data restore — not who owns a view.

Anything else that needs a global privilege is a genuine finding. Do not fix it by widening the grant: the scoped grant is the strongest guarantee that this job cannot touch real data.

only NGB free The run-wide preflight against restore_verify_min_free_gb. It is a health gate, not a capacity calculation — the per-target check below is the real protection.

needs about NMB (… bytes x N) but only NMB is free The per-target check, which runs before each download and knows how large that particular archive is. Either the database has outgrown the host, or restore_verify_expansion_factor is set too high.

The factor is peak bytes on disk divided by the compressed archive size — the extracted .sql plus the InnoDB tablespace it becomes, not simply the compression ratio. Derived per database from the source sizes and two direct measurements, it comes to 26–41; the configured 45 covers all five with margin. Do not lower it per target to make something fit: it is the only thing standing between a grown database and a full disk.

another run is still in progress Expected if a run overruns into the next timer firing.

flock releases the lock when the holding process exits, so there is no such thing as a stale lock here: if you see this message, something really is holding it. Find it rather than clearing it —

sudo fuser -v /run/restore-verify.lock
systemctl show -p ActiveState --value restore-verify.service

Note that ActiveState reads activating, not active, for the whole run: restore-verify.service is Type=oneshot without RemainAfterExit. Do not delete the lock file. Removing it while a run holds it does not stop that run — it just lets the next one create a fresh file and start in parallel, which is the exact overlap the lock exists to prevent.