# Backup и restore

Production baseline: ежедневный `pg_dump` custom format, непрерывный WAL/PITR когда RPO этого требует, versioned S3 object copies и отдельный экспорт manifest/checksums. Копии шифруются recipient-based ключом вне сервера; retention задаётся политикой владельца. Секреты и ключ расшифрования в архив не входят.

Restore runbook: остановить записи, проверить checksum/decrypt, восстановить в новую БД, применить только необходимые миграции, сверить tenant counts/outbox/inbox/audit, проверить `/ready`, выполнить выборочную сверку вложений, переключить connection string, сохранить audit evidence. Никогда не восстанавливать поверх единственной production БД.

Тест восстановления обязателен по расписанию в изолированной среде. До принятия RPO/RTO и retention документ является архитектурным шаблоном, а не обещанием показателей.

Tenant export/import использует versioned archive, ULID, schema version, object references и checksum. Import идемпотентен и не может перезаписать другой tenant.

## Pilot implementation — K18

Status: implementation in progress. A pilot restore drill succeeded on 2026-09-26;
the remaining schedule, retention and migration gate are listed below. Do not
use the presence of an encrypted file alone as permission to migrate tenant data.

## Storage and keys

`deploy/pilot/backup.sh` streams `pg_dump -Fc` through OpenSSL CMS encryption.
Only the public recipient certificate belongs on the server. Keep the private
recipient key in separate, access-controlled storage outside that server, with a
recovery copy. Loss of this key makes backups unrecoverable.

Configure the variables in `deploy/pilot/backup.env.example` using an operator-owned
environment file, never a committed secrets file. For the current test setup, use
`BACKUP_TRANSFER_MODE=external-pull` and schedule `scripts/pull-pilot-backups.sh`
on this PC with `PILOT_SSH_TARGET`, `PILOT_REMOTE_BACKUP_DIRECTORY` and
`BACKUP_LOCAL_DIRECTORY`. The PC initiates SSH, so it does not need an SSH server.
Configure non-interactive SSH and verify the server's host key out of band. Do not
disable host-key verification. A SHA-256 sidecar detects transfer corruption;
it is not a digital signature. Other deployments may use push mode with an
off-host SCP destination.

The PC pull script keeps 90 days of local copies by default. Set
`BACKUP_LOCAL_RETENTION_DAYS` to an integer from 1 to 36500 to change this period.
After a successful pull and freshness check, it removes only direct child files with
the generated `krona-staff-<UTC timestamp>-<pid>.dump.cms` name whose one-line
SHA-256 sidecar names that same file and matches its bytes. It removes the matching
`.sha256` sidecar with the encrypted file. Unverifiable pairs and symlinks are
preserved, as are unrelated files and directories. This policy applies to PC copies;
it does not prune the server's backup directory.

The server script fails if dumping or encryption fails. In push mode it also fails
if off-host transfer fails. It does not write an unencrypted dump to disk. In
external-pull mode a successful server run reports `pending_pull`, never an
off-host success. The PC pull script verifies every transferred encrypted file
against its checksum and exits nonzero on transfer or integrity failure.

## Scheduling

The service and timer in `deploy/pilot/` are templates. Adjust their working and
configuration paths to the actual installation before enabling them. The timer
runs daily at 03:30 in the server's timezone, with up to ten minutes of jitter.
Monitor both the server service and the PC pull task exit statuses. Verify that
the PC directory contains a recent copy; the server-only encrypted file is not
evidence of off-host protection.

Do not enable the schedule until the destination, recovery-key custody, server-side
retention policy, and a successful restore drill have been agreed and tested.

## Required restore drill before tenant migration

1. Produce a consistent dump, encrypt it, and retrieve the off-host copy.
2. Verify the encrypted file's SHA-256 checksum at the destination.
3. Decrypt with the off-host key. Restore into a new disposable PostgreSQL 17
   instance, with no connection to the pilot network and no exposed database port.
   Never run a test provisioning script against the pilot cluster: it may alter
   roles or drop databases.
4. Restore required roles and grants, schema, data, forced RLS, constraints and
   migration history. A restore using `--no-owner --no-acl` alone is not enough
   evidence that production privileges can be recovered.
5. Compare per-table and per-tenant row counts and payroll ledger totals per
   currency against a manifest from the same consistent snapshot. Check foreign
   keys, forced RLS, and isolation using a non-superuser application role.
6. Run the tenant migration on that restored copy and verify that only the tenant
   changes: row counts and ledger totals are preserved, with one additional audit
   record for the migration.
7. Save a report containing timestamp, source identity, backup checksum, PostgreSQL
   version, commands and exit statuses, before/after counts, totals and isolation
   result. Do not include passwords, tokens, employee names, or biometric data.
8. Only after a successful drill and an agreed maintenance window may the pilot
   application be stopped and the operation repeated on a fresh backup.

## Outstanding acceptance items

- Off-host storage and retention policy selected and configured.
- Restore automation and repeatable reporting for later backups.
- A fail-closed backup gate before migrations in `setup.sh`.

Until these items are complete, K18 remains open and pilot tenant migration is blocked.

## Pilot restore drill — 2026-09-26

An encrypted `pg_dump -Fc` from `krona-staff-pilot-postgres-1` was pulled to the
owner's PC. Its SHA-256 was
`2fc680ae3a6ce63a27b53209da9b506bcee86b76d8bba9a6a06de24bac1dee47`.
It was decrypted on the PC and streamed through SSH into a new PostgreSQL 17
container with `network=none`, no host ports, and temporary storage. The restore
command returned 0 with ownership and ACLs restored. The reports produced by
`scripts/restore-drill-report.sql` matched for every tenant table: 51 employees,
3,830 ledger entries, 3,353 work items, 2,894 daily metrics and 49 audit rows;
the other nonempty tables matched too. All 27 tenant tables had ENABLE and FORCE
RLS. The RUB ledger sum matched at 717,829,954 minor units. A nonsuperuser,
non-BYPASSRLS application role saw 51 employees under the source tenant and zero
under the target. The restore report and encrypted backup are stored on the PC
in `C:/Users/trush/Documents/KronaStaffBackups/`. This was a restore drill, not
yet a tenant migration or a check that the backup schedule runs unattended.