Promed MRS: Database Backup & Restore Documentation

Overview

This document provides comprehensive technical documentation for the Promed HIS database backup and restore system, including remote access procedures and security considerations.

Quick Commands

Manual Operations

# Manual backup (creates compressed .sql.gz file)
./backend/database/backup.sh

# Restore from backup
./backend/database/restore.sh <backup_file.sql.gz>

# Manual cleanup of old backups
./backend/database/cleanup.sh

Automated Setup

# Setup daily automated backups (runs at 2:00 AM, 7-day retention)
./backend/database/setup_daily_backup.sh

Backup Features

Compression

• Format: All backups are compressed using gzip (.sql.gz)
• Space Savings: ~80% compression ratio
• Original Size: ~10MB uncompressed
• Compressed Size: ~2-3MB compressed

Automation

• Schedule: Daily backups at 2:00 AM via cron
• Retention Policy: 7-day sliding window cleanup
• Logging: Comprehensive logging to backend/database/backup_cron.log
• Error Handling: Automatic error detection and logging

File Naming Convention

• Format: promedmrs_backup_YYYYMMDD_HHMMSS.sql.gz
• Example: promedmrs_backup_20251026_203311.sql.gz
• Timestamp: Date and time of backup creation

Remote Access via SFTP

FileZilla Connection Setup

Basic Configuration

1. Protocol: SFTP – SSH File Transfer Protocol
2. Host: Your server IP address
3. Port: 22 (standard SSH port)
4. Logon Type: Normal
5. Username: Your server username
6. Password: Your server password

Advanced Configuration

• Site Manager: Create saved connection profiles
• Default Remote Directory: /path/to/promedmrs/backend/database/backups/
• Bookmarks: Quick access to backup directory
• Transfer Mode: Binary (for compressed files)

Directory Structure

backend/database/
├── backup.sh                    # Manual backup script
├── restore.sh                   # Restore script
├── daily_backup.sh              # Automated daily backup
├── setup_daily_backup.sh        # Setup script
├── cleanup.sh                   # Cleanup script
├── backup_cron.log              # Backup logs
└── backups/                     # Backup files directory
    └── promedmrs_backup_*.sql.gz  # Backup files

Security Considerations

Authentication

• SSH Keys: Recommended for production environments
• Password Authentication: Acceptable for development/testing
• Two-Factor Authentication: Consider for enhanced security

Encryption

• SFTP: Provides encrypted file transfer
• SSH Tunnel: All data encrypted in transit
• No Additional Encryption: Files stored in compressed format

Access Control

• File Permissions: Ensure proper read/write permissions
• User Privileges: Limit access to authorized personnel
• Firewall Rules: No additional ports required (uses port 22)

Backup Process Details

Manual Backup

1. Execution: ./backend/database/backup.sh
2. Process:
   • Connects to MariaDB container
   • Creates mysqldump with full database
   • Compresses output using gzip
   • Saves as timestamped file
3. Output: Compressed backup file in database directory

Automated Daily Backup

1. Schedule: Cron job at 2:00 AM daily
2. Process:
   • Creates compressed backup
   • Logs backup status
   • Cleans up old backups (>7 days)
   • Reports disk usage
3. Monitoring: Check backup_cron.log for status

Restore Process

1. Prerequisites: Valid backup file (.sql.gz or .sql)
2. Process:
   • Stops promedmrs backend
   • Detects file type (compressed/uncompressed)
   • Decompresses if needed
   • Restores to database
   • Restarts promedmrs backend
3. Validation: Automatic error checking and reporting

Troubleshooting

Common Issues

• Permission Denied: Check file permissions and user access
• Connection Failed: Verify SSH/SFTP configuration
• Backup Failed: Check database container status
• Restore Failed: Verify backup file integrity

Log Analysis

• Location: backend/database/backup_cron.log
• Format: Timestamped entries with status messages
• Monitoring: Regular log review recommended
• Alerts: Set up monitoring for backup failures

Best Practices

Backup Management

• Regular Testing: Test restore procedures periodically
• Offsite Storage: Consider remote backup storage
• Retention Policy: Adjust retention based on requirements
• Monitoring: Implement backup success monitoring

Security

• Access Control: Limit backup access to authorized users
• Encryption: Use encrypted connections for transfers
• Audit Trail: Maintain logs of backup/restore operations
• Regular Updates: Keep backup scripts updated

Maintenance

Regular Tasks

• Log Rotation: Manage log file sizes
• Disk Space: Monitor backup directory usage
• Script Updates: Keep backup scripts current
• Testing: Regular restore testing

Monitoring

• Backup Success: Daily verification
• Disk Usage: Monitor storage consumption
• Error Rates: Track backup failure rates
• Performance: Monitor backup duration