Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

The following contains best practices for backups to an on prem deployment.

Microsoft SQL Databases

These are typically hosted on a Linux container but may also be hosted on an existing SQL Server instance.

...

Expand
titleSuggested backup procedure

PostgreSQL Regular Backup Instructions Using pg_dump

1. Prerequisites

  • Install PostgreSQL Client Tools:

    Code Block
    languagebash
    # For Debian/Ubuntu
    sudo apt-get update
    sudo apt-get install postgresql-client
    
    # For CentOS/RHEL
    sudo yum install postgresql
  • Ensure Database Access:

    • The backup user must have read permissions on the target database.

2. Configure Password-less Authentication

  1. Create .pgpass File:

    Code Block
    languagebash
    touch ~/.pgpass
    chmod 600 ~/.pgpass
  2. Add Database Credentials:

    Code Block
    hostname:port:database:username:password

    Example:

    Code Block
    localhost:5432:your_database:your_username:your_password

3. Create Backup Script

Example Script (backup_postgres.sh):

Code Block
languagebash
#!/bin/bash

# Variables
DB_NAME="your_database"
DB_USER="your_username"
BACKUP_DIR="/path/to/backup"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql.gz"
RETENTION_DAYS=30
EMAIL="admin@example.com"

# Create Backup Directory
mkdir -p "$BACKUP_DIR"

# Perform Backup
pg_dump -U "$DB_USER" "$DB_NAME" | gzip > "$BACKUP_FILE"

# Check Backup Success
if [ $? -eq 0 ]; then
    echo "Backup successful: $BACKUP_FILE"
else
    echo "Backup failed for $DB_NAME" | mail -s "PostgreSQL Backup Failure" "$EMAIL"
    exit 1
fi

# Remove Old Backups
find "$BACKUP_DIR" -type f -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -exec rm {} \;

# Optional: Log the Backup
echo "$(date): Backup completed for $DB_NAME" >> "$BACKUP_DIR/backup.log"
  • Make Script Executable:

    Code Block
    languagebash
    chmod +x /path/to/backup_postgres.sh

4. Schedule Backups with Cron

  1. Edit Crontab:

    Code Block
    languagebash
    crontab -e
  2. Add Cron Job (Daily at 2 AM):

    Code Block
    0 2 * * * /path/to/backup_postgres.sh >> /path/to/backup.log 2>&1

5. Verify Backups

  • Check Backup Directory:

    Ensure new .sql.gz files are appearing as scheduled.

  • Test a Backup Restoration:

    Code Block
    languagebash
    createdb test_restore_db
    gunzip -c /path/to/backup/your_database_YYYYMMDD_HHMMSS.sql.gz | psql -U your_username -d test_restore_db
    • Verify Data:

      Code Block
      languagebash
      psql -U your_username -d test_restore_db -c "\\dt"
    • Remove Test Database:

      Code Block
      languagebash
      dropdb test_restore_db

6. Optional: Secure and Offsite Storage

  • Encrypt Backups (Using GPG):

    Code Block
    languagebash
    gpg --symmetric --cipher-algo AES256 "$BACKUP_FILE"
    rm "$BACKUP_FILE"
  • Sync to Remote Server:

    Add to the backup script:

    Code Block
    languagebash
    rsync -avz "$BACKUP_FILE.gpg" remote_user@remote_host:/remote/backup/dir/
    • Ensure SSH keys are set up for password-less access.

7. Monitoring and Alerts

  • Check Logs Regularly:

    Review /path/to/backup.log for backup statuses.

  • Set Up Email Alerts:

    Ensure the script sends emails on failure as shown in the backup script.


Restoration Steps

  1. Create a New Database:

    Code Block
    languagebash
    createdb restored_db
  2. Restore from Backup:

    Code Block
    languagebash
    gunzip -c /path/to/backup/your_database_YYYYMMDD_HHMMSS.sql.gz | psql -U your_username -d restored_db
    • If Encrypted:

      Code Block
      languagebash
      gpg --decrypt /path/to/backup/your_database_YYYYMMDD_HHMMSS.sql.gz.gpg | gunzip | psql -U your_username -d restored_db
  3. Verify Restoration:

    Code Block
    languagebash
    psql -U your_username -d restored_db -c "\\dt"
  4. Remove Test Database (if applicable):

    Code Block
    languagebash
    dropdb restored_db

Dashboard / Ingestor (Windows Server)

These servers don’t need regular backups as they don’t contain user information.

You may wish utilize server snapshots for disaster recovery.

Backing up the service directories before upgrades is recommended for easy rollbacks if required.

...

If modifications were made to the docker-compose.yml or docker-compose.override.yml files, these should be backed up too before upgrades.

The docker volumes contain database content and don’t need to be backed up if the databases have been backed up using the above methods. Other volumes contain models which will automatically be re-downloaded if required as well as application logs.