Module 10 – Export, Backup & Data Portability

This module covers safe export formats, scheduled backups (pg_dump), uploads to object storage, and restore procedures for the MCP Attendance System.

1. CSV/Excel Export Endpoint (FastAPI)

Return CSV for month/semester reports. Streaming response avoids memory spikes.

# app/export.py
from fastapi import APIRouter, Depends
from fastapi.responses import StreamingResponse
import csv, io

router = APIRouter()

@router.get('/reports/monthly/csv')
async def export_monthly_csv(year: int, month: int, semester: int, db=Depends(get_db)):
    start = f"{year}-{month:02d}-01"
    end = f"{year}-{month:02d}-31"
    query = """
    SELECT st.student_code, st.name,
      (COUNT(*) FILTER (WHERE a.status='present')::float / NULLIF(COUNT(*),0) * 100) AS attendance_pct
    FROM attendance a
    JOIN students st ON st.id = a.student_id
    WHERE a.date BETWEEN :start AND :end AND st.semester = :semester
    GROUP BY st.id, st.student_code, st.name
    ORDER BY attendance_pct DESC;
    """
    rows = await db.fetch_all(query, values={'start':start,'end':end,'semester':semester})

    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow(['student_code','name','attendance_pct'])
    for r in rows:
        writer.writerow([r['student_code'], r['name'], r['attendance_pct']])
    buf.seek(0)
    return StreamingResponse(iter([buf.read()]), media_type='text/csv')

2. Scheduled Backup with pg_dump (Linux cron)

Simple daily backup with retention rotation.

#!/bin/bash
# /usr/local/bin/backup_mcp.sh
BACKUP_DIR=/backups/mcp
mkdir -p "$BACKUP_DIR"
FNAME="$BACKUP_DIR/mcp_db_$(date +%F).sql.gz"
pg_dump -U postgres mcp_db | gzip > "$FNAME"
# keep 14 days
find "$BACKUP_DIR" -type f -mtime +14 -name 'mcp_db_*.sql.gz' -delete
# optional: upload to S3
/usr/local/bin/aws s3 cp "$FNAME" s3://my-mcp-backups/ --storage-class STANDARD_IA

Cron entry (run daily at 02:00): 0 2 * * * /usr/local/bin/backup_mcp.sh

3. Backup to S3 using boto3 (Python)

This script uploads the latest backup to S3 and tags it with the date & env.

# upload_backup.py
import boto3
from pathlib import Path

s3 = boto3.client('s3')
BUCKET = 'my-mcp-backups'

def upload(path: str):
    p = Path(path)
    key = f"backups/{p.name}"
    s3.upload_file(str(p), BUCKET, key, ExtraArgs={'StorageClass':'STANDARD_IA'})
    print('Uploaded', key)

if __name__ == '__main__':
    import sys
    upload(sys.argv[1])

4. Restore Procedure (pg_restore / psql)

Restore full DB from a gzipped dump.

# restore_mcp.sh
set -e
FILE=$1
if [ -z "$FILE" ]; then
  echo "Usage: $0 backupfile.sql.gz"
  exit 1
fi
gunzip -c "$FILE" | psql -U postgres -d mcp_db

5. Point-in-Time Recovery & WAL Archiving (Optional)

Enable WAL archiving and continuous backup for critical systems. Configure postgresql.conf:

# postgresql.conf
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
wal_level = replica

6. Database Migrations (Alembic)

Use Alembic for schema migrations to keep environments in sync.

# Install
pip install alembic

alembic init alembic
# then configure alembic.ini to point to DATABASE_URL
# create migration after model changes
alembic revision --autogenerate -m "add attendance_audit"
alembic upgrade head

7. Docker-compose Backup Strategy

You can run pg_dump from a temporary container targeting the DB service.

# run from host to dump the running postgres container
docker run --rm --network $(docker-compose -f docker-compose.yml ps -q db | xargs docker inspect --format '{{range .NetworkSettings.Networks}}{{.NetworkID}}{{end}}') \
  -v $(pwd)/backups:/backups postgres:15 bash -c "pg_dump -h db -U postgres mcp_db | gzip > /backups/mcp_db_$(date +%F).sql.gz"

8. Data Portability & GDPR-like Considerations

• Provide per-student export (CSV) on request.
• Support data deletion/obfuscation requests — use soft-delete or anonymization.
• Limit backups retention based on policy (e.g., 7 years for academic records).

9. Quick Disaster Recovery Checklist

1. Identify last good backup file and WAL archives.
2. Restore DB to a staging environment.
3. Run integrity checks and sanity queries.
4. Switch application to restored database after smoke tests.