-- ───────────────────────────────────────────────────────────────────────────── -- 058 — Backup subsystem seed, sized for a 1 GB R2 budget -- -- The client's budget for this deployment is ONE GIGABYTE of R2, total. That is -- not a footnote — it dictates every retention number below, and it is small -- enough that the naive settings from the plan would blow it in under a day. -- -- The arithmetic that matters, and why archive_timeout is the dangerous knob: -- -- A WAL segment is 16 MiB. `archive_timeout` forces Postgres to CLOSE the -- current segment on a timer even when almost nothing has been written, and -- pgBackRest then archives that mostly-empty segment. Compressed with zstd an -- idle segment lands around 20-30 KB, which sounds free until you multiply: -- -- archive_timeout=60s -> 1440 segments/day -> ~35 MB/day -> ~1.0 GB/month ✗ blows the entire budget on WAL alone -- archive_timeout=300s -> 288 segments/day -> ~7 MB/day -> ~210 MB/month ✓ -- archive_timeout=900s -> 96 segments/day -> ~2 MB/day -> ~70 MB/month ✓✓ -- -- So the plan's production-shaped `archive_timeout=60s` is exactly wrong here. -- This deployment uses 300s, accepting a ~5 minute floor on RPO in exchange -- for a budget that holds. That is a deliberate, recorded trade — not an -- oversight — and it is the single most important line in this file. -- -- Budget allocation, 1024 MB total: -- -- Full backups 2 x ~25 MB = 50 MB (database is ~13 MB dumped today) -- Differentials 4 x ~10 MB = 40 MB -- WAL, 7 days 7 x 7 MB = 49 MB -- Merchant snapshots = 30 MB -- --------------------------------------- -- Working set = 169 MB -- Hard limit (90%) = 921 MB -- Headroom = 752 MB -- -- The headroom is deliberate and should not be spent. It absorbs a migration -- that rewrites a large table (which can generate more WAL in an hour than a -- normal week), and it is what lets a restore drill run without the drill -- itself pushing the repository over its own ceiling. -- -- Retention is 7 days here, not the 30 the plan describes. At 1 GB, 30 days is -- not purchasable. The admin screen must therefore show the ACTUAL window from -- recovery_points rather than a configured "30 days" that is not true — a -- recoverability claim nobody checked is the specific failure this whole -- subsystem exists to prevent. -- ───────────────────────────────────────────────────────────────────────────── BEGIN; -- ── The one cluster ────────────────────────────────────────────────────────── -- -- One row, not two. forgestack_shopora and forgestack_chatbot are databases -- inside a single cluster sharing one PGDATA and one archive_command, so they -- share one stanza and move together on any physical restore. INSERT INTO platform.backup_targets ("id", "code", "name", "engine", "stanza", "host", "port", "databases", "dsnRef", "pgDataPath", "sortOrder") VALUES ('bkt_forgestack', 'forgestack', 'Forgestack cluster', 'postgres', 'forgestack', 'localhost', 5432, ARRAY['forgestack_shopora', 'forgestack_chatbot'], 'DATABASE_URL', '/var/lib/postgresql/16/main', 0) ON CONFLICT ("code") DO NOTHING; -- ── Repositories ───────────────────────────────────────────────────────────── -- -- repo1 is local and exists so archive-push never blocks a commit when R2 is -- unreachable. It gets a small slice of local disk and short retention: its job -- is to absorb an outage, not to be a second archive. INSERT INTO platform.backup_repositories ("id", "code", "name", "kind", "repoIndex", "pathPrefix", "encrypted", "immutable", "appendOnly", "retentionFull", "retentionDiff", "retentionDays", "quotaBytes", "softLimitPct", "hardLimitPct", "sortOrder") VALUES ('bkr_local', 'local', 'Local spool (outage buffer)', 'posix', 1, '/var/lib/pgbackrest', false, false, false, 1, 2, 3, -- 2 GB of local disk. Larger than the R2 budget on purpose: this is the -- buffer that keeps Postgres writing when R2 is down, and running it out is -- how a stuck archive_command fills pg_wal and takes the database offline. 2147483648, 70, 90, 0) ON CONFLICT ("code") DO NOTHING; -- repo2 is R2 and carries the client's 1 GB ceiling. INSERT INTO platform.backup_repositories ("id", "code", "name", "kind", "repoIndex", "endpoint", "bucket", "region", "pathPrefix", "credentialRef", "encrypted", "immutable", "appendOnly", "retentionFull", "retentionDiff", "retentionDays", "quotaBytes", "softLimitPct", "hardLimitPct", "sortOrder") VALUES ('bkr_r2', 'r2-primary', 'Cloudflare R2 (1 GB budget)', 's3', 2, NULL, 'forgestack-db-backups', 'auto', '/pgbackrest', -- An env key. Resolved inside the agent, never read by the API. 'R2_BACKUP_WRITE', true, -- immutable/appendOnly start FALSE and are flipped on only once the Phase 0 -- spike has actually verified R2's object-lock behaviour. Claiming -- immutability we have not tested is worse than admitting we lack it. false, false, 2, 4, 7, 1073741824, 70, 90, 1) ON CONFLICT ("code") DO NOTHING; -- ── Policies ───────────────────────────────────────────────────────────────── -- -- Weekly full + daily diff + 6-hourly incremental. No 30-day retention here: -- retentionDays 7 matches what the budget actually buys. INSERT INTO platform.backup_policies ("id", "targetId", "name", "backupType", "cronExpression", "timezone", "windowMinutes", "retentionCount", "retentionDays", "slaHours", "sortOrder") VALUES ('bkp_full', 'bkt_forgestack', 'Weekly full', 'full', '0 2 * * 0', 'Asia/Kolkata', 240, 2, 7, 180, 0), ('bkp_diff', 'bkt_forgestack', 'Daily differential', 'diff', '0 2 * * 1-6', 'Asia/Kolkata', 240, 4, 7, 30, 1), ('bkp_incr', 'bkt_forgestack', '6-hourly incremental','incr','0 */6 * * *', 'Asia/Kolkata', 60, 8, 3, 8, 2), -- Merchant snapshots: the nightly baseline. Triggered snapshots are the -- exception and are admitted by rule in code, not scheduled here. ('bkp_snap', 'bkt_forgestack', 'Nightly merchant snapshots', 'snapshot', '15 3 * * *', 'Asia/Kolkata', 120, 3, 3, 30, 3) ON CONFLICT ("id") DO NOTHING; -- ── Maintenance tasks, per database ────────────────────────────────────────── -- -- These are per-database because VACUUM/ANALYZE/REINDEX genuinely are, unlike -- backups. Nothing here holds a lock by default: requiresLock stays false, so -- a blocking task is an explicit choice an operator makes, not something that -- happens at 3am because a default said so. INSERT INTO platform.maintenance_tasks ("id", "targetId", "code", "name", "taskType", "databaseName", "cronExpression", "windowMinutes", "maxLoadPct", "requiresLock", "sortOrder") VALUES ('mt_analyze_shopora', 'bkt_forgestack', 'analyze.shopora', 'Nightly ANALYZE (shopora)', 'analyze', 'forgestack_shopora', '0 4 * * *', 120, 70, false, 0), ('mt_analyze_chatbot', 'bkt_forgestack', 'analyze.chatbot', 'Nightly ANALYZE (chatbot)', 'analyze', 'forgestack_chatbot', '10 4 * * *', 120, 70, false, 1), ('mt_bloat_shopora', 'bkt_forgestack', 'bloat.shopora', 'Weekly bloat report', 'bloat_report', 'forgestack_shopora', '30 4 * * 0', 120, 80, false, 2), ('mt_index_shopora', 'bkt_forgestack', 'index_usage.shopora', 'Weekly index-usage report', 'index_usage', 'forgestack_shopora', '45 4 * * 0', 120, 80, false, 3), -- REINDEX CONCURRENTLY: non-blocking, but slow and WAL-heavy. On a 1 GB -- budget a full reindex can generate more WAL than a week of normal traffic, -- so it is monthly and the quota check will refuse it if the repo is tight. ('mt_reindex_shopora', 'bkt_forgestack', 'reindex.shopora', 'Monthly REINDEX CONCURRENTLY', 'reindex', 'forgestack_shopora', '0 5 1 * *', 240, 60, false, 4), ('mt_purge_shopora', 'bkt_forgestack', 'retention_purge.shopora','Daily retention purge', 'retention_purge', 'forgestack_shopora', '20 3 * * *', 60, 80, false, 5), ('mt_health_shopora', 'bkt_forgestack', 'health.shopora', 'Hourly health snapshot', 'connection_report','forgestack_shopora', '5 * * * *', 15, 95, false, 6) ON CONFLICT ("code") DO NOTHING; COMMIT;