Backup & Restore

Protect your DataMed data with comprehensive backup strategies and recovery procedures for PostgreSQL and SQLite.

Backup & Restore

Protecting patient data is critical. This guide covers backup strategies, automation, and recovery procedures for DataMed’s database.

Why Backups Matter

Data Loss Prevention

Hardware failures, software bugs, or human error can cause data loss. Backups provide recovery points.

Regulatory Compliance

Healthcare data regulations often require backup and retention policies.

Migration Safety

Backups let you test migrations and rollback if issues occur.

Disaster Recovery

Complete system failure requires recent backups to restore operations quickly.

PostgreSQL Backups (Production)

Manual Backup with pg_dump

Create a complete database backup:

pg_dump -h localhost -U datamed_user -d datamed > backup_$(date +%Y%m%d_%H%M%S).sql

Options:

• -h: Database host
• -U: Database user
• -d: Database name
• >: Redirect output to file

You’ll be prompted for the database password. Set PGPASSWORD environment variable to automate.

Backup with Compression

Save storage space with gzip:

pg_dump -h localhost -U datamed_user datamed | gzip > backup_$(date +%Y%m%d_%H%M%S).sql.gz

Custom Format Backup

Use PostgreSQL’s custom format for flexibility:

pg_dump -h localhost -U datamed_user -F c -d datamed > backup_$(date +%Y%m%d).dump

Benefits:

• Faster compression
• Selective table restore
• Parallel restore support

Backup Specific Tables

pg_dump -h localhost -U datamed_user -d datamed -t patients_patient -t patients_ingreso > patients_backup.sql

Restoring PostgreSQL Backups

Restore from SQL File

1. Stop Application

   Prevent writes during restore:

   # Render: Scale to 0 instances
   # Docker: docker-compose stop web
   # Systemd: sudo systemctl stop datamed
   
2. Drop Existing Database (Optional)

   If restoring completely:

   dropdb -h localhost -U datamed_user datamed
   createdb -h localhost -U datamed_user datamed
   
3. Restore Data

   psql -h localhost -U datamed_user -d datamed < backup_20260305.sql
   

   Or for compressed:

   gunzip -c backup_20260305.sql.gz | psql -h localhost -U datamed_user -d datamed
   
4. Verify Data

   Check row counts:

   SELECT COUNT(*) FROM patients_patient;
   SELECT COUNT(*) FROM patients_ingreso;
   
5. Restart Application

   Resume normal operations:

   sudo systemctl start datamed
   

Restore from Custom Format

pg_restore -h localhost -U datamed_user -d datamed -c backup_20260305.dump

Options:

• -c: Clean (drop) database objects before restoring
• -j 4: Use 4 parallel jobs for faster restore

SQLite Backups (Development)

Copy Database File

Simplest method for SQLite:

cp db.sqlite3 backups/db_$(date +%Y%m%d).sqlite3

SQLite Dump

Create SQL text dump:

sqlite3 db.sqlite3 .dump > backup_$(date +%Y%m%d).sql

Restore SQLite from SQL

sqlite3 db_new.sqlite3 < backup_20260305.sql
mv db.sqlite3 db.sqlite3.old
mv db_new.sqlite3 db.sqlite3

Automated Backup Scripts

Daily Backup Script

Create /usr/local/bin/backup_datamed.sh:

backup_datamed.sh

#!/bin/bash

# Configuration
DB_USER="datamed_user"
DB_NAME="datamed"
BACKUP_DIR="/var/backups/datamed"
RETENTION_DAYS=30

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Generate backup filename with timestamp
BACKUP_FILE="$BACKUP_DIR/datamed_$(date +%Y%m%d_%H%M%S).sql.gz"

# Create backup
pg_dump -U $DB_USER $DB_NAME | gzip > $BACKUP_FILE

# Check if backup was successful
if [ $? -eq 0 ]; then
    echo "Backup successful: $BACKUP_FILE"
    
    # Delete backups older than retention period
    find $BACKUP_DIR -name "datamed_*.sql.gz" -type f -mtime +$RETENTION_DAYS -delete
    
    echo "Old backups cleaned up (retention: $RETENTION_DAYS days)"
else
    echo "Backup failed!"
    exit 1
fi

Make executable:

chmod +x /usr/local/bin/backup_datamed.sh

Schedule with Cron

Run daily at 2:00 AM:

crontab -e

Add:

0 2 * * * /usr/local/bin/backup_datamed.sh >> /var/log/datamed_backup.log 2>&1

Render-Specific Backups

Render provides automatic PostgreSQL backups, but you should also maintain your own.

Manual Backup from Render

1. Get Database Connection String

   Render dashboard → Your database → Connection String (External)

2. Run pg_dump Locally

   pg_dump "postgresql://user:[e-mail]:5432/db" > backup.sql
   
3. Store Securely

   Save backup to secure location (encrypted storage, separate region)

Automated Backup from Render

Use GitHub Actions to schedule backups:

.github/workflows/backup.yml

name: Database Backup

on:
  schedule:
    - cron: '0 2 * * *'  # Daily at 2 AM UTC
  workflow_dispatch:  # Manual trigger

jobs:
  backup:
    runs-on: ubuntu-latest
    steps:
      - name: Backup Database
        run: |
          pg_dump "${{ secrets.DATABASE_URL }}" | gzip > backup_$(date +%Y%m%d).sql.gz
      
      - name: Upload to S3
        uses: aws-actions/configure-aws-credentials@v1
        with:
          aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }}
          aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          aws-region: us-east-1
      
      - run: |
          aws s3 cp backup_$(date +%Y%m%d).sql.gz s3://datamed-backups/

Backup Storage Options

Local Storage

Pros: Fast, simple
Cons: Vulnerable to hardware failure
Use for: Development, short-term backups

AWS S3

Pros: Durable, versioned, encrypted
Cons: Cost scales with storage
Use for: Production, long-term retention

Google Cloud Storage

Pros: Similar to S3, good performance
Cons: Requires GCP account
Use for: Production, GCP infrastructure

Backblaze B2

Pros: Lower cost than S3
Cons: Slower retrieval
Use for: Cost-effective long-term storage

Backup Verification

Always verify backups can be restored:

Test Restore Process

1. Create Test Database

   createdb -U datamed_user datamed_test
   
2. Restore Backup

   psql -U datamed_user -d datamed_test < backup_20260305.sql
   
3. Verify Data Integrity

   SELECT COUNT(*) FROM patients_patient;
   SELECT COUNT(*) FROM patients_ingreso;
   SELECT * FROM patients_patient LIMIT 5;
   
4. Drop Test Database

   dropdb -U datamed_user datamed_test
   

Monthly Verification Schedule

0 3 1 * * /usr/local/bin/verify_backup.sh >> /var/log/backup_verification.log 2>&1

Django Data Export (Backup Alternative)

Export data using Django’s dumpdata:

python manage.py dumpdata > datamed_data.json

Export Specific Apps

python manage.py dumpdata patients exams > clinical_data.json

Restore from Django JSON

python manage.py loaddata datamed_data.json

Not recommended for large databases - JSON exports are slow and large. Use pg_dump for production.

Disaster Recovery Plan

Recovery Time Objective (RTO)

Target: < 4 hours from disaster to full operation

Recovery Point Objective (RPO)

Target: < 24 hours of data loss (daily backups)

Recovery Procedure

1. Assess Damage

   Determine scope: database corruption, server failure, data deletion?

2. Provision New Infrastructure

   If hardware failed:

   • Deploy new Render service
   • Create new PostgreSQL database
   • Configure environment variables
3. Restore Latest Backup

   psql -U datamed_user -d datamed < latest_backup.sql
   
4. Apply Recent Migrations

   If backup is old:

   python manage.py migrate
   
5. Verify Application

   • Test login
   • View patient list
   • Create test exam record
   • Check dashboard analytics
6. Resume Operations

   Update DNS if necessary, notify users, monitor for issues

Backup Checklist

Daily Tasks

• Automated backup runs successfully
• Backup file size reasonable (not 0 bytes)
• Backup stored off-server

Weekly Tasks

• Review backup logs for errors
• Verify backup file integrity
• Check storage capacity

Monthly Tasks

• Test restore procedure
• Review retention policy
• Audit backup access controls
• Update disaster recovery documentation

Quarterly Tasks

• Full disaster recovery drill
• Review and update backup scripts
• Evaluate backup storage costs

Security Considerations

Encrypt backups - Patient data is sensitive. Use encryption at rest and in transit.

Encryption at Rest

# Encrypt backup with GPG
pg_dump -U datamed_user datamed | gzip | gpg --encrypt --recipient [e-mail] > backup.sql.gz.gpg

# Decrypt for restore
gpg --decrypt backup.sql.gz.gpg | gunzip | psql -U datamed_user -d datamed

Access Control

• Restrict backup access to authorized personnel only
• Use IAM roles for cloud storage (AWS, GCP)
• Enable audit logging on backup storage
• Rotate encryption keys regularly

Next Steps

Database Schema

Understand what data you’re backing up

Migrations

Backup before schema changes

Deployment

Integrate backups into deployment process

Configuration

Configure database connection for backups