[Operability/Data resilience] Establish backup, restore, and disaster-recovery acceptance evidence

[seonghobae]

Acquisition / operator gap

Protected develop@622e5e6c3d534f230c390f10e3832efadfc01825 persists primary PostgreSQL state in the Compose postgres_data volume and replica/CDC target state in replica_postgres_data, while durable-job/idempotency/migration work increasingly makes PostgreSQL authoritative operational state. Persistent volumes are not backup/recovery evidence; the product needs a versioned recovery contract for each supported deployment profile and truthful evidence of what can and cannot be restored consistently.

This does not mean the repository should invent an SLA, cloud backup product, or one universal database topology.

RCA

• Immediate cause: persistent volumes and migration/recovery semantics existed without an executable backup/restore acceptance path.
• Technical root cause: application durability, database migration recovery, CDC/Kafka offsets, connector side effects, and deployment backup ownership evolved as separate concerns.
• Control failure: CI did not bind a backup to exact source/schema identity, restore it into an explicitly empty target, and prove application-level invariants.
• Acquisition risk: buyers cannot infer recovery guarantees from storage durability or a successful pg_dump alone.

Bounded architecture

Preferred baseline — profile-specific PostgreSQL recovery contract

For each supported standalone/deployment profile, identify authoritative database/store ownership, encryption/key responsibility, deployment-owned PostgreSQL-native backup mechanism, exact backup provenance, restore/version compatibility, post-restore schema/application invariants, CDC/Kafka/DLT/external-target reconciliation, and forward-recovery boundaries.

Alternative — host/platform-owned managed backup

For managed PostgreSQL deployments, backup/PITR implementation may remain host-owned, but mightyETL still defines and tests application-level consistency/restart/reconciliation against a restored database.

Rejected shortcuts

• treating Docker named volumes as backup;
• copying a live PostgreSQL volume directory without a database-consistent method and calling it recoverable;
• inventing RPO/RTO numbers without measured restore evidence;
• restoring application tables while ignoring Flyway/schema lineage;
• claiming database restore reverses Kafka/warehouse/external side effects;
• storing backup credentials/keys in Git or ordinary logs;
• using migration clean as rollback.

Live implementation state — active PR #208

Draft PR #208 (ops/postgres-recovery-manifest-622e5e6) remains the bounded direct-develop owner for repository-level PostgreSQL recovery evidence. Active-PR behavior is not implemented_on_develop until protected integration.

Exact current source head: f33de0a8a6709947c0b19a94a931f1870afa386c.

The branch currently provides:

• private pg_dump --format=custom logical backup bundles under umask 077;
• pg_restore --list structural verification before publication/restore;
• exact application source SHA, PostgreSQL server version, Flyway schema version, archive SHA-256 and UTC creation provenance;
• independently supplied archive and manifest SHA-256 evidence before mutable bundle provenance is trusted;
• same-filesystem atomic backup-bundle publication plus collision reservation;
• fail-closed restore into an explicitly provisioned empty target only;
• same PostgreSQL-major compatibility enforcement;
• pg_restore --exit-on-error --single-transaction --no-owner --no-privileges so a restore-command failure cannot be accepted after partial application;
• post-restore Flyway schema-version verification;
• required mightyETL relation checks for processed_data, etl_idempotency_records, and etl_job_records;
• restored idempotency-ledger validation against the protected lowercase-64-hex key/digest constraints;
• restored durable-job validation against protected hash/digest constraints, lifecycle state domain, nonnegative attempt count, payload lifecycle, and the exact V2 failure-code grammar ^[a-z][a-z0-9_]{2,127}$;
• count-only/stable failure classifications without exporting restored row contents or identifiers;
• explicit separation from Kafka, Debezium, DLT and external-target recovery domains;
• truthful RPO: not measured / RTO: not measured status.

Latest TDD evidence

Representative durable-row test-only head 5ea1d328a40544240504d271ebf8a5f01292d723 reached hosted CI with production/test compilation healthy and ETL 287 tests with exactly one failure, zero errors/skips because the restore script did not yet evaluate restored idempotency rows. Subsequent GREEN head 41f840d49f170496bf60cf2fd0d2f210712b88a4 made those checks pass.

A fresh schema-authority audit then found the first implementation's recovery-only failure_code regex was looser than protected V2. Test-only head 510d033982f935aa9d933ea90dcb52824f2c6b7a required the exact migration grammar first; hosted CI again ran ETL 287 tests with exactly one failure, zero errors/skips, specifically restore must enforce the exact protected durable-job failure-code grammar. Exact current GREEN f33de0a8a6709947c0b19a94a931f1870afa386c changes only that regex to the protected V2 authority.

Current-head hosted evidence is terminal-success: CI 31380283009 passed on macOS/Ubuntu/Windows; Dependency Review 31380283063, CycloneDX SBOM 31380283122, SAST Semgrep 31380283074, and Security Scan 31380283100 all completed successfully. macOS CI checked synthetic merge fb4743f2d2908944e455dd717e3d57750da582e5 and ran ETL 287/287, CDC 106/106 and gateway 3/3 green with full reactor BUILD SUCCESS.

These results remain development/merge-preview evidence. The current workflow checked the synthetic merge, protected JaCoCo still reports Analyzed bundle 'etl-service' with 0 classes, scanner dependency-graph completeness remains #196, and formal independent approval is absent. Nothing here is protected-merge authorization.

Remaining acceptance

1. canonical PRD/TRD/Architecture/ERD/Operability/Migration/Release/Security/Threat Model/Traceability identifies durable state ownership and backup/restore authority per supported deployment profile;
2. exact backup artifacts carry independently verifiable integrity/provenance and enough version/schema metadata to reject incompatible restores;
3. a realistic ephemeral PostgreSQL rehearsal executes backup -> destructive loss/replacement -> restore -> service startup/readiness -> migration validation -> application invariant checks;
4. durable job/idempotency rows, immutable replay/cancellation evidence and owner/tenant boundary chosen by [Product/Data authority] Decide and enforce tenancy isolation model #186 survive or fail according to a documented contract as those schemas integrate;
5. restore failure cannot leave an accepted partially restored state;
6. restored data cannot silently mix with newer incompatible schema/application state;
7. PostgreSQL rollback/restore interaction with Kafka/Debezium offsets and [Data governance/Reliability] Govern CDC dead-letter payload retention and replay authority #181 DLT state is explicitly reconciled or fail-closed;
8. external target side effects are never claimed rolled back unless the connector proves transaction/idempotency/compensation semantics;
9. backup data receives at least the confidentiality/tenant/retention/access protection of primary data and privileged restore access is auditable;
10. failure during restore has a defined incident/retry/replacement path and does not destroy the last known good backup;
11. any RPO/RTO/SLO claim is profile-specific and measured; until then documentation remains not measured;
12. release acceptance ([Release/Supply chain] Establish reproducible exact-head artifacts and provenance #165) binds compatible migration/restore evidence to the exact release artifact;
13. accepted literal-source CI/security, complete scanner/dependency evidence, non-vacuous owned-production coverage, independent review and protected-develop operational proof pass before acquisition-ready recovery claims.

Current classification

active_pr via #208 for bounded PostgreSQL backup/restore evidence. Full disaster recovery remains known_gap: application startup/readiness on a restored database, destructive-loss replacement procedure, cross-domain Kafka/Debezium/DLT/external-target reconciliation, future replay/cancellation/tenant state, release binding, and measured RPO/RTO are not yet proven.

Relationships: #143–#148 durable-state lifecycle; #162/#164/#205 coverage evidence; #165 release/provenance; #181 DLT lifecycle; #183/#184 Flyway schema authority; #186 tenancy; #196 scanner completeness; #159 canonical documentation.