107 lines
7.1 KiB
Markdown
107 lines
7.1 KiB
Markdown
# 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.
|