Database Backup & Disaster Recovery Runbook

Educational data is mission-critical. A comprehensive backup strategy ensures academic transcripts, payment ledgers, and attendance records are protected against hardware failure, human error, or data corruption.

1. Automated PostgreSQL Daily Backups

Run this script as a root cron job on the host machine every night at 2:00 AM:

#!/usr/bin/env bash
set -eo pipefail

BACKUP_DIR="/var/backups/yeneschool"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_FILE="${BACKUP_DIR}/yeneschool_db_${TIMESTAMP}.sql.gz"
CONTAINER_NAME="sms_postgres"
DB_USER="yene_admin"
DB_NAME="yeneschool_production"

# Ensure backup directory exists
mkdir -p "${BACKUP_DIR}"

# Execute compressed pg_dump directly from container
docker exec -t "${CONTAINER_NAME}" pg_dump -U "${DB_USER}" -d "${DB_NAME}" --clean --if-exists | gzip > "${BACKUP_FILE}"

# Enforce 30-day retention policy (prune backups older than 30 days)
find "${BACKUP_DIR}" -name "yeneschool_db_*.sql.gz" -type f -mtime +30 -delete

echo "[$(date)] Backup completed successfully: ${BACKUP_FILE}"

To install the cron job:

# Open crontab editor
crontab -e

# Add daily 2:00 AM job
0 2 * * * /usr/local/bin/backup-yeneschool.sh >> /var/log/yeneschool-backup.log 2>&1

2. Backing Up Uploaded Student Files & Documents

Uploaded assets (student registration photos, National Exam result slips, teacher certifications) are stored in the persistent Docker volume mapped to /uploads:

# Archive the uploads volume directory
tar -czf "/var/backups/yeneschool/uploads_${TIMESTAMP}.tar.gz" -C /var/lib/docker/volumes/yeneschool_uploads_data/_data .

3. Disaster Recovery Restoration Procedure

To restore a database backup onto a fresh server or recover from accidental corruption:

Step 1: Ensure Containers are Running

docker compose up -d postgres

Step 2: Unpack and Stream Restore File

# Decompress and feed SQL dump directly into psql
gunzip < /var/backups/yeneschool/yeneschool_db_20261007_020000.sql.gz | docker exec -i sms_postgres psql -U yene_admin -d yeneschool_production

Step 3: Run Database Integrity Verifications

Verify that core table counts match expected figures:

docker exec -it sms_postgres psql -U yene_admin -d yeneschool_production -c "
  SELECT 
    (SELECT COUNT(*) FROM \"School\") AS total_schools,
    (SELECT COUNT(*) FROM \"Student\") AS total_students,
    (SELECT COUNT(*) FROM \"AttendanceRecord\") AS total_attendance_records;
"

Step 4: Restart Application Stack

docker compose restart backend frontend

All users can now log back into the restored system.