[OPS-01] Database Backup/Restore Automation + Point-in-Time Recovery (PITR)

[grantfox-oss]

Problem

Current docker-compose.yml only mounts a PostgreSQL volume (postgres_data). No automated backups, no PITR, no restore drills documented.

Risks

Scenario	Impact	Current Mitigation
Disk failure / volume corruption	Total data loss	None
Accidental DROP TABLE / bad migration	Hours of downtime	Manual pg_dump if remembered
Ransomware / TRUNCATE	Irrecoverable	None
Need to clone prod → staging	Manual, error-prone	None

Requirements

1. Automated Backups (PITR-ready)

• Tool: pgBackRest or wal-g (recommended: pgBackRest — supports S3/GCS/Azure, parallel, compression, encryption)
• Schedule:
  • Full backup: daily 02:00 UTC
  • Incremental: every 6h
  • WAL archiving: continuous (streaming replication slot)
• Retention: 30 daily fulls + 7 weekly + 12 monthly
• Storage: S3-compatible (MinIO for dev, AWS S3/GCS for prod) with lifecycle policy
• Encryption: AES-256 at rest (pgBackRest --repo1-cipher-type=aes-256-cbc)

2. Point-in-Time Recovery (PITR)

• Restore to any second within retention window
• Target: RPO < 5 min (WAL archiving), RTO < 30 min (restore parallelism)
• Documented pgbackrest restore --type=time --target=... procedure

3. Restore Automation (CI/CD + Runbook)

• Staging refresh job: Weekly, restores latest backup to staging-db, runs migrations, validates schema checksum
• Disaster drill: Quarterly, restore to fresh cluster, verify app comes up, document timing
• Runbook: docs/DISASTER_RECOVERY.md with:
  • Step-by-step restore commands
  • Contact tree (on-call, infra, security)
  • RTO/RPO targets
  • Post-restore validation checklist

4. Monitoring & Alerting

• Backup success/failure → alert (PagerDuty/Slack)
• WAL lag > 5 min → alert
• Backup age > 26h → alert (missing daily)
• Restore drill overdue → alert

Implementation Plan

Phase	Task	Owner	Est.
1	Add pgbackrest sidecar to docker-compose.yml (dev) + Helm chart (prod)	Infra	1d
2	Configure S3 repo, encryption, retention	Infra	0.5d
3	Implement WAL archiving (replication slot)	Infra	0.5d
4	Write restore script + staging refresh job (GitHub Actions)	Infra	1d
5	Document runbook (docs/DISASTER_RECOVERY.md)	Infra/Backend	0.5d
6	First disaster drill (schedule, execute, document)	Team	2h
7	Add alerts (Prometheus rules / pgBackRest webhook)	Infra	0.5d

Acceptance Criteria

• pgbackrest --stanza=micopay backup succeeds in CI
• pgbackrest --stanza=micopay restore --type=time --target="2026-07-27 12:00:00" restores to fresh DB in < 30 min
• Staging DB auto-refreshed weekly from backup (green check in Actions)
• docs/DISASTER_RECOVERY.md exists and is accurate
• Alerts fire on backup failure / WAL lag / stale backup
• Quarterly drill scheduled in team calendar

References

• pgBackRest: https://pgbackrest.org/
• PostgreSQL PITR: https://www.postgresql.org/docs/current/backup-pitr.html
• WAL archiving: https://www.postgresql.org/docs/current/continuous-archiving.html

[Timothy2025-20]

Hi maintainer,
I’m particularly interested in tackling this issue as it aligns well with my experience.

A bit about myself, I have 4 years of experience building production-grade software, so I’m comfortable with strict testing, CI pipelines, and writing clean, documented code. And i will like to resolve this issue if assigned

Thank you and Looking forward to collaborating

[grantfox-oss]

🦊 GrantFox — @Timothy2025-20 has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #343)
2. Your PR will be reviewed by the Micopay maintainers

Good luck! Track your progress on GrantFox.

[Timothy2025-20]

@grantfox-oss PR #346 is ready for review: #346

[grantfox-oss]

🎉 This issue has been marked as completed on GrantFox as part of the Official Campaign | FWC26 campaign!

@Timothy2025-20's PR #346 was approved and merged by @josealfredo79.

🏆 @Timothy2025-20: You earned 35 FoxPoints for this contribution! Your current tier: Explorer (802 total points). Track your full progress on GrantFox.

👏 Great work, @Timothy2025-20! Keep contributing to Micopay.