Phase 4 — Backup & Restore Multi-DB Support #109

New issue

Open

opened 2026-07-09 09:56:57 +12:00 by fastie81 · 0 comments

fastie81 commented

2026-07-09 09:56:57 +12:00

Owner

Issue: Phase 4 — Backup & Restore Multi-DB Support

Type: Refactoring / Feature
Priority: High
Epic: Epic: Database-per-Club Multi-Tenancy Split & Data Migration
Status: Backlog

1. Context & Purpose

The backup manager currently performs a single pg_dump of the monolithic database. When data is split across a Central database and multiple Club databases, the backup service must be modified to export/import multiple databases to guarantee full system recovery.

2. Technical Specification

2.1. System Backup Refactoring (`backup_service.py`)

Modify create_system_backup() in app/core/orchestration/backup_service.py:

Dump the Central database:
- Run pg_dump on the Central database using the primary connection credentials → export to central.dump.
Loop through all registered clubs:
- Query the Central database clubs table for all clubs with a database_name (e.g. honbu_club_<id>).
- For each club database, run a separate pg_dump command → export to club_<id>.dump.
Zip all dump files (central.dump and all club_*.dump files) along with member photographs and club logos into a single system backup zip file.
Clean up the intermediate .dump files.

2.2. System Restore Refactoring (`backup_service.py`)

Modify restore_system_backup(zip_filepath):

Unzip the backup file into a temporary folder.
Restore the Central Database:
- Run pg_restore on central.dump to populate the Central database schema and configuration.
Restore Club Databases:
- Locate all files matching club_*.dump in the temporary folder.
- Extract the club ID from the filename.
- Connect to the database server using administrative credentials.
- Run DROP DATABASE IF EXISTS honbu_club_<id> and CREATE DATABASE honbu_club_<id>.
- Execute pg_restore with the target club_<id>.dump file to populate the schema and data for each club.
Move restored photos and logos back into their respective static asset folders.

2.3. Club-level Backup Refactoring

Modify create_club_backup(club_id):

Construct the database name (honbu_club_<club_id>).
Run pg_dump targeting only that specific club database.
Package the database dump along with photos belonging to that club into a single ZIP file.

2.4. Profile Photos Upload and Persistence Verification (Issue #90)

Verify and resolve the issue where uploaded profile photos do not save and appear blank upon refresh.
Audit photo storage operations in app/core/operations/member_ops.py and repository integrations to guarantee write persistence before backup routines execute.

3. Verification Plan

3.1. Automated Unit Tests

Write tests in tests/unit/test_backup_split.py:

Mock connection metadata and CLI commands (subprocess.Popen / subprocess.check_call).
Assert that calling create_system_backup executes pg_dump exactly 1 + N times (where N is the number of active clubs).
Assert that restore_system_backup executes database creation and restore commands for each backup file.

3.2. Manual Verification

Deploy to staging with multiple clubs.
Upload images to member profiles.
Run System Backup from the admin interface.
Inspect the generated ZIP file; ensure central.dump and files like club_1.dump exist inside.
Trigger a restore of the backup and verify all members, photos, and club settings load correctly.
All 192 tests must pass successfully.

Part of Epic #105

# Issue: Phase 4 — Backup & Restore Multi-DB Support **Type:** Refactoring / Feature **Priority:** High **Epic:** [Epic: Database-per-Club Multi-Tenancy Split & Data Migration](file:///mnt/f/forgejo-git/club-manager/docs/database_split_issue.md) **Status:** Backlog --- ## 1. Context & Purpose The backup manager currently performs a single `pg_dump` of the monolithic database. When data is split across a Central database and multiple Club databases, the backup service must be modified to export/import multiple databases to guarantee full system recovery. --- ## 2. Technical Specification ### 2.1. System Backup Refactoring (`backup_service.py`) Modify `create_system_backup()` in [app/core/orchestration/backup_service.py](file:///mnt/f/forgejo-git/club-manager/app/core/orchestration/backup_service.py): - Dump the Central database: - Run `pg_dump` on the Central database using the primary connection credentials → export to `central.dump`. - Loop through all registered clubs: - Query the Central database `clubs` table for all clubs with a `database_name` (e.g. `honbu_club_<id>`). - For each club database, run a separate `pg_dump` command → export to `club_<id>.dump`. - Zip all dump files (`central.dump` and all `club_*.dump` files) along with member photographs and club logos into a single system backup zip file. - Clean up the intermediate `.dump` files. ### 2.2. System Restore Refactoring (`backup_service.py`) Modify `restore_system_backup(zip_filepath)`: - Unzip the backup file into a temporary folder. - Restore the Central Database: - Run `pg_restore` on `central.dump` to populate the Central database schema and configuration. - Restore Club Databases: - Locate all files matching `club_*.dump` in the temporary folder. - Extract the club ID from the filename. - Connect to the database server using administrative credentials. - Run `DROP DATABASE IF EXISTS honbu_club_<id>` and `CREATE DATABASE honbu_club_<id>`. - Execute `pg_restore` with the target `club_<id>.dump` file to populate the schema and data for each club. - Move restored photos and logos back into their respective static asset folders. ### 2.3. Club-level Backup Refactoring Modify `create_club_backup(club_id)`: - Construct the database name (`honbu_club_<club_id>`). - Run `pg_dump` targeting only that specific club database. - Package the database dump along with photos belonging to that club into a single ZIP file. --- ### 2.4. Profile Photos Upload and Persistence Verification (Issue #90) - Verify and resolve the issue where uploaded profile photos do not save and appear blank upon refresh. - Audit photo storage operations in `app/core/operations/member_ops.py` and repository integrations to guarantee write persistence before backup routines execute. ## 3. Verification Plan ### 3.1. Automated Unit Tests Write tests in `tests/unit/test_backup_split.py`: - Mock connection metadata and CLI commands (`subprocess.Popen` / `subprocess.check_call`). - Assert that calling `create_system_backup` executes `pg_dump` exactly `1 + N` times (where `N` is the number of active clubs). - Assert that `restore_system_backup` executes database creation and restore commands for each backup file. ### 3.2. Manual Verification - Deploy to staging with multiple clubs. - Upload images to member profiles. - Run System Backup from the admin interface. - Inspect the generated ZIP file; ensure `central.dump` and files like `club_1.dump` exist inside. - Trigger a restore of the backup and verify all members, photos, and club settings load correctly. - All 192 tests must pass successfully. --- **Part of Epic #105**

fastie81 referenced this issue from a commit

2026-07-20 14:30:55 +12:00

feat(backup): Refactor system and club backup/restore for multi-tenancy (#109, #90)