ochenstarik/krona-staff
1
0
Fork 0
Code Issues Pull requests 1 Projects Releases Packages Wiki Activity Actions
krona-staff/docs/BACKUP-RESTORE.md

7.1 KiB
Raw Permalink Blame History

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.